April 20, 2004 at 10:16 am
Hi,
I have the following in a stored procedure
BEGIN TRAN
DECLARE @batchno varchar(15)
DECLARE @seqno int
SELECT TOP 1 @batchno = d.batchno, @seqno = seqno
FROM detail AS d
INNER JOIN batch AS b
ON d.batchno = b.batchno
AND b.value = 3
AND d.flag = 'Y'
UPDATE detail
SET flag = 'A'
WHERE batchno = @batchno
AND seqno = @seqno
SELECT *
FROM detail
WHERE batchno = @batchno
AND seqno = @seqno
COMMIT TRAN
After certain number of concurrent users, I get a deadlock situation.
Can some one tell me how I can avoid the dead lock situation.
Thanks,
Senthil.
April 21, 2004 at 7:45 am
I changed the stored procedure as follows and it works fine now.
DECLARE @batchno varchar(15)
DECLARE @seqno int
SELECT TOP 1 @batchno = d.batchno, @seqno = seqno
FROM detail AS d WITH (UPDLOCK, READPAST)
INNER JOIN batch AS b
ON d.batchno = b.batchno
AND b.value = 3
AND d.flag = 'Y'
UPDATE detail
SET flag = 'A'
WHERE batchno = @batchno
AND seqno = @seqno
SELECT *
FROM detail
WHERE batchno = @batchno
AND seqno = @seqno
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply