欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

SQL常见死锁例子及分析

程序员文章站 2022-04-17 14:40:05
...

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)