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