[Question] Canceling active queries on the gateway

Ignition
1

I'd like to understand what happens when we hit the Cancel button on the gateway, [Connections - Databases].

image
image1646×513 28.4 KB

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")
system.db.execSProcCall(sp)

Here's a snippet of the procedure:

BEGIN
SET NOCOUNT ON;
DECLARE @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;

BEGIN TRANSACTION
BEGIN TRY
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

END TRY
BEGIN CATCH
select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
ROLLBACK TRANSACTION;
exec dbo.sys_exception_msg;
END CATCH
END

2

When you press the cancel button, we issue a cancel request to the database statement. However, both the DBMS and the JDBC driver need to support cancelling, and we get no information back on whether the cancellation is supported or happened.

2 Likes