sqlserver中的锁:

概念：

◆共享锁

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

共享 (S) 用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。

◆更新锁

更新 (U) 锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S) 锁，然后修改行，此操作要求锁转换为排它 (X) 锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。

若要避免这种潜在的死锁问题，请使用更新 (U) 锁。一次只有一个事务可以获得资源的更新 (U) 锁。如果事务修改资源，则更新 (U) 锁转换为排它 (X) 锁。否则，锁转换为共享锁。

更新 (U)用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。

排它锁

排它 (X) 用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。

排它 (X) 锁可以防止并发事务对资源进行访问。其它事务不能读取或修改排它 (X) 锁锁定的数据

增删改查使用：

SELECT 语句在默认隔离级别下会产生共享锁，该锁查询完就释放，不需要等待所在的事务提交。

UPDATE 语句先使用更新锁锁定数据，然后升级成排它锁

INSERT,UPDATE,DELETE 语句使用排它锁

排它锁不受隔离级别控制,共享锁受隔离级别控制
结论如下:
在任何隔离级别下，事务在执行写操作时都申请排它锁（exclusive lock），持有排它锁直到事务结束，排它锁不受隔离级别的控制；而共享锁（Shared Lock）受到隔离级别的控制，隔离级别影响Shared Lock的申请和释放：
在 Read Uncommitted隔离级别下，读操作不会申请Shared Lock；
在 Read Committed（不使用row-versioning），Repeatable Read 和 Serializable隔离级别下，都会申请Shared Lock；
在 Read Committed（不使用row-versioning） 隔离级别下，在读操作执行时，申请和持有Share Lock；一旦读操作完成，释放Shared Lock;
在 Repeatable Read 和 Serializable隔离级别下，事务会持有Shared Lock，直到事务结束（提交或回滚）；
在Serializable隔离级别下，事务会持有范围Shared Lock（Range Lock），锁定一个范围，在事务活跃期间，其他事务不允许在该范围中进行更新（Insert 或 delete）操作；

本人结论:

  1. SQLServer默认隔离级别: Read Committed.
  2. 更新锁和更新锁互斥。共享锁和和共享锁可以共享。共享锁和和更新锁可以共享。排它锁独占。
  3. SQLServer隔离级别只会影响共享锁。
  4. 事务(可以是数据库事务,可以是程序事务)在执行写操作时都申请排它 (X) 锁（exclusive lock),持有排它锁直到事务结束，排它锁不受隔离级别的控制；

试验1: 排它锁独占

图1 :删除一个表

mysql共享间隙锁死锁 sqlserver 共享锁_sql

图2:设置Read uncommitted隔离级别,查询非阻塞(不申请锁)

mysql共享间隙锁死锁 sqlserver 共享锁_mysql共享间隙锁死锁_02

图3:删除表,阻塞(排它锁独占,与排它锁互斥)

mysql共享间隙锁死锁 sqlserver 共享锁_sql_03

图4:SQLServer默认隔离级别,查询阻塞(排它锁独占,与共享锁互斥)

mysql共享间隙锁死锁 sqlserver 共享锁_sqlserver_04

试验2: 数据库事务持有更新锁期间,其他事务能申请共享锁,不能申请更新锁

1.更新锁与共享锁可以共享

在一个查询里:

BEGIN TRANSACTION
SELECT * FROM DrainModels WITH (UPDLOCK) WHERE DrainKind = 'Camera'
waitfor delay '00:00:10'
update DrainModels set Maker='888' where DrainKind = 'Camera'
commit TRANSACTION

在另一个查询里:

SELECT * FROM DrainModels WHERE DrainKind = 'Camera'

可以马上查询到数据。

2.更新锁和更新锁互斥

但如果要更新数据，必须等其他更新锁释放后才能执行。

update DrainModels set Maker='777' where DrainKind = 'Camera'

试验3: 测试下不同隔离级别下：共享锁和共享锁是否可以共享

查询1:

set transaction isolation level Repeatable Read;
BEGIN TRANSACTION
SELECT * FROM DrainModels WHERE DrainKind = 'Camera'
waitfor delay '00:00:10'
commit TRANSACTION

查询2:

SELECT * FROM DrainModels WHERE DrainKind = 'Camera'

结果:秒查

mysql共享间隙锁死锁 sqlserver 共享锁_mysql共享间隙锁死锁_05

结论:不同隔离级别下：共享锁和共享锁可以共享

试验4: 测试下不同隔离级别下：共享锁和更新锁是否可以共享

查询1:(先加更新锁)

set transaction isolation level Repeatable Read;
BEGIN TRANSACTION
SELECT * FROM DrainModels WITH (UPDLOCK) WHERE DrainKind = 'Camera'
waitfor delay '00:00:10'
commit TRANSACTION

查询2:(后加共享锁)

SELECT * FROM DrainModels WHERE DrainKind = 'Camera'

结果:秒查

mysql共享间隙锁死锁 sqlserver 共享锁_sqlserver_06


查询3:(先加共享锁)

set transaction isolation level Repeatable Read;
BEGIN TRANSACTION
SELECT * FROM DrainModels WHERE DrainKind = 'Camera'
waitfor delay '00:00:10'
commit TRANSACTION

查询4:(后加更新锁)

set transaction isolation level Repeatable Read;
BEGIN TRANSACTION
SELECT * FROM DrainModels WITH (UPDLOCK) WHERE DrainKind = 'Camera'
commit TRANSACTION

结果:秒查

mysql共享间隙锁死锁 sqlserver 共享锁_数据库_07

结论:不同隔离级别下：共享锁和更新锁可以共享