Article ID: 324417 - View products that this article applies to.
This article was previously published under Q324417
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).
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.
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:
This behavior is by design.
The READ COMMITTED transaction isolation level is ANSI compliant.
Steps to Reproduce the Behavior
Article ID: 324417 - Last Review: October 3, 2003 - Revision: 3.2
Contact us for more help