Subject Confusion regard transaction isolation levels
Author Wilson, Fred
Ok, we have some confusion regarding transaction isolation level modes

Using Snapshot or Repeatable Read, it's assumed that when txnA starts it
get's a "snapshot" of the current database and can not see changes made by
other transactions (also snapshot mode). For the most part this seems to
work as expected.
- TxnA starts a transaction
- TxnB starts a transaction
- TxnA reads from a table (Select * from ..... ). All is well
- TxnB reads from the same table (Select * from ... ). All is well
- TxnB deletes a row from the same table and commits
- TxnA reads from the table again (Select * from ...), and all is as
expected. TxnA still sees the "deleted" row.
- Ok commit everthing and do, basically the same, except instead of deleting
a row, TxnB adds a row. TxnA won't see it, as expected.
- Ok, lastly,
- 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"

Fred Wilson
SE, Bell & Howell