SQL Server uses locks to maintain consistency while multiple transactions execute simultaneously. We know the various types of locks at various levels. But what is the life time or age of a lock? Let me answer it one by one for three types of locks:
Shared Locks (S): Age of shared lock is decided by the isolation level of session. Following table describes the isolation level and age of Shared lock:
| Isolation Level | Age of Shared Lock |
| READ UNCOMMITTED | No lock held |
| READ COMMITTED | Execution time of SELECT statement |
| READ COMMITTED WITH SNAPSHOT | No lock held |
| REPEATABLE READ | Execution time of outermost user defined Transaction |
| SNAPSHOT | No lock held |
| SERIALIZABLE | Execution time of outermost user defined Transaction |
Exclusive Locks (X): Execution time of outermost user defined Transaction.
Update Locks (U): Execution time of outermost user defined Transaction.
Yes, age of exclusive locks and update locks is always the length of outermost user defined transaction. There is no impact of isolation levels on the life time of these locks.
This behavior is same for row or key, page and table levels. Age of “intent lock” is same as the age of their base lock like Intent Shared (IS) lock’s age is same to that of its relevant Shared (S) lock.
No comments:
Post a Comment