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

This article was previously published under Q324417
This article has been archived. It is offered "as is" and will no longer be updated.
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 HOLDLOCKGOBEGIN 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 HOLDLOCKSET STATISTICS PROFILE ONGOBEGIN TRAN        SELECT * FROM titles (XLOCK) WHERE title_id = 'BU1111'					
  5. In connection 2, copy and paste the following code:
    SET STATISTICS PROFILE ONGOSELECT * 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.000Rows        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: 01/17/2015 05:44:30 - Revision: 3.2

  • Microsoft SQL Server 2000 Standard Edition
  • kbnosurvey kbarchive kbprb KB324417
Feedback