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

Is this your queries right? UPDATE workflow_request SET updated = @P1 WHERE id = @P2 and UPDATE workflow_request SET current_state_id = @P1, status_id = @P2, updated_by_id = @P3, updated = @P4 WHERE id = @P5 — Racer SQL, Jul 27, 2016 at 11:05
The conflict is over two rows on the same data page. Is there overlap in the ID values that the two queries are updating? Do you have an explicit transaction around both statements (i.e. BEGIN TRAN...COMMIT) that includes other updates to this table? This is essentially what MartinSmith asked, but I want to be very clear on this point. If yes, can you please post more of the entire batch, not just the queries getting the deadlock? You might need to experiment with table hints on the UPDATEs: WITH (PAGLOCK) or WITH(ROWLOCK). Maybe start by adding WITH (PAGLOCK) to one of them. — Solomon Rutzky, Aug 1, 2016 at 20:24

Stoleg · Accepted Answer · 2016-08-02 11:02:18Z

Here is how any deadlock happens:

Transaction 1 (T1) starts. It locks some resources to work with, not all.
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.
T2 Requests locks some other available resources. T2 does not release locks until commit.
T2 requests resources locked by T1. It is waiting for T1 to finish and release resources.
Now T1 requests lock on resources already locked by T2. resources are locked, so T1 waits until T2 releases them.
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.

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.

Thanks - great answer - easy to understand and some options - i will look at how i can get the application avoiding the situation of a deadlock occurring and go from there — Manse, Aug 2, 2016 at 11:13
If you share logic of your trans, you can get a solution too. — Stoleg, Aug 2, 2016 at 11:25

Craig Efrein · Accepted Answer · 2016-08-01 10:46:20Z

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);

You then click on the corresponding deadlock graph
Save the xml result as an .xdl file
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.

@ManseUK, having a visual reference can be useful. At least for me it would. — Craig Efrein, Aug 1, 2016 at 13:01

Stack Exchange Network

current community

your communities

more stack exchange communities

SQL Server Deadlock

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged sql-server deadlock or ask your own question.

Hot Network Questions

SQL Server Deadlock

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-server deadlock or ask your own question.

Related

Hot Network Questions