Ignore the red circle 
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
Should I be using a system function instead of making my own query?
Thanks for any info.
Explicit transactions via our system function are ‘bridging’ to calls to the Java Database Connector 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.
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:
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.
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:
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.
Isn’t there a way to lock the db table while the query execute etc?
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.
Do you really want nobody else to query those tables or do you just want nobody to see partially inserted data?
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.
If they are using two API calls, then they need to use a transaction, too. Yours might actually be working.
If they make 2 API calls to barcode=12345 then I think the blame lies with me though…
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?
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
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.
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.