# \[SOLVED\] Does Ignition allow the user to create SQL EXPLICIT TRANSACTIONS?

**URL:** <https://forum.inductiveautomation.com/t/solved-does-ignition-allow-the-user-to-create-sql-explicit-transactions/50362>\
**Category:** Ignition\
**Tags:** ignition79, database, scripting, vision\
**Created:** [August 26, 2021, 10:20pm UTC](https://forum.inductiveautomation.com/t/solved-does-ignition-allow-the-user-to-create-sql-explicit-transactions/50362 "2021-08-26T22:20:41Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![bmeyers](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bmeyers/32/37453_2.png) [@bmeyers](https://forum.inductiveautomation.com/u/bmeyers)\
**Post date:** [August 26, 2021, 10:20pm UTC](https://forum.inductiveautomation.com/t/solved-does-ignition-allow-the-user-to-create-sql-explicit-transactions/50362/1 "2021-08-26T22:20:41Z")

</div>

Ignore the red circle 🙂

 ![code](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/1/51c22126a91e1637afa2a265671bdd3bca5b028b.jpeg)

I have created some BEGIN TRAN / COMMIT TRAN queries I run on a tag change script and the customer is showing me evidence the transactions are not ATOMIC… this got me looking at Ignition system functions and I found this one:

[https://docs.inductiveautomation.com/display/DOC/system.db.beginTransaction](https://docs.inductiveautomation.com/display/DOC/system.db.beginTransaction)

Should I be using a system function instead of making my own query?

Thanks for any info.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [August 26, 2021, 10:45pm UTC](https://forum.inductiveautomation.com/t/solved-does-ignition-allow-the-user-to-create-sql-explicit-transactions/50362/2 "2021-08-26T22:45:48Z")

</div>

Explicit transactions via our system function are ‘bridging’ to calls to the **J** ava **D** ata **b** ase **C** onnector API, which itself uses drivers (per database) with ‘guarantees’ about transactions. I’d consider it best practice to use explicit transactions in code, rather than relying on the query string you submit to be wrapped correctly.

---

<div class="post-metadata">

**Author:** ![bmeyers](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bmeyers/32/37453_2.png) [@bmeyers](https://forum.inductiveautomation.com/u/bmeyers)\
**Post date:** [August 27, 2021, 1:18pm UTC](https://forum.inductiveautomation.com/t/solved-does-ignition-allow-the-user-to-create-sql-explicit-transactions/50362/3 "2021-08-27T13:18:13Z")

</div>

> [@paul-griffith](#):
>
> I’d consider it best practice to use explicit transactions in code, rather than relying on the query string you

Is there any way to prove my SQL query is not being wrapped correctly? Obviously, no errors are appearing and the data is being updated eventually... I am fine converting to something like the example in the Ignition docs:

```auto
txId =system.db.beginTransaction(timeout=5000)
status=2

for machineId in range(8):
    system.db.runPrepUpdate("UPDATE MachineStatus SET status=? WHERE ID=?",args=[status, machineId], tx=txId)

system.db.commitTransaction(txId)
system.db.closeTransaction(txId)
```

I just wish there was more feedback that I shouldn't be sending SQL queries this way.

---

<div class="post-metadata">

**Author:** ![bmeyers](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bmeyers/32/37453_2.png) [@bmeyers](https://forum.inductiveautomation.com/u/bmeyers)\
**Post date:** [August 27, 2021, 1:26pm UTC](https://forum.inductiveautomation.com/t/solved-does-ignition-allow-the-user-to-create-sql-explicit-transactions/50362/4 "2021-08-27T13:26:35Z")

</div>

[https://docs.inductiveautomation.com/display/DOC79/system.db.beginTransaction](https://docs.inductiveautomation.com/display/DOC79/system.db.beginTransaction)

Based on the document it sounds like databases can have multiple connections. Does that mean I can’t update data atomically without changing maximum connections?

My root problem is I want to execute the following code atomically:

```auto
INSERT INTO table1 (col1, col2, col3) VALUES (x, y, z);
INSERT INTO table2 (col4, col5, col6) VALUES (a, b, c);
INSERT INTO table3 (col8, col8, col9) VALUES (e, f, g);
```

and I don’t want anyone reading those tables until I am done.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [August 27, 2021, 1:35pm UTC](https://forum.inductiveautomation.com/t/solved-does-ignition-allow-the-user-to-create-sql-explicit-transactions/50362/5 "2021-08-27T13:35:47Z")

</div>

Isn’t there a way to lock the db table while the query execute etc?

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [August 27, 2021, 1:41pm UTC](https://forum.inductiveautomation.com/t/solved-does-ignition-allow-the-user-to-create-sql-explicit-transactions/50362/6 "2021-08-27T13:41:22Z")

</div>

The SQL Server docs are pretty clear that either of these methods should work (`BEGIN TRAN` in your query or JDBC transactions) as long as you don't try to use both at once.

> [@bmeyers](#):
>
> My root problem is I want to execute the following code atomically:
> 
> ```auto
> INSERT INTO table1 (col1, col2, col3) VALUES (x, y, z);
> INSERT INTO table2 (col4, col5, col6) VALUES (a, b, c);
> INSERT INTO table3 (col8, col8, col9) VALUES (e, f, g);
> 
> ```
> 
> and I don’t want anyone reading those tables until I am done.

Do you really want nobody else to query those tables or do you just want nobody to see partially inserted data?

---

<div class="post-metadata">

**Author:** ![bmeyers](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bmeyers/32/37453_2.png) [@bmeyers](https://forum.inductiveautomation.com/u/bmeyers)\
**Post date:** [August 27, 2021, 2:03pm UTC](https://forum.inductiveautomation.com/t/solved-does-ignition-allow-the-user-to-create-sql-explicit-transactions/50362/7 "2021-08-27T14:03:28Z")

</div>

The customer has an **Inventory** table and a **Details** table.

I update the Inventory table with a count of what should be in the details table. I then insert into the details table rows equal to the count.

The customer then has 2 API calls; 1 to the Inventory and 1 to the Details. They are getting some API calls where 1 table is updated and the other isn’t.

So we added BEGIN TRAN and COMMIT TRAN to all the SQL queries in the hopes our updates would become atomic but they got another error message recently suggesting the atomic updates are not working.

---

<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:** [August 27, 2021, 2:05pm UTC](https://forum.inductiveautomation.com/t/solved-does-ignition-allow-the-user-to-create-sql-explicit-transactions/50362/8 "2021-08-27T14:05:10Z")

</div>

If they are using two API calls, then they need to use a transaction, too. Yours might actually be working.

---

<div class="post-metadata">

**Author:** ![bmeyers](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bmeyers/32/37453_2.png) [@bmeyers](https://forum.inductiveautomation.com/u/bmeyers)\
**Post date:** [August 27, 2021, 2:06pm UTC](https://forum.inductiveautomation.com/t/solved-does-ignition-allow-the-user-to-create-sql-explicit-transactions/50362/9 "2021-08-27T14:06:23Z")

</div>

If they make 2 API calls to barcode=12345 then I think the blame lies with me though…

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [August 27, 2021, 2:07pm UTC](https://forum.inductiveautomation.com/t/solved-does-ignition-allow-the-user-to-create-sql-explicit-transactions/50362/10 "2021-08-27T14:07:20Z")

</div>

How do you mean? They make 2 calls; 1 reads data from before your transaction, the other reads data from after. Wouldn’t you expect that to be wrong? Or did barcode=12345 get inserted by your transaction and not exist prior?

---

<div class="post-metadata">

**Author:** ![bmeyers](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bmeyers/32/37453_2.png) [@bmeyers](https://forum.inductiveautomation.com/u/bmeyers)\
**Post date:** [August 27, 2021, 2:11pm UTC](https://forum.inductiveautomation.com/t/solved-does-ignition-allow-the-user-to-create-sql-explicit-transactions/50362/11 "2021-08-27T14:11:04Z")

</div>

Ahhh, I miscommunicated what they are reading from the API calls. They are seeing a count of 24 for example and zero details come in. They claim they are calling the Inventory API before the Details API.

Could their API calls be executed out of order? It is a 3rd party software calling the API through the web-dev Ignition module.

ignitionserver001/main/webdev/Inventory?barcode=12345  
ignitionserver001/main/webdev/Details?barcode=12345

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [August 27, 2021, 2:12pm UTC](https://forum.inductiveautomation.com/t/solved-does-ignition-allow-the-user-to-create-sql-explicit-transactions/50362/12 "2021-08-27T14:12:09Z")

</div>

> [@bmeyers](#):
>
> Could their API calls be executed out of order? It is a 3rd party software calling the API through the web-dev Ignition module.

If they call them concurrently there is no ordering guarantee. If they call API 1, wait for the result, then call API 2, it will of course be in order.

---

<div class="post-metadata">

**Author:** ![bmeyers](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bmeyers/32/37453_2.png) [@bmeyers](https://forum.inductiveautomation.com/u/bmeyers)\
**Post date:** [August 27, 2021, 2:14pm UTC](https://forum.inductiveautomation.com/t/solved-does-ignition-allow-the-user-to-create-sql-explicit-transactions/50362/13 "2021-08-27T14:14:54Z")

</div>

> [@Kevin.Herron](#):
>
> If they call them concurrently there is no ordering guarantee. If they call API 1, wait for the result, then call API 2, it will of course be in order.

This might be the problem. I'll reach out to the customer and see if they can verify how they make API calls.

---

<div class="post-metadata">

**Author:** ![bmeyers](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bmeyers/32/37453_2.png) [@bmeyers](https://forum.inductiveautomation.com/u/bmeyers)\
**Post date:** [August 27, 2021, 3:12pm UTC](https://forum.inductiveautomation.com/t/solved-does-ignition-allow-the-user-to-create-sql-explicit-transactions/50362/14 "2021-08-27T15:12:16Z")

</div>

The customer has confirmed they are not waiting for a response from API call #1 which explains why this problem is fairly rare. I am going to help them by creating a 3rd API call that combines the 2 original API calls.

Thanks for all your help.