# SQL Transactions rarely not committing to tables

**URL:** <https://forum.inductiveautomation.com/t/sql-transactions-rarely-not-committing-to-tables/108232>\
**Category:** Ignition\
**Created:** [July 8, 2025, 2:41pm UTC](https://forum.inductiveautomation.com/t/sql-transactions-rarely-not-committing-to-tables/108232 "2025-07-08T14:41:18Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![alex.chmelka](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alex.chmelka/32/26672_2.png) [@alex.chmelka](https://forum.inductiveautomation.com/u/alex.chmelka)\
**Post date:** [July 8, 2025, 2:41pm UTC](https://forum.inductiveautomation.com/t/sql-transactions-rarely-not-committing-to-tables/108232/1 "2025-07-08T14:41:18Z")

</div>

Hello there. My team is running into a very challenging issue that we haven't been able to resolve completely. We have a kiosk where a user requests a serial number. The screen looks for the next incrementable serial number for the part, returns the serial number, and IF a valid serial number is returned, will send a print request to a label printer to attach to a document for the part. Below is heavily redacted snippets of our sql meant to show our transaction methodology.

```python
--
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @SerialNumber TABLE ([SerialNumber] VARCHAR(46),RN INT);

BEGIN TRANSACTION TR1
BEGIN TRY
IF <STATEMENT IS TRUE>
INSERT INTO <TABLE> (SerialNumber)
	OUTPUT INSERTED.[SerialNumber] 
		INTO @SerialNumber([SerialNumber])
	<SELECT STATEMENT>
	COMMIT TRANSACTION TR1
END
ELSE 
-- Roll back transaction and return error code
BEGIN
	ROLLBACK TRANSACTION TR1
	INSERT INTO @SerialNumber([SerialNumber],[RN]) VALUES (-1,0)
END

SELECT TOP 1 
	[SerialNumber]
FROM (
	SELECT T.[SerialNumber],T.[RN]
	FROM @SerialNumber T

) T
ORDER BY [RN] ASC
END TRY

-- Catch errors, roll back transaction, and return error code
BEGIN CATCH
	BEGIN TRY
		ROLLBACK TRANSACTION TR1
	END TRY
	BEGIN CATCH
	END CATCH
	SELECT TOP 1 CAST('-1' AS VARCHAR) [SerialNumber]
END CATCH

```

We run this via a named query execution system.db.runNamedQuery. If the query functions as expected, it returns the created serial number. If a non valid serial number returns, such as -1, the script will not continue, and will warn the user without printing a label.

This functions the vast majority of the time. However, at unpredictable, rare moments, this query will return the created serial number without actually inserting the returned row to the target table. This is allowing the user to print labels of serial numbers that are not actually being entered into the database, causing big headaches with traceability. The query does not fail when this happens, making it very difficult to actually catch whatever is happening and when it happens.

I was wondering if perhaps we were having transactions not committing to the database and if anyone else has run into this problem. Could it be our isolation level? Do transactions within Ignition SQL cause potential issues with the driver? Thanks for any help. I can try and give more details on our full query if that helps.

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [July 8, 2025, 2:48pm UTC](https://forum.inductiveautomation.com/t/sql-transactions-rarely-not-committing-to-tables/108232/2 "2025-07-08T14:48:42Z")

</div>

It has been a while since I used transactions but I "believe" the BEGIN TRANSACTION should be INSIDE the BEGIN TRY and then utilize a BEGIN CATCH to return any error codes?

---

<div class="post-metadata">

**Author:** ![nicole.c](https://avatars.discourse-cdn.com/v4/letter/n/43a26b/32.png) [@nicole.c](https://forum.inductiveautomation.com/u/nicole.c)\
**Post date:** [July 8, 2025, 9:38pm UTC](https://forum.inductiveautomation.com/t/sql-transactions-rarely-not-committing-to-tables/108232/3 "2025-07-08T21:38:22Z")

</div>

I'm not super familiar with the interactions between the OUTPUT, table variables, and the TRY-CATCH but reading some documentation suggests that the OUTPUT value may still be inserted into the table variable there if the insert fails. I'm not sure how it might get returned to the client if the try-catch errors but maybe it's happening in some other part of your code?

Have you tried logging some of the errors in the CATCH statements? I imagine it might just be unique constraint errors but it might help.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [July 8, 2025, 9:46pm UTC](https://forum.inductiveautomation.com/t/sql-transactions-rarely-not-committing-to-tables/108232/4 "2025-07-08T21:46:16Z")

</div>

Consider ditching the script. Just ensure that there is a unique index on that column, so a simple INSERT statement will fail on duplicate. Catch the error. No transaction needed.

---

<div class="post-metadata">

**Author:** ![alex.chmelka](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alex.chmelka/32/26672_2.png) [@alex.chmelka](https://forum.inductiveautomation.com/u/alex.chmelka)\
**Post date:** [July 8, 2025, 9:58pm UTC](https://forum.inductiveautomation.com/t/sql-transactions-rarely-not-committing-to-tables/108232/5 "2025-07-08T21:58:05Z")

</div>

Tried this, but this table has multiple primary keys, making getKey() not viable. I removed all try-catches and removed the transactions, instead using a namedQuerytransaction to handle it.

However, I still needed to have a select statement to get the output of the insert. Can the JDBC driver really not handle multiple SQL "executions" well as long as the select output is consistent? We have some very heavy queries all throughout our Ignition projects that have never seemed to have given us issues like this before. I was hoping that layering transactions in the SQL on top of however the JDBC driver handles transactions was the culprit of this specific problem.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [July 8, 2025, 10:33pm UTC](https://forum.inductiveautomation.com/t/sql-transactions-rarely-not-committing-to-tables/108232/6 "2025-07-08T22:33:34Z")

</div>

> [@alex.chmelka](#):
>
> Can the JDBC driver really not handle multiple SQL "executions"

It cannot. Strictly speaking, vanilla JDBC doesn't support scripts like yours at all. That you can run them is a purely brand-specific.

> [@alex.chmelka](#):
>
> this table has multiple primary keys

Perhaps you need [system.db.runPrepInsert()](https://www.automation-pros.com/toolkit/doc/utility.html#runprepinsert), which can return multiple-column keys.

Ooops. Based on your non-standard identifier quoting, it seems like you are using an incompetent DB brand, so maybe not.

But anyways, make a multiple column unique index. Compute the desired new SN in Ignition and simply try to insert it. The unique index will reject dupes for you.

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [July 9, 2025, 12:57am UTC](https://forum.inductiveautomation.com/t/sql-transactions-rarely-not-committing-to-tables/108232/7 "2025-07-09T00:57:33Z")

</div>

Are you using MSSQL?

Can you use a Stored Procedure to run this SQL?

Have you tried a test setup (in the SSMS) where you can run this SQL say 5,000 or 100,000 times and see what happens on each INSERT? Perhaps, as @nicole.c suggested, throwing a SELECT or INSERT statement in the CATCH block might give you something to work with.

Assuming an error IS occurring, SQL Server has error handling functions, as I imagine most DBs do, that can provide additional information.

EDIT: A stored procedure or user-defined function can calculate and return the next viable Serial Number as well.

---

<div class="post-metadata">

**Author:** ![nicole.c](https://avatars.discourse-cdn.com/v4/letter/n/43a26b/32.png) [@nicole.c](https://forum.inductiveautomation.com/u/nicole.c)\
**Post date:** [July 9, 2025, 12:57pm UTC](https://forum.inductiveautomation.com/t/sql-transactions-rarely-not-committing-to-tables/108232/8 "2025-07-09T12:57:19Z")

</div>

I don't think there's an inherent problem with using the transactions within SQL and the JDBC driver, it's mostly down to the design of this particular code and how it was handling errors. Practically, your code needs to consider what will happen in an error state and any side effects or aftereffects of those errors, and it doesn't look to me like you are. 🙂

---

<div class="post-metadata">

**Author:** ![andrew.brauer](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/andrew.brauer/32/34853_2.png) [@andrew.brauer](https://forum.inductiveautomation.com/u/andrew.brauer)\
**Post date:** [July 9, 2025, 5:52pm UTC](https://forum.inductiveautomation.com/t/sql-transactions-rarely-not-committing-to-tables/108232/9 "2025-07-09T17:52:50Z")

</div>

This logic would be better suited in a stored procedure rather than a named query. Additionally, I recommend placing the `COMMIT` statement at the end of the `TRY` block. This ensures that if an error occurs, the `ROLLBACK` will properly undo all inserts, updates, or deletes within the transaction. I also suggest starting the `BEGIN TRANSACTION` immediately after the `BEGIN TRY` to clearly scope the transaction within the error handling block.

This is a standard template I use for writing a MSSQL stored procedure with `TRY` / `CATCH` and `TRANSACTION` control.

```sql
USE [<dbName>]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author>
-- Create date: <date>
-- Description:	this stored procedure ....
-- =============================================
CREATE PROCEDURE [dbo].[<procName>] (
    @i_param1 AS VARCHAR(50)
    , @i_param2 AS NVARCHAR(MAX)
    )
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

  
    BEGIN TRY
        BEGIN TRAN;
        --
        -- do work here
        -- insert statement 1
        -- insert statement 2
        -- update statement 1
         
        --
        --commit changes
        COMMIT;
    END TRY

    BEGIN CATCH
        -- Rollback any outstanding transactions
        IF @@TRANCOUNT > 0
            ROLLBACK;

        -- Re-throw the original error so that Ignition is notified with the error
        THROW;
    END CATCH;
END;

```

In the `CATCH` block, I include a `ROLLBACK` to undo any changes. Additionally, you can log the error details along with the serial number into an error log table. I also use `THROW` at the end of the `CATCH` block to propagate the error and ensure the caller is notified.