distinct from lock granularity
Intention locks
Shared Locks
Shared lock on table while reading.
No shared data lock unless SERIALIZABLE or through FOR SHARE or LOCK IN SHARE MODE.
Exclusive Locks
DDL/DML
UPDATE
SHARED_WRITEon tableSHARED_READon parent
metadata locks
data locks
IX(Insert exclusive) on tableX REC_NOT_GAP(Exclusive) on row
Lock Compatability
Two intention locks are always compatible with each other. This means that even if a transaction has an intention exclusive lock, it will not prevent another transaction to take an intention lock. It will however stop the other transaction from upgrading its intention lock to a full lock.
X |
IX |
S |
IS |
|
|---|---|---|---|---|
X |
No | No | No | No |
IX |
No | Yes | No | Yes |
S |
No | No | Yes | Yes |
IS |
No | Yes | Yes | Yes |