patternsqlMinor
When does SQL Server acquire locks?
Viewed 0 times
sqlacquiredoeswhenserverlocks
Problem
The list of isolation levels in SQL Server found here states that write locks acquired within a transaction are retained until the end of the transaction. However it doesn't mention anything about when these locks are acquired.
Are locks by default acquired at the beginning of a transaction, or just when they are needed? If the latter is true, would it therefore be advantageous in large transactions to perform write operations as late as possible in order to minimize the amount of time that X locks are held?
Are locks by default acquired at the beginning of a transaction, or just when they are needed? If the latter is true, would it therefore be advantageous in large transactions to perform write operations as late as possible in order to minimize the amount of time that X locks are held?
Solution
Are locks by default acquired at the beginning of a transaction, or just when they are needed?
Locks are acquired immediately before reading or writing occurs. Depending on the locking granularity selected by the storage engine, locks may be acquired at the row, page, partition, or object (table) level.
If the latter is true, would it therefore be advantageous in large transactions to perform write operations as late as possible in order to minimize the amount of time that X locks are held?
Yes, it might be advantageous for concurrency, depending on the isolation level in use by concurrent transactions.
Related reading:
Locks are acquired immediately before reading or writing occurs. Depending on the locking granularity selected by the storage engine, locks may be acquired at the row, page, partition, or object (table) level.
If the latter is true, would it therefore be advantageous in large transactions to perform write operations as late as possible in order to minimize the amount of time that X locks are held?
Yes, it might be advantageous for concurrency, depending on the isolation level in use by concurrent transactions.
Related reading:
- Lock Escalation (Database Engine) - Books Online
- Understanding SQL Server Concurrency - Kalen Delaney (free ebook)
Context
StackExchange Database Administrators Q#90556, answer score: 5
Revisions (0)
No revisions yet.