Getting one record at a time

I have a stored procedure that I call from a VB6 program:
DECLARE @.myid UNIQUEIDENTIFIER
BEGIN TRAN
SET @.myid = NEWID()
UPDATE data WITH (ROWLOCK) SET UID=@.myID, Done=1, DateTimeDone=GetDate()
WHERE ID=
(SELECT TOP 1 ID FROM data WITH ( XLOCK, READPAST) WHERE done=0 and ready=1)
COMMIT TRAN
SELECT PostCode,MPN,ID FROM data WITH (NOLOCK) WHERE UID=@.myID
The ID column is a clustered index.
This gives me the next available row for each user (there are 40 users), I
am regularly getting deadlocks.
Any help would be greatly appreciated.
Thanks,
JkJamz
I'm not sure undertsabd what you are trying to do but read up please this
chapter
SET TRANSACTION ISOLATION LEVEL in the BOL
"Jamz" <Jamz@.discussions.microsoft.com> wrote in message
news:D406B8AE-1DF6-4425-A4C0-DCEC4A328B87@.microsoft.com...
> I have a stored procedure that I call from a VB6 program:
> DECLARE @.myid UNIQUEIDENTIFIER
> BEGIN TRAN
> SET @.myid = NEWID()
> UPDATE data WITH (ROWLOCK) SET UID=@.myID, Done=1, DateTimeDone=GetDate()
> WHERE ID=
> (SELECT TOP 1 ID FROM data WITH ( XLOCK, READPAST) WHERE done=0 and
ready=1)
> COMMIT TRAN
> SELECT PostCode,MPN,ID FROM data WITH (NOLOCK) WHERE UID=@.myID
>
> The ID column is a clustered index.
> This gives me the next available row for each user (there are 40 users), I
> am regularly getting deadlocks.
> Any help would be greatly appreciated.
> Thanks,
> Jk|||Because XLOCK = <snip from Books OnLine>Use an exclusive lock that will be
held until the end of the transaction on all data processed by the statement
.
This lock can be specified with either PAGLOCK or TABLOCK, in which case the
exclusive lock applies to the appropriate level of granularity.</snip from
Books OnLine>
"HELD UNTIL THE END OF THE TRANSACTION ON ALL DATA PROCESSED... "
Your statement "Select Top 1..." Processes all rows in the table With Done
= 0, and Ready = 1, So it will pur a row Lock on every one of those rows an
d
hold it until the Commit Tran call...
This is a good example of why, unless you know exactly what you are doing,
and have carefully and identified a specific scenario where a lock hint is
necessary and appropriate, you should avoid them...
now, Next point...
Your query updates the row whose id is equal to the output of the subquery,
which returns the TOP 1 id from the table... But there is no Order By in the
subquery... This means that the value returned could be any ID in the table,
and SQL is under no oblication to return any one of the rows rather than any
other. Unless Order By clause is included, the Order of the rows in a
resultset is not guaranteed, and so the row returned by a TOP 1 select is
also not gauranteed...
Next point... assuming you put in an Order By (What that might be I don;t
know Order By PostCode, or what...) But assuming it's Order By ID, and that
you really want the Lowest id value (or the highest?) then you don;t need
Order By or top 1... and you can do all this without any of the Lock Hints o
r
explicit Transactions...
Select @.ID = Min(ID) -- [Or Max(ID)]
FROM data
WHERE done=0 and ready=1
-- --
UPDATE data SET
UID = NewID(), Done=1,
DateTimeDone = GetDate()
WHERE ID=@.ID
-- --
Select PostCode, MPN, ID
FROM data
WHERE ID = @.ID
-- --
"Jamz" wrote:

> I have a stored procedure that I call from a VB6 program:
> DECLARE @.myid UNIQUEIDENTIFIER
> BEGIN TRAN
> SET @.myid = NEWID()
> UPDATE data WITH (ROWLOCK) SET UID=@.myID, Done=1, DateTimeDone=GetDate()
> WHERE ID=
> (SELECT TOP 1 ID FROM data WITH ( XLOCK, READPAST) WHERE done=0 and ready=
1)
> COMMIT TRAN
> SELECT PostCode,MPN,ID FROM data WITH (NOLOCK) WHERE UID=@.myID
>
> The ID column is a clustered index.
> This gives me the next available row for each user (there are 40 users), I
> am regularly getting deadlocks.
> Any help would be greatly appreciated.
> Thanks,
> Jk|||Thanks for your help, I'll try that.
I don't really care which record is brought back, I only care that it is not
used by anyone else.
When I wrote something similar to what you are suggesting I got the same
record being returned to different users.
Admittedly I had not ordered (or done a Min(x)) on it.
When I wrapped things in a transaction it did not help either.
Thanks,
JK
"CBretana" wrote:
> Because XLOCK = <snip from Books OnLine>Use an exclusive lock that will b
e
> held until the end of the transaction on all data processed by the stateme
nt.
> This lock can be specified with either PAGLOCK or TABLOCK, in which case t
he
> exclusive lock applies to the appropriate level of granularity.</snip from
> Books OnLine>
> "HELD UNTIL THE END OF THE TRANSACTION ON ALL DATA PROCESSED... "
> Your statement "Select Top 1..." Processes all rows in the table With Do
ne
> = 0, and Ready = 1, So it will pur a row Lock on every one of those rows
and
> hold it until the Commit Tran call...
> This is a good example of why, unless you know exactly what you are doing,
> and have carefully and identified a specific scenario where a lock hint is
> necessary and appropriate, you should avoid them...
> now, Next point...
> Your query updates the row whose id is equal to the output of the subquery
,
> which returns the TOP 1 id from the table... But there is no Order By in t
he
> subquery... This means that the value returned could be any ID in the tabl
e,
> and SQL is under no oblication to return any one of the rows rather than a
ny
> other. Unless Order By clause is included, the Order of the rows in a
> resultset is not guaranteed, and so the row returned by a TOP 1 select is
> also not gauranteed...
> Next point... assuming you put in an Order By (What that might be I don;t
> know Order By PostCode, or what...) But assuming it's Order By ID, and tha
t
> you really want the Lowest id value (or the highest?) then you don;t need
> Order By or top 1... and you can do all this without any of the Lock Hints
or
> explicit Transactions...
> Select @.ID = Min(ID) -- [Or Max(ID)]
> FROM data
> WHERE done=0 and ready=1
> -- --
> UPDATE data SET
> UID = NewID(), Done=1,
> DateTimeDone = GetDate()
> WHERE ID=@.ID
> -- --
> Select PostCode, MPN, ID
> FROM data
> WHERE ID = @.ID
> -- --
>
> "Jamz" wrote:
>|||Sorry, I understand what you are doing... Then you do need a transaction, bu
t
more importantly, you need to set the Isolation level one level higher than
Read Committed, (which is the default), to precvent concurrent users from
attempting t oread the min (ID) until you have set the done flag...
try this:
NOTE: Remember to reset the Isolation level after the commit...
Declare @.ID Integer
Set Isolation Level Repeatable Read
Begin Transaction
Select @.ID = Min(ID) FROM data
WHERE done=0 and ready=1
-- --
UPDATE data SET
UID = NewID(), Done=1,
DateTimeDone = GetDate()
WHERE ID=@.ID
Commit Transaction
Set Isolation Level Read Committed
-- --
Select PostCode, MPN, ID
FROM data
WHERE ID = @.ID
-- --
"Jamz" wrote:
> Thanks for your help, I'll try that.
> I don't really care which record is brought back, I only care that it is n
ot
> used by anyone else.
> When I wrote something similar to what you are suggesting I got the same
> record being returned to different users.
> Admittedly I had not ordered (or done a Min(x)) on it.
> When I wrapped things in a transaction it did not help either.
> Thanks,
> JK
> "CBretana" wrote:
>|||Thanks for that. I think that will fix it. I was missing the transaction
level setting, I went down the locking hint route instead.
Cheers
JK
"CBretana" wrote:
> Sorry, I understand what you are doing... Then you do need a transaction,
but
> more importantly, you need to set the Isolation level one level higher tha
n
> Read Committed, (which is the default), to precvent concurrent users from
> attempting t oread the min (ID) until you have set the done flag...
> try this:
> NOTE: Remember to reset the Isolation level after the commit...
> Declare @.ID Integer
> Set Isolation Level Repeatable Read
> Begin Transaction
> Select @.ID = Min(ID) FROM data
> WHERE done=0 and ready=1
> -- --
> UPDATE data SET
> UID = NewID(), Done=1,
> DateTimeDone = GetDate()
> WHERE ID=@.ID
> Commit Transaction
> Set Isolation Level Read Committed
> -- --
> Select PostCode, MPN, ID
> FROM data
> WHERE ID = @.ID
> -- --
>
> "Jamz" wrote:
>