After an exception of kind "Timeout trying to lock table" (unknown cause), you need to restart H2 to access to the record.

[IntensiveH2]

In some case (not systematically reproducible and I don't understand
why), I have an exception of kind "Timeout trying to lock table".
After, this exception, I rollback the session (check under debugger)
but this record is locked definitely.
If I restart H2, I can access to this record again.
Little analysis under debugger:
After exception, we execute the "rollbackTo(int index, boolean
trimToSize) " method but the test "undoLog.size() > index" is false
and we never revert the row (row.setDeleted(false);)

Configuration:
H2 version=Version 1.2.143 (2010-09-18)
url="jdbc:h2:file:\dist\h2\cockpit
\cockpit;MVCC=TRUE;AUTOCOMMIT=OFF;LOCK_MODE=3;LOG=2;database_event_listener='com.XXX.mserver.H2Listener'"
Additional settings:
set CACHE_SIZE 131072
set WRITE_DELAY 0
SET MAX_MEMORY_ROWS 100000
SET MAX_LOG_SIZE 32
SET DEFAULT_LOCK_TIMEOUT 3000
System.setProperty("h2.serverResultSetFetchSize", "1000");
System.setProperty("h2.serverCachedObjects", "256");


Extract of my log file below:
INFO | jvm 1 | 2010/10/11 09:07:19 |
org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ; SQL
statement:
INFO | jvm 1 | 2010/10/11 09:07:19 | UPDATE MCTASKPARAMETERS SET
idmctask=?,creationdate=?,updatedate=?,idmccommandparameter=?,value=?,isselected=?
WHERE id = ? [50200-134]
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.h2.message.DbException.getJdbcSQLException(DbException.java:316)
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.h2.message.DbException.get(DbException.java:156)
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.h2.command.Command.executeUpdate(Command.java:203)
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:
141)
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:
127)
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.continuent.sequoia.controller.loadbalancer.AbstractLoadBalancer.executeStatementExecuteUpdateOnBackend(AbstractLoadBalancer.java:
988)
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.continuent.sequoia.controller.loadbalancer.singledb.SingleDB.statementExecuteUpdate(SingleDB.java:
340)
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.continuent.sequoia.controller.requestmanager.RequestManager.loadBalanceStatementExecuteUpdate(RequestManager.java:
952)
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.continuent.sequoia.controller.requestmanager.RequestManager.statementExecuteUpdate(RequestManager.java:
694)
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.continuent.sequoia.controller.virtualdatabase.VirtualDatabase.statementExecuteUpdate(VirtualDatabase.java:
661)
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.continuent.sequoia.controller.virtualdatabase.VirtualDatabaseWorkerThread.statementExecuteUpdate(VirtualDatabaseWorkerThread.java:
2306)
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.continuent.sequoia.controller.virtualdatabase.VirtualDatabaseWorkerThread.run(VirtualDatabaseWorkerThread.java:
454)
INFO | jvm 1 | 2010/10/11 09:07:19 | Caused by:
org.h2.jdbc.JdbcSQLException: Concurrent update in table
"MCTASKPARAMETERS": another transaction has updated or deleted the
same row [90131-134]
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.h2.message.DbException.getJdbcSQLException(DbException.java:316)
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.h2.message.DbException.get(DbException.java:167)
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.h2.message.DbException.get(DbException.java:144)
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.h2.table.RegularTable.removeRow(RegularTable.java:316)
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.h2.table.Table.updateRows(Table.java:400)
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.h2.command.dml.Update.update(Update.java:124)
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.h2.command.CommandContainer.update(CommandContainer.java:70)
INFO | jvm 1 | 2010/10/11 09:07:19 | at
org.h2.command.Command.executeUpdate(Command.java:198)
INFO | jvm 1 | 2010/10/11 09:07:19 | ... 9 more

[Thomas Mueller]

Hi,

According to the error message, you are using H2 version 1.2.134
(error code 90131-134), but I don't think this is the problem. I think
it's a deadlock. The easiest way to reproduce the problem is using two
connections that try to update the same rows in a different order:

Class.forName("org.h2.Driver");
String dir = "~/data/h2database/h2/data";
DeleteDbFiles.execute(dir, null, true);
String url = "jdbc:h2:" + dir + "/test;MVCC=TRUE";
Connection conn1 = DriverManager.getConnection(url);
Statement stat1 = conn1.createStatement();
stat1.execute("create table test(id int primary key, data varchar)");
stat1.execute("insert into test values(1, 'A'), (2, 'B')");
Connection conn2 = DriverManager.getConnection(url);
Statement stat2 = conn2.createStatement();
conn1.setAutoCommit(false);
conn2.setAutoCommit(false);
stat1.execute("update test set data='a' where id=1");
stat2.execute("update test set data='b' where id=2");
stat1.execute("update test set data='b' where id=2");
stat2.execute("update test set data='a' where id=1");
conn1.close();
conn2.close();

or

Class.forName("org.h2.Driver");
String dir = "~/data/h2database/h2/data";
DeleteDbFiles.execute(dir, null, true);
String url = "jdbc:h2:" + dir + "/test;MVCC=TRUE";
Connection conn1 = DriverManager.getConnection(url);
final Statement stat1 = conn1.createStatement();
stat1.execute("create table test(id int primary key, data varchar)");
stat1.execute("insert into test values(1, 'A'), (2, 'B')");
Connection conn2 = DriverManager.getConnection(url);
Statement stat2 = conn2.createStatement();
conn1.setAutoCommit(false);
conn2.setAutoCommit(false);
stat1.execute("update test set data='a' where id=1");
stat2.execute("update test set data='b' where id=2");
new Thread() {
public void run() {
try {
stat1.execute("update test set data='b' where id=2");
} catch (SQLException e) {
e.printStackTrace();
}
}
}.start();
stat2.execute("update test set data='a' where id=1");
conn1.close();
conn2.close();

Most likely this is what is happening here. It is also known as the
dining philosophers problem:
http://en.wikipedia.org/wiki/Dining_philosophers_problem

One solution is to automatically re-try the transaction, another
solution is to always lock the rows in the same order.

When using MVCC, H2 doesn't give a detailed error message about the
cause of the deadlock. When not using MVCC, the error message will be
more detailed, but only when using multiple tables:

Class.forName("org.h2.Driver");
String dir = "~/data/h2database/h2/data";
DeleteDbFiles.execute(dir, null, true);
String url = "jdbc:h2:" + dir + "/test";
Connection conn1 = DriverManager.getConnection(url);
final Statement stat1 = conn1.createStatement();
stat1.execute("create table a(id int)");
stat1.execute("create table b(id int)");
Connection conn2 = DriverManager.getConnection(url);
Statement stat2 = conn2.createStatement();
conn1.setAutoCommit(false);
conn2.setAutoCommit(false);
stat1.execute("insert into a values(1)");
stat2.execute("insert into b values(1)");
new Thread() {
public void run() {
try {
stat1.execute("insert into b values(2)");
} catch (SQLException e) {
e.printStackTrace();
}
}
}.start();
stat2.execute("insert into a values(2)");
conn1.close();
conn2.close();

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Deadlock detected.
The current transaction was rolled back. Details: "
Session #2 (user: ) is waiting to lock PUBLIC.B while locking PUBLIC.A
(exclusive).
Session #3 (user: ) is waiting to lock PUBLIC.A while locking PUBLIC.B
(exclusive).";
SQL statement: insert into a values(2) [40001-144]

Regards,
Thomas

[Gili]

Thomas,

I am seeing a similar problem in version 1.4.182 where the database seems to go into a bad state, leaving a particular table locked forever.

Is it possible for me to somehow list all database connections and find out which locks each one is holding? I don't have a minimal testcase, but I can reproduce this issue 100% so if you tell me how to look up the locks I can debug this further on my end.

Thanks,

Gili

[Thomas Mueller]

Hi,

There is a system table (information_schema.locks) that contains the lock info.

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.