Select the product you need help with

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

Article ID: 324417 - View products that this article applies to.

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).

Back to the top | Give Feedback

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.

Back to the top | Give Feedback

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'

Back to the top | Give Feedback

STATUS

This behavior is by design.

The READ COMMITTED transaction isolation level is ANSI compliant.

Back to the top | Give Feedback

MORE INFORMATION

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'

In connection 3, copy and paste the following code:
EXEC sp_lock

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

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)

Back to the top | Give Feedback