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