THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

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. 

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

Published Wednesday, December 13, 2006 7:25 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Mark Harvey said:

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
December 20, 2006 12:18 PM
 

gaurav said:

i am having one question

try

{

con1.ConnectionString = SQLConnectionString;

if (con1.State == ConnectionState.Closed)

{

con1.Open();

}

trans = con1.BeginTransaction();

SqlCommand cmd1 = new SqlCommand(“select name from testinfo WITH(XLOCK) where ID=’” + textBox1.Text + “‘”, con1,trans);

SqlDataReader dr1 = cmd1.ExecuteReader();

if (dr1.HasRows)

{

while (dr1.Read())

{

label1.Text = dr1["name"].ToString();

}

}

catch (Exception ee)

{

trans.Commit();

con1.Close();

MessageBox.Show(“Access By Other User “);

}

}

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.

June 6, 2013 12:56 AM
 

James Dingle said:

Trace the locks; XLOCK works fine.  The SELECT on the other hand, does not ask for a Shared lock on the row.  You can force it by changing the SQL statement to:

select *

from test with (repeatableread)

Or the select needs to be more than a select.  Imagine you have such statement:

Insert Into test2 Select testId From test Where testId = 1

Then SQL turns the select into repeatable read lock mode and the statement has to wait for the first one.

August 8, 2013 3:08 PM
 

drsql said:

@James

I wasn't suggesting that there was anything wrong, just that the concept of an "exclusive" lock doesn't always keep others away.

August 8, 2013 4:23 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement