SQLSERVER2008 死锁有关问题
来源:互联网 发布:骑行装备 知乎 编辑:程序博客网 时间:2024/04/28 16:14
SQLSERVER2008 死锁问题
Application 偶尔会发生死锁的情况,以前就做了一些分析,初步定位了两段有冲突的地方,今天特意做了一下试验,并把DBCC监控死锁的命令打开,果然发现是两段SQL同时执行可能会发生冲突,但分析了半天,仍没有找出死锁的原因,请大家来看看,并说说怎么设计来避免。通过Application 加锁的方式来规避也是可以的,但改动要麻烦点,看通过SQLServer的索引设计或者写法的改变是否可以避免这个问题.
Query1 第一个connection:
在一个查询的Connection中查询多个备件,但其中是没有事务的.
waitfor DELAY '00:00:05'
WHILE (1=1)
BEGIN
select pcp.* from PartCountryPlannerReviewReason pcp
inner join ValidationReviewReason vrr on pcp.ReviewReasonID = vrr.ID
WHERE pcp.ChubID = 1 AND pcp.Material in
('620893-001','670539-001','671352-001','608150-001','681958-001','605961-001','604054-001','657095-601','613458-001')
AND pcp.ReviewedOn is null AND vrr.CanBeBatchReviewed = 1
END
query1 执行计划:
select pcp.* from PartCountryPlannerReviewReason pcp inner join ValidationReviewReason vrr on pcp.ReviewReasonID = vrr.ID WHERE pcp.ChubID = 1 AND pcp.Material in ('620893-001','670539-001','671352-001','608150-001','681958-001','605961-001','604054-001','657095-601','613458-001') AND pcp.ReviewedOn is null AND vrr.CanBeBatchReviewed = 1
|--Nested Loops(Inner Join, OUTER REFERENCES:([pcp].[ReviewReasonID]))
|--Clustered Index Scan(OBJECT:([AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[PK_PartCountryPlannerReviewReason] AS [pcp]), WHERE:([AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[ChubID] as [pcp].[ChubID]=(1) AND [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[ReviewedOn] as [pcp].[ReviewedOn] IS NULL AND ([AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'604054-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'605961-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'608150-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'613458-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'620893-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'657095-601' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'670539-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'671352-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'681958-001')))
|--Clustered Index Seek(OBJECT:([AMEETOT3Trans].[dbo].[ValidationReviewReason].[PK_ValidationReviewReason] AS [vrr]), SEEK:([vrr].[ID]=[AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[ReviewReasonID] as [pcp].[ReviewReasonID]), WHERE:([AMEETOT3Trans].[dbo].[ValidationReviewReason].[CanBeBatchReviewed] as [vrr].[CanBeBatchReviewed]=(1)) ORDERED FORWARD)
Query 2 第二个Connnection:
WHILE(1=1)
BEGIN
UPDATE PRR
SET Remark = 'XXX test'
FROM PartCountryPlannerReviewReason PRR
inner join InventorySafetyRop isr on prr.ChubID = isr.CHUBID AND PRR.Material = isr.Material
WHERE PRR.ChubID = 1 AND isr.IsValidated = 1
Application 偶尔会发生死锁的情况,以前就做了一些分析,初步定位了两段有冲突的地方,今天特意做了一下试验,并把DBCC监控死锁的命令打开,果然发现是两段SQL同时执行可能会发生冲突,但分析了半天,仍没有找出死锁的原因,请大家来看看,并说说怎么设计来避免。通过Application 加锁的方式来规避也是可以的,但改动要麻烦点,看通过SQLServer的索引设计或者写法的改变是否可以避免这个问题.
Query1 第一个connection:
在一个查询的Connection中查询多个备件,但其中是没有事务的.
waitfor DELAY '00:00:05'
WHILE (1=1)
BEGIN
select pcp.* from PartCountryPlannerReviewReason pcp
inner join ValidationReviewReason vrr on pcp.ReviewReasonID = vrr.ID
WHERE pcp.ChubID = 1 AND pcp.Material in
('620893-001','670539-001','671352-001','608150-001','681958-001','605961-001','604054-001','657095-601','613458-001')
AND pcp.ReviewedOn is null AND vrr.CanBeBatchReviewed = 1
END
query1 执行计划:
select pcp.* from PartCountryPlannerReviewReason pcp inner join ValidationReviewReason vrr on pcp.ReviewReasonID = vrr.ID WHERE pcp.ChubID = 1 AND pcp.Material in ('620893-001','670539-001','671352-001','608150-001','681958-001','605961-001','604054-001','657095-601','613458-001') AND pcp.ReviewedOn is null AND vrr.CanBeBatchReviewed = 1
|--Nested Loops(Inner Join, OUTER REFERENCES:([pcp].[ReviewReasonID]))
|--Clustered Index Scan(OBJECT:([AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[PK_PartCountryPlannerReviewReason] AS [pcp]), WHERE:([AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[ChubID] as [pcp].[ChubID]=(1) AND [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[ReviewedOn] as [pcp].[ReviewedOn] IS NULL AND ([AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'604054-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'605961-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'608150-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'613458-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'620893-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'657095-601' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'670539-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'671352-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'681958-001')))
|--Clustered Index Seek(OBJECT:([AMEETOT3Trans].[dbo].[ValidationReviewReason].[PK_ValidationReviewReason] AS [vrr]), SEEK:([vrr].[ID]=[AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[ReviewReasonID] as [pcp].[ReviewReasonID]), WHERE:([AMEETOT3Trans].[dbo].[ValidationReviewReason].[CanBeBatchReviewed] as [vrr].[CanBeBatchReviewed]=(1)) ORDERED FORWARD)
Query 2 第二个Connnection:
WHILE(1=1)
BEGIN
UPDATE PRR
SET Remark = 'XXX test'
FROM PartCountryPlannerReviewReason PRR
inner join InventorySafetyRop isr on prr.ChubID = isr.CHUBID AND PRR.Material = isr.Material
WHERE PRR.ChubID = 1 AND isr.IsValidated = 1
文献:http://www.verydemo.com/demo_c155_i11318.html
0 0
- SQLSERVER2008 死锁有关问题
- asp 连接sqlserver2008有关问题
- 死锁的有关问题
- sqlserver2008 死锁总结
- 软考中有关死锁的问题
- 操作系统--死锁应对的有关问题(1)
- SQLServer2008问题
- 有关万向节死锁(Gimbal Lock)的问题
- sqlserver2008 死锁解决方法及性能优化方法
- sqlserver2008 死锁解决方法及性能优化方法
- 有关死锁的讨论
- 有关mysql死锁
- sqlserver2008的使用问题
- SQLServer2008 R2 卸载问题
- SQLserver2008驱动问题
- 摘录:sqlserver2008驱动问题
- Hibernate5+sqlserver2008 分页问题
- Sqlserver2008相关配置问题
- jquery对象和Dom对象区别
- Linux-2.6平台上的SCSI子系统
- 分析Netty工作流程
- AssetBundle打包优化解决方案
- 下一条街
- SQLSERVER2008 死锁有关问题
- ASP.NET程序中常用的三十三种代码
- 开发容易赚钱不易 说说这几年混迹过的广告平台
- Paint类介绍
- Understanding Linux CPU Load - when should you be worried?
- IOS开发,启动界面对IOS7和IOS6的适配问题
- javascript table排序 这个更简单了,不用改变现在的表格结构
- supermap使用小结
- Socket的几种