Lock Level MVCC and Isolation Levels
When a table is opened, the settings for isolation level, lock level, and readlock affect the locking strategy used. The default settings for the installation are determined by DBMS configuration parameters.
You can override the isolation level at the session level with the statement:
SET SESSION ISOLATION LEVEL level
where level is one of the following.
Note: The following descriptions assume lock level is MVCC.
READ UNCOMMITTED
Implicitly treats this isolation level as if it had been specified as READ COMMITTED. In addition, as with non-MVCC lock levels, causes the transaction to be READ ONLY, disallowing updating operations.
READ COMMITTED
Uses MVCC protocols. The point in time of the MVCC snapshot is the start of the statement, providing statement level read consistency. A statement sees only data that was committed at the start of that statement, or changes made by the transaction itself.
If an UPDATE or DELETE statement detects that a row has been updated after it was read, the current statement is automatically rolled back and retried.
If the ON_ERROR action was set to ROLLBACK TRANSACTION, then instead of aborting and retrying the statement, an update conflict will abort the transaction with error E_US125B.
REPEATABLE READ
Implicitly treats this isolation level as if it had been specified as SERIALIZABLE.
SERIALIZABLE
Uses MVCC protocols. The point in time of the MVCC snapshot is the start of the transaction, providing transaction level read consistency. A statement sees only data that was committed at the start of the transaction, or changes made by the transaction itself.
If an UPDATE or DELETE statement detects that a row has been updated after it was read, the error E_US125B (unable to serialize) is issued, and the statement is aborted.
If the ON_ERROR action was set to ROLLBACK TRANSACTION, the entire transaction is aborted.
Last modified date: 11/28/2023