Is this SQL statement concurrent safe?
Is the SQL statement below "safe" for concurrent access? At what point will it close A
? If before INSERT
there is a chance that the first one @count
might be wrong?
BEGIN TRAN;
SELECT @count = count(1) from A
DELETE FROM A where x=z
SELECT @newCount = count(1) from A
SELECT @newCount - @count
COMMIT TRAN;
+3
source to share
1 answer
BEGIN TRAN;
DECLARE @RC INT
EXEC @RC = sp_getapplock @Resource='ArchiveLock', @LockMode='Exclusive', @LockOwner='Transaction', @LockTimeout=15000
SELECT @count = count(1) from A
DELETE FROM A where x=z
SELECT @newCount = count(1) from A
SELECT @newCount - @count
COMMIT TRAN;
Try it. I used this recently to handle a lot of Delet statements that are called by multiple processes in parallel. Sp_getapplock creates a lock until the transaction is completed, which will make another process wait.
Hope this helps!
+1
source to share