0

I have an application which will be chosen as the "deadlock victim" in SQL Server. There are multiple threads which try to execute below queries.

Query 

merge Table_X as target  
using (values ('14410')) as source (CUST_ID) on target.CUST_ID = '14410'
when matched then  
    update 
       SET CUST_NAME = 'xyz', CLOSE_DATE = NULL,
           xx = 2, COMPLETE = 'No', 
           qwert = CASE  WHEN qwert is null and 'Low' = 'High' THEN getDate() ELSE null END, 
           ACTIVE = 1, xcount = '913af80db3f424e34a9055e0ea9bc391'  

when not matched then  
   INSERT (CUST_ID, CUST_NAME, OPEN_DATE, CLOSE_DATE, xx, COMPLETE, qwert, ddd, ACTIVE, xcount )  
   VALUES ('14410', 'U.S. Robotics and Mechanical Men', '2007-08-31 15:14:23.0', NULL, 2, 'No', NULL, 0, 1, '913af80db3f424e34a9055e0ea9bc391') ;

DECLARE @54229 numeric(19,0)
SET @54229 = ( SELECT id from Table_X  where CUST_ID = '14410' )

insert into Table_Y (xyz, abc, ax, ay, az, bx, bxz, bz, UPDATED, abcd) 
   select 
       xyz, abc, ax, ay, az, bx, bxz, bz, UPDATED, abcd 
   from 
       Table_Z 
   where 
       abcd = @54229

merge Table_Z as target 
using (values (@54229)) as source (abcd) on target.abcd = @54229 
when matched then  
    UPDATE  
       SET xyz = 1.1, abc = 1.1, ax = 1.1, ay = 1.1, az = 1.1, 
           bx = 1.1, bxz = 'Low', bz = 1.1, UPDATED = getdate()

when not matched then 
    INSERT (xyz, abc, ax, ay, az, IS_FORCE_EDD, bx, bxz, bz, UPDATED, abcd) 
    VALUES(1.1, 1.1, 1.1, 1.1, 1.1, 0, 1.1,'Low', 1.1, getdate(), @54229);

INSERT INTO Table_A(ACTION, DATE_TIME, PROFILE_ID, USER_ID)
VALUES('Profile Change', getdate(), @54229, 1)

Deadlock diagram

Can anyone explain this deadlock diagram?

And provide me the solution to prevent this deadlock.

I have read multiple stuff and tried with (nolock), isolation level, but I am not getting proper solution.

Is there any indexes related issue possible for a deadlock victim?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
NIrav Modi
  • 6,038
  • 8
  • 32
  • 47
  • a deadlock message can also come up if it is just to slow to finish. try improving the performance of your query. – Hogan Jan 01 '15 at 14:58
  • @Hogan a deadlock error is only thrown if an actual deadlock is detected. – Martin Smith Jan 01 '15 at 16:22
  • @MartinSmith - I don't believe it is possible to detect a deadlock -- I believe a heuristic is used and this heuristic can give a "false positive" in cases of one process locking a resources and being "slow" to free it. – Hogan Jan 01 '15 at 16:43
  • @hogan well you believe wrong then. Google deadlock detection algorithms. The deadlock graph will contain details of the circular wait. – Martin Smith Jan 01 '15 at 16:56

2 Answers2

1

I have created the non-clustered index and after that I haven't observed the deadlock victim exceptions.

CREATE NONCLUSTERED INDEX [T_abcd] ON [dbo].[Table_Z]
    (
        [abcd] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
NIrav Modi
  • 6,038
  • 8
  • 32
  • 47
0

I think getting a read lock on TABLE_Z early in your code will solve the problem.

Here's a question about that: Obtain Update Table Lock at start of Stored Procedure in SQL Server.

Edit: You are reading from TABLE_Z before you are writing to it. Lets say you have two threads/connections (A & B) running:

A takes a read lock

B takes a read lock

A tries to take a write lock but has to wait for B to release the read lock.

B tries to take a write lock but has to wait for A to release the read lock.

Now there's a deadlock.

Community
  • 1
  • 1
idstam
  • 2,848
  • 1
  • 21
  • 30