> What you described as actual behaviour seems correct. TxnB was the first
> to post an update so it got the lock. When TxnA came to request an

update,

> it encountered the lock. Now it will have to wait until TxnB commits or
> rolls back, in order to get a chance at securing a lock. Until that
> happens (with NOWAIT), txnA will continue to encounter the deadlock or
> (with WAIT) will wait, with no lock conflict reported, until txnB releases
> the lock...not recommended.

In his situation using two snapshot transactions, it wouldn't wait for the
other transaction to either commit or rollback. It could only wait for the
other to rollback. If the other transaction commits then a different
snapshot transaction cannot touch that record anymore and so the snapshot
transaction would have to end and start anew after the other one commits to
have an opportunity to update that record.

As far as the NOWAIT goes. As long as it is possible to wait for a rollback
it would wait if you asked it to, but if the other transaction did commit,
it would then immediately toss up the deadlock exception rather than let the
operation take effect.

As far as I'm aware, the only way you can get a smooth operation to just
patiently rip through the data is to use committed isolation with the right
recversion setting coupled with lockwait so that it will consider deadlocks
when reading records, wait for resolution and then based upon the committed
isolation it will either begin operating on the newly committed record it
was waiting on or move forward with the original if rolled back. I also tend
to couple this with serverautocommit which means from a client, upon
initiation, I can start something that will for sure complete and commit on
the server.

This can often be a hang up for people so I like to clarify it.

Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com