when do lock actually happen in a transaction?

Question

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?

Answer 1

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.

Answer 2

You can see for yourself using DMVs. In SSMS open two query tabs. Paste this into one:

select * from sys.dm_tran_active_transactions
select * from sys.dm_tran_locks

Paste your statements into the other.

Run my queries. This will show background activity on the server which can be ignored.

Run just your BEGIN TRAN A; statement and re-run the DMV queries. You'll see there's a new transaction with name=A but no further locks.

Now hightlight just your select statement and run it and also the DMV queries again. Nothing has changed! What happened to the S locks? Well, running under the default READ COMMITTED isolation level the locks are release as soon as the DB engine has finished processing the particular row. See section "SQL Server Locking Read Committed" here. So the S locks were taken while the query was execution but had been released by the time the DMVs were examined again. (You can see the locks and releases happening using extended events. There are queries in this answer which you can modify to see this for yourself.)

We can make the locks remain to the end of the transaction using the HOLDLOCK table hint. Change your SELECT to ..FROM Production.Products WITH(HOLDLOCK).. and re-run the SELECT and the DMV queries. You will see shared locks on the table (and other objects).

Run just the UPDATE statement. The shared (S) locks have become exclusive (X) locks. Because these locks are protecting altered data they are always held until the end of the transaction - no additional hints are required.

Running your COMMIT will release the locks and remove the transaction.

For your original question - when was the exclusive lock taken on row with productid 3 - the answer is, when the query processor came to that particular row as it was working through the query plan generated for that update statement.

Answer 3

Every row lock is taken or converted exactly at the moment when that row is accessed by the transaction. The share lock on the row WHERE productid = 2 will be taken at some time after "time a", but before "time b". The update lock on the row WHERE productid = 3 will be taken _at some time after "time b", then converted to the exclusive lock shortly after.

This is the reason why deadlocks are possible -- if all locks were taken in advance, deadlocks would not happen.

the sql server won't parse the commands in advanced to see it need a share lock and update lock and place them in the first line of the transaction?

SQL Server (or any other DBMS for that matter) can do no such thing, because at the start of a transaction it is unknown if rows that possibly match statement predicates even exist. You need to go and try and find them first, one at a time, which is the natural place to put the necessary locks on them.