Sams Teach Yourself Transact-SQL in 21 Days, Second Edition

Understanding Lock Persistence

You've already seen that exclusive locks are held throughout a transaction and are released when the transaction is committed or rolled back. Let's try a similar experiment with a select statement. We'll start with this query:

begin transaction
select CustomerID
  from Orders
 where OrderID = 10258

CustomerID
----------
ERNSH

The first line of the query creates a transaction. The select statement acquires a shared lock on the row in the Orders table. Intent locks are acquired. Let's look at the locks held by this process:

spid   dbid   ObjId      IndId  Type Resource Mode     Status
------ ------ ---------- ------ ---- -------- -------- ------
    57      6          0      0 DB            S        GRANT

Even though the process acquired locks during the select process, ...