Lock hint question

[Anita]

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!

[Dan Guzman]

> 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!

[Anita]

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!