Transaction Isolation Level
The SET TRANSACTION ISOLATION LEVEL statement controls the isolation level of the transaction.
The SET TRANSACTION statement must be issued before a transaction starts and the settings last only until that transaction 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.
Ingres 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.
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.
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.
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.