Lock that reads original data BUT does not honour exclusive locks?

Hi all,
Is there a lock that is similar to NOLOCK hint by not honouring exclusive
locks, BUT reads original (committed) data (no dirty reads) instead?
Pls kindly advise. Help is much appreciated. TQ.Not in SQL Server 2000, but there will be in SQL Server 2005.
Jacco Schalkwijk
SQL Server MVP
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:42536077-EBBC-4025-9035-952D44E556BB@.microsoft.com...
> Hi all,
> Is there a lock that is similar to NOLOCK hint by not honouring exclusive
> locks, BUT reads original (committed) data (no dirty reads) instead?
> Pls kindly advise. Help is much appreciated. TQ.

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!> 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" <anonymous@.devdex.com> wrote in message
news:4005dee1$0$70304$75868355@.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!|||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!

Lock hint ?

Here I have a question like I have created one stored procedure which
InsertCustomer and call that stored procedure in the customer form... the
problem is that when more than 50 - 60 people access the same form
simultaneously then it gave me some time time out error or some times gave
me primary violation error... duplication key not allowed.
Can any one help like is there any lock hint I have to use in stored
procedure?
Thanks
Without seeing exactly what you are doing with the sp it is pretty hard to
say what the trouble is. How are you generating the PK?
Andrew J. Kelly SQL MVP
"Roy" <roy@.hotmail.com> wrote in message
news:uu808QxZFHA.3808@.TK2MSFTNGP09.phx.gbl...
> Here I have a question like I have created one stored procedure which
> InsertCustomer and call that stored procedure in the customer form... the
> problem is that when more than 50 - 60 people access the same form
> simultaneously then it gave me some time time out error or some times gave
> me primary violation error... duplication key not allowed.
> Can any one help like is there any lock hint I have to use in stored
> procedure?
> Thanks
>
>
|||This is basically a identity column so in the stored procedure I didn't use
this column.. it's auto inserted column.
Thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uimiYrxZFHA.3320@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Without seeing exactly what you are doing with the sp it is pretty hard to
> say what the trouble is. How are you generating the PK?
> --
> Andrew J. Kelly SQL MVP
>
> "Roy" <roy@.hotmail.com> wrote in message
> news:uu808QxZFHA.3808@.TK2MSFTNGP09.phx.gbl...
the[vbcol=seagreen]
gave
>
|||Again, without seeing the actual stored procedure and the DDL for the
underlying table we can't begin to give an intelligent answer.
Andrew J. Kelly SQL MVP
"Joh" <joh@.mailcity.com> wrote in message
news:uiDPgqHaFHA.3400@.tk2msftngp13.phx.gbl...
> This is basically a identity column so in the stored procedure I didn't
> use
> this column.. it's auto inserted column.
> Thanks
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uimiYrxZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> the
> gave
>