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
Sometimes this ends up in a deadlock and I'm trying to figure out why.
After reading up a little on how locking works in Derby is it possible
that the order of events is:
1. tx1 issues select, returns rows 1,2
2. tx2 obtains X lock on row 2
3. tx1 obtains S lock on row 1, reads row 1
4. tx2 tries to obtain X lock on row 1 (can't because tx1 has S lock)
5. tx1 tries to obtain S lock on row 2(can't because tx2 has X
lock)...deadlock
Is tx1 supposed to release the S lock on row 1 before trying to get a S
lock on row 2? I need to see what the lock table dump has, but I wanted
to try to understand the underlying locking algorithm in this case.
-Randy
