10

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?

Levi Botelho
  • 375
  • 2
  • 11

1 Answers1

5

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:

Paul White
  • 94,921
  • 30
  • 437
  • 687