DeadLock - Two Process Locked Same Index

1 vote

0 answers

236 views

sql-server index locking deadlock lock-escalation

                          We have met a scenario that two process acquired exclusive lock on same index ans the same time two are requesting for shared lock to other process which eventually leads to dead lock.

I am still not sure how come **Select Statement acquires exclusive lock**!!

DeadLock XML ::

Deadlock Graph for the Reference

Additional Code Block

    IF (SELECT COUNT(1) FROM (
		IF (SELECT COUNT(1) FROM (
		SELECT b.Shop FROM [Batch] b
			INNER JOIN [Process] o ON o.procNo = b.procNo			
			WHERE b.Ship = N'P' 			
		) B)
		UPDATE b4 SET StringVal3 = (
				SELECT TOP 1 Shop FROM (
					        SELECT COUNT(1) FROM (
							SELECT b.Shop FROM [Batch] b
								INNER JOIN [Process] o ON o.procNo = b.procNo			
								WHERE b.Ship = N'P' 
					) B)
			FROM [Batch] b4 WHERE b4.Ship = N'P' AND b4.Unique = 0 AND b4.Flag = 4

Asked by Parthi (11 rep)

Nov 26, 2018, 11:01 AM
Last activity: Nov 26, 2018, 04:17 PM

DeadLock - Two Process Locked Same Index

Related Questions