I’d like to understand what happens when we hit the Cancel button on the gateway, [Connections - Databases].
My issue here was:
I’m running a stored procedure from a timer script on the gateway. This procedure has an exclusive lock request on the database (SQL Server). We did this to prevent multiple gateways from running the same procedure at the same time. All of this works fine. Yesterday I noticed that the procedure was too slow, so I decided to cancel it from the gateway. It went away from the list of active queries however the transaction got lost somewhere because it was still holding the lock. I would assume canceling would do it. So after altering the procedure and running it again, it couldn’t acquire a lock because there was this ghost process/transaction or something holding the lock. The only solution I found was killing the process on the database.
So I’d like to understand what are the actions taken by the gateway when this button is hit so I can treat it properly on the database (exit the procedure, commit/rollback and release the lock).
Here’s the timer script:
sp = system.db.createSProcCall(“sp_alan_test”, “DB”)
Here’s a snippet of the procedure:
SET NOCOUNT ON;
DECLARE @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
DECLARE @lock_result int;
EXEC @lock_result = sp_getapplock @Resource = ‘sp_alan_test’, @LockMode = ‘Exclusive’, @LockTimeout = 5000
IF @lock_result < 0 raiserror ('Lock could not be acquired for sp_alan_test.',18,1); waitfor delay '00:00:15' COMMIT TRANSACTION
select @ErrorMessage = ERROR_MESSAGE() + ’ Line ’ + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();