笔记67-徐 死锁案例分析 P333

笔记67-徐 死锁案例分析 P333

--死锁案例分析 P333

--先开启监视死锁的开关1222，让SQL遇到死锁时，在errorlog里打印出
--死锁的详细内容

DBCC TRACEON(1222,-1)

--这里使用范例表dbo.Employee_Demo_Heap

--这张表在EmployeeID和ManagerID字段上各有一个非聚集索引，但是没有聚集索引


--现在用下面脚本来模拟出一个死锁来。在一个连接里，运行下面语句，反复开启
--事务。在这个事务里，先修改一条NationalIDNumber='480951955'的记录，然后
--把他查询出来。完成以后，再提交事务

USE [AdventureWorks]
GO
SET NOCOUNT ON
GO
WHILE 1=1
BEGIN
BEGIN TRAN
UPDATE [dbo].[Employee_Demo_Heap]
SET [BirthDate] =GETDATE()
WHERE [NationalIDNumber]='480951955'
SELECT * FROM [dbo].[Employee_Demo_Heap]
WHERE [NationalIDNumber]='480951955'
COMMIT TRAN
END


--在另一个连接里也运行这些语句，唯一差别是这次修改和查询是另一条
--NationalIDNumber='407505660'的记录

USE [AdventureWorks]
GO
SET NOCOUNT ON
--当 SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。当 SET NOCOUNT 为 OFF 时，返回计数。如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。
GO
WHILE 1=1
BEGIN
BEGIN TRAN
UPDATE dbo.[Employee_Demo_Heap]
SET [BirthDate]=GETDATE()
WHERE [NationalIDNumber]='407505660'
SELECT * FROM [dbo].[Employee_Demo_Heap]
WHERE [NationalIDNumber]='407505660'
COMMIT TRAN
END


--两条语句一起运行，无须多长时间就会有其中一个连接遇到死锁的错误

--消息1205，级别13，状态45，第4行
--事务（进程ID 54）与另一个进程被死锁在锁资源上，并且已被选作死锁牺牲品
--请重新运行该事务

DBCC TRACEON(3604)
DBCC PAGE(9,1,6685,3)

--常见的四种解决死锁的方法
--1、调整索引，以调整执行计划，减少锁的申请数目，从而消除死锁
--如果死锁的双方不会申请对方要申请的锁，那么死锁也不会发生。如果
--数据库设计者能够引导SQL使用seek的执行计划，只读取要返回的数据，
--那么申请的锁数量会大大降低，死锁的几率也会减少

--上面的情况可以在NationalIDNumber字段上加一个索引，SQL可以更快地
--找到数据，而无须申请这麽多锁。执行计划都变成seek类型
USE [AdventureWorks]
CREATE NONCLUSTERED INDEX NationalIDNumber ON [Employee_Demo_Heap]([NationalIDNumber] ASC)

--注意:加索引的时候,如果数据库在运行环境,由于某些查询还在查询数据表那么索引不会添加成功
--除非,预先在建立好表结构的时候就加上索引,或者在晚上停机时间加索引

--这是一种比较好的方法,能够提高语句执行效率,又消除死锁。所以应该尽可能地使用
--但是他也有局限性，对语句和数据库有下面两个要求：

--（1）死锁双方处理的数据本身没有交叉
--如果双方都要修改或返回同样的数据，那么再优化索引，可能也没有用处

--（2）DBA要有权力在数据库里调整索引设计

--有些数据库是跟着应用来的，DBA必须征得应用开发团队的同意，才能修改数据库设计
--调整之前，要考虑到修改可能会带来的负面作用。不要这个死锁消除了，其他地方
--又出现新的死锁和阻塞

--当这个方法不可用的时候，可以考虑下面三个比较粗鲁的方法

--2、使用“nolock”参数，让select 语句不要申请S锁，减少锁的申请数目，从而
--消除死锁
--很多死锁发生在S锁上面，我们可以通过在语句里加“nolock”参数的方法，让
--select语句不要去申请S锁，死锁自然也会消失
USE [AdventureWorks]
GO
SET NOCOUNT ON
GO
WHILE 1=1
BEGIN
BEGIN TRAN
UPDATE [dbo].[Employee_Demo_Heap]
SET [BirthDate] =GETDATE()
WHERE [NationalIDNumber]='480951955'
SELECT * FROM [dbo].[Employee_Demo_Heap] WITH (NOLOCK)
WHERE [NationalIDNumber]='480951955'
COMMIT TRAN
END

--这个方法解决死锁可以说是立竿见影，所以很多用户很喜欢使用他
--但是缺点很明显
--with (nolock)这个参数意味着，select语句将能接收脏读，这是SQL
--支持的最低一级事务隔离级别。用户要能够理解他的副作用，并且能够
--接受，才能使用
--这种方法只能解决S锁参与的死锁问题，如果死锁发生在U锁或X锁上，with (nolock)
--没有帮助
--这个方法要去修改语句本身。如果语句是由应用程序动态生成，而不是写在SQL
--的存储过程里，要应用开发人员才能够修改
DECLARE @a NVARCHAR(4000)
SET @a='SELECT * FROM [dbo].[Employee_Demo_Heap] WITH (NOLOCK)
WHERE [NationalIDNumber]='+'480951955'
EXEC(@a)

--3、升级锁粒度,将死锁转化为一个阻塞问题
--死锁产生的原因是双方都申请到了一个资源，同时又要申请对方的资源。
--如果一方一个资源都没有申请到，那么发生的就是阻塞，而不是死锁
--从这个角度讲，如果能想办法让一方被另一方阻塞住，什么资源都申请不到
--那死锁就不会发生

--从上面的例子里，死锁发生在同一个page上的不同rid上。如果语句直接申请Page
--级别的锁，同时就只能有一个人得到锁资源，而另一个人会被阻塞住。因此，
--使用pagelock这个参数，也能解决这里的死锁问题。

USE [AdventureWorks]
GO
SET NOCOUNT ON
GO
WHILE 1=1
BEGIN
BEGIN TRAN
UPDATE [dbo].[Employee_Demo_Heap] WITH (PAGLOCK)
SET [BirthDate] =GETDATE()
WHERE [NationalIDNumber]='480951955'
SELECT * FROM [dbo].[Employee_Demo_Heap] WITH (PAGLOCK)
WHERE [NationalIDNumber]='480951955'
COMMIT TRAN
END

--这种方法虽然不会降低事务隔离级别，但是语句更容易被阻塞住，最终
--的并发度难免受到影响。因此实际上也是一种粗鲁的方法。没有第一种
--通过加索引的方法那么精细。如果能用第一种方法，还是使用第一种方法
--比较负责任

--4、使用快照隔离级别
--由于这个死锁里有S锁参与，这里把事务隔离级别从默认的已提交读
--改成快照，对缓解这个死锁也会有很好帮助。在语句的开头加上
--这句话就行
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO

USE [AdventureWorks]
GO
SET NOCOUNT ON
GO
WHILE 1=1
BEGIN
BEGIN TRAN
UPDATE [dbo].[Employee_Demo_Heap]
SET [BirthDate] =GETDATE()
WHERE [NationalIDNumber]='480951955'
SELECT * FROM [dbo].[Employee_Demo_Heap]
WHERE [NationalIDNumber]='480951955'
COMMIT TRAN
END

-----------------------------------总结------------------------------------------------------
--分析和解决死锁的时候，思路要开阔一点，从设计着手