Hi Viktor, I had similar problems when creating indexes on very large tables in parallel (using last stable build 1.3.176). I debugged this and found out that H2 synchronizes the index creations so that indexes are created sequentially (Maybe this applies to other DML/DDL like yours too). If one index creation thread waits longer than the specified LOCK_TIMEOUT, then Timeout trying to lock table "SYS" is thrown.
My current workaround is to set the LOCK_TIMEOUT to Integer.MAX_VALUE. Kind regards Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail: mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com From: Viktor Voytovych <vvoytov...@llnw.com> To: h2-database@googlegroups.com Date: 25.09.2014 09:26 Subject: Re: [h2] Timeout trying to lock table "SYS" during table creation Sent by: h2-database@googlegroups.com I've started getting this: Caused by: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table "SYS"; SQL statement: INSERT INTO TABLE_83e6fcbd876844f39b8a4d5dd53fe021 (time,bytes) VALUES (?,?) [50200-181] at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) at org.h2.message.DbException.get(DbException.java:179) at org.h2.message.DbException.get(DbException.java:155) at org.h2.mvstore.db.MVTable.doLock1(MVTable.java:168) at org.h2.mvstore.db.MVTable.lock(MVTable.java:130) at org.h2.engine.Database.lockMeta(Database.java:884) at org.h2.engine.Database.updateMeta(Database.java:1572) at org.h2.command.ddl.Analyze.analyzeTable(Analyze.java:125) at org.h2.mvstore.db.MVTable.analyzeIfRequired(MVTable.java:654) at org.h2.mvstore.db.MVTable.addRow(MVTable.java:641) at org.h2.command.dml.Insert.insertRows(Insert.java:156) at org.h2.command.dml.Insert.update(Insert.java:114) at org.h2.command.CommandContainer.update(CommandContainer.java:78) at org.h2.command.Command.executeUpdate(Command.java:254) at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:157) at org.h2.jdbc.JdbcPreparedStatement.executeBatch(JdbcPreparedStatement.java:1183) at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297) at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297) at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:899) at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:884) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587) ... 52 more and this: Caused by: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table "SYS"; SQL statement: CREATE MEMORY TABLE IF NOT EXISTS TABLE_e3af436af66140ff958c008e578c9e33(account_id BIGINT, session_id VARCHAR) NOT PERSISTENT [50200-181] at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) at org.h2.message.DbException.get(DbException.java:179) at org.h2.message.DbException.get(DbException.java:155) at org.h2.mvstore.db.MVTable.doLock1(MVTable.java:168) at org.h2.mvstore.db.MVTable.lock(MVTable.java:130) at org.h2.engine.Database.lockMeta(Database.java:884) at org.h2.command.ddl.CreateTable.update(CreateTable.java:135) at org.h2.command.CommandContainer.update(CommandContainer.java:78) at org.h2.command.Command.executeUpdate(Command.java:254) at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:185) at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:159) at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:421) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396) ... 36 more On Tuesday, September 23, 2014 7:48:11 PM UTC+3, Viktor Voytovych wrote: Thanks. Testing it. On Tuesday, September 23, 2014 11:04:53 AM UTC+3, Noel Grandin wrote: On 2014-09-23 09:57 AM, Viktor Voytovych wrote: > Thanks. > Would you recommend Version 1.3.176 (2014-04-05) that is Last Stable or Version 1.4.181 (2014-08-06) that is Beta? > Since you are running a pure in-memory database, I would recommend 1.4.181 The information in this message may be confidential. It is intended solely for the addressee(s). If you are not the intended recipient, any disclosure, copying or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. The information in this message may be confidential. It is intended solely for the addressee(s). If you are not the intended recipient, any disclosure, copying or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. ================================================================================================================ Disclaimer The information contained in this e - mail and any attachments ( together the "message") is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl ================================================================================================================ -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.