当前位置:Gxlcms > mysql > 锁住你的记录:sqlserver锁定数据库中的一行记录

锁住你的记录:sqlserver锁定数据库中的一行记录

时间:2021-07-01 10:21:17 帮助过:18人阅读

关于锁这一块一直没弄懂,刚好今天发现一个比较有意思的帖子,连接为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吧


果然不出所料!!!

是不同的回话插入的,这也就解释了为么在判断时候加了行级排它锁,却仍然锁不住记录的原因

并发插入的时候,因为各个回话是取数据库中检测记录,数据库中不存在就插入,却忽视了各个会话之间可能存在的重复值

假如是唯一索引,会话之间也是需要等待的,确保索引的唯一性。

这也就解释了,用行级排它锁“锁定”一行记录的时候,在锁定的条件上建议唯一索引的原因。


人气教程排行