Re: [h2] Row disappears within the same transaction after a failed update (constraint violation)
This is my initial analysis - I don't know how to solve it yet, but either someone else will, or I will get back to it at a later date. This problem appears to be specific to the combination of an in-memory database and MVCC=true, which is on by default in version 1.4.x The problem is occurring somewhere in the stacktrace below. I suspect it has to do with one of MultiVersionIndex.add(Session, Row) RegularTable.addRow(Session, Row) both which are attempting to do an insert/remove combination. I suspect that one of them is incorrectly restoring state when a unique constraint violation occurs. Thread [H2 Console thread] (Suspended (breakpoint at line 103 in BaseIndex)) owns: Database (id=103) owns: Session (id=104) TreeIndex(BaseIndex).getDuplicateKeyException(String) line: 103 TreeIndex.add(Session, Row) line: 69 MultiVersionIndex.add(Session, Row) line: 59 RegularTable.addRow(Session, Row) line: 120 RegularTable(Table).updateRows(Prepared, Session, RowList) line: 467 Update.update() line: 146 CommandContainer.update() line: 79 CommandContainer(Command).executeUpdate() line: 254 JdbcStatement.executeInternal(String) line: 186 JdbcStatement.execute(String) line: 160 WebThread(WebApp).getResult(Connection, int, String, boolean, boolean) line: 1391 WebThread(WebApp).query(Connection, String, int, int, StringBuilder) line: 1064 WebApp$1.next() line: 1026 WebApp$1.next() line: 1 WebThread.process() line: 168 WebThread.run() line: 94 Thread.run() line: 662 -- 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.
Re: [h2] Row disappears within the same transaction after a failed update (constraint violation)
Hi, Thanks a lot for the test case! I can reproduce it using an in-memory database, with MVCC enabled. I think for the next version, I will disable the whole RegularTable / MultiVersionIndex / TreeIndex code, and use an in-memory MVStore instead. In the long run, this will allow us to reduce the code complexity quite a bit. I hope it doesn't slow down in-memory databases too much. 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+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.
Re: [h2] Row disappears within the same transaction after a failed update (constraint violation)
Hi, By the way, a shorter SQL script test case: drop table test; create table test(id int primary key, a int unique, b int); set autocommit false; insert into test values (1, 1, 1); insert into test values (2, 2, 2); -- gives a constraint violation update test set a = 1 where id = 2; -- the row that failed to update is no longer found by its old values select * from test where id = 2; select * from test where a = 2; -- both selects above fail to find anything... -- ...but this works select * from test where b = 2; Regards, Thomas On Thursday, April 17, 2014, Noel Grandin wrote: > > This is my initial analysis - I don't know how to solve it yet, but either > someone else will, or I will get back to it at a later date. > > > This problem appears to be specific to the combination of an in-memory > database and MVCC=true, which is on by default in version 1.4.x > > The problem is occurring somewhere in the stacktrace below. > I suspect it has to do with one of > MultiVersionIndex.add(Session, Row) > RegularTable.addRow(Session, Row) > both which are attempting to do an insert/remove combination. > I suspect that one of them is incorrectly restoring state when a unique > constraint violation occurs. > > Thread [H2 Console thread] (Suspended (breakpoint at line 103 in > BaseIndex)) > owns: Database (id=103) > owns: Session (id=104) > TreeIndex(BaseIndex).getDuplicateKeyException(String) line: 103 > TreeIndex.add(Session, Row) line: 69 > MultiVersionIndex.add(Session, Row) line: 59 > RegularTable.addRow(Session, Row) line: 120 > RegularTable(Table).updateRows(Prepared, Session, RowList) line: > 467 > Update.update() line: 146 > CommandContainer.update() line: 79 > CommandContainer(Command).executeUpdate() line: 254 > JdbcStatement.executeInternal(String) line: 186 > JdbcStatement.execute(String) line: 160 > WebThread(WebApp).getResult(Connection, int, String, boolean, > boolean) line: 1391 > WebThread(WebApp).query(Connection, String, int, int, > StringBuilder) line: 1064 > WebApp$1.next() line: 1026 > WebApp$1.next() line: 1 > WebThread.process() line: 168 > WebThread.run() line: 94 > Thread.run() line: 662 > > > > -- > 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. > -- 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.
Re: [h2] Row disappears within the same transaction after a failed update (constraint violation)
Hi, This should be fixed with the latest version of H2. Regards, Thomas On Thursday, April 17, 2014, Thomas Mueller wrote: > Hi, > > By the way, a shorter SQL script test case: > > drop table test; > create table test(id int primary key, a int unique, b int); > set autocommit false; > insert into test values (1, 1, 1); > insert into test values (2, 2, 2); > -- gives a constraint violation > update test set a = 1 where id = 2; > -- the row that failed to update is no longer found by its old values > select * from test where id = 2; > select * from test where a = 2; > -- both selects above fail to find anything... > -- ...but this works > select * from test where b = 2; > > Regards, > Thomas > > On Thursday, April 17, 2014, Noel Grandin > > > wrote: > >> >> This is my initial analysis - I don't know how to solve it yet, but >> either someone else will, or I will get back to it at a later date. >> >> >> This problem appears to be specific to the combination of an in-memory >> database and MVCC=true, which is on by default in version 1.4.x >> >> The problem is occurring somewhere in the stacktrace below. >> I suspect it has to do with one of >> MultiVersionIndex.add(Session, Row) >> RegularTable.addRow(Session, Row) >> both which are attempting to do an insert/remove combination. >> I suspect that one of them is incorrectly restoring state when a unique >> constraint violation occurs. >> >> Thread [H2 Console thread] (Suspended (breakpoint at line 103 in >> BaseIndex)) >> owns: Database (id=103) >> owns: Session (id=104) >> TreeIndex(BaseIndex).getDuplicateKeyException(String) line: 103 >> TreeIndex.add(Session, Row) line: 69 >> MultiVersionIndex.add(Session, Row) line: 59 >> RegularTable.addRow(Session, Row) line: 120 >> RegularTable(Table).updateRows(Prepared, Session, RowList) line: >> 467 >> Update.update() line: 146 >> CommandContainer.update() line: 79 >> CommandContainer(Command).executeUpdate() line: 254 >> JdbcStatement.executeInternal(String) line: 186 >> JdbcStatement.execute(String) line: 160 >> WebThread(WebApp).getResult(Connection, int, String, boolean, >> boolean) line: 1391 >> WebThread(WebApp).query(Connection, String, int, int, >> StringBuilder) line: 1064 >> WebApp$1.next() line: 1026 >> WebApp$1.next() line: 1 >> WebThread.process() line: 168 >> WebThread.run() line: 94 >> Thread.run() line: 662 >> >> >> >> -- >> 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. >> > -- 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.
