At our customers location, during their billing and exporting routine, handling hundreds of orders, our software recieved a Error “Transaction was deadlocked on lock resources with another proces and has been chosen as deadlock victim. Rerun the transaction (40001)) Attempting to access a record from the dbo.orderrecords file. Returning to previous window”
As far as I assume, this is an MS SQL Server generated order. I found that within T-SQL I can use “SET DEADLOCK_PRIORITY HIGH” to ensure my billing process will never be the deadlock victim anymore. However, neither the help, google or the search in Clarion Hub can direct me to the way of setting the high priority within Clarion.
Our programmers use plain clarion commands for their data access. Hardly any pure SQL commands are used.
is there also a way of detecting these errors within the code, so the routines can loop their commands, breaking out when there is no deadlock?
Thank you in advance for your time & shared knowledge.
It’s very likely your application is causing the deadlock, hopefully two different instances. I would put the effort into determining where the dead lock is coming from and trying to resolve it.
sp_BlitzLock is part of the free SQL first responder’s toolkit and you can use it to examine your dead lock afterwards and help track down what object were involved in the deadlock and hopefully point you to where to look in your code base.
Would have been an option, however it has happend once now, the first time in about 6 months. So to have the user start a tracing program ahead of every billing round, is not viable. I was hoping for a result-code that the program could act upon. I do not know if the ‘benign’ would suffice.
Interesting reading. Thank you. We are however just using the SQL database, but not issuing SQL commands. It is all still clarion based, like they are TPS files. With TSQL commands I would set the deadlock priority to high for these conditions.
Stored Procedures are very very fast compared to using a clarion process, but obviously you share the logic when the code is moved to a stored procedure, but another way that might stop the deadlock problem is to have some additional tables in the dct and sql server which are used for doing this batch process.
Copy over the records/rows in question into the temp batch files, process them using clarion, then copy them back. That way it shouldnt matter or interfere, but some companies have batch processes as an End of Day function.
I saw this alot with some IBM mainframes, once all day to day functions, data inputting, orders received, orders sent had been completed an End of Day process was initiated which did all the totally and processing but it required every on site to be logged out the system, once completed every could log back in then.
Admittedly thats like 30years+ ago and we are now more than ever a 24hr society, but that might be another option.