db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bogdan Calmac" <bc4...@gmail.com>
Subject lock escalation and deadlocks
Date Wed, 01 Aug 2007 03:13:52 GMT
First of all hello to all derby users,

Now, I have a pretty simple unit test that results in a deadlock:
 - one thread is inserting records in a table (in transactions of N=200 records)
 - another thread comes from behind and reads the same records (select
* where id > $lastReadID)

After examining the deadlock trace the explanation is could be this:
 - the insert thread has a bunch of X row locks and tries to escalate
to an X table lock
 - the select thread has a pending S row lock and tries to escalate to
an S table lock

It looks logical but to me it seems too limiting. What this means is
that you cannot insert and select from the same table concurrently.

It think the logic of escalation should avoid requesting a table lock
where there is another transaction that holds row locks and is waiting
for a table lock. At that point you know that escalation will result
for sure in a deadlock.

The problem is of course fixed if I force table level locking, but I
would have liked to be able to insert at the end of the table in the
same time somebody else select from the beginning.

What do you think?

I use derby, transaction isolation is READ_COMMITTED and the
table in question has primary key(id).

Here is the trace I was talking about:
*** The following row is the victim ***
226       |ROW          |S   |0        |(1,7)        |WAIT |T
*** The above row is the victim ***
183       |ROW          |X   |1        |(1,62)       |GRANT|T
183       |ROW          |X   |1        |(3,7)        |GRANT|T
183       |TABLE        |IX  |476      |Tablelock    |GRANT|T
226       |TABLE        |IS  |2        |Tablelock    |GRANT|T
183       |ROW          |X   |1        |(2,66)       |GRANT|T
183       |ROW          |X   |1        |(1,65)       |GRANT|T

View raw message