Sql Server锁总结

 

MSSQL 并发控制小结

 

l 并发操作中容易出现的问题：

1.       更新丢失

a)         数据库中的原值是200,A用户在一个事务里将其更新为300，同时B用户在一个事务里将其更新为400,但是B事务因为一些原因导致事务rollback了，当AB执行完后，数据库中的值仍为200，这样A的更新就丢失了

2.       脏读

a)         数据库中的原值为200，A用户在一个事务里将其更新为300,在该事务还没有Commit & rollback之前，B用户会读到300,如果后来A事务rollback了，这样B就出现了脏读。

3.       不可重复读

a)         数据库中的原值为200, A用户在一个事务里，第一次select时，结果为200，紧接着B用户在另一个事务中更新为300，然后当A再做select操作，结果就不再是200，而是300，这就出现了同一事务中对同一行数据的两次select结果不一致，该行数据就为不可重复读。

4.       虚读

a)         数据库中值大于200的行数原来有10行，A用户在一个事务里，第一次select后，结果为10行，紧接着B用户在另一个事务中insert了一条新数据，如300，然后当A再做select操作，结果就不再是10，而是11，新增的那行数据就为虚读。

l 为解决这些问题，而生的隔离级别：

1.       末授权读取

a)         隔离级别最低，不允许丢失更新

b)         允许多个事务同时读数据，但不允许多个事务同时写数据

2.       授权读取

a)         不允许脏读，也不允许丢失更新

b)         事务中已经修改的行，在没提交事务之前，禁止被其它事务select

c)         保证一个事务不会读到其它事务已经修改但末提交的数据

d)         为SQL Server的默认隔离级别

3.       授权读取快照

a)         不允许脏读，也不允许丢失更新

b)         事务中已经修改的行，在没提交事务之前， 允许被其它事务select，访问的是该行的历史版本（可通过READ_COMMITED_SNAPSHOT设置）。

c)         保证一个事务不会读到其它事务已经修改但末提交的数据

4.       可重复读取

a)         不允许脏读，也不允许丢失更新，也不允许不可重复读

b)         事务中已经修改的行，在没提交事务之前，禁止被其它事务select

c)         事务中已经读取的行，在没提交事务之前，也禁止其它事务的修改，允许其它事务读取

d)         所有共享锁也必须保持到事务结束。

5.       序列化

a)         最严格的事务隔离，禁止相关事务的并发，相关事务只能一个接一个地执行。

b)         所有共享锁也必须保持到事务结束。

c)         不仅需要锁定已经读数据，还要使用‘键范围锁’锁定潜在的数据，防止其它事务insert新的数据。

l 并发控件模型

1.         悲观

a)         在悲观模型中，写者总是阻塞读者和写者，而读者也会阻塞写者                  

2.         乐观

a)         在乐观模型中，唯一可能发生阻塞的是写者阻塞写者。

b)         上面5种隔离级别中，只有授权读取快照属于乐观模型

l 各种锁

1.         共享锁

a)         读取数据时，会在一行，一页，或者整个表上加上共享锁

b)         共享锁不会妨碍其它事务的读取操作，但会使其它事务的修改操作陷入等待

2.         排他锁

a)         插入，更新，删除操作时，会加排他锁

b)         排它锁会持续锁定，直到该事务结束

3.         更新锁

a)         当执行一个修改操作时，需要先找着等待修改的记录的位置，此时会加上更新锁，来保护数据，当找着正确的位置时，更新锁会升级为排他锁。

b)         与共享锁相容

4.         意向锁

a)         当执行一行数据修改时，该行会加排他锁，则该行锁在的页，或者表会加意向排他锁，防止另外的事务在其父容器上，如页级或者表级获取排他锁。

l 锁的观察

1.         sys.dm_tran_locks

a)         该视图用来取代sp_lock存储过程,来观察系统中的锁状态

b)         select object_name(object id)可查看是哪张表

2.         死锁

a)         循环死锁

                                       i.              两个事务互相持有对方想要的锁，就会产生死锁。

b)         转换死锁

                                       i.              两个事务均持有一个页上的共享锁，又想将共享锁转换为排他锁，由于;双方共享锁的存在，所以无法完成，而产生死锁。

c)         死锁时的优先级

                                       i.              可设置 SET DEADLOCK_PRIORITY来设置优先级，当死锁发生时，低优先级的事务将会作为牺牲者

                                     ii.              当优先级一致时，回滚开销较低的会话会作为牺牲者。

 

附一个经典的死锁:

    SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
string sql1 = "Update Lock1 SET C1=C1+1";
string sql2 = "SELECT * FROM Lock1";
ExecuteNonQuery(tran, sql1); 

ExecuteNonQuery(null, sql2); 

重现锁:

Query1: 

Begin Tran
  insert [User] values(1,'asdf')
  WaitFor Delay '00:00:10';

Rollback Tran; 

Query2:

  select * from [User] 

 解决方法：

       a). 把SELECT放在Update语句前：SELECT不在事务中，且执行完毕会释放S锁；
       b). 把SELECT也放加入到事务中：ExecuteNonQuery(tran, sql2);

       c). SELECT加With(NOLock)