SQL常见死锁例子及分析

use XTS
--步骤一=============创建测试表===================================================
--测试表1
if(exists(select 1 from sysobjects where id=OBJECT_ID('testa')))
   drop table testa
CREATE TABLE testa
(
	id int primary key,--Id,并标记为主建
	name varchar(10),--姓名
	age decimal(26,4),--年龄
	city varchar(10),--所在城市
	address varchar(10),--家庭地址
	remark text --备注
)
--创建非聚集索引
create unique nonclustered index testa_idx1 on testa(name,age) 

--测试表2
if(exists(select 1 from sysobjects where id=OBJECT_ID('testb')))
   drop table testb
CREATE TABLE testb
(
	id int primary key,--Id,并标记为主建
	name varchar(10),--姓名
	age decimal(26,4),--年龄
	city varchar(10),--所在城市
	address varchar(10),--家庭地址
	remark text --备注
)

--创建非聚集索引
create unique nonclustered index testb_idx1 on testb(name,age) 


--步骤二===================添加测试数据================================================
insert into testa(id,name,age,city,address)
select 1,'joe',20,'hz','zjwz'

insert into testa(id,name,age,city,address)
select 2,'jill',25,'hz','zjhz'

insert into testa(id,name,age,city,address)
select 3,'Bob',27,'hz','zjhz'

insert into testb(id,name,age,city,address)
select 1,'joe',20,'hz','zjwz'

insert into testb(id,name,age,city,address)
select 2,'jill',25,'hz','zjhz'

insert into testb(id,name,age,city,address)
select 3,'Bob',27,'hz','zjhz'

--添加多一点,是为了查询效率降低，以便快速得到验证结果
declare @i int=4
while (@i<1000)
begin
 insert into testa(id,name,age,city,address)
select @i,@i,27,'xxxx','xxxx'
set @[email protected]+1
end

--步骤三===================创建测试存储过程及备用表================================================
IF OBJECT_ID ('p1') IS NOT NULL DROP PROC p1
IF OBJECT_ID ('p2') IS NOT NULL DROP PROC p2
--创建备份表，用于查询存储过程的结果存储
if(exists(select 1 from sysobjects where id=OBJECT_ID('testback')))
   drop table testback
CREATE TABLE testback
(
	id int primary key,--Id,并标记为主建
	name varchar(10),--姓名
	age decimal(26,4),--年龄
	city varchar(10),--所在城市
	address varchar(10)--家庭地址
)

if(exists(select 1 from sysobjects where id=OBJECT_ID('testback1')))
   drop table testback1
CREATE TABLE testback1
(
	id int primary key,--Id,并标记为主建
	name varchar(10),--姓名
	age decimal(26,4),--年龄
	city varchar(10),--所在城市
	address varchar(10)--家庭地址
)

if(exists(select 1 from sysobjects where id=OBJECT_ID('testback2')))
   drop table testback2
CREATE TABLE testback2
(
	id int primary key,--Id,并标记为主建
	name varchar(10),--姓名
	age decimal(26,4),--年龄
	city varchar(10),--所在城市
	address varchar(10)--家庭地址
)

--创建存储过程p1,更新存储过程
CREATE PROC p1 AS
   UPDATE testa SET age = age+1 WHERE id = 1
   UPDATE testa SET age = age-1 WHERE id = 1
GO

--创建存储过程p2,查询存储过程
CREATE PROC p2 AS
truncate table testback
insert into testback(id,name,age,address)
select id,name,age,address from testa where name='joe'
GO

---死锁案例一============两个事物各占有自己的资源，同时又需要对方的资源==================
--查询一
begin transaction
update a set address='TT' from testa a where id=1
waitfor delay '00:00:10'
select * from testb where id=1
commit transaction
--查询二
begin transaction
update a set address='TT' from testb a where id=1
waitfor delay '00:00:10'
select * from testa where id=1
commit transaction


--死锁案例二============书签查询引起的死锁=============================================
--高频率update
while (1=1) exec p1

--高频率select
while (1=1) exec p2

Set statistics profile off
UPDATE testa SET age = age+1 WHERE id = 1
select id,name,age,address from testa where name='joe'

--死锁三================在较高隔离级别的事务中,两个事务同时执行查询及更新语句============
--查询事务一
--需要设置事务隔离级别可重复渎或加事务保持锁
SET TRANSACTION ISOLATION LEVEL Repeatable read
while(1=1)
begin
begin transaction
truncate table testback1
insert into testback1(id,name,age,address)
select id,name,age,address from testa where id=1
waitfor delay '00:00:10'
update testa set age=age+1 where id=1
update testa set age=age-1 where id=1
commit transaction
end


--查询事务二
SET TRANSACTION ISOLATION LEVEL Repeatable read
while(1=1)
begin
begin transaction
truncate table testback2
insert into testback2(id,name,age,address)
select id,name,age,address from testa where id=1
waitfor delay '00:00:10'
update testa set age=age+1 where id=1
update testa set age=age-1 where id=1
commit transaction
end

--死锁四===========同一个表当两个事务都在更新不同的记录时,即使没有更新索上的字段，
--也会引起死锁,因为查询的字段没有全部在所建的普通索引上面,所以同样需要通过聚集索引做全表查询
--但是聚集索引上的行在同一时间点上被不同事务在拥有,这样就造成了两个事务查询的时候都无法获取
--全部的资源，即造成了死锁
--测试数据如下 
drop table testx
create table testx(id int primary key,name varchar(10),age int)
create index testx_ind1 on testx(name)
insert into testx(id,name,age)
select '1','袁*','22'
insert into testx(id,name,age)
select '2','程*','20'


create table testx1(id int primary key,name varchar(10),age int)
create table testx2(id int primary key,name varchar(10),age int)

--查询一
while(1=1)
begin
begin transaction
update testx set age=age+1 where name='程*'
truncate table testx1
insert into testx1(id,name,age)
select id,name,age from testx where name='程*'
commit transaction
end

--查询二
while(1=1)
begin
begin transaction
update testx set age=age+1 where name='袁*'
truncate table testx2
insert into testx2(id,name,age)
select id,name,age from testx where name='袁*'
commit transaction
end

--那你们可能就会有疑问,那我是不是单纯更新,不做查询的时候也会死锁？因为更新如果也是通过非主键字段更新时，也是全表扫描。
--答案：不会，至于为什么不会，我们只能理解数据库本身在更新和查询时上锁的原理不一样，这个我们也可以做个测试例子。
--查询一=====事务A更新20秒后结束
begin transaction
update testx set age=40 where name='程*'
waitfor delay '00:00:20'
commit transaction

                     
--查询二======事务B在5秒后结束
begin transaction
update testx set age=40 where name='袁*'
waitfor delay '00:00:05'
commit transaction

--查询三====再分开执行下面两个查询
select * from testx where name='袁*'
select * from testx where name='程*'

--测试结果:
--当我们依次执行查询一,再执行查询二，最后执行查询三:
--结果就是查询二5秒后(不需要等待查询一完成)就执行完成了，而查询三无论执行那个查询语句都需要等待20秒后（即必须要等待查询一完成）才能出来查询查询结果
--结论就是更新和查询时上锁原理不一样。

--=================隔离级别的演示=============================
DBCC USEROPTIONS 
--未提交读（Read uncommitted）演示
--查询一
SET TRANSACTION ISOLATION LEVEL Read uncommitted
select * from testa where id=100
begin transaction
update testa set city='newxx' where id=100
waitfor delay '00:00:10'
rollback transaction 

--查询二
SET TRANSACTION ISOLATION LEVEL Read uncommitted
select * from testa where id=100

--已提交读（Read committed）也叫不可重复渎
--查询一
SET TRANSACTION ISOLATION LEVEL Read committed
select * from testa where id=100
begin transaction
update testa set city='newx' where id=100
waitfor delay '00:00:10'
rollback transaction 

--查询二
SET TRANSACTION ISOLATION LEVEL Read committed
select * from testa where id=100

--可重复读（Repeatable read）演示(保证在同一个事务中，渎取数据不会被其他事务更改)
--查询一
SET TRANSACTION ISOLATION LEVEL Repeatable read
begin transaction
select * from testa where id=100
waitfor delay '00:00:10'
commit transaction

--查询二
SET TRANSACTION ISOLATION LEVEL Repeatable read
update testa set city='new' where id=100


--可序列化演示(事务的*别，保证事务的串行执行)
--查询一
SET TRANSACTION ISOLATION LEVEL Serializable
begin transaction
select * from testa
update testa set city='new' where id=100
waitfor delay '00:00:10'
select * from testa 
commit transaction 

--查询二
SET TRANSACTION ISOLATION LEVEL Serializable
insert into testa(id,name,age,city,address)
select 4000,'Bobx',27,'hz','zjhz'


--创建覆盖索引
drop index testa_idx1 on testa
create index testa_idx1 on testa(name,age) include(id,address)


--设置隔离级别为可重复渎
SET TRANSACTION ISOLATION LEVEL Repeatable read
DBCC USEROPTIONS 


--开启事务隔离的方法
declare @sql varchar(8000) 
select @sql = ' 
ALTER DATABASE ' + DB_NAME() + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ; 
ALTER DATABASE ' + DB_NAME() + ' SET TRANSACTION ISOLATION LEVEL read committed; 
ALTER DATABASE ' + DB_NAME() + ' SET MULTI_USER;' 
Exec(@sql) 

--查询事务隔离
DBCC Useroptions 


--清除缓存
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL') 

--开启SQL性能分析
Set statistics profile on

--开启SQL执行时间统计
set statistics time ON 

--开启磁盘的读写统计
set statistics io on

--锁查询相关
SELECT request_session_id, resource_type, resource_associated_entity_id,
request_status, request_mode, resource_description
FROM sys.dm_tran_locks

select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName   
from   sys.dm_tran_locks where resource_type='OBJECT'

sp_lock
sp_who
select *  from sys.sysprocesses
dbcc inputbuffer(spid)