XLOCK Hint is ignored even though specifying explictly?
Interesting newbie question on the forums to share.
I am selecting a value from a table(column).I do not want any 2 users can select same value at the same time..in other word 1 value only be selected by individual user. in order to do it I am simply using with (xlock) in select statement within begin tran...commit tran. In this way I am hoping to avoid above mentioned situation.
But it is not working...as when I fire second select statement (without commit tran of first statement) for same rows and same table..to make sure that if table select same records that selected in first statement..
THey have just run this following commands
Begin Tran
Select au_lname from authors with (xlock)
and then
Select au_lname from authors
But both statements are display the records, as per the locking hint the second statement should have been blocked to get results. Here is why on such behaviour within SQL 2000 verison.
By default if you have issued 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). Similarly within SQL 2000 optimizer will put 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.
One of the KBA refers that :
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'
This behavior is by design.
The READ COMMITTED transaction isolation level is ANSI compliant.