469,578 Members | 1,914 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,578 developers. It's quick & easy.

Lock hint question

Hi All,

I have a question about lock hint for you :

If the first user currently run a select command
with share lock and hold it. What kind of
lock (lock hint) should be used by the second user
in the select command (from the same table) so that
this command will wait until the first user releases
the lock ?

I have tried using tablock, tablockx, xlock and
updlock hint in the select command for the second
user, but it is not successful. Below is my
unsuccessful test :

The first user :
Begin Transaction
select fprefix from ut1 where fprefix = '000'

The second user :
select * from ut1 with (tablockx)

Please help me

Thanks in advance

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #1
2 3480
> The first user :
Begin Transaction
select fprefix from ut1 where fprefix = '000'

The second user :
select * from ut1 with (tablockx)
With the default READ COMMITTED transaction isolation level, any locks
acquired during the select will be released when the select completes. The
second user will then be able to acquire any type of lock on the table.

The example below will override the default isolation level:

The first user :
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT fprefix FROM ut1 WHERE fprefix = '000'

The second user :
SELECT * FROM ut1 WITH (TABLOCKX)

Of course, this approach reduces concurrency. See the Books Online
<tsqlref.chm::/ts_set-set_74bw.htm> for more information.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Anita" <an*******@devdex.com> wrote in message
news:40***********************@news.frii.net... Hi All,

I have a question about lock hint for you :

If the first user currently run a select command
with share lock and hold it. What kind of
lock (lock hint) should be used by the second user
in the select command (from the same table) so that
this command will wait until the first user releases
the lock ?

I have tried using tablock, tablockx, xlock and
updlock hint in the select command for the second
user, but it is not successful. Below is my
unsuccessful test :

The first user :
Begin Transaction
select fprefix from ut1 where fprefix = '000'

The second user :
select * from ut1 with (tablockx)

Please help me

Thanks in advance

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 20 '05 #2
Dan Guzman,

Yes, now it is successful with SERIALIZABLE hint.
Many thanks for your reply

Regards,

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

17 posts views Thread by euan_woo | last post: by
190 posts views Thread by blangela | last post: by
3 posts views Thread by stefan.albert | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.