SQL Server – When Do Locks Actually Happen in a Transaction?

lockingsql servertransaction

Let's say we have a transaction A:

BEGIN TRAN A;
//--------------------< time a
 SELECT productid, unitprice
 FROM Production.Products
 WHERE productid = 2;
//--------------------< time b
 UPDATE Production.Products
 SET unitprice += 1.00
 WHERE productid = 3;
COMMIT TRAN A;

We know that for the transaction A (under default Read Committed isolation level), a share lock (for productid 2) is needed and a exclusive lock(for productid 3) is needed.

My question is, for the exclusive lock for productid 3, when did the lock happen? At the beginning of the transaction, at time a or at time b when the transaction actually starts to do the update?

Best Answer

The exclusive lock is taken when the UPDATE happens.

Here's what the documentation says:

Depending on the current transaction isolation level settings, many resources acquired to support the Transact-SQL statements issued by the connection are locked by the transaction until it is completed with either a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement.

This is vague enough that it doesn't really answer the question, so let's read further:

Although BEGIN TRANSACTION starts a local transaction, it is not recorded in the transaction log until the application subsequently performs an action that must be recorded in the log, such as executing an INSERT, UPDATE, or DELETE statement.

The transaction log is the truth. Until the update, nothing is recorded in the log. Therefore, the exclusive lock is only needed when the update occurs.

If you ran each statement one line at a time and reviewed the locks on the table in question, you'd see this behaviour yourself.

Related Question