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.
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
It’s a SQL Server 2000 database. Besides being extremely old the design probably isn’t very good.
Use a gateway timer script writing to a memory tag. Failures in the query won’t disturb the current tag content.
That’s an old DB. Forget what I said about the SQL transactions. From what I understand they are only 2005 onwards
You might use the WITH (NOLOCK) to prevent deadlock.
Ex. SELECT * FROM Person.Contact WITH (NOLOCK)
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. 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!