This is another instance of "ISAM think". In an ISAM table based system
you save time by looking, locking, and then updating in you
application.

In a DBMS system, you incur a great amount of cost working this way to
no effect. The update operation must always perform the search, lock,
and update because the DBMS is a separate application the only responds
to simple commands. Because it must always performs those steps on
update, you have no reason to perform them in advance.

Your optimum solution (the one that minimizes I/O and has optimal
reliability) is to try to post the update, and handle the planned
"failure", which to your specific application is actually the sign that
your design was successful, and go on.

Bear in mind that SQL is good at working with sets, but poor at picking
the N'th (or first) row from a set. If your foreign table is even
mid-sized (more than a few hundred thousand rows) then this approach
would be very slow. It would be much faster just to add the foreign
table rows as they are actually needed.


Since much of the .NET framework was actually architected by the same
gentleman who architected Delphi, error handling in the .NET framework
can be expected to have a lot in common with Delphi error handling.
Helen's Delphi experience

On Tue, 2004-11-23 at 22:26, Helen Borrie wrote:
>
> At 10:32 PM 23/11/2004 -0500, you wrote:
>
> >If it helps, basically what I need to do:
> >
> >1) Find the first record in the table with an FK that has null.
> >
> >2) Update that FK to point to a specified record in the foreign table.
> >
> >The conflict would occur when two transactions perform #1 before the other
> >hits #2 as they will retrieve the same record in step 1. I realize that
> >likely the only way to detect this is try and catch the exception. I was
> >just soliciting possible other/better ideas.
>
> No, the "idea" is that the first to post wins. From your other message, it
> seems that you think the locking doesn't occur at post time. You argued:
>
> ":: If that was your intention, then as soon as the first
> :: transaction assigns an item to Destination B, no other item
> :: can be assigned to that destination unless the first
> :: transaction changes its mind, rolls back the change and once
>
> If its committed. But the two transactions may try to run at the same time.
>
> Please accept that this continued disbelief on your part about the way
> concurrency works is causing you all kinds of confusion. Locks and lock
> conflicts kick in at Post. So, two transactions can be running at the same
> time, but they can't both successfully post a change to the same
> record. One secures the lock, the other excepts (or, if this other
> transaction was set to WAIT, it waits until the lock is released by a
> commit or rollback by the first transaction...)
>
> The way MGA works is that, when a transaction posts a change to a record,
> the server writes a new version of the record to disk. That is, the new
> record version now exists on disk, but only the transaction that wrote it
> knows about it. The latest committed version (the one that everyone else
> can see) remains unchanged as long as that new record version remains
> uncommitted.
>
> As soon as a new *version* of a record exists, no other transaction is
> allowed to post *another* new record version - even though the original
> transaction's new record version is not yet committed. To get your head
> around multi-user concurrency it's crucial to understand this. That's how
> it works: a transaction posts a change: if it's allowed, then win; if
> it's not allowed, then lose. Lose --> intercept the exception and do what
> you gotta do.
>
>
> >The other problem is that I just simulated a similar situation, and at least
> >in the .net provider it comes back as a generic FB exeption. I have to see
> >if there is some way to detect this other than just looking at the exception
> >text.
>
> Definitely don't rely on the exception text to ascertain the
> exception. Every DB exception has a distinct 9-digit gdscode. The
> gdscodes are (kinda loosely) grouped beneath the SQLCodes. I guess the
> SQLCode is the "generic" exception you're referring to. However, the
> gdscodes (a.k.a ISC error codes) are what your application should be
> looking for. There may be several but the one you are interested in the
> last one, which is the first in the array.
>
> iberror.h publishes all of the gdscodes, together with their symbolic
> names. One of the things that API conversions do (must do) is publish
> their own language-specific symbols representing each code. The various
> Delphi interfaces do this by simply mimicking the symbol names that are
> published in iberror.h. It would be astonishing if the .NET driver doesn't
> do something very, very similar. Since the .net driver is open source, you
> shouldn't have to exert much perspiration to find the module that contains
> these symbols.
>
> If you want a complete listing of the gdscodes with their symbols, English
> text messages and SQLCodes, you can download it from the Contributed Code
> area of the www.ibobjects.com website.
>
> The obvious thing for a .net programmer to do is to find out how the .NET
> driver delivers the various pieces of the error status vector to your
> application interface. This is a totally formalised structure whose fields
> are available. If the .net provider is delivering the text messages then,
> count on it, the gdscodes are available too.
>
> ./heLen
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>