Microsoft KB Archive/201909

= BUG: Locking Behavior in Transactions Is Incorrect =

Article ID: 201909

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q201909



BUG #:52851 (SHILOH)

BUG #:52851 (SQLBUG_70)



SYMPTOMS
In read-committed transaction isolation level, which is the default in SQL Server, the Intent share (IS) lock on data pages that are held by a transaction may not be released, even after the commit or rollback, of the same.

This behavior is exhibited when two transactions are run simultaneously from different SQL Server Query Analyzer connections. The second transaction proceeds to completion after the first transaction rolls back or commits.

The second transaction continues to hold an Intent shared lock on the page, even after the run has been completed.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.

Microsoft has confirmed this to be a problem in SQL Server 2000.



MORE INFORMATION
  Create a table called test in the pubs database as follows: Use Pubs go Create table test(      id    int,       name  char(10)) go   Create a clustered index on the table you just created: create clustered index test_index on test(id) go   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   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

</li>  From connection C1, run the following code to see the locks that are held by the system process ID (SPID): sp_lock @@spid go </li>  The output from sp_lock for the particular SPID 54 on database pubs with a database ID (DBID) of 5 from connection C1 is: <pre class="fixed_text">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. </li></ol>

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   From Query Analyzer, open a connection C1 and run the following: begin tran update test set id = 10 where id = 5 go </li>  From Query Analyzer, open another connection C2, and run the following: Begin tran select name from test where id between 3 and 5 go </li>  Return to connection C1 and run: rollback transaction go </li>  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: <pre class="fixed_text">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. </li></ol>

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.

Keywords: kbbug kbpending KB201909

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.