BUG: Locking Behavior in Transactions Is Incorrect

MORE INFORMATION

1. Create a table called test in the pubs database as follows:

   Use Pubs
   go
   Create table test(
          id    int,
          name  char(10))
   go
   					

2. Create a clustered index on the table you just created:

   create clustered index test_index on test(id)
   go
   					

3. Insert values into table test:

   insert test values (1, 'sample1')
   insert test values (2, 'sample2')
   insert test values (3, 'sample3')
   insert test values (4, 'sample4')
   insert test values (5, 'sample5')
   go
   					

When a transaction is started from Query Analyzer and is not committed or rolled back, the sp_lock output indicates that no page locks are being held. But when two transactions are started simultaneously in two separate SQL Query Analyzer connections and either connection rolls back or commits, the other transaction continues to hold the page lock.

Example of Expected Behavior

1. Open a connection C1 to SQL Server with Query Analyzer, and then run the following:

   Begin tran
   select name from test where id between 3 and 5
   go
   					

   You receive the following output, as expected:

   name       
   ---------- 
   sample3   
   sample4   
   sample5
   						

2. From connection C1, run the following code to see the locks that are held by the system process ID (SPID):

   sp_lock @@spid
   go
   					

3. The output from sp_lock for the particular SPID 54 on database pubs with a database ID (DBID) of 5 from connection C1 is:

   spid   dbid   ObjId       IndId  Type Resource         Mode     Status 
   ------ ------ ----------- ------ ---- ---------------- -------- ------ 
   54     5      0           0      DB                    S        GRANT
   					

   where the output indicates that there are no IS page locks held on the data pages of the table as expected.

From the preceding output, it should be clear that under read-committed isolation level, you do not hold any locks on the table against which you ran the select statement even though you are still in an open transaction.

Example of Problem Behavior

1. From Query Analyzer, open a connection C1 and run the following:

   begin tran
   update test set id = 10 where id = 5
   go
   					

2. From Query Analyzer, open another connection C2, and run the following:

   Begin tran
   select name from test where id between 3 and 5
   go
   					

3. Return to connection C1 and run:

   rollback transaction
   go
   					

4. As soon as the transaction from C1 is rolled back, the transaction from C2 completes successfully. Now, from connection C2, run the following:

   sp_lock @@spid
   go
   					

   The output from sp_lock for the particular SPID 8, on database pubs with a DBID of 5 from connection C2 is:

   spid   dbid   ObjId       IndId  Type    Resource   Mode Status
   ----   ----   ---------   ------ ----    --------   ---- ------
   8      5      165575628   1      PAG     1:96       IS   GRANT
   8      5      0           0      DB                 S    GRANT
   					

   The actual SPID and DBID may vary from case to case.

From the preceding output, note that even after the specific select statement is completed, it continues to hold an IS Page lock on the data page.