Microsoft KB Archive/324417

= PRB: An XLOCK Hint in a Serializable Transaction May Be Ignored =

Article ID: 324417

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q324417



SYMPTOMS
If you issue a SELECT query with an XLOCK hint in the context of a serializable transaction, the X KEY lock is not honored by other connections that try to read the same record (or records).



CAUSE
SQL Server 2000 will optimize away row locks at the KEY level in READ_COMMITTED scans if it can determine that these locks are not required for query correctness. By default, the other connections in this scenario have an isolation level of READ_COMMITTED, which explains why there are no S row locks that are acquired and consequently, the X KEY lock in the first connection is effectively ignored.



WORKAROUND
To work around this behavior, you can specify the XLOCK and PAGLOCK hints for the SELECT query. By doing so, the problem does not occur because an X PAG lock is incompatible with the IS PAG lock, which is what queries in other READ_COMMITTED connections try to acquire. For example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE -- equivalent to HOLDLOCK GO BEGIN TRAN SELECT * FROM titles (XLOCK, PAGLOCK) WHERE title_id = 'BU1111'



STATUS
This behavior is by design.

The READ COMMITTED transaction isolation level is ANSI compliant.



Steps to Reproduce the Behavior
 Connect to an instance of SQL Server 2000. Use the pubs database. Open two additional connections in Query Analyzer (for a total of three connections).  In the first connection, copy and paste the following code: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE -- equivalent to HOLDLOCK SET STATISTICS PROFILE ON GO BEGIN TRAN SELECT * FROM titles (XLOCK) WHERE title_id = 'BU1111'   In connection 2, copy and paste the following code: SET STATISTICS PROFILE ON GO SELECT * FROM titles WHERE title_id = 'BU1111' </li>  In connection 3, copy and paste the following code: EXEC sp_lock </li>  Run the code in connection 1, and then check the locking behavior by running the code in connection 3. You see an exclusive (X) lock on a KEY of the clustered index (the single row that is being affected): <pre class="fixed_text">Rows       Executes    StmtText --- ---          1.00        1.00 SELECT * FROM titles (XLOCK) WHERE title_id = 'BU1111' 1.00       1.00   |--Clustered Index Seek(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]), SEEK:([titles].[title_id]='BU1111') ORDERED FORWARD)

spid  dbid   ObjId       IndId  Type Resource         Mode     Status -- -- --- --   --  51.00   4.00        0.00   0.00 DB                    S        GRANT 52.00  4.00        0.00   0.00 DB                    S        GRANT 53.00  5.00        0.00   0.00 DB                    S        GRANT 53.00  5.00 2,121,058,5   1.00 PAG  1:99             IX       GRANT 53.00  5.00 2,121,058,5   0.00 TAB                   IX       GRANT 53.00  5.00 2,121,058,5   1.00 KEY  (a4006ba2e306)   X        GRANT 54.00  5.00        0.00   0.00 DB                    S        GRANT 55.00  5.00        0.00   0.00 DB                    S        GRANT 55.00  1.00 85,575,343.   0.00 TAB                   IS       GRANT </li>  Run the code in connection 2. The output shows that the clustered index is being used, but the row is being read even though there is an exclusive lock on it that connection 1 is holding: <pre class="fixed_text">title_id title                                                                           type         pub_id price                 advance               royalty     ytd_sales   notes                                                                                                                                                                                                    pubdate -- - - --- --- -- BU1111   Cooking with Computers: Surreptitious Balance Sheets                             business     1389   11.9500               5000.0000             10          3876        Helpful hints on how to use your electronic resources to the best advantage. 1991-06-09 00:00:00.000

Rows       Executes    StmtText --- --- --      1.00        1.00 SELECT * FROM [titles] WHERE [title_id]=@1 1.00       1.00   |--Clustered Index Seek(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]), SEEK:([titles].[title_id]=[@1]) ORDERED FORWARD) </li></ol>

Keywords: kbprb KB324417

-

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

© Microsoft Corporation. All rights reserved.