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

[专注or全面]

首先感谢黄版主的指导，以下是本人对今天的问题的测试和总结，本人才疏学浅，如有不对，敬请指出，非常感谢


关于锁这一块一直没弄懂，刚好今天发现一个比较有意思的帖子，连接为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 )--注意这里加上<span style="font-family: Arial, Helvetica, sans-serif;">xlock,rowlock，行级排它锁</span>
			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吧

果然不出所料！！！


是不同的回话插入的，这也就解释了为么在判断时候加了行级排它锁，却仍然锁不住记录的原因
并发插入的时候，因为各个回话是取数据库中检测记录，数据库中不存在就插入，却忽视了各个回话之间可能存在的重复值
假如是唯一索引，回话之间也是需要等待的，确保索引的唯一性。
这也就解释了，用行级排它锁“锁定”一行记录的时候，在锁定的条件上建议唯一索引的原因。

[seusoftware]

传说中的幻读（phantom reads）

[寡亾]

此贴不读，对不起人民！顶！

[以学习为目的]

支持这种好帖子

[flai]

最近也遇到这样的问题，mark.

[BiChangQing]

不错，收藏！

[HeavenInShanghai]

点个赞。楼主试验验证了微软的官方文档： Lock hints ROWLOCK, UPDLOCK, AND XLOCK that acquire row-level locks may place locks on index keys　rather than the actual data rows. For example, if a table has a nonclustered index, and a SELECT statement using a lock hint is handled by a covering index, a lock is acquired on the index key in the covering index rather than on the data row in the base table.

[w87875251l]

先收藏 ， 有时间再看。。。。

[Dino系我]

引用 28 楼 wmxcn2000 的回复:

-- LZ 把 下面两句，写成一句就可以了
    IF NOT EXISTS(SELECT 1 FROM Table WHERE TId = @Tid)
    BEGIN
        INSERT INTO Table (INSERTDATE,TID     ) VALUES (GETDATE(),  @Tid);
    END
-- 修改如下 ：

INSERT INTO table(INSERTDATE,TID ) 
SELECT top 1 GETDATE(),  @Tid from mytable 
where NOT EXISTS(SELECT 1 FROM Table WHERE TId = @Tid)

这种情况下@tid已经存在了 难道就不插入了吗 这种业务信息应该是不能丢的吧

[Dino系我]

看了很久，不确定各位前辈最后是讨论到哪一步了

[Dino系我]

引用 44 楼 zhong_han_jun 的回复:

看了很久，不确定各位前辈最后是讨论到哪一步了

晕 习惯摁了下ctrl+enter 我想问下 现在是对于这种高并发插入数据导致类似id重复的问题是怎么解决的，看大家说的我总结了两点，不知道是哪一种 1、使用唯一约束或唯一索引，并且在存储过程里加with(xlock,rowlock)解决 2、使用唯一约束或唯一索引，不使用行锁，对于插入重复导致数据库报错的问题在程序中用类似try{}catch{}去控制

[越过越咸]

还是唯一索引是王道

[tosuba]

引用 2 楼 xxzxwsx 的回复:

建立唯一索引之后，是不是不用排它锁也能支持并发啦？

我试了下，不用排它锁并发也没有重复记录，效率比使用排它锁略高。

[黄_瓜]

引用 33 楼 Beirut 的回复:

方法1、设置事务的级别提升为SERIALIZABLE 方法2、设置唯一约束或者唯一索引 方法3、merge 这个是08或者更高版本用的。不过这个也最简单。

竟然又看到这个帖子，看到自己的回复 方法3 merge这个也是不能防止的，推荐一下方式处理 begin tran -- 使查询的更新锁保留到事务结束 if not exists( select * from tb with(holdlock, updlock) where id = @id ) insert tb values( @id, @value ); --else -- update tb set value = @value where id = @id; commit tran 推荐使用这种单句的处理方式，不用显式的事务控制，避免考虑与其他会显式使用事务的地方的交互性 declare @id int, @value int insert tb select @id, @value where not exists( select * from tb with(holdlock, updlock) where id = @id ) --if @@rowcount = 0 -- update tb set value = @value where id = @id; 不推荐这种方式，容易出来死锁 declare @id int, @value int merge tb with(holdlock, updlock) using( values(@id, @value) ) data( id, value) on data.id = tb.id when not matched by target then insert values( id, value ) --when matched then update set value = data.value 另外，where 这个判断是否重复的条件列，需要创建索引，否则因为锁的关系，数据处理的效率会极低 如果表中本来就没有重复数据，应该直接建立唯一索引（UNIQUE INDEX），这个对于查询优化有很大的帮助

[土豆131421]

建立了唯一索引，就不要用 XLOCK 和ROWLOCK 了

[wzm12349]

学习中.................感觉不错....................

[衣舞晨风]

楼主您好，之前没用过SQLStress，想问一下，你那边有这方面的教程吗？我把数据库连接建立好了，但不清楚怎么使用啊

[江南雪_158]

学些学习

[转身@未来]

讨论不错，值得学习。

[yinhuaiwang]

学习中 。。。。。。。。。。。。。。。不错

[DishBirder]

分析的很好，就需要这样的帖子