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

Article translations Article translations
Article ID: 324417 - View products that this article applies to.
This article was previously published under Q324417
Expand all | Collapse all

On This Page

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.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Connect to an instance of SQL Server 2000.
  2. Use the pubs database.
  3. Open two additional connections in Query Analyzer (for a total of three connections).
  4. 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'
    					
  5. In connection 2, copy and paste the following code:
    SET STATISTICS PROFILE ON
    GO
    SELECT * FROM titles WHERE title_id = 'BU1111'
    					
  6. In connection 3, copy and paste the following code:
    EXEC sp_lock
    					
  7. 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):
    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
    					
  8. 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:
    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)
    					

Properties

Article ID: 324417 - Last Review: October 3, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbprb KB324417

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com