Oystein Grovlen - Sun Norway wrote:
Randy Letness wrote:
I'm trying to debug a deadlock. There are two transactions: one
updates rows from table foo, and one selects the same rows from table
foo for reading. Is it possible to get in a deadlock in this
situation? I'm using the default READ_COMMITTED transaction level.
The transactions look like:
begin tx1 | begin tx2
select from foo | delete from foo
| insert into foo
| insert into foo
commit | commit
I would have thought that tx1 would hold only one lock at a time since
it is using READ_COMMITTED. Hence, I cannot see how tx1 should be
part of a deadlock.
Will there only be one transaction of each type at the same time? Two
instances of tx2 could possible deadlock if they access the same two
rows in different order (e.g, both inserts a row with the same primary
key as the one deleted by the other).
Yeah thats what I would think. The Derby docs say: Transactions using
the TRANSACTION_READ_COMMITTED isolation level release the lock when the
transaction steps through to the next row. Does this mean for reads
that the shared lock for the previous row is released before acquiring
the lock for the next row, or is the previous lock only released when
the next lock is acquired? There is only one transaction that is
updating, but there can be multiple transactions reading. I can
reproduce this easily by writing a test with a single thread that does
constant updates and multiple threads doing constant reads.
-Randy
