SQL Server Deadlock

Question

I am running SQL Server 2012 Service Pack 1 + Non Security Update (KB2793634). I am frequently getting deadlocks occur, 99% of the time with the same 2 queries. How can i prevent this deadlock from happening ?

Here is the deadlock details :

<deadlock-list>
 <deadlock victim="process578a88188">
  <process-list>
   <process id="process578a88188" taskpriority="0" logused="2036" waitresource="KEY: 6:72057594089963520 (6533c2597184)" waittime="2954" ownerId="28095456501" transactionname="user_transaction" lasttranstarted="2016-07-20T13:30:16.490" XDES="0x38670a6c8" lockMode="X" schedulerid="3" kpid="5536" status="suspended" spid="124" sbid="1" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-07-20T13:30:16.627" lastbatchcompleted="2016-07-20T13:30:16.610" lastattention="1900-01-01T00:00:00.610" clientapp="PHP" hostname="WL-DC-A-QUEUE01" hostpid="3208" loginname="simpro" isolationlevel="read committed (2)" xactid="28095456501" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="52" sqlhandle="0x02000000c1ad4b2a727cb2c9f7ba407e398d53c0824708850000000000000000000000000000000000000000">
UPDATE workflow_request SET updated = @P1 WHERE id = @P2     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P1 nvarchar(26),@P2 int)UPDATE workflow_request SET updated = @P1 WHERE id = @P2    </inputbuf>
   </process>
   <process id="process61e0a9868" taskpriority="0" logused="8484" waitresource="KEY: 6:72057594089963520 (49806bcce5bc)" waittime="3032" ownerId="28095456347" transactionname="user_transaction" lasttranstarted="2016-07-20T13:30:16.407" XDES="0x46e5c03a8" lockMode="U" schedulerid="4" kpid="5896" status="suspended" spid="91" sbid="1" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-07-20T13:30:16.550" lastbatchcompleted="2016-07-20T13:30:16.547" lastattention="1900-01-01T00:00:00.547" clientapp="PHP" hostname="WL-DC-A-QUEUE01" hostpid="2664" loginname="simpro" isolationlevel="read committed (2)" xactid="28095456347" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="108" sqlhandle="0x0200000029bc6522620287ccc7de73b3135f3b2e2fc578260000000000000000000000000000000000000000">
UPDATE workflow_request SET current_state_id = @P1, status_id = @P2, updated_by_id = @P3, updated = @P4 WHERE id = @P5     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P1 char(1),@P2 int,@P3 int,@P4 nvarchar(26),@P5 int)UPDATE workflow_request SET current_state_id = @P1, status_id = @P2, updated_by_id = @P3, updated = @P4 WHERE id = @P5    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594089963520" dbid="6" objectname="simprov4.dbo.workflow_request" indexname="PK__workflow__3213E83F498EEC8D" id="lock1474033780" mode="X" associatedObjectId="72057594089963520">
    <owner-list>
     <owner id="process61e0a9868" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process578a88188" mode="X" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594089963520" dbid="6" objectname="simprov4.dbo.workflow_request" indexname="PK__workflow__3213E83F498EEC8D" id="lock4e91ab400" mode="X" associatedObjectId="72057594089963520">
    <owner-list>
     <owner id="process578a88188" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process61e0a9868" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

Here is the table create statement :

CREATE TABLE [dbo].[workflow_request](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [requester_id] [int] NULL,
    [sim_id] [int] NULL,
    [action_id] [int] NULL,
    [activate_data_id] [int] NULL,
    [cancel_data_id] [int] NULL,
    [allocate_ip_data_id] [int] NULL,
    [transition_id] [int] NULL,
    [current_state_id] [int] NULL,
    [status_id] [int] NULL,
    [locked_by_id] [int] NULL,
    [customer_request_batch_id] [int] NULL,
    [batch_id] [int] NULL,
    [skip] [bit] NULL,
    [network_reference] [nvarchar](255) NULL,
    [locked] [bit] NOT NULL,
    [last_result] [bit] NULL,
    [last_message] [varchar](max) NULL,
    [retry] [bit] NOT NULL,
    [retry_count] [smallint] NOT NULL,
    [created] [datetime2](6) NOT NULL,
    [updated] [datetime2](6) NOT NULL,
    [sim_swap_data_id] [int] NULL,
    [enable_full_bar_data_id] [int] NULL,
    [disable_full_bar_data_id] [int] NULL,
    [edit_sims_data_id] [int] NULL,
    [enable_roaming_data_id] [int] NULL,
    [disable_roaming_data_id] [int] NULL,
    [sim_refresh_data_id] [int] NULL,
    [suspend_data_id] [int] NULL,
    [send_to_network] [bit] NULL,
    [spreference] [nvarchar](50) NULL,
    [updated_by_id] [int] NULL,
    [skip_creation_email] [bit] NULL,
    [skip_completion_email] [bit] NULL,
    [auto_complete] [bit] NULL,
    [send_to_customer] [bit] NULL,
    [manual_override] [bit] NULL,
    [ignore_failed_flag] [bit] NULL,
    [failed_status_id] [int] NULL,
    [email_to] [nvarchar](255) NULL,
    [delete_sims_data_id] [int] NULL,
    [undelete_sims_data_id] [int] NULL,
    [update_apn_data_id] [int] NULL,
PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F161A0C43] FOREIGN KEY([customer_request_batch_id])
REFERENCES [dbo].[workflow_customer_request_batch] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F161A0C43]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F4355328A] FOREIGN KEY([update_apn_data_id])
REFERENCES [dbo].[workflow_update_apn_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F4355328A]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F44F7BE9D] FOREIGN KEY([suspend_data_id])
REFERENCES [dbo].[workflow_suspend_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F44F7BE9D]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F4AA4DEB] FOREIGN KEY([sim_refresh_data_id])
REFERENCES [dbo].[workflow_sim_refresh_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F4AA4DEB]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F4E0AB869] FOREIGN KEY([disable_roaming_data_id])
REFERENCES [dbo].[workflow_disable_roaming_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F4E0AB869]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F5131BD6D] FOREIGN KEY([cancel_data_id])
REFERENCES [dbo].[workflow_cancel_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F5131BD6D]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F54A7A1D9] FOREIGN KEY([allocate_ip_data_id])
REFERENCES [dbo].[workflow_allocate_ip_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F54A7A1D9]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F575024C3] FOREIGN KEY([sim_swap_data_id])
REFERENCES [dbo].[workflow_sim_swap_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F575024C3]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F5E711585] FOREIGN KEY([failed_status_id])
REFERENCES [dbo].[workflow_request_failed_status] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F5E711585]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F6BF700BD] FOREIGN KEY([status_id])
REFERENCES [dbo].[workflow_request_status] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F6BF700BD]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F7A88E00] FOREIGN KEY([locked_by_id])
REFERENCES [dbo].[core_user] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F7A88E00]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F896DBBDE] FOREIGN KEY([updated_by_id])
REFERENCES [dbo].[core_user] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F896DBBDE]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F8BF1A064] FOREIGN KEY([transition_id])
REFERENCES [dbo].[workflow_transition] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F8BF1A064]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F9527B049] FOREIGN KEY([enable_roaming_data_id])
REFERENCES [dbo].[workflow_roaming_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F9527B049]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F97C93AED] FOREIGN KEY([enable_full_bar_data_id])
REFERENCES [dbo].[workflow_enable_full_bar_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F97C93AED]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F98A046EB] FOREIGN KEY([current_state_id])
REFERENCES [dbo].[workflow_state] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F98A046EB]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F9D32F035] FOREIGN KEY([action_id])
REFERENCES [dbo].[workflow_action] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F9D32F035]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192FAC7C372D] FOREIGN KEY([disable_full_bar_data_id])
REFERENCES [dbo].[workflow_disable_full_bar_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192FAC7C372D]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192FC79087FC] FOREIGN KEY([activate_data_id])
REFERENCES [dbo].[workflow_activate_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192FC79087FC]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192FE7793046] FOREIGN KEY([delete_sims_data_id])
REFERENCES [dbo].[workflow_delete_sims_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192FE7793046]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192FED442CF4] FOREIGN KEY([requester_id])
REFERENCES [dbo].[core_user] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192FED442CF4]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192FEF677A38] FOREIGN KEY([undelete_sims_data_id])
REFERENCES [dbo].[workflow_undelete_sims_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192FEF677A38]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192FF81AF80C] FOREIGN KEY([sim_id])
REFERENCES [dbo].[core_sim] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192FF81AF80C]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192FF94413E8] FOREIGN KEY([edit_sims_data_id])
REFERENCES [dbo].[workflow_edit_sims_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192FF94413E8]
GO

Execution plans for each query - first query UPDATE workflow_request SET updated = @P1 WHERE id = @P2 and second query UPDATE workflow_request SET current_state_id = @P1, status_id = @P2, updated_by_id = @P3, updated = @P4 WHERE id = @P5

Answer 1

Here is how any deadlock happens:

1. Transaction 1 (T1) starts. It locks some resources to work with, not all.
2. T1 does its job on first set and requests lock for the next set of resources. It does not release resources, because it happens only when T COMMITs, usually when T ends.
3. T2 Requests locks some other available resources. T2 does not release locks until commit.
4. T2 requests resources locked by T1. It is waiting for T1 to finish and release resources.
5. Now T1 requests lock on resources already locked by T2. resources are locked, so T1 waits until T2 releases them.
6. Deadlock: T1 waiting for T2 while T2 waiting for T1. SQL Server kills one them. I would not be covering logic for this here.

Note: it applies to any resource the lock is applied to, not necessarily individual row.

Problem: Two Transactions need same resource over same period of time.

Options: 1. Change resource that's being locked. If lock is at page level and different rows on this page are needed, then WITH (ROWLOCK) should help. It will increase overheads of locking, but will allow different transactions to lock different rows on the same page.

2. Time period. If Transactions commit more frequently, time a row is locked reduced. It reduces chances for deadlocks, but does not prevent them from happening. There 2 main ways for this
   • Commit after each row update. Wrap UPDATE in a separate nested transaction. If each (sub-) transaction updates 1 row at a time, there will be no deadlocks. However it reduces performance, so need to be tested first.
   • Make each transaction shorter.

You may require more complex solution if logic of your transactions does not allow approaches above.

Answer 2

Could you provide the deadlock graph? As you are using SQL Server 2012, it should be available in the system_health extended events session.

This query was provided by Jonathan Kehayias in his pluralsight deadlock class.

SELECT 
    XEvent.query('data[@name="xml_report"]/value/deadlock') AS deadlock_graph
FROM    (SELECT CAST([target_data] AS XML) AS TargetData
         FROM sys.dm_xe_session_targets AS st
         INNER JOIN sys.dm_xe_sessions AS s 
            ON [s].[address] = [st].[event_session_address]
         WHERE [s].[name] = N'system_health'
           AND [st].[target_name] = N'ring_buffer') AS Data
CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent);

1. You then click on the corresponding deadlock graph
2. Save the xml result as an .xdl file
3. Then open it up in sql server management studio.

Once you've opened up the .xdl file in SSMS, hover your mouse over the ovals and you'll get the queries that participated in the deadlock.

This will provide you with additional information useful in resolving the deadlock.