SESSION ISOLATION LEVEL
The SET SESSION ISOLATION LEVEL statement controls the isolation level for the current session.
The SET SESSION statement must be issued before a transaction starts and the settings last only until that session is completed.
The following three phenomena are relevant to isolation level:
• Dirty read—Reading a row that another session has changed but not yet committed.
• Non-repeatable read—Reading a row, then reading it again in the same transaction and getting different column values.
• Phantom read—Reading a set of rows (as specified by a WHERE clause), then reading with the same qualification and getting a different set of rows.
Note: All three phenomena pertain to reading. Updating a row always takes a write lock on the row and holds it until end of transaction. This discussion is for row level locks. If LOCKMODE is set to take page or table locks, the results will be as strict or stricter.
Actian X supports the ANSI/ISO SQL92 standard levels of isolation:
READ UNCOMMITTED
Allows dirty read, non-repeatable read, and phantom read.
No read locking is done.
Updaters are never blocked.
Note: If transaction access mode is not specified and level of isolation is READ UNCOMMITTED, access mode is READ ONLY; otherwise it is READ WRITE.
READ COMMITTED
Prevents dirty read.
Allows non-repeatable read and phantom read.
To read a row, a readlock is taken on it and then the readlock is released after the read. Thus, if someone is changing the row, the readlock request runs into the write lock and waits.
Update transactions are only temporarily blocked.
Uses MVCC protocols if the lock level is MVCC. The point in time of the MVCC snapshot is the start of the query, providing statement level read consistency. Each query sees only data that was committed before the statement began, and does not see changes made by the statement itself. If an UPDATE statement detects that a row has been updated after it was read, the current statement is rolled back and retried.
REPEATABLE READ
Prevents dirty and non-repeatable reads.
Allows phantoms.
To read a row, a readlock is taken on it.
If the row qualifies, the readlock is held until the end of the transaction, else it is released. This blocks writers from updating a row that has been read, preventing non-repeatable read.
Uses MVCC protocols if the lock level is MVCC. The point in time of the MVCC snapshot is the start of the transaction, providing transaction level read consistency. The transaction sees only data that was committed at the start of the transaction. However, queries made by a repeatable read transaction do not see any changes made by earlier queries within the transaction. If an UPDATE statement detects that a row has been updated after it was read, the error E_DM0020_UNABLE_TO_SERIALIZE is returned and the transaction is aborted.
SERIALIZABLE
Locks are required on all data before being read. No locks are released until the transaction ends.
Prevents dirty read, non-repeatable read, and phantom read.
To read a row, a readlock is taken on it and the readlock is held until the end of transaction.
Depending on the storage structure, either leaf locks or value locks are taken on the rows covered by the WHERE clause and they are held until the end of the transaction. This prevents phantoms.
Updaters cannot insert or delete qualifying rows because of the leaf or value locks.
Uses MVCC protocols if the lock level is MVCC. The point in time for the MVCC snapshot is the start of the transaction, providing transaction level read consistency. The transaction sees only data that was committed at the start of the transaction. However, queries made by a serializable transaction see any changes made by earlier queries within the transaction. If an UPDATE statement detects that a row has been updated after it was read, the error E_DM0020_UNABLE_TO_SERIALIZE is returned and the transaction is aborted.
The behaviors described above assume that the SET LOCKMODE setting for READLOCK has not been changed from the default, which is READLOCK=SHARED. The system will attempt to take out a readlock on rows as specified above, but its precise behavior is determined by the READLOCK setting.
The
LOCKMODE (see
LOCKMODE) can be changed without affecting the isolation level, and vice versa. The READLOCK setting should be changed only with careful consideration.
Last modified date: 04/26/2024