A little more info:
I have a test program with a "writer thread", a thread that continuously
performs update transactions, and a "reader thread", a thread that does
selects on the data that is being updated. So with two threads, there
should be at most 2 transactions in progress at any instant. When the
deadlock occurs, the lock dump table shows otherwise. There are 3
distinct transaction ids (1,2,3 for simplicity). Looking at the locks,
I can tell that tx1 and tx3 are update transactions (they have exclusive
locks) and tx2 is the read tx (has shared locks). This doesn't make
sense because there is only a single thread doing updates so there
should only be a single update transaction active. Tx2 is waiting on a
lock held by tx1 and tx3 is waiting on a lock held by tx2. For some
reason the locks held by tx1 aren't released, and that is why the
deadlock occurs. Its almost as if the locks from the update transaction
aren't being fully released after a commit. Tx1 isn't waiting on
anything, but the locks are still there. So something screwy is going
on. Is there such thing as a phantom lock?
Also, I can't reproduce this on a MySQL database. I'm using the same
code, different JDBC driver.
-Randy
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
