June 12, 2015 at 1:36 am
Morning all,
brief scenario - 25 vms trying to run records from sql - 1 machine aquires a lock through below sp that record runs on that machine, i have an issue where intermittently 2 machines get the same lock, i'm very confused as to why, code that aquires lock as follows: (fyi this is called from .net 4.5 c# app)
begin tran
--get a lock
set @returnval = (select top 1 RecordID from tbl_main where (locked = 0) and (Caseworked = 0) and inprogress = 0)
if (COALESCE(@ReturnVal,0) <> 0)
begin
update tbl_main set locked = 1,LockedBy = @UserName,lockedat = GETDATE(), inprogress = 1 where recordid = @ReturnVal
--add a comment
set @myoutput = 2
end
else
--No records need working
begin
set @returnval = 0
END
commit tran
any help greatly appreciated:-D
June 12, 2015 at 2:00 am
You have concurrency issues because your code reads the recordid before writing to the table and locking it, so multiple connections could read the same recordid under the default transaction isolation level.
This is how I would do it:
SET NOCOUNT,
XACT_ABORT,
QUOTED_IDENTIFIER,
ANSI_NULLS,
ANSI_PADDING,
ANSI_WARNINGS,
ARITHABORT,
CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
BEGIN TRAN;
BEGIN TRY;
WITH rowToLock AS (
SELECT TOP(1) *
FROM tbl_main
WHERE locked = 0
AND Caseworked = 0
AND inprogress = 0
ORDER BY RecordID
)
UPDATE rowToLock
SET locked = 1,
LockedBy = @UserName,
lockedat = GETDATE(),
inprogress = 1,
@returnval = RecordId;
IF (COALESCE(@ReturnVal,0) <> 0)
BEGIN
--add a comment
SET @myoutput = 2;
END
ELSE
BEGIN
--No records need working
SET @returnval = 0;
END
IF XACT_STATE() = 1
COMMIT;
END TRY
BEGIN CATCH;
IF XACT_STATE() <> 0
ROLLBACK;
THROW;
END CATCH
Hope this helps,
Gianluca
-- Gianluca Sartori
June 12, 2015 at 2:03 am
thanks - i have opted for slightly simpiler way of doing it:
using the app lock sp - basically queues up the calls to proc so only 1 user can use it at a time (ensures full isolation)
Exec @rc =sp_getapplock @Resource='GetLock', @LockMode='Exclusive', @LockOwner='Transaction', @LockTimeout = 15000
so far seems to be working, if not my next attempt will be OUTPUT INSERTED on the update statement.
if neither of these work i will try your suggestion as it is probably the right way to do it! 😀
June 12, 2015 at 2:33 am
matthew.green 36969 (6/12/2015)
thanks - i have opted for slightly simpiler way of doing it:using the app lock sp - basically queues up the calls to proc so only 1 user can use it at a time (ensures full isolation)
Exec @rc =sp_getapplock @Resource='GetLock', @LockMode='Exclusive', @LockOwner='Transaction', @LockTimeout = 15000
so far seems to be working, if not my next attempt will be OUTPUT INSERTED on the update statement.
if neither of these work i will try your suggestion as it is probably the right way to do it! 😀
Slightly simpler? You're shooting ants with a rifle 😀
sp_getapplock should be used when no other locking mechanism is working, which is not the case here.
Anyway, whatever works for you...
-- Gianluca Sartori
June 12, 2015 at 2:49 am
mutant ants 😀 - any guidance on why it shouldnt be used (does it have monsterous overhead or something), or is it just as it forces 1 read at a time as opposed to concurrent hence exta milliseconds delay per case?
June 12, 2015 at 3:08 am
No, nothing particular, but I tend to avoid it when I can achieve the same with row locks.
-- Gianluca Sartori
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply