# \[Question\] Canceling active queries on the gateway

**URL:** <https://forum.inductiveautomation.com/t/question-canceling-active-queries-on-the-gateway/26026>\
**Category:** Ignition\
**Created:** [June 21, 2019, 8:56pm UTC](https://forum.inductiveautomation.com/t/question-canceling-active-queries-on-the-gateway/26026 "2019-06-21T20:56:18Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![alan\_pereira](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alan_pereira/32/79668_2.png) [@alan\_pereira](https://forum.inductiveautomation.com/u/alan_pereira)\
**Post date:** [June 21, 2019, 8:56pm UTC](https://forum.inductiveautomation.com/t/question-canceling-active-queries-on-the-gateway/26026/1 "2019-06-21T20:56:18Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/b/bbff8636260a5855839f3c9991431d7972e74e1a.png)

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

---

<div class="post-metadata">

**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\
**Post date:** [June 21, 2019, 10:40pm UTC](https://forum.inductiveautomation.com/t/question-canceling-active-queries-on-the-gateway/26026/2 "2019-06-21T22:40:13Z")

</div>

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.