SQL Transactions

Ignition
1

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:

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:

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:

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.

2

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.