14. Understanding Multiversion Concurrency Control : Table Lock Level Compatibility in an MVCC-capable Database
 
Share this page                  
Table Lock Level Compatibility in an MVCC-capable Database
To prevent incompatible and potentially destructive combinations of lock levels in a table, a LK_TBL_MVCC lock is acquired when a table is opened, and is released when the table is closed. This lock is not taken if the database or the table is opened for exclusive access.
Lock Level
MVCC Lock Mode
Compatible With
TABLE
PAGE
ROW
MVCC
TABLE
LK_S
Y
Y
Y
PAGE
LK_S
Y
Y
Y
ROW
LK_IS
Y
Y
Y
Y
MVCC
LK_IX
 
 
Y
Y
Transactions that open a table for either MVCC or row locking can co-exist and run concurrently. Any other MVCC mix causes one of the transactions to wait for the MVCC lock.
Serializable row locking transactions:
• Are incompatible with MVCC as they may internally change their lock level to PAGE to prevent phantom reads.
• Attempt to acquire the MVCC lock with a mode of LK_S.
If a ROW locking, write access transaction is running with SET NOLOGGING, its MVCC lock mode is upgraded to LK_S. Because MVCC works by producing read consistent pages from the log records, missing log records cannot be tolerated.
SET NOLOGGING sessions with write access to the database cannot open tables with a lock level of MVCC; the following error is returned:
E_DM0012_MVCC_INCOMPATIBLE