Help with Select Top 1
-
Here is the original code in a stored procedure..the thought here is to retrieve the oldest trackingCode by cTime(or next oldest trackingCode if locked); and then update the cTime so the next time this record would not be used....it appears that when multiple applications evoke this stored procedure at the same time, the same trackingCode is retrieved.
-- Original
BEGIN TRANSACTION getnextcode
declare @trackingCode char(4)
SELECT TOP 1 @trackingCode = trackingCode
FROM tblSerial ( READPAST, ROWLOCK)
ORDER BY cTime
UPDATE tblSerial
SET ctime=GETDATE()
WHERE trackingcode=@trackingcode
COMMIT TRANSACTION getnextcode
-- More code that uses @trackingCode
I tried to combine both statements into one...I still need to use @trackingCode in more code that follows...
-- New
BEGIN TRANSACTION getnextcode
declare @trackingCode char(4)
UPDATE tblSerial
SET ctime=GETDATE()
WHERE trackingCode = (SELECT TOP 1 @trackingCode = trackingCode
FROM tblSerial ( READPAST, ROWLOCK)
ORDER BY cTime)
COMMIT TRANSACTION getnextcode
but get error: Incorrect syntax near '='
Thanks in advance for your help.
-
is it mandatory for you to use the @trackingcode variable. just remove that from ur code and use this it will surely work.
update tblserial set ctime = getdate() where trackingcode in
(SELECT TOP 1 trackingCode
FROM tblSerial ( READPAST, ROWLOCK) ORDER BY cTime)
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Yes, I had removed the @trackingCode variable, but it is mandatory as this is variable is used in more code in this stored procedure...
-
Then how about this?
declare @trackingCode char(4)
select @trackingcode = (top1 trackingCode
FROM tblSerial ( READPAST, ROWLOCK) ORDER BY cTime)
UPDATE tblSerial
SET ctime=GETDATE()
WHERE trackingCode = @trackingCode
John
-
The last sql statements provided:
declare @trackingCode char(4)
select @trackingcode = (top1 trackingCode
FROM tblSerial ( READPAST, ROWLOCK) ORDER BY cTime)
UPDATE tblSerial
SET ctime=GETDATE()
WHERE trackingCode = @trackingCode
I assume "top1" should be "top 1"; of which still gets error: Incorrect syntax near the keyword 'top'.
And I still need to ensure when this stored procedure is run at the same time , that the top 1 trackingCode is not the same. Thanks again.
-
Richard
You're right - TOP 1. And, on seeing it again, I don't think you need the parentheses round TOP 1 ... cTime.
I don't understand what you mean about the top 1 trackingcode not being the same... not the same as what?
John
-
For starters, the syntax would be
select top 1 @trackingcode = trackingCode
FROM tblSerial ( READPAST, ROWLOCK)
ORDER BY cTime
--
UPDATE tblSerial
SET ctime=GETDATE()
WHERE trackingCode = @trackingCode
However, I believe that you want to retrieve a row from the tblSerial table and then simultaneously
a) use the retrieved value, and
b) insert a new value in to the table for other users to use.
I can see a couple of solutions, but the easiest may be to use an identity column. You can insert in to the table and use the scope_identity() function to get the automatically generated identity value. These values will not conflict even when multiple users are performing the insert. You are still able to obtain the latest value, but there won't be a time element.... This could be recitifed by still including a datetime field in the table and populating it with the value of getDate() during the insert to the table.
Alternatively you may need to use exclusive locks when performing the initial data selection but this will introduce contention issues, depending on how long the subsequent processing in the transaction takes.
Good luck - let us know how you go.
-
I still had error: Incorrect syntax near top.
I changed to use "set" and this seams to work but not much different from my original code. If I have multiple applications running and evoking this stored procedure at the same time, then each application could possibly return the same top 1 trackingCode value; because the select top 1 does not lock the record.
declare @trackingCode char(4)
set @trackingcode = (select top 1 trackingCode
FROM tblSerial ( READPAST, ROWLOCK) ORDER BY cTime)
UPDATE tblSerial
SET ctime=GETDATE()
WHERE trackingCode = @trackingCode
xxx
-
For code like this to work you need to use UPDLOCK, or XLOCK, and have an index on cTime.
BEGIN TRANSACTION getnextcode
declare @trackingCode char(4)
SELECT TOP 1 @trackingCode = trackingCode
FROM tblSerial (UPDLOCK, READPAST)
ORDER BY cTime
UPDATE tblSerial
SET ctime=GETDATE()
WHERE trackingcode = @trackingCode
COMMIT TRANSACTION getnextcode
N.B. If this is a nested transaction, the locks will be held until the outermost transaction is committed.
-
THANKS!!
I created an index for cTime.
What is the difference between ROWLOCK and UPDLOCK?
I am assumming since READPAST is also used, I will not get any deadlocks??
-
BOL gives good documentation on locking. In brief it appears you were confused between the lock granularity, ROWLOCK, PAGLOCK, TABLOCK etc and the lock type, shared, UPDLOCK, XLOCK etc.
In your original query:
SELECT TOP 1 @trackingCode = trackingCode
FROM dbo.tblSerial WITH (READPAST, ROWLOCK)
ORDER BY cTime
the ROWLOCK is only a hint to the optimizer to lock the row, not the page or table. (It would probably have used a ROWLOCK anyway especially if there was an index on the ordered column, cTime.) As a SELECT only reads, a shared (read) lock will be applied by default. A shared lock allows other transactions to read the same row and that was why multiple transactions were getting the same trackingCode.
When the query is changed to FROM dbo.tbleSerial WITH (UPDLOCK), an UPDLOCK will be applied to the row. An UPDLOCK can only be applied if no other transaction has an UPLOCK or XLOCK. This means that a second transaction would have to wait for the first UPDLOCK to be released before it could apply an UPDLOCK. If the READPAST hint is also used, then the second transaction would not wait for the UPDLOCK to be released but would read past the UPDLOCK and apply an UPDLOCK to the first row that did not have an UPDLOCK or XLOCK. The UPDLOCK here allows greater concurrency than a XLOCK as other transactions will be able to place shared locks. Using READPAST means that you are not worried about a second transaction getting the second lowest value if the first transaction has to be rolled back.
When the UPDATE is done the UPDLOCK is promoted to a XLOCK. This could potentially be another source of blocking as this can only be done if there are no shared locks on the row. In reality this is only likely to be a problem if you have long report transactions running at an isolation level of REPEATABLE READ or above.
The COMMIT will release the locks. I presume your code will roll back the transaction if it fails for any reason. If your code is not doing this there is the potential for locks being held for a very long time. Rollback can be done using SET XACT_ABORT, TRY/CATCH, inline error handling etc.
You had better read up on deadlocks as your code only has the possibility for blocking and not a deadlock.
-
Thanks again for your explaination. The BOL describes these hints, but I really did not understand them. Yes, I will have to read more about the Dead Lock issue, the current script does not roll back. Thanks again for your help.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply