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.