insert, select --> deadlock (2 threads, 1 table)

[Mo Maison <mo...@yahoo.fr>]

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

Re: insert, select --> deadlock (2 threads, 1 table)

[Mo Maison <mo...@yahoo.fr>]

Le 26/06/2010 22:25, Bryan Pendleton a écrit :
>> Now, I have remarked that creating an index on column 'SOMEDATA'
>> is sufficient to remove the deadlock.
>
> I think an alternate approach would be to commit the insert, then
> perform the select in a separate transaction.

This is not an option of course : all queries must be performed
in the same transaction in case rollback would be needed at a
later time.

>
> And yet a third way, I believe, would be to set your transaction
> isolation to a lower level than SERIALIZABLE.

Deadlock appears at READ_COMMITED, REPEATABLE_READ
and SERIALIZABLE transaction levels.
However at a lower level (READ_UNCOMMITED), the problem
disappears.
Again, this is not an option for me.

>
> But creating an index on the other column seems best, if you are
> routinely using it in the WHERE clause.

OK, that solves my problem for now, but my test can easily be
generalized.
For example, each thread can do an INSERT, followed by a SELECT
without any criteria (say, to display the whole table data after a
new data has been inserted -- I checked that the deadlock still
appears in this case).

I am surprised that this basic use case results in a deadlock.
Should I fill a bug report ?

Thanks for your answers,

   M. Maison

Re: insert, select --> deadlock (2 threads, 1 table)

[Bryan Pendleton <bp...@gmail.com>]

> Now, I have remarked that creating an index on column 'SOMEDATA'
> is sufficient to remove the deadlock.

I think an alternate approach would be to commit the insert, then
perform the select in a separate transaction.

And yet a third way, I believe, would be to set your transaction
isolation to a lower level than SERIALIZABLE.

But creating an index on the other column seems best, if you are
routinely using it in the WHERE clause.

thanks,

bryan