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,