SQL Server Deadlock – How to Resolve

deadlocksql server

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

Best Answer

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.

Best Answer

Related Solutions

Sql-server – Shared and IX locks causing deadlock (Sql server)

Oracle 11g hunting down the deadlock, maybe foreign key

Related Question