-- 创建测试表
-- drop table test
create table test(id int not null,name varchar(10))
insert into test(id,name)
values (1,'aa'),(2,'bb')
select * from test
-- 打开查询窗口1执行以下语句
print @@spid
begin tran
update test
set name = convert(varchar(10),@@spid)
where id=1
-- rollback tran
-- 打开查询窗口2执行以下语句
print @@spid
begin tran
update test
set name = convert(varchar(10),@@spid)
where id=2
-- rollback tran
-- 查看锁资源,@@spid=52 已执行完成,但是没有结束事务
-- 两个session都产生了3个排他的行锁、页锁、表锁
sp_lock
解决方法:
-- 创建聚集索引
alter table [dbo].[test] add constraint [pk_test] primary key clustered (id)
可以看到,两个事务是可并行执行,互不影响。如果【查询2】中有提交或回滚,则事务立即结束,不在堵塞。
若未被提交,查看锁资源情况。发现2个事务只有键锁是排他锁,页和表是意向锁。也就是不同键是可以同时使用互不排斥的。
查看行键值,可知道是对应更改的哪行数据。
select %%lockres%%,* from test(nolock)
总结:聚集索引有利于快速定位数据行,防止全表按顺序扫描,降低并发事务引起的死锁。