Does XLOCK always prevent reads by others?

I was going back through my files of draft blogs for the past 6 months (particularly looking for things that are wrong in my book,) and I found this one that covers something that I found really fascinating.) In this thread, a person was attempting to create a pessimistic lock by applying an XLOCK in a hint. Seemed reasonable to me, since an XLOCK is incompatible with all other lock types, right?

Well, not exactly. It turns out that SQL Server does a particular optimization where, if it knows that a page has not changed, it reads through an exclusive lock (which are used to mark when a page is being written to) since the definition of READ COMMITTED is technically being met.

and you will find that the select statement is blocked. This statement will continue to be locked is no longer considered dirty (it has been flushed to disk). Clearly this dirty page condition is not a good thing to rely on to block other users.

Per KB Article 324417, you can use PAGLOCK along with the XLOCK hint to give you a blocking condition everytime, but you can also guess that you will get more than the single row KEY lock you were actually desiring. (read the KB article for some more information). Also credit to Tibor Karaszi for his reply in this post (and a few others) about the subject.

Hopefully one day of the real deep thinkers who have blogs will blog about this topic in more detail. (Hint Hint?)

Comment Notification

Comments

I've come across this also, and had the same thought - is it a bug? I found the same functionality exists in 2000 and in 2005 !!! I was looking for a pessimistic lock and the only way I have found is to make one is an update of the row!!!
Regards,
Mark

this code is working properly ,it generate an exception which is correct , gives an message “access by another user ” ,but this system define exception required lot of time min 10 sec it take , so i want to find any other condition which will show that this row is access by another user.is there is any other parameter so i can identify that this row is exclusive lock by other user.