# SQL Transactions

**URL:** <https://forum.inductiveautomation.com/t/sql-transactions/2384>\
**Category:** Ignition\
**Created:** [December 3, 2010, 3:18pm UTC](https://forum.inductiveautomation.com/t/sql-transactions/2384 "2010-12-03T15:18:33Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Step7](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@Step7](https://forum.inductiveautomation.com/u/Step7)\
**Post date:** [December 3, 2010, 3:18pm UTC](https://forum.inductiveautomation.com/t/sql-transactions/2384/1 "2010-12-03T15:18:33Z")

</div>

I need some clarification on something.

I’ve got a situation where throughout my code, I will build up a collection of update and insert queries and run them all at once every couple of seconds. So, I might build a string like this:

```auto
BEGIN TRANSACTION
UPDATE Table1 SET Status = 8 WHERE Quantity = 0 
UPDATE Table2 SET Status = 1 WHERE Quantity > 0 
COMMIT
BEGIN TRANSACTION
INSERT INTO Table1 (ProductID,Target) VALUES (17,1000)
UPDATE Table1 SET Start = getdate() WHERE ProductID=17
COMMIT
```

I’ll follow this up with this:

```auto
try:
    system.db.runUpdateQuery(q)
    q=''
except:
    print 'Query Failed"
```

What I’m wondering is if in theory I would be better off combining all of the transactions into one transaction, so my string looked like this:

```auto
BEGIN TRANSACTION
UPDATE Table1 SET Status = 8 WHERE Quantity = 0 
UPDATE Table2 SET Status = 1 WHERE Quantity > 0 
INSERT INTO Table1 (ProductID,Target) VALUES (17,1000)
UPDATE Table1 SET Start = getdate() WHERE ProductID=17
COMMIT
```

What isn’t clear to me is how exactly your system.db.runUpdateQuery works. In the first case, if the first transaction succeeds and the second fails, does your function have its own “COMMIT” and roll back both? If not, it seems like I definitely need to strip out all but the first and last BEGIN TRANSACTION and COMMIT, since I wouldn’t know which part of the query failed.

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [December 3, 2010, 4:30pm UTC](https://forum.inductiveautomation.com/t/sql-transactions/2384/2 "2010-12-03T16:30:17Z")

</div>

Well, in order to use transactions you have to use the system.db transaction functions. Here is what you need to do: [code]txId = system.db.beginTransaction(timeout=5000)  
system.db.runPrepUpdate(“UPDATE Table1 SET Status = 8 WHERE Quantity = 0”, args=[], tx=txId)  
system.db.runPrepUpdate("UPDATE Table2 SET Status = 1 WHERE Quantity \> 0 ", args=[], tx=txId)  
system.db.commitTransaction(txId)  
system.db.closeTransaction(txId)

txId = system.db.beginTransaction(timeout=5000)  
system.db.runPrepUpdate(“INSERT INTO Table1 (ProductID,Target) VALUES (17,1000)”, args=[], tx=txId)  
system.db.runPrepUpdate(“UPDATE Table1 SET Start = getdate() WHERE ProductID=17”, args=[], tx=txId)  
system.db.commitTransaction(txId)  
system.db.closeTransaction(txId)[/code]The system.db.runUpdateQuery is only made to run a single query or a stored procedure. You could have put this logic in a stored procedure as well.