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

专注or全面 2014-05-27 09:03:12
加精

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


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

果然不出所料!!!


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








...全文
20188 55 打赏 收藏 转发到动态 举报
写回复
用AI写文章
55 条回复
切换为时间正序
请发表友善的回复…
发表回复
DishBirder 2016-02-12
  • 打赏
  • 举报
回复
分析的很好,就需要这样的帖子
yinhuaiwang 2015-10-05
  • 打赏
  • 举报
回复
学习中 。。。。。。。。。。。。。。。不错
转身@未来 2015-09-29
  • 打赏
  • 举报
回复
讨论不错,值得学习。
江南雪_158 2015-08-18
  • 打赏
  • 举报
回复
学些学习
衣舞晨风 2015-04-04
  • 打赏
  • 举报
回复
楼主您好,之前没用过SQLStress,想问一下,你那边有这方面的教程吗?我把数据库连接建立好了,但不清楚怎么使用啊
wzm12349 2015-03-25
  • 打赏
  • 举报
回复
学习中.................感觉不错....................
土豆131421 2015-02-10
  • 打赏
  • 举报
回复
建立了唯一索引,就不要用 XLOCK 和ROWLOCK 了
黄_瓜 2015-02-06
  • 打赏
  • 举报
回复
引用 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),这个对于查询优化有很大的帮助
tosuba 2015-01-05
  • 打赏
  • 举报
回复
引用 2 楼 xxzxwsx 的回复:
建立唯一索引之后,是不是不用排它锁也能支持并发啦?
我试了下,不用排它锁并发也没有重复记录,效率比使用排它锁略高。
越过越咸 2014-12-20
  • 打赏
  • 举报
回复
还是唯一索引是王道
Dino系我 2014-12-16
  • 打赏
  • 举报
回复
引用 44 楼 zhong_han_jun 的回复:
看了很久,不确定各位前辈最后是讨论到哪一步了
晕 习惯摁了下ctrl+enter 我想问下 现在是对于这种高并发插入数据导致类似id重复的问题是怎么解决的,看大家说的我总结了两点,不知道是哪一种 1、使用唯一约束或唯一索引,并且在存储过程里加with(xlock,rowlock)解决 2、使用唯一约束或唯一索引,不使用行锁,对于插入重复导致数据库报错的问题在程序中用类似try{}catch{}去控制
Dino系我 2014-12-16
  • 打赏
  • 举报
回复
看了很久,不确定各位前辈最后是讨论到哪一步了
Dino系我 2014-12-16
  • 打赏
  • 举报
回复
引用 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已经存在了 难道就不插入了吗 这种业务信息应该是不能丢的吧
w87875251l 2014-12-15
  • 打赏
  • 举报
回复
先收藏 , 有时间再看。。。。
HeavenInShanghai 2014-08-21
  • 打赏
  • 举报
回复
点个赞。楼主试验验证了微软的官方文档: 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.
BiChangQing 2014-07-31
  • 打赏
  • 举报
回复
不错,收藏!
flai 2014-07-07
  • 打赏
  • 举报
回复
最近也遇到这样的问题,mark.
以学习为目的 2014-06-12
  • 打赏
  • 举报
回复
支持这种好帖子
寡亾 2014-06-10
  • 打赏
  • 举报
回复
此贴不读,对不起人民!顶!
seusoftware 2014-06-09
  • 打赏
  • 举报
回复
传说中的幻读(phantom reads)
加载更多回复(34)

27,581

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧