SQL Server - 使用聚集索引时如何存储数据页

Question

Manse

Asked: 2016-07-28 01:55:26 +0800 CST2016-07-28 01:55:26 +0800 CST 2016-07-28 01:55:26 +0800 CST

SQL 服务器死锁

772

我正在运行 SQL Server 2012 Service Pack 1 + 非安全更新 (KB2793634)。我经常遇到死锁，99% 的时间都是使用相同的 2 个查询。我怎样才能防止这种僵局发生？

这是死锁的详细信息：

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

这是表创建语句：

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

每个查询的执行计划 -第一个查询 UPDATE workflow_request SET updated = @P1 WHERE id = @P2和第二个查询 UPDATE workflow_request SET current_state_id = @P1, status_id = @P2, updated_by_id = @P3, updated = @P4 WHERE id = @P5

2 个回答

Voted

Stoleg · Answer 1 · 2016-08-03T03:02:18+08:00

以下是任何死锁的发生方式：

事务 1 (T1) 开始。它锁定了一些资源来使用，而不是全部。
T1 在第一组资源上完成它的工作并请求锁定下一组资源。它不释放资源，因为它只发生在 T COMMITs 时，通常是T结束时。
T2 Requests 锁定了一些其他可用的资源。T2 在提交之前不会释放锁。
T2 请求被 T1 锁定的资源。它正在等待 T1 完成并释放资源。
现在 T1 请求锁定已被 T2 锁定的资源。资源被锁定，所以 T1 一直等到 T2 释放它们。
死锁：T1 等待 T2，而 T2 等待 T1。SQL Server 杀死了其中一个。我不会在这里涵盖逻辑。

注意：它适用于应用锁的任何资源，不一定是单个行。

问题：两个事务在同一时间段内需要相同的资源。

选项： 1. 更改被锁定的资源。如果锁定在页面级别并且需要此页面上的不同行，那么WITH (ROWLOCK)应该有所帮助。它会增加锁定的开销，但会允许不同的事务锁定同一页上的不同行。

时间段。如果事务更频繁地提交，则一行被锁定的时间会减少。它减少了死锁的机会，但并不能阻止死锁的发生。有两种主要方法
- 在每行更新后提交。包装UPDATE在一个单独的嵌套事务中。如果每个（子）事务一次更新 1 行，则不会出现死锁。但是它会降低性能，因此需要先进行测试。
- 缩短每笔交易。

如果您的交易逻辑不允许上述方法，您可能需要更复杂的解决方案。

Craig Efrein · Answer 2 · 2016-08-02T02:08:55+08:00

你能提供死锁图吗？当您使用 SQL Server 2012 时，它应该在 system_health 扩展事件会话中可用。

此查询由 Jonathan Kehayias 在他的 pluralsight 死锁课程中提供。

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

然后你点击相应的死锁图
将 xml 结果保存为 .xdl 文件
然后在sql server management studio中打开它。

在 SSMS 中打开 .xdl 文件后，将鼠标悬停在椭圆上，您将获得参与死锁的查询。

这将为您提供有助于解决死锁的附加信息。

SQL 服务器死锁

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

SQL 服务器死锁

2 个回答

相关问题