Deadlocked on Lock

sql
nettalk
c10
Tags: #<Tag:0x00007f60c135ac40> #<Tag:0x00007f60c135aa38> #<Tag:0x00007f60c135a880>

#1

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


#2

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)


#3

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


#4

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.


#5

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


#6

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:

  1. Add a ‘deleted’ field to each table.
  2. Instead of deleting the record, update the record to ‘deleted = 1’.
  3. Add a 1 minute timer on the main window.
  4. 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