SQL Server multi threaded SELECT and DELETE statement

0
votes

I have quite a big problem with one of our process (I did not design it... whoever it was eh....)

I have multi-threaded (1-50) process that executes two stored procedures:

  1. Read data from client table
  2. Delete data from client table

I have managed to get INDEX SCAN instead TABLE SCAN to it works fairly fast (it won't get faster). Sometimes I get DEADLOCKs on delete because of SELECT lock U that is understandable.

So far:

  1. Performance Optimised to scan indexes instead of table
  2. ROW_ESCALATION changed to DISABLED (it helped a lot)

Any ideas how to get rid of U locks/deadlocks?

More info and logs

  • Graph Deadlock Graph
  • Logs - Trace on 1204, 1222

05:14.6 spid20s process id=process4fe9048 taskpriority=0 logused=0 waitresource=PAGE: 46:1:435153 waittime=14827 ownerId=421628674 transactionguid=0x73a1e22f2db893448163a72c7caf84b5 transactionname=user_transaction lasttranstarted=2019-03-12T16:04:57.190 XDES=0x8062f2e0 lockMode=U schedulerid=12 kpid=10964 status=suspended spid=96 sbid=0 ecid=1 priority=0 trancount=0 lastbatchstarted=2019-03-12T16:04:59.790 lastbatchcompleted=2019-03-12T16:04:59.780 clientapp=.Net SqlClient Data Provider hostname=SomeHost hostpid=470604 isolationlevel=read committed (2) xactid=421628674 currentdb=46 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 05:14.6 spid20s executionStack 05:14.6 spid20s frame procname=SOmeDB.dbo.pr_DeleteCLient line=14 stmtstart=296 stmtend=434 sqlhandle=0x03002e00c33ec3309ab2b800d1a900000100000000000000 05:14.6 spid20s DELETE FROM dbo.Client 05:14.6 spid20s WHERE InvestorCode = @InvestorCode
05:14.6 spid20s inputbuf

  • sys.processes enter image description here
1
sql-servertsqldeadlock
A classic "first thing to try" is augment the delete with a WITH (UPDLOCK, ROWLOCK) hint, forcing update locks to be taken in the scan phase, instead of shared locks that later have to be upgraded to exclusive locks when the delete goes through -- the latter is a rife source of deadlocks. Obviously, this does have the potential for reducing concurrency. Using snapshot isolation is another option to consider, at the very least to remove readers from the equation. - Jeroen Mostert
Did not worked ;( - VoonArt

1 Answers

0
votes

Deadlocks normally happens when you read data with the intention to update or delete it later by just putting a shared lock, the following delete statement can’t acquire the necessary update locks, because they are already blocked by the shared Locks acquired in the different session causing the deadlock.

If it is running in multiple sessions, it's better to use WITH UPDLOCK or WITH (SERIALIZABLE) like following sample 

DECLARE @IdToDelete INT
SELECT @IdToDelete =ID FROM [Your_Table] WITH (SERIALIZABLE) WHERE A=B

DELETE [Your_Table]    
WHERE ID=@IdToDelete