Error storing tag values

Ignition
1

I been getting an intermedia message but don’t know what it actually means, can someone help?

1:45:00 PM
TagStore
Error storing tag values.
java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1237)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:936)
at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeBatch(DelegatingStatement.java:59)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeBatch(SRConnectionWrapper.java:629)
at com.inductiveautomation.ignition.gateway.sqltags.providers.datasource.tagstore.DBTagStore$BatchStatement.finish(DBTagStore.java:3155)
at com.inductiveautomation.ignition.gateway.sqltags.providers.datasource.tagstore.DBTagStore.internalStoreTagValues(DBTagStore.java:1380)
at com.inductiveautomation.ignition.gateway.sqltags.providers.datasource.tagstore.DBTagStore.storeTagValues(DBTagStore.java:1257)
at com.inductiveautomation.ignition.gateway.sqltags.providers.AbstractStoreBasedTagProvider.tagValuesChanged(AbstractStoreBasedTagProvider.java:1918)
at com.inductiveautomation.ignition.gateway.sqltags.scanclasses.SimpleExecutableScanClass$ScanClassTagEvaluationContext.processAndReset(SimpleExecutableScanClass.java:1122)
at com.inductiveautomation.ignition.gateway.sqltags.scanclasses.SimpleExecutableScanClass.run(SimpleExecutableScanClass.java:863)
at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:573)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask$Sync.innerRunAndReset(Unknown Source)
at java.util.concurrent.FutureTask.runAndReset(Unknown Source)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101(Unknown Source)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.runPeriodic(Unknown Source)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

2

You have a deadlock. What version of Ignition are you using?

3

Windows 7 64bit
Ignition Version 7.5.6 (b1317)
Java Version 6 Update 31
MySQL 5.5 64bit

What does a deadlock mean?

4

It's when process A requests something from process B and waits for a answer before continuing. Meanwhile, process B asks process A for something and waits for process A to answer before continuing.

For a longer definition see here.

5

Hi,

In this case we should clarify things a bit. You’re running into database deadlock detection, which is quite a bit different than if you were to run into a real deadlock in Ignition.

Basically, in cases like this, it tends to go along the lines of one part of the system trying to read the data of the table at the same time that another part is writing. When one of those operations is taking a long time, the database may say that it’s a “deadlock”, and will throw the exception to break the wait.

Since the error indicates that it’s trying to update tag values, my guess is that you probably have many tags in the external provider, and that perhaps every once in a while (and certainly on startup) many of them are changing and need to be written. The write could take some time, and at the same time, the system is trying to poll for changes.

How many tags do you have? If you go to Status>Database connections, and turn on “enable live values”, do you see some queries lingering for seconds? If so, how long do they normally last? Can you stop the “live values” and copy them in here?

Ultimately, the goal would be to reduce the contention by speeding up the database (through various means), or reducing the number of writes, or the poll frequency.

Has this always done this, or did it just start?

Regards,

6

[quote=“Colby.Clegg”]Hi,

In this case we should clarify things a bit. You’re running into database deadlock detection, which is quite a bit different than if you were to run into a real deadlock in Ignition.

Basically, in cases like this, it tends to go along the lines of one part of the system trying to read the data of the table at the same time that another part is writing. When one of those operations is taking a long time, the database may say that it’s a “deadlock”, and will throw the exception to break the wait.

Since the error indicates that it’s trying to update tag values, my guess is that you probably have many tags in the external provider, and that perhaps every once in a while (and certainly on startup) many of them are changing and need to be written. The write could take some time, and at the same time, the system is trying to poll for changes.

How many tags do you have? If you go to Status>Database connections, and turn on “enable live values”, do you see some queries lingering for seconds? If so, how long do they normally last? Can you stop the “live values” and copy them in here?

Ultimately, the goal would be to reduce the contention by speeding up the database (through various means), or reducing the number of writes, or the poll frequency.

Has this always done this, or did it just start?

Regards,[/quote]

I only have 717 Tags, with two scan rates. Default 1sec, Fast 250ms. Why so fast you might ask, well I have to scan a barcode and do a query and return the value back to the PLC in a very short time.
The Fast Rate only has 17 Tags.

Originally I had set the Realtime SQLTags to be Database Driving since than I changed it to Internal Provider and I haven’t had the issues described before.

7

Hi,

The rates are OK, the question would probably be “how many values are changing each cycle”? Adjusting the tag deadbands could help.

However, if I understand, you’re now using the internal provider, so this is no longer an issue… right? (the internal provider doesn’t run into this issue, because it doesn’t have to store so many values)

Regards,

8

[quote=“Colby.Clegg”]Hi,

The rates are OK, the question would probably be “how many values are changing each cycle”? Adjusting the tag deadbands could help.

However, if I understand, you’re now using the internal provider, so this is no longer an issue… right? (the internal provider doesn’t run into this issue, because it doesn’t have to store so many values)

Regards,[/quote]

That is correct I’m using the internal provider.