Deadlocked on Lock

Trent_Crockett · May 22, 2019, 2:04am

Hi Everyone,

I have a C10 NT11 web app that acts as an API server with a MS SQL Server 2012 SP3 database. This web app receives requests from Android and iOS phones/tablets + Windows desktop C8 apps.

Every day the web app receives LOTS of ‘deadlocked on lock’ error messages, usually when trying to delete a record from the database. I think it is happening because the web app is receiving lots of requests to select and return data, while receiving requests to delete data from the same table. Here is an example of the deadlocked error message:

This is killing the web app when running as a service. Are there any MS SQL experts out there that can help with resolving this issue? The driver string I’m using for the database tables is:

/BUSYHANDLING=2 /MULTIPLEACTIVERESULTSETS=TRUE /ISOLATIONLEVEL=1 /BUSYRETRIES=5 /LOGONSCREEN=FALSE /VERIFYVIASELECT=TRUE /APPENDBUFFER=2000

Regards,
Trent

Graham_Dawson · May 22, 2019, 11:16am

Why not add a deleted indicator field eg DeletedInd BYTE and set that to True instead of actually trying to delete the record.
You’ll need to setup your browses and processes etc to ignore records where DeletedInd = True and provide a ‘clear down’ procedure to really delete those records periodically. (during quiet periods)

Trent_Crockett · May 22, 2019, 11:18am

Hi Graham,

It’s a good idea, but at the moment I can’t update the native mobile apps with this setting.

EDIT: Actually this might work as I don’t need to update the mobile apps for the right data to be sent to them. Will let you know how this goes.

Regards,
Trent

Richard · May 24, 2019, 6:18am

Trent,

Are there any MS SQL experts out there that can help with resolving this issue?

Yes.

I have alot of very recent experience with Microsoft SQL Server and am happy to take this one offline to discuss in detail.

ProAssist · May 25, 2019, 4:49pm

This is the driver options we have been using for many years:
/BUSYHANDLING=2 /BUSYRETRIES=500 /ISOLATIONLEVEL=1

Trent_Crockett · May 27, 2019, 6:46am

Hi Everyone,

Thank you for the suggestions. I’ve tried some things today and think the issue is resolved. Here is what I’ve done:

Add a ‘deleted’ field to each table.
Instead of deleting the record, update the record to ‘deleted = 1’.
Add a 1 minute timer on the main window.
In the timer loop 5 times and delete all records in a raw {prop:sql} script for each table. Break if delete is successful. Repeat for each table that needs to delete records.

So far it has been running successfully without any deadlocks.

Regards,
Trent