Christoff, thank you. For me ";MULTI_THREADED=0" works the best. Other options show worse performance. Also I can confirm that MULTI_THREADED=1 has issues and should not be used in production (according to the documentation http://www.h2database.com/html/grammar.html?#set_multi_threaded - "Currently, enabling this is experimental only.")
On Thursday, September 25, 2014 10:43:46 AM UTC+3, schmitzc wrote: > > 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:[email protected] <javascript:> > www: http://www.finaris.de und http://www.rapidrep.com > > > > From: Viktor Voytovych <[email protected] <javascript:>> > To: [email protected] <javascript:> > Date: 25.09.2014 09:26 > Subject: Re: [h2] Timeout trying to lock table "SYS" during table > creation > Sent by: [email protected] <javascript:> > ------------------------------ > > > > 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 *[email protected]* <javascript:>. > To post to this group, send email to *[email protected]* > <javascript:>. > Visit this group at *http://groups.google.com/group/h2-database* > <http://groups.google.com/group/h2-database>. > For more options, visit *https://groups.google.com/d/optout* > <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 > > ================================================================================================================ > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
