db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mo Maison <momai...@yahoo.fr>
Subject insert, select --> deadlock (2 threads, 1 table)
Date Sat, 26 Jun 2010 20:07:04 GMT

Hello,

As we have encountered deadlocks in our JEE application,
I have investigated the problem. It appears it is very
simple, one table and two threads (one connection per
thread) is enough to reproduce it deterministically.

CREATE TABLE TESTDATA (
   ID INT NOT NULL GENERATED ALWAYS AS IDENTITY
     CONSTRAINT ID_PK PRIMARY KEY,
   SOMEDATA VARCHAR(40)
)


Basically, each thread does the same thing :
- insert a row in table 'TESTDATA', with a unique value
   in 'SOMEDATA'
- perform a select to retrieve the inserted row,
   with a " where SOMEDATA='the_value_inserted_above' "
- commit() the transaction

With this TESTDATA table (and connection is not in autocommit
mode), this result in a deadlock.
Problem occurs in embedded or server mode, with the following
settings :
   conn.getTransactionIsolation() = 2 (READ_COMMITED)
   conn.getAutoCommit() = false

Error message looks like this :

Lock : ROW, TESTDATA, (1,8)
   Waiting XID : {166, S} , APP, select ID, SOMEDATA from TESTDATA
                                 WHERE SOMEDATA=?
   Granted XID : {167, X}
Lock : ROW, TESTDATA, (1,7)
   Waiting XID : {167, S} , APP, select ID, SOMEDATA from TESTDATA
                                 WHERE SOMEDATA=?
   Granted XID : {166, X}

Here is the output of "select * from SYSCS_DIAG.LOCK_TABLE"
when the deadlock occurs (XID and locknames are not the same
because it was another execution) :

XID|TYPE |MODE|TABLENAME|LOCKNAME |STATE|TABLETYPE| LOCK&|INDEXNAME

199|ROW  |X   |TESTDATA |(2,7)    |GRANT|T        |1     |NULL
200|ROW  |S   |TESTDATA |(2,7)    |WAIT |T        |0     |NULL
200|ROW  |X   |TESTDATA |(2,6)    |GRANT|T        |1     |NULL
199|ROW  |S   |TESTDATA |(2,6)    |WAIT |T        |0     |NULL
199|TABLE|IX  |TESTDATA |Tablelock|GRANT|T        |2     |NULL
200|TABLE|IX  |TESTDATA |Tablelock|GRANT|T        |2     |NULL
199|TABLE|IS  |TESTDATA |Tablelock|GRANT|T        |1     |NULL
200|TABLE|IS  |TESTDATA |Tablelock|GRANT|T        |1     |NULL

Now, I have remarked that creating an index on column 'SOMEDATA'
is sufficient to remove the deadlock.
In this case, the lock table looks like this before the commits :
(I added a sleep() before commit() to take this snapshot)

XID|TYPE |MODE|TABLENAME|LOCKNAME |STATE|TABLETYPE| LOCK&|INDEXNAME

168|ROW  |X   |TESTDATA |(1,7)    |GRANT|T        |1     |NULL
167|ROW  |X   |TESTDATA |(1,8)    |GRANT|T        |1     |NULL
167|TABLE|IX  |TESTDATA |Tablelock|GRANT|T        |3     |NULL
168|TABLE|IX  |TESTDATA |Tablelock|GRANT|T        |3     |NULL

It is of course a good idea to create an index (and it may not be
a so good idea to perform a SELECT just after the INSERT, but hey,
this is what hibernate does).

I just wanted to be sure that this behavior is known, and that
index creation will always be sufficient to avoid this deadlock.
What do you think derby developers ?

Thanks in advance for your answers,

   M. Maison



Mime
View raw message