sql server 共享锁

sql server 共享锁

一. 为什么要引入锁

多个用户同时对数据库的并发操作时会带来以下数据不一致的问题:

1、丢失更新

A,B两个用户读同一数据并进行修改,其中一个用户的修改结果破坏了另一个修改的结果,比如订票系统

2、脏读

A用户修改了数据,随后B用户又读出该数据,但A用户因为某些原因取消了对数据的修改,数据恢复原值,此时B得到的数据就与数据库内的数据产生了不一致

3、不可重复读

A用户读取数据,随后B用户读出该数据并修改,此时A用户再读取数据时发现前后两次的值不一致

并发控制的主要方法是封锁,锁就是在一段时间内禁止用户做某些操作以避免产生数据不一致

二、什么是共享锁

共享 (S) 锁允许并发事务读取 (SELECT) 一个资源。资源上存在共享 (S) 锁时，任何其它事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享 (S) 锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享 (S) 锁。

三、共享锁示例

为下面描述方便，这里用T1代表一个数据库执行请求，T2代表另一个请求

1、实例1

T1:    select * from table (请想象它需要执行1个小时之久，后面的sql语句请都这么想象）
T2:    update table set column1='hello'


			

过程
 

T1运行 （加共享锁)
T2运行
If T1 还没执行完
    T2等......
else
    锁被释放
    T2执行
endif
 

说明

T2之所以要等，是因为T2在执行update前，试图对table表加一个排他锁，而数据库规定同一资源上不能同时共存共享锁和排他锁。所以T2必须等T1执行完，释放了共享锁，才能加上排他锁，然后才能开始执行update语句。

 

2、实例2

T1:    select * from table
T2:    select * from table


		

分析

T1运行，则table被加锁，比如叫lockA
T2运行，再对table加一个共享锁，比如叫lockB。
 

说明

两个锁是可以同时存在于同一资源上的（比如同一个表上）。这被称为共享锁与共享锁兼容。这意味着共享锁不阻止其它session同时读资源，但阻止其它session update
 

3、实例3

T1:    select * from table
T2:    select * from table
T3:    update table set column1='hello'

		

说明

这次，T2不用等T1运行完就能运行，T3却要等T1和T2都运行完才能运行。因为T3必须等T1和T2的共享锁全部释放才能进行加排他锁然后执行update操作。
 

4、实例4：（死锁的发生）
 

T1:
begin tran
select * from table (holdlock) (holdlock意思是加共享锁，直到事物结束才释放)
update table set column1='hello'

T2:
begin tran
select * from table(holdlock)
update table set column1='world'

		

说明

假设T1和T2同时达到select，T1对table加共享锁，T2也对加共享锁，当T1的select执行完，准备执行update时，根据锁机制，T1的共享锁需要升级到排他锁才能执行接下来的update.在升级排他锁前，必须等table上的其它共享锁释放，但因为holdlock这样的共享锁只有等事务结束后才释放，所以因为T2的共享锁不释放而导致T1等(等T2释放共享锁，自己好升级成排
他锁），同理，也因为T1的共享锁不释放而导致T2等。死锁产生了。
 

5、实例5

T1:
begin tran
update table set column1='hello' where id=10

T2:
begin tran
update table set column1='world' where id=20


		

6、实例6

T1:
begin tran
select * from table(xlock) (xlock意思是直接对表加排他锁)
update table set column1='hello'

T2:
begin tran
select * from table(xlock)
update table set column1='world'

		

说明

当T1的select 执行时，直接对表加上了排他锁，T2在执行select时，就需要等T1事物完全执行完才能执行。排除了死锁发生。
但当第三个user过来想执行一个查询语句时，也因为排他锁的存在而不得不等待，第四个、第五个user也会因此而等待。在大并发情况下，让大家等待就显得性能就太不友好了。