Help with Select Top 1

Question

Help with Select Top 1

Richard Cranston

Hall of Fame

Points: 3680
More actions
January 30, 2007 at 7:09 am

#103309

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.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

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

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 2

Yes, I had removed the @trackingCode variable, but it is mandatory as this is variable is used in more code in this stored procedure...

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

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

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 4

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.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

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

Ian Yates SSCoach Points: 19738 More actions · Answer 6

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.

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 7

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

Ken McKelvey SSCoach Points: 18830 More actions · Answer 8

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.

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 9

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

Ken McKelvey SSCoach Points: 18830 More actions · Answer 10

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.

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 11

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.