锁住你的记录:sqlserver锁定数据库中的一行记录
关于锁这一块一直没弄懂,刚好今天发现一个比较有意思的帖子,连接为http://bbs.csdn.net/topics/390797757 跟我对锁的疑惑差不多,就是,如何锁定一条记录,防止并发 说是存储过程插入了两条相同的记录, 存储过程的脚本如下: ALTER PROC [dbo].[Insert] @
关于锁这一块一直没弄懂,刚好今天发现一个比较有意思的帖子,连接为http://bbs.csdn.net/topics/390797757
跟我对锁的疑惑差不多,就是,如何锁定一条记录,防止并发
说是存储过程插入了两条相同的记录,
存储过程的脚本如下:
ALTER PROC [dbo].[Insert] @Tid Int AS BEGIN IF NOT EXISTS(SELECT 1 FROM Table WHERE TId = @Tid) BEGIN INSERT INTO Table (INSERTDATE,TID ) VALUES (GETDATE(), @Tid); END END
看了一下他的存储过程,也做了是否存在的判断,但这种判断在并发执行下是远远不够的,因为可能有多个会话判断到某一条记录不存在,然后同时插入,所以,出现帖子中描述的问题就不足为奇了,这个测试起来也很简单,接触sqlquerystress这个工具,开启多个线程,每个线程多次循环插入
首先,建立一张表,类似这个一个存储过程,表上建立非唯一的索引
if exists(select 1 from sys.objects where type='U' and name ='testlock1') drop table testlock1 --建表 create table testlock1 ( id int, Createdate datetime, ) --建立索引 create index index_1 on testlock1(id) --建立存储过程插入数据 create proc ups_TestLock @i int as begin begin try begin tran if not exists(select 1 from t where id=@i ) begin insert into testlock1 values (@i,GETDATE()); end commit end try begin catch rollback end catch end
关于并发测试,我们借助于sqlquerystress这个工具,下面会有截图,测试脚本如下
declare @i int set @i=cast( rand()*100000 as int)--生成一个100000以内的随机数 exec test_p @i
在sqlquerystress这个工具中,开启30个线程,每个现成循环插入2000条数据
如截图
好了,记录插入完成(本文不是性能测试,不用太关注时间指标),有没有重复的数据呢?
直接上图,有图有真相,重复记录还真不少
原因在哪里?上面说了,因为可能有多个回话判断到某一条记录不存在,然后同时插入,这样就造成了插入重复数据的情况
那么,改如何做判断才能防止类似的并发造成的问题呢?
于是我想到锁,其实想到锁的时候我心里是没谱的,一直没太弄明白那些显式的锁提示,到底行不行,有没有问题,于是就测试吧
于是我把存储过程改成这样
alter proc ups_TestLock
@i int
as
begin
begin try
begin tran
if not exists(select 1 from t with(xlock,rowlock) where id=@i )--注意这里加上xlock,rowlock,行级排它锁
begin
insert into testlock1 values (@i,GETDATE());
end
commit
end try
begin catch
rollback
end catch
end
用truncate table testlock1 清空刚才的测试表,继续上的测试
令人不解的是这次还有重复记录,虽然比一开始少了一些,但是锁定的问题归总还是没有解决
想来想想去不知道问题出在哪里,用sp_lock @@spid查看回话的锁信息的时候,确实有一个key级的排它锁,但是为什么没有锁定记录呢?
如图
后来上网查,有人说要建立唯一索引,才能锁定一行记录,将索引改成唯一索引后
如下脚本
drop index index_1 on testlock1
create unique index index_1 on testlock1(id)
在测试,发现确实没有重复记录了,想想是不是巧合呢?
又反反复复测了即便,确实没有重复的,证明在查询条件上建立唯一索引后,然后加上xlock,rowlock后,确实“锁住”记录了,解决了并发问题
事情到这里还没有结束,为什么呢?下班时候,在公交车上还在想这个问题……
后来想想,非唯一索引无法“锁定”记录,出现重复的问题,唯一索引解决了并发,
问题肯定还是并发时候,因为是多线程并行插入的,会不会是不同线程同时插入的,
就是说:A,B两个线程同时插入一条id为12345的数据,他们在插入之前判断的时候,数据库那个时刻中,确实没有id为12345的数据
所以就同时插入了,那么根据这里的推理,重复数据肯定是不同回话插入的,
想起来真令人兴奋,测测看吧
于是我将表结构修改为如下,增加一个插入的回话ID列
if exists(select 1 from sys.objects where type='U' and name ='testlock1') drop table testlock1 --建表 create table testlock1 ( id int, Createdate datetime, SessionID varchar(50) ) --建立索引 create index index_1 on testlock1(id) alter proc ups_TestLock @i int as begin begin try begin tran if not exists(select 1 from testlock1 with(xlock,rowlock) where id=@i ) begin insert into testlock1 values (@i,GETDATE(),@@spid);--这里插入一列回话ID end commit end try begin catch rollback end catch end
继续用sqlquerystress测试,线程还是30个,每个线程循环插入2000次
再次用该脚本查询
select COUNT(1),id,Createdate from testlock1 group by id,Createdate having(COUNT(1))>1
有两条重复的,那么我们就看看这两条重复数据的回话ID吧
果然不出所料!!!
是不同的回话插入的,这也就解释了为么在判断时候加了行级排它锁,却仍然锁不住记录的原因
并发插入的时候,因为各个回话是取数据库中检测记录,数据库中不存在就插入,却忽视了各个会话之间可能存在的重复值
假如是唯一索引,会话之间也是需要等待的,确保索引的唯一性。
这也就解释了,用行级排它锁“锁定”一行记录的时候,在锁定的条件上建议唯一索引的原因。
上一篇: php怎么取得变量中的数组并赋值到变量中