Named Query Deadlocked

Ignition
1

I have a barcode scanner attached to a PLC which we use to scan 3 separate barcodes for each transaction. We then have 3 separate tags in the PLC to act as a buffer to take in each barcode and then these trigger tag change scripts in ignition.
Each tag change script runs a named query which returns information on how the machine should run depending on the barcode.
we are getting errors if the barcodes are scanned quickly though, we get a transaction deadlock error with the named query(see below).

Is there a simple way to build a queue for these named queries? I was thinking about trying to use the object as part of the named query so i can start the next named query when when the previous one has started but then i end up not being able to use the tag change script to trigger the queries.
Thanks,

ava.lang.Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 110) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

2

That almost invariably means your query is doing something bad. AB-BA deadlock bad. You should not be papering over it with a queue. Your DB should be naturally interleaving these queries.

3

The named query is pretty simple

INSERT INTO BarcodeData (Equipment, Barcode, Flag, ScanDate) VALUES (:Equipment, :Barcode, :Flag, :ScanDate)

would this be something that needs adjusting in the DB?

4

I’m going to guess there is a complicated trigger firing in the DB upon insert, and there’s an AB-BA deadlock inside that (which is a form of stored procedure). So yes, you need help from your DBA.

5

Ah ok, there may be… I left that to our DB administrator in IT. I will follow up with him. Thanks for the help