History Query Deadlocks

Ignition
1

Hey guys,

I’ve been getting these deadlock errors more frequently (I think because I’ve been putting a lot of Sparkline Charts on pages):

HistoryDeadlockError.png1413×490 13.2 KB

Is there a way to add the MSSQL “WITH (NOLOCK)” command when reading and writting from tag history tables? I don’t think the table needs to be locked on transactions since the Ignition server should be the only source writting to those tables correct? It may speed up history transactions as well. I’m no SQL wizard so I’m not sure if that would help or hurt. Just wondering, thanks!

2

Hi,

The real question is probably more along the lines of “what’s causing the deadlocks, and can they be fixed”. Basically, the SCE table has always been a bit touchy. It gets updated a LOT, specifically the “end_time” column, which happens to be indexed. It also references the scid, which isn’t index, so it’ll lock the index and the data to find the correct row. On the query side of things, it’s looking at the scid, start_time, and end_time, which involve the same locks, but possibly in a different order. That’s where the problem comes in. Now, we’ve only seen this become an issue when the table gets somewhat sizable, and the queries start to take longer. In theory, this table shouldn’t get very large- new rows should only be inserted when the system hasn’t run for a bit. But, we’ve seen a variety of situations where this doesn’t happen, and new rows get inserted more than they should.

Ok, first I’ll answer your question: No, there isn’t a way to add the NOLOCK hint. However, you can change the default isolation level for transactions, which can have the same result. The impact is a bit further reaching, since it affects all transactions and not just that query, but I can’t immediately think of a problem with this, as most transactions are used for efficiency, not integrity purposes. Reading uncommitted rows shouldn’t be a problem. This can be changed in the advanced section of the database connection- “read uncommitted” is the setting that is basically equivalent to “NOLOCK”.

Back to my original line of thought: How many rows are in the sqlth_sce table? If more than a few thousand, it might be worth simply clearing it out, starting over, and adjusting the “stale detection multiplier” (if using 7.5) to avoid unnecessary rows. This setting is under SQLTags>Historian>{DB}>Advanced. To “clear out” this table, you can delete all rows, wait for new ones to be inserted (one per scan class), and then update the start_time to be 0. As mentioned, this table is used to detect when Ignition wasn’t running properly, in order to return bad quality instead of flat values, but generally this isn’t a big concern for people.

Let me know what you find,

3

Thanks for the list of things to check, Colby.

I had 9,828 rows in the sqlth_sce table so I cleared that out, waited for all of the scan classes to trigger and then set all of the start_time entries to 0. I also bumped up the “stale detection multiplier” from 2 to 4 so I’ll keep an eye on that table and see how many new rows come in.

I also changed the default isolation level to “read uncommitted” so I’ll see if that helps out too.

I’ll let you know if I see anymore deadlock errors in the console but I have a feeling that the tips you gave me will solve the problem. Thanks for the help!

4

Hi,

9k rows isn’t really all that many, so maybe there’s something else that’s taking time. I haven’t check yet, but I think that most of the data insertion is done inside of one transaction, meaning that multiple tables will potentially be locked at one time, and that a slow operation on any of them could affect a different query trying to get to another. From our side, I think the goal will be to reduce the number of operations done in the transaction, and improve caching.

At any rate, the changes you made should still help, but let me know if they don’t.

Regards,

5

Those changes seemed to have made a big difference. I haven’t had any deadlock errors so far and retrieving history trend data is noticeably faster. I’ll watch it for a few more days to make sure it stays that way but so far it’s been a big improvement.