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.
So, say you have the following table:
create table test
(
testId int primary key
)
go
insert into test (testId)
values (1)
go
Then in one connection run this:
set transaction isolation level serializable
begin transaction
select *
from test (xlock)
An then in another, this one should be blocked, right?
select *
from test
You will see that that isn't true. But, rollback the transaction of the the first batch and this time update the table, then start a transaction:
--in one connection run this:
set transaction isolation level serializable
update test
set testId = testId
begin transaction
select *
from test (xlock)
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?)
Crossposted to http://drsql.spaces.live.com