February 7, 2006 at 11:50 am
We have multiple instances of VB code that executes the following stored procedure. It appears that the same 'trackingcode' is used. I need to ensure Record Locking to avoid this issue.
SELECT TOP
1 @trackingCode = trackingCode
FROM tblSerial ORDER BY cTime
We tried the below table Hints but still have issues.
SELECT TOP
1 @trackingCode = trackingCode
FROM tblSerial ( READPAST, ROWLOCK)
ORDER BY cTime
How can I ensure the select statements locks the record until it is updated later in the code? Thanks for the help.
February 7, 2006 at 12:09 pm
Hello Richard,
Can you run these two queries simultaneously and check whether the locked record is fetched or not in the second query?
Select top 1 @trackingcode = trackingcode from tblSerial with Readpast order by cTime
Select * from tblSerial order by cTime
Thanks and have a nice day!!!
Lucky
February 7, 2006 at 12:52 pm
I ran both queries and the second did fetch the record.
I also tried to add the 'BEGIN TRANSACTION' and 'COMMIT' statements; but that did not help.
February 7, 2006 at 3:16 pm
February 8, 2006 at 7:11 am
This is the latest code:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT TOP 1 trackingCode
FROM tblSerial ( READPAST, ROWLOCK)
ORDER BY cTime
COMMIT TRANSACTION
I get the following error:
Server: Msg 650, Level 16, State 1, Line 5
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.
February 8, 2006 at 3:21 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply