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 Grammar 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