SQL Tag Deadlock Recovery

Ignition
1

I have a SQL tag that updates every 5 minutes. It takes about 5 seconds to run. Every 2 or 3 days we will get a deadlock error and lose the tag value until I manually intervene to cause it to execute the query again (usually by opening the tag in the browser and saving it again, or disabling it/reenabling it.)

Is there a way to put something along the lines of a try/except in the SQL driving the tag? I would like it to retain the previous value until the query runs successfully again. Not a big deal if we miss an update every couple of days.

Thanks!

2

What type database are you talking to?

MS SQL does have Try/Catch functionality in SQ as explained in this example

I’m not sure about other DB brands.

But I am curious to know why you are getting deadlocks on something that I assume should only be read functionality

3

It’s a SQL Server 2000 database. Besides being extremely old the design probably isn’t very good.

4

Use a gateway timer script writing to a memory tag. Failures in the query won’t disturb the current tag content.

1 Like
5

That’s an old DB. Forget what I said about the SQL transactions. From what I understand they are only 2005 onwards

6

You might use the WITH (NOLOCK) to prevent deadlock.

Ex. SELECT * FROM Person.Contact WITH (NOLOCK)

7

Thanks for all the suggestions!

=> Peter: I spent a good part of an hour trying to figure out why I was getting syntax errors on the Try/Catch before figuring out SQL 2000 didn’t support it. :frowning: Fortunately the DB is scheduled to be updated in the coming months.

=> Phil: I think I may implement your suggestion even if I can resolve the deadlocks, just as a safety measure.

=> Adnaram: I have been trying to educate myself on the pros and cons of NOLOCK. I don’t think dirty data would be much of an issue with my application. I think I am going to try to modify the query to incorporate temp tables instead of all of the nested querying to reduce the chances of a deadlock condition.

Pretty sure I will solve this one way or another, thanks again for the suggestions!