SQL Sever - Deadlock/Blockking Query

Question

I work for an EPOS company and have come across an issue with deadlocks occurring in a database only when replication has been set up in the database.

If no replication has been set up and one or more EPOS tills try to post a transaction into the EPOS database the transaction records posts into the database successfully and two of the tables in the database get transaction data recorded against them - ProductTranHeader, ProductTranDetail and

Once replication has been set up on the database and you have one or more EPOS tills try to post a transaction into the EPOS database at the exact same time we get a deadlock error message

DEADLOCK EXCEPTION: Transaction (Process ID 66) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

We have set up logging that records any locks in the database and are seeing the below in the logs

DBCC Results for SPID 52 (BLOCKED) [SQLSTATE 01000]
DBCC Results for SPID 58 (BLOCKING) [SQLSTATE 01000]

Going by the logs it appears to reference the ProductTranDetail table.

I've removed the 3 indexes on the ProductTranDetail table one index at a time and tested - the issue still happened.

I then rebuilt all indexes on the same table and the issue still occurs.

I then scripted in the indexes again one at a time and tested further - the same issue happened.

Is there anything else to check would be causing this issue?

Answer 1

It would be really helpful to get the deadlock graph. A query from https://www.red-gate.com/simple-talk/sql/database-administration/handling-deadlocks-in-sql-server/ The complete article helps you with how to troubleshoot deadlocks.

   -- Retrieve from Extended Events in 2012
 SELECT  XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
 FROM    ( SELECT    XEvent.query('.') AS XEvent
           FROM      ( SELECT    CAST(target_data AS XML) AS TargetData
                       FROM      sys.dm_xe_session_targets st
                                 JOIN sys.dm_xe_sessions s
                                  ON s.address = st.event_session_address
                       WHERE     s.name = 'system_health'
                                 AND st.target_name = 'ring_buffer'
                     ) AS Data
                     CROSS APPLY TargetData.nodes
                   ('RingBufferTarget/event[@name="xml_deadlock_report"]')
                     AS XEventData ( XEvent )
         ) AS src;