Hello, I am trying to move an application from postgresql to H2 for ease of use reasons. It has exceeded my expectations so far, working surprisingly well. The application is heavily threaded and uses multiple database connections to insert and query data. The problem I'm having is that there is one part of the code that is trying to do simultaneous inserts of data that might be identical. There is a unique constraint on the table to make sure we don't duplicate any rows. The problem comes when we try to recover from the constraint violation, getting the id of the row that was inserted.
The logic is generally: 1. Lookup for row with tuple, If found, return id 2. Start transaction 3. Try to insert row with tuple 4. Commit. If successful, get id of inserted row and return If unsuccessful, rollback and lookup again as in #1 and return that id The code assumes that after a unique constraint violation, it will be able to look up the value in the database. This works well in postgresql. In H2, however, when the unique constraint violation happens it occasionally cannot locate the id. I have determined that we will only see the value from the other transaction that inserted the row in the lookup after the other transaction has committed the change, which makes sense due to the read committed or serializable transaction isolation. However, it seems like we shouldn't get the unique constraint violation until that point either, since according to the local transaction, that row doesn't exist, so there can be no violation. This looks like due to the way H2 stores the value in the index on the first insert, but without showing it to the user, so that the constraint violation happens. I created a test case that mimics the problem and tested it with postgresql and mysql with innodb. The code can be found here: http://pastebin.com/hDgtYtHd The output that's expected the way the postgres happens: Did not find yet as expected. Recovered: 1 Recovered separate statement: 1 With postgresql 8.3 and with mysql 5.1 in transaction isolation mode read committed, the transaction performing the duplicate insertion blocks until the original insertion either commits or rolls back. If the first is committed, the latter receives a unique constraint violation error. If it is rolled back, the blocking insert completes successfully. mysql's innodb with serialization isolation behaves a little differently. If the first insert happened before the lookup in the other transaction, the lookup (SELECT) blocks until the other transaction is complete (commit or rollback). mysql shows roughly the same behavior as H2 without MVCC. Either of these behaviors would help the application, since without major modification, it can't be used without MVCC. Thanks for any help, -Scott -- You received this message because you are subscribed to the Google Groups "H2 Database" group. 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.
