锁住你的记录

在使用SQL时，大都会遇到这样的问题，你Update一条记录时，需要通过Select来检索出其值或条件，然后在通过这个值来执行修改操作。

    但当以上操作放到多线程中并发处理时会出现问题：某线程select了一条记录但还没来得及update时，另一个线程仍然可能会进来select到同一条记录。

一般解决办法就是使用锁和事物的联合机制：

如：

1. 把select放在事务中, 否则select完成, 锁就释放了
2. 要阻止另一个select , 则要手工加锁, select 默认是共享锁, select之间的共享锁是不冲突的, 所以, 如果只是共享锁, 即使锁没有释放, 另一个select一样可以下共享锁, 从而select出数据

BEGIN TRAN
SELECT * FROM table WITH(TABLOCKX)

或者 SELECT * FROM table WITH(UPDLOCK, READPAST) 具体情况而定。

UPDATE ....
COMMIT TRAN

锁描述：
HOLDLOCK：将共享锁保留到事务完成，而不是在相应的表、行或数据页不再需要时就立即释放锁。HOLDLOCK 等同于 SERIALIZABLE。
NOLOCK 不要发出共享锁，并且不要提供排它锁。当此选项生效时，可能会读取未提交的事务或一组在读取中间回滚的页面。有可能发生脏读。仅应用于 SELECT 语句。
PAGLOCK：在通常使用单个表锁的地方采用页锁。
READCOMMITTED：用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下，SQL Server 2000 在此隔离级别上操作。
READPAST：跳过锁定行。此选项导致事务跳过由其它事务锁定的行（这些行平常会显示在结果集内），而不是阻塞该事务，使其等待其它事务释放在这些行上的锁。 READPAST 锁提示仅适用于运行在提交读隔离级别的事务，并且只在行级锁之后读取。仅适用于 SELECT 语句。
READUNCOMMITTED：等同于 NOLOCK。
REPEATABLEREAD：用与运行在可重复读隔离级别的事务相同的锁语义执行扫描。
ROWLOCK：使用行级锁，而不使用粒度更粗的页级锁和表级锁。
SERIALIZABLE：用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于 HOLDLOCK。
TABLOCK：使用表锁代替粒度更细的行级锁或页级锁。在语句结束前，SQL Server 一直持有该锁。但是，如果同时指定 HOLDLOCK，那么在事务结束之前，锁将被一直持有。
TABLOCKX 使用表的排它锁。该锁可以防止其它事务读取或更新表，并在语句或事务结束前一直持有。
UPDLOCK：读取表时使用更新锁，而不使用共享锁，并将锁一直保留到语句或事务的结束。UPDLOCK：的优点是允许您读取数据（不阻塞其它事务）并在以后更新数据，同时确保自从上次读取数据后数据没有被更改。
XLOCK：使用排它锁并一直保持到由语句处理的所有数据上的事务结束时。可以使用 PAGLOCK 或 TABLOCK 指定该锁，这种情况下排它锁适用于适当级别的粒度。
————————————————

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

跟我对锁的疑惑差不多，就是，如何锁定一条记录，防止并发
说是存储过程插入了两条相同的记录，
存储过程的脚本如下：

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这个工具，下面会有截图，测试脚本如下

SQL code

 

?

1 2 3

declare @i int set @i=cast(  rand()*100000 as int)--生成一个100000以内的随机数 exec test_p @i

在sqlquerystress这个工具中，开启30个线程，每个现成循环插入2000条数据
如截图


好了，记录插入完成（本文不是性能测试，不用太关注时间指标），有没有重复的数据呢？
直接上图，有图有真相，重复记录还真不少


原因在哪里？上面说了，因为可能有多个回话判断到某一条记录不存在，然后同时插入，这样就造成了插入重复数据的情况
那么，改如何做判断才能防止类似的并发造成的问题呢？
于是我想到锁，其实想到锁的时候我心里是没谱的，一直没太弄明白那些显式的锁提示，到底行不行，有没有问题，于是就测试吧
于是我把存储过程改成这样

SQL code

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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级的排它锁，但是为什么没有锁定记录呢？
如图

后来上网查，有人说要建立唯一索引，才能锁定一行记录，将索引改成唯一索引后
如下脚本

SQL code

 

?

1 2	drop index index_1 on testlock1 create unique index index_1 on testlock1(id)

在测试，发现确实没有重复记录了，想想是不是巧合呢？又反反复复测了即便，确实没有重复的，证明在查询条件上建立唯一索引后，然后加上xlock,rowlock后
确实“锁住”记录了，解决了并发问题

事情到这里还没有结束，为什么呢？下班时候，在公交车上还在想这个问题……
后来想想，非唯一索引无法“锁定”记录，出现重复的问题，唯一索引解决了并发，
问题肯定还是并发时候，因为是多线程并行插入的，会不会是不同线程同时插入的，
就是说：A，B两个线程同时插入一条id为12345的数据，他们在插入之前判断的时候，数据库那个时刻中，确实没有id为12345的数据
所以就同时插入了，那么根据这里的推理，重复数据肯定是不同回话插入的，
想起来真令人兴奋，测测看吧
于是我将表结构修改为如下，增加一个插入的回话ID列

SQL code

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

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次

再次用该脚本查询

SQL code

 

?

1 2 3

select COUNT(1),id,Createdate from testlock1 group by id,Createdate having(COUNT(1))>1

有两条重复的，那么我们就看看这两条重复数据的回话ID吧

果然不出所料！！！


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

锁住你的记录

原文：https://www.cnblogs.com/Alex80/p/11505722.html