Transaction Isolation

Transaction isolation is provided for all data manipulation language (DML) statements. Most data definition language (DDL) statements commit the current transaction. See the  for details.

This database supports the following transaction isolation levels:

• Read Committed
  This is the default level. Read locks are released immediately after executing the statement, but write locks are kept until the transaction commits. Higher concurrency is possible when using this level.
  To enable, execute the SQL statement SET LOCK_MODE 3
  or append ;LOCK_MODE=3 to the database URL: jdbc:h2:~/test;LOCK_MODE=3
• Serializable
  Both read locks and write locks are kept until the transaction commits. To enable, execute the SQL statement SET LOCK_MODE 1
  or append ;LOCK_MODE=1 to the database URL: jdbc:h2:~/test;LOCK_MODE=1
• Read Uncommitted
  This level means that transaction isolation is disabled.
  To enable, execute the SQL statement SET LOCK_MODE 0
  or append ;LOCK_MODE=0 to the database URL: jdbc:h2:~/test;LOCK_MODE=0

When using the isolation level 'serializable', dirty reads, non-repeatable reads, and phantom reads are prohibited.

• Dirty Reads
  Means a connection can read uncommitted changes made by another connection.
  Possible with: read uncommitted
• Non-Repeatable Reads
  A connection reads a row, another connection changes a row and commits, and the first connection re-reads the same row and gets the new result.
  Possible with: read uncommitted, read committed
• Phantom Reads
  A connection reads a set of rows using a condition, another connection inserts a row that falls in this condition and commits, then the first connection re-reads using the same condition and gets the new row.
  Possible with: read uncommitted, read committed

Two Phase Commit

The two phase commit protocol is supported. 2-phase-commit works as follows:

• Autocommit needs to be switched off
• A transaction is started, for example by inserting a row
• The transaction is marked 'prepared' by executing the SQL statement PREPARE COMMIT transactionName
• The transaction can now be committed or rolled back
• If a problem occurs before the transaction was successfully committed or rolled back (for example because a network problem occurred), the transaction is in the state 'in-doubt'
• When re-connecting to the database, the in-doubt transactions can be listed with SELECT * FROM INFORMATION_SCHEMA.IN_DOUBT
• Each transaction in this list must now be committed or rolled back by executing COMMIT TRANSACTION transactionName or ROLLBACK TRANSACTION transactionName
• The database needs to be closed and re-opened to apply the changes