Subject Re: [ib-support] Re: Confusion regard transaction isolation levels
Author Jason Chapman (JAC2)
OK from memory, and that is never good. Ihave just been through this with a
developer because he had a similar confusion.

""csswa"" <csswa@...> wrote in message
> --- In ib-support@y..., "Wilson, Fred" <fred.wilson@b...> wrote:
> > - TxnA starts a transaction
> > - TxnB starts a transaction
> > - TxnB updates a single row in the table but doesn't commit
> > - TxnA updates the same row in the same table and the excute fails
> with a
> > "deadlock". We would have expected the second person that calls
> commit to
> > fail, not to fail at execution time. It doesn't matter who start
> their
> > transaction first, the first one to actually "execute" the SQL
> statement
> > wins (even without committing).
> > What are we missing. This doesn't seem "correct"
You can define the "wait" state of a transaction, e.g. SNAPSHOT WAIT |
NO-WAIT (can't remember the syntax but the manual will).
Sounds like you are using no-wait (which is nearly always what is required).
In your example, no-wait barfs as soon as an update conflicts with another
update where the successful updates transaction was active at the point the
second transactions started (i.e. the update was not visible to the second

With Wait, the second TX would "hang" until the first one decided to commit
or rollback. On rollback it would allow the change and move on, on commit,
it would generate a deadlock. I never use this as 90% of TX's that start,
commit and the client with the "hung" TX gets bored!

IB will not let the record fork ever, i.e. it can not have a base version
with two uncommited modifications (the fork), waiting for one or other to
commit, thankfully. If it did it would make developing linear logic much
more complicated for little gain.

Now, have I answered your Q or just the one my developer was grilling me

BTW Andrew, using "limbo" can confuse as there is (as you know), a
transaction state of limbo.

Cheers all,


> No, that seems right to me, Fred. I don't have an in-depth
> understanding of transactions, but as soon as anyone starts to update
> a record (via uncommitted sql or an unposted db-linked control)
> nobody else should be able to edit it *until* the first person elects
> to commit or rollback. Possibly what you mean is the duration of the
> transactions. Person 1's transaction could be in limbo for minutes
> because they are working with a form that opens a transaction and
> waits for the user to hit a 'commit' button, whereas person 2 could
> have a form that only starts/executes/commits the update transaction
> when that user hits the 'commit' button. This is my understanding of
> the situation (corrections appreciated).
> Regards,
> Andrew Ferguson
> To unsubscribe from this group, send an email to:
> Your use of Yahoo! Groups is subject to