Hi H2 Group, I'm looking for any assistance with understanding a possible regression in the behaviour of the IDENTITY() function. I've searched around and found another recent comment on this, which may be related...
https://groups.google.com/forum/?fromgroups=#!topic/h2-database/v3b_GQYDJAE The background is that we have a rather old production system that is using H2 1.2.133. Over the last year we have considered switching the 1.3.xxx release but have been wary due to seeing an important transactional batch update misbehave when using the 1.3.xxx series of H2. We'd prefer to switch to the newer release series what with all the great work and maintenance updates that it receives etc. Therefore we finally got round to giving this some serious investigation and have created a reproducible test case. Here's a basic test database we create (this is reproduces in a simple way the same core structure as our current production 1.2.133 DB). The important table is PACKAGE_TRANSACTIONS. IDTEST is just a table to record the results of the tests.. Database Initialisation ======================= DROP TABLE PACKAGE_TRANSACTIONS IF EXISTS; CREATE TABLE IF NOT EXISTS PACKAGE_TRANSACTIONS ( ID INTEGER AUTO_INCREMENT PRIMARY KEY, EVENTTIME TIMESTAMP ); DROP TABLE IDTEST IF EXISTS; CREATE TABLE IF NOT EXISTS IDTEST ( ID INTEGER, S1 INTEGER ); Batch Update ============ The following batch is invoked withe executeBatch() and replicates in a very simple way a subset of our production transaction batch update. Notice that we are assigning the value of the IDENTITY() function to the variable @TRANSACTIONID (in retrospect we should have used an explicitly named sequence - but we went with the convenience of the AUTO_INCREMENT and would like to stick with this if possible to avoid updating the production schema). SET TRACE_LEVEL_SYSTEM_OUT 3; INSERT INTO IDTEST VALUES ( 0, SESSION_ID() ) ; --Store our SESSION_ID() INSERT INTO PACKAGE_TRANSACTIONS VALUES ( NULL, NOW() ) ; SET @TRANSACTIONID=SELECT IDENTITY(); INSERT INTO IDTEST VALUES ( @TRANSACTIONID, SESSION_ID() ); --Store @TRANSACTIONID and record SESSION_ID() again SET TRACE_LEVEL_SYSTEM_OUT 1; Query IDTEST Table ================== After running the test batch operations in a new operation we query the IDTEST table. We expect to see two rows. The second row should show the @TRANSACTIONID and SESSION_ID() values... SELECT * FROM IDTEST The expected result is as follows: Expect ====== resultset : row : ID : 0 S1 : 2 row : null ID : 1 //CORRECT: @TRANSACTIONID == IDENTITY() == 1 S1 : 2 Notice that S1 is the same value and shows that the batch operation was all performed in the same session. RESULTS ======= Here are the results (with detailed stdout trace logging). First our production H2 1.2.133 result which is working as we expect and as it always has done... H2 1.2.133 - CORRECT - expected behaviour ========================================= 09-25 11:17:20 jdbc[2]: /*SQL */SET TRACE_LEVEL_SYSTEM_OUT 3; 09-25 11:17:20 lock: 1 exclusive write lock requesting for SYS 09-25 11:17:20 lock: 1 exclusive write lock added for SYS 09-25 11:17:20 index: SYS_DATA remove ( /* key:128200 */ 20, 0, 3, 'CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_512CDA63_EB48_4CDE_BF43_64AB955A9A59 START WITH 1 BELONGS_TO_TABLE') 09-25 11:17:20 pageStore: log - s:1 table:0 row:( /* key:128200 */ 20, 0, 3, 'CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_512CDA63_EB48_4CDE_BF43_64AB955A9A59 START WITH 1 BELONGS_TO_TABLE') 09-25 11:17:20 index: SYS_DATA add ( /* key:128210 */ 20, 0, 3, 'CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_512CDA63_EB48_4CDE_BF43_64AB955A9A59 START WITH 33 BELONGS_TO_TABLE') 09-25 11:17:20 pageStore: log + s:1 table:0 row:( /* key:128210 */ 20, 0, 3, 'CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_512CDA63_EB48_4CDE_BF43_64AB955A9A59 START WITH 33 BELONGS_TO_TABLE') 09-25 11:17:20 pageStore: log commit s:1 09-25 11:17:20 lock: 1 exclusive write lock unlock SYS 09-25 11:17:20 lock: 2 exclusive write lock requesting for PACKAGE_TRANSACTIONS 09-25 11:17:20 lock: 2 exclusive write lock added for PACKAGE_TRANSACTIONS 09-25 11:17:20 index: PACKAGE_TRANSACTIONS_DATA add ( /* key:1 */ 1, TIMESTAMP '2012-09-25 11:17:20.741') 09-25 11:17:20 pageStore: log + s:2 table:32 row:( /* key:1 */ 1, TIMESTAMP '2012-09-25 11:17:20.741') 09-25 11:17:20 jdbc[2]: /*SQL #:1 t:2*/INSERT INTO PACKAGE_TRANSACTIONS VALUES ( NULL, NOW() ); 09-25 11:17:20 pageStore: log commit s:2 09-25 11:17:20 lock: 2 exclusive write lock unlock PACKAGE_TRANSACTIONS 09-25 11:17:20 jdbc[2]: /*SQL t:1*/SET @TRANSACTIONID=SELECT SCOPE_IDENTITY(); 09-25 11:17:20 lock: 2 exclusive write lock requesting for IDTEST 09-25 11:17:20 lock: 2 exclusive write lock added for IDTEST 09-25 11:17:20 index: IDTEST_DATA add ( /* key:1 */ 1) 09-25 11:17:20 pageStore: log + s:2 table:35 row:( /* key:1 */ 1) 09-25 11:17:20 jdbc[2]: /*SQL #:1 t:1*/INSERT INTO IDTEST VALUES ( @TRANSACTIONID ); 09-25 11:17:20 pageStore: log commit s:2 09-25 11:17:20 lock: 2 exclusive write lock unlock IDTEST RESULT OF SELECT * FROM IDTEST resultset : row : ID : 0 S1 : 2 row : null ID : 1 //CORRECT: @TRANSACTIONID == IDENTITY() == 1 S1 : 2 PROBLEMS ======== Here we test the same batch but using the latest H2 1.3.169. Notice that we try both IDENTITY() and SCOPE_IDENTITY() but both return NULL... H2 1.3.169 INCORRECT - SCOPE_IDENTITY() returns NULL ===================================================== 09-25 12:10:25 lock: 2 exclusive write lock requesting for IDTEST 09-25 12:10:25 lock: 2 exclusive write lock added for IDTEST 09-25 12:10:25 index: IDTEST_DATA add ( /* key:1 */ 0, 2) 09-25 12:10:25 pageStore: log + s: 2 table: 35 row: ( /* key:1 */ 0, 2) 09-25 12:10:25 jdbc[2]: /*SQL #:1 t:1*/INSERT INTO IDTEST VALUES ( 0, SESSION_ID() ); 09-25 12:10:25 pageStore: log commit s: 2 09-25 12:10:25 lock: 2 exclusive write lock unlock IDTEST 09-25 12:10:25 lock: 1 exclusive write lock requesting for SYS 09-25 12:10:25 lock: 1 exclusive write lock added for SYS 09-25 12:10:25 index: SYS_DATA remove ( /* key:128403 */ 32, 0, 3, 'CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_B5D9EF2F_E4C9_4AF4_9788_26C81B678759 START WITH 1 BELONGS_TO_TABLE') 09-25 12:10:25 pageStore: log - s: 1 table: 0 row: ( /* key:128403 */ 32, 0, 3, 'CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_B5D9EF2F_E4C9_4AF4_9788_26C81B678759 START WITH 1 BELONGS_TO_TABLE') 09-25 12:10:25 index: SYS_DATA add ( /* key:128408 */ 32, 0, 3, 'CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_B5D9EF2F_E4C9_4AF4_9788_26C81B678759 START WITH 33 BELONGS_TO_TABLE') 09-25 12:10:25 pageStore: log + s: 1 table: 0 row: ( /* key:128408 */ 32, 0, 3, 'CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_B5D9EF2F_E4C9_4AF4_9788_26C81B678759 START WITH 33 BELONGS_TO_TABLE') 09-25 12:10:25 pageStore: log commit s: 1 09-25 12:10:25 lock: 1 exclusive write lock unlock SYS 09-25 12:10:25 lock: 2 exclusive write lock requesting for PACKAGE_TRANSACTIONS 09-25 12:10:25 lock: 2 exclusive write lock added for PACKAGE_TRANSACTIONS 09-25 12:10:25 index: PACKAGE_TRANSACTIONS_DATA add ( /* key:1 */ 1, TIMESTAMP '2012-09-25 12:10:25.649') 09-25 12:10:25 pageStore: log + s: 2 table: 20 row: ( /* key:1 */ 1, TIMESTAMP '2012-09-25 12:10:25.649') 09-25 12:10:25 jdbc[2]: /*SQL #:1 t:2*/INSERT INTO PACKAGE_TRANSACTIONS VALUES ( NULL, NOW() ); 09-25 12:10:25 pageStore: log commit s: 2 09-25 12:10:25 lock: 2 exclusive write lock unlock PACKAGE_TRANSACTIONS 09-25 12:10:25 jdbc[2]: /*SQL */SET @TRANSACTIONID=SELECT SCOPE_IDENTITY(); 09-25 12:10:25 lock: 2 exclusive write lock requesting for IDTEST 09-25 12:10:25 lock: 2 exclusive write lock added for IDTEST 09-25 12:10:25 index: IDTEST_DATA add ( /* key:2 */ NULL, 2) 09-25 12:10:25 pageStore: log + s: 2 table: 35 row: ( /* key:2 */ NULL, 2) 09-25 12:10:25 jdbc[2]: /*SQL #:1 t:1*/INSERT INTO IDTEST VALUES ( @TRANSACTIONID, SESSION_ID() ); 09-25 12:10:25 pageStore: log commit s: 2 09-25 12:10:25 lock: 2 exclusive write lock unlock IDTEST RESULT OF SELECT * FROM IDTEST resultset : row : null ID : 0 S1 : 2 row : null ID : null //This is wrong should be 1 S1 : 2 H2 1.3.169 INCORRECT - IDENTITY() returns NULL =============================================== 09-25 12:09:24 lock: 2 exclusive write lock requesting for IDTEST 09-25 12:09:24 lock: 2 exclusive write lock added for IDTEST 09-25 12:09:24 index: IDTEST_DATA add ( /* key:1 */ 0, 2) 09-25 12:09:24 pageStore: log + s: 2 table: 35 row: ( /* key:1 */ 0, 2) 09-25 12:09:24 jdbc[2]: /*SQL #:1*/INSERT INTO IDTEST VALUES ( 0, SESSION_ID() ); 09-25 12:09:24 pageStore: log commit s: 2 09-25 12:09:24 lock: 2 exclusive write lock unlock IDTEST 09-25 12:09:24 lock: 1 exclusive write lock requesting for SYS 09-25 12:09:24 lock: 1 exclusive write lock added for SYS 09-25 12:09:24 index: SYS_DATA remove ( /* key:128385 */ 32, 0, 3, 'CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_EBB2FE86_204C_47D0_A1BE_B0B2BE256888 START WITH 1 BELONGS_TO_TABLE') 09-25 12:09:24 pageStore: log - s: 1 table: 0 row: ( /* key:128385 */ 32, 0, 3, 'CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_EBB2FE86_204C_47D0_A1BE_B0B2BE256888 START WITH 1 BELONGS_TO_TABLE') 09-25 12:09:24 index: SYS_DATA add ( /* key:128390 */ 32, 0, 3, 'CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_EBB2FE86_204C_47D0_A1BE_B0B2BE256888 START WITH 33 BELONGS_TO_TABLE') 09-25 12:09:24 pageStore: log + s: 1 table: 0 row: ( /* key:128390 */ 32, 0, 3, 'CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_EBB2FE86_204C_47D0_A1BE_B0B2BE256888 START WITH 33 BELONGS_TO_TABLE') 09-25 12:09:24 pageStore: log commit s: 1 09-25 12:09:24 lock: 1 exclusive write lock unlock SYS 09-25 12:09:24 lock: 2 exclusive write lock requesting for PACKAGE_TRANSACTIONS 09-25 12:09:24 lock: 2 exclusive write lock added for PACKAGE_TRANSACTIONS 09-25 12:09:24 index: PACKAGE_TRANSACTIONS_DATA add ( /* key:1 */ 1, TIMESTAMP '2012-09-25 12:09:24.065') 09-25 12:09:24 pageStore: log + s: 2 table: 20 row: ( /* key:1 */ 1, TIMESTAMP '2012-09-25 12:09:24.065') 09-25 12:09:24 jdbc[2]: /*SQL #:1 t:1*/INSERT INTO PACKAGE_TRANSACTIONS VALUES ( NULL, NOW() ); 09-25 12:09:24 pageStore: log commit s: 2 09-25 12:09:24 lock: 2 exclusive write lock unlock PACKAGE_TRANSACTIONS 09-25 12:09:24 jdbc[2]: /*SQL */SET @TRANSACTIONID=SELECT IDENTITY(); 09-25 12:09:24 lock: 2 exclusive write lock requesting for IDTEST 09-25 12:09:24 lock: 2 exclusive write lock added for IDTEST 09-25 12:09:24 index: IDTEST_DATA add ( /* key:2 */ NULL, 2) 09-25 12:09:24 pageStore: log + s: 2 table: 35 row: ( /* key:2 */ NULL, 2) 09-25 12:09:24 jdbc[2]: /*SQL #:1*/INSERT INTO IDTEST VALUES ( @TRANSACTIONID, SESSION_ID() ); 09-25 12:09:24 pageStore: log commit s: 2 09-25 12:09:24 lock: 2 exclusive write lock unlock IDTEST RESULT OF SELECT * FROM IDTEST resultset : row : null ID : 0 S1 : 2 row : null ID : null //This is wrong should be 1 S1 : 2 TEST IF AUTO_INCREMENT is differs from IDENTITY column type =========================================================== Finally we tried changing the DB schema to use IDENTITY as the column type to check that this was not a change in behaviour of AUTO_INCREMENT... CREATE TABLE IF NOT EXISTS PACKAGE_TRANSACTIONS ( ID INTEGER IDENTITY, EVENTTIME TIMESTAMP ); However it produces the same result... 09-25 12:11:05 lock: 2 exclusive write lock requesting for IDTEST 09-25 12:11:05 lock: 2 exclusive write lock added for IDTEST 09-25 12:11:05 index: IDTEST_DATA add ( /* key:1 */ 0, 2) 09-25 12:11:05 pageStore: log + s: 2 table: 35 row: ( /* key:1 */ 0, 2) 09-25 12:11:05 jdbc[2]: /*SQL #:1 t:1*/INSERT INTO IDTEST VALUES ( 0, SESSION_ID() ); 09-25 12:11:05 pageStore: log commit s: 2 09-25 12:11:05 lock: 2 exclusive write lock unlock IDTEST 09-25 12:11:05 lock: 1 exclusive write lock requesting for SYS 09-25 12:11:05 lock: 1 exclusive write lock added for SYS 09-25 12:11:05 index: SYS_DATA remove ( /* key:128421 */ 32, 0, 3, 'CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_F4E957F7_EA56_4879_B84B_027223D92A69 START WITH 1 BELONGS_TO_TABLE') 09-25 12:11:05 pageStore: log - s: 1 table: 0 row: ( /* key:128421 */ 32, 0, 3, 'CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_F4E957F7_EA56_4879_B84B_027223D92A69 START WITH 1 BELONGS_TO_TABLE') 09-25 12:11:05 index: SYS_DATA add ( /* key:128426 */ 32, 0, 3, 'CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_F4E957F7_EA56_4879_B84B_027223D92A69 START WITH 33 BELONGS_TO_TABLE') 09-25 12:11:05 pageStore: log + s: 1 table: 0 row: ( /* key:128426 */ 32, 0, 3, 'CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_F4E957F7_EA56_4879_B84B_027223D92A69 START WITH 33 BELONGS_TO_TABLE') 09-25 12:11:05 pageStore: log commit s: 1 09-25 12:11:05 lock: 1 exclusive write lock unlock SYS 09-25 12:11:05 lock: 2 exclusive write lock requesting for PACKAGE_TRANSACTIONS 09-25 12:11:05 lock: 2 exclusive write lock added for PACKAGE_TRANSACTIONS 09-25 12:11:05 index: PACKAGE_TRANSACTIONS_DATA add ( /* key:1 */ 1, TIMESTAMP '2012-09-25 12:11:05.811') 09-25 12:11:05 pageStore: log + s: 2 table: 20 row: ( /* key:1 */ 1, TIMESTAMP '2012-09-25 12:11:05.811') 09-25 12:11:05 jdbc[2]: /*SQL #:1 t:2*/INSERT INTO PACKAGE_TRANSACTIONS VALUES ( NULL, NOW() ); 09-25 12:11:05 pageStore: log commit s: 2 09-25 12:11:05 lock: 2 exclusive write lock unlock PACKAGE_TRANSACTIONS 09-25 12:11:05 jdbc[2]: /*SQL */SET @TRANSACTIONID=SELECT SCOPE_IDENTITY(); 09-25 12:11:05 lock: 2 exclusive write lock requesting for IDTEST 09-25 12:11:05 lock: 2 exclusive write lock added for IDTEST 09-25 12:11:05 index: IDTEST_DATA add ( /* key:2 */ NULL, 2) 09-25 12:11:05 pageStore: log + s: 2 table: 35 row: ( /* key:2 */ NULL, 2) 09-25 12:11:05 jdbc[2]: /*SQL #:1*/INSERT INTO IDTEST VALUES ( @TRANSACTIONID, SESSION_ID() ); 09-25 12:11:05 pageStore: log commit s: 2 09-25 12:11:05 lock: 2 exclusive write lock unlock IDTEST RESULT OF SELECT * FROM IDTEST resultset : row : null ID : 0 S1 : 2 row : null ID : null //This is wrong should be 1 S1 : 2 Possible Answer? ================ In searching around we came across a recent post from Thomas which suggests that SET of a variable causes the scope identity to be reset? http://stackoverflow.com/questions/12319700/identity-scope-identity-doesnt-work-in-h2-when-assigned If this is the case the recommended approach is rather counter intuitive... SELECT @TRANSACTIONID := SCOPE_IDENTITY(); However if we change our batch statement to replace the SET with SELECT we the get an exception... SQL Batch Execution Failed Statements [SELECT @TRANSACTIONID := SCOPE_IDENTITY(); org.h2.jdbc.JdbcSQLException: Method is not allowed for a query. Use execute or executeQuery instead of executeUpdate; SQL statement: SELECT @TRANSACTIONID := SCOPE_IDENTITY(); [90001-169]] Possible Workarounds? ===================== 1. Is the change of behaviour between 1.2.133 and 1.3.xxx something we should expect to see? 2. If so is there any way around the SELECT assignment in the BATCH problem we see if we try to use the recommended solution? 3. Perhaps there is a way to determine the underlying identity of the AUTO_INCREMENT column's sequence? Any help would be gratefully received. Thanks in advance, Peter -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/5eXMvjS4gpwJ. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
