#1349 HSQLDB hangs forever on a dead lock case

[Leonid Bushuev]

HSQL version 2.3.2.

Get a table with two rows and a primary key:

create table odd_table(column1 integer not null primary key, column2 integer);
insert into odd_table values (1,111),(2,222);
commit;

Then, in two connections:

query 1 in connection A: update odd_table set column2 = 555 where column1 = 1;
query 2 in connection B: update odd_table set column2 = 666 where column1 = 2;
query 3 in connection B: update odd_table set column2 = 777 where column1 = 1;
query 4 in connection A: update odd_table set column2 = 888 where column1 = 2;

I expect an exception "deadlock" on query 4.
However, HSQL hangs forever yet on query 2 :(

Is there a workaround? How can I detect a dead lock?

My application can work with several different RDBMS, and with other systems I can detect a deadlock, roll the current transaction back and re-try my queries a bit later.
With HSQL DB in this case my application hangs forever :(

Thanks in advance!

[Leonid Bushuev]

For the test:

query 1 in connection A: update odd_table set column2 = 555 where column1 = 1;
query 2 in connection B: update odd_table set column2 = 666 where column1 = 2;
query 3 in connection A: update odd_table set column2 = 777 where column1 = 2;
query 4 in connection B: update odd_table set column2 = 888 where column1 = 1;

Stack traces:

The first thread:

update odd_table set column2 = 666 where column1 = 2
waiting
java.lang.Thread.State: WAITING
blocks update odd_table set column2 = 888 where column1 = 1@2104
at sun.misc.Unsafe.park(Unsafe.java:-1)
at java.util.concurrent.locks.LockSupport.park(LockSupport.java:156)
at java.util.concurrent.locks.AbstractQueuedSynchronizer.parkAndCheckInterrupt(AbstractQueuedSynchronizer.java:811)
at java.util.concurrent.locks.AbstractQueuedSynchronizer.doAcquireSharedInterruptibly(AbstractQueuedSynchronizer.java:969)
at java.util.concurrent.locks.AbstractQueuedSynchronizer.acquireSharedInterruptibly(AbstractQueuedSynchronizer.java:1281)
at org.hsqldb.lib.CountUpDownLatch.await(CountUpDownLatch.java:166)
at org.hsqldb.Session.executeCompiledStatement(Session.java:1350)
at org.hsqldb.Session.execute(Session.java:991)

  - locked <0x847> (a org.hsqldb.Session)
  at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(JDBCPreparedStatement.java:4647)
  at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(JDBCPreparedStatement.java:310)
  - locked <0x848> (a org.hsqldb.jdbc.JDBCPreparedStatement)

The second thread:

update odd_table set column2 = 888 where column1 = 1
waiting for monitor entry
java.lang.Thread.State: BLOCKED
blocks TC: 01:35:38 SQL TRAN: rollback; main@1
waiting for TC: 01:35:28 SQL DML: update odd_table set column2 = 666 where column1 = 2; update odd_table set column2 = 666 where column1 = 2@2097 to release lock on <0x847> (a org.hsqldb.Session)
at org.hsqldb.Session.execute(Session.java:948)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(JDBCPreparedStatement.java:3885)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(JDBCConnection.java:1666)</init>

  - locked <0x846> (a org.hsqldb.jdbc.JDBCConnection)

[Leonid Bushuev]

When I perform the same test but with two different tables, I'm getting the "transaction rollback; serialization failure" exception. I threat it as a deadlock exception. Am I right?

[Fred Toussi]

You are apparently using HSQLDB in the default LOCKS mode. The engine locks the table for the first session and when you attempt to modify the table with the second session it waits until the first session commits. Note you are not yet getting to the "deadlock" situation.

Use the engine in MVCC mode and it will respond the way you expect. There is a setting for engine behaviour when a deadlock may occur. See the Guide on Sessions and Transactions.

[Leonid Bushuev]

Thank you, Fred,
in MVLocks mode it works as expected.

[Leonid Bushuev]

P.S. but I'd expect also that the message of the deadlock exception contain 'deadlock' word, in additional to "transaction rollback; serialization failure".

[Fred Toussi]

The message is a SQL message meaning deadlock was avoided.