一、问题描述：

sqlserver 频发发生死锁的问题，通过分析xml_deadlock_report 扩展事件分析，发现是update和select之间产生了死锁，具体死锁报告分析请参看另一篇文章，这边文章主要针对如何解决查询和更新死锁问题。

SQLServer默认的隔离级别为：“read commited” (已提交读)，默认隔离级别下，SQLServer数据库查询会获取共享 (S) 锁；更新需要获取更新锁(U)和排他锁(X), 并且S锁和U锁可以并存，但是X锁是独占，也就是说在这种隔离级别下，读写操作互斥，读操作会阻塞写操作，写操作也会阻塞读操作。

默认隔离级别(RC)下，sqlserver是依靠锁来解决脏读问题（更新没提交，就无法查询；查询没有结束就不能执行更新操作)，我们知道Oracle和mysql等关系型数据库都是依靠多版本控制(MVCC)来解决此类问题，那么sqlserver没有MVCC吗？

好在SQLServer2005引入了基于行版本控制的隔离级别。这种隔离级别允许读取者得到行的一个前面已提交的值，而不会阻塞写，但是需要手动开启这个特性；

二、sqlserver中的锁模式:

1、共享锁(S锁)

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

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

2、更新锁(U锁)

首先updtae语句，对于符合条件的数据添加U锁(注意U锁和X锁不兼容)真正更新数据时，把U锁升级成X锁，即update过程，是U锁升级X锁，修改数据例如，update table1 set type='1’ where type='b'实际过程是首先对扫描的数据添加U锁，对于不符合条件的，释放U锁;对于符合条件的，把U锁升级成X锁修改数据。

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

数据库引擎在准备执行更新时放置更新 (U) 锁。 U 锁与 S 锁兼容， U 锁与 U 锁互斥，所以一次只有一个事务可以在给定资源上持有 U 锁。许多并发事务可以持有 S 锁，但只有一个事务可以持有资源上的 U 锁。 更新 (U) 锁最终升级到独占锁（X）以更新行。

总结起来：

• 当执行UPDATE操作时，SQL Server首先会对要更新的数据行加上 更新锁（U）。这个更新锁允许其他事务读取数据（使用共享锁），但不允许其他事务修改数据（因为更新锁与更新锁冲突）。

• 如果UPDATE操作实际修改了数据行（即找到了匹配的行并且执行了更新），那么更新锁（U）会升级为 排它锁（X）。排它锁会阻止其他事务读取或修改数据行，直到当前事务完成

3、排它锁(X锁)

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

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

增删改查使用：

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

2）UPDATE 使用更新锁定数据，然后升级成排它锁；

3）INSERT,DELETE 语句使用排它锁；

4、锁和隔离级别的关系:

在任何隔离级别下，事务在执行写操作时都申请排它锁（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）操作；

5、锁模式之间兼容性：

三、SQL SERVER如何开启MVCC（使用row-versioning）

1、SQLServer提供两种快照隔离级别开启行版本控制：

• Read Committed Snapshot Isolation (已提交读快照隔离，RCSI)
• Snapshot Isolation (快照隔离级别)

我们重点介绍下第一种，因为第一种方式无需修改当前应用，而第二种方式则需要修改当前应用程序代码来适配。

2、开启 Read Commited Snapshot Isolation 语法：

ALTER DATABASE 数据库名 SET READ_COMMITTED_SNAPSHOT ON

注意：执行这条命令前，需要先断开数据库所有连接，否则执行该命令会一直阻塞下去。

3、执行过后通过如下命令查询配置是否生效:

SELECT name, is_read_committed_snapshot_on FROM sys.databases WHERE name = '数据库名';

4、sqlserver开启MVCC后的锁：

对于使用基于行版本控制的隔离级别的事务，读操作不对数据请求共享锁。这意味着使用行版本控制的读取器不会妨碍其他读取器或编写器访问同一数据。同理，写操作也不会妨碍读操作。但是，写操作会互斥(即使是在基于行版本控制的隔离级别下运行)。两个写操作不能同时修改同一数据。换句话说：开启mvcc后，在 READ COMMITTED 快照隔离级别下运行的所有查询将使用行版本控制，这意味着读取操作不会阻止更新操作;

四、解决死锁问题：

1、方法1：查询语句加nolock

在SQL Server中，可以在SELECT查询中使用WITH (NOLOCK)来指定不使用锁，能够读取被事务锁定的数据，也称为脏读。在查询中使用nolock时，不加共享锁来阻止其他事务修改当前事务读取的数据，其他事务添加的排他锁不会阻碍当前事务读取锁定数据。提高了并发操作，但代价是可能会读取到以后会被其他事务回滚的数据修改。

使用方式：

SELECT * FROM TableName WITH (NOLOCK) WHERE Condition;

影响与注意事项：

1）性能提升：通过不加锁，nolock可以提高查询性能，减少锁竞争和死锁的可能性。

2）数据一致性问题：由于nolock允许读取未提交的数据，因此可能会出现脏读、幻读和不可重复读的情况。这可能会导致读取到的数据与实际数据不一致。

3）模式稳定性锁定：虽然nolock不会锁定行级数据，但它会获取模式稳定性(Sch-S)锁定，这可以防止在执行查询时更改表的结构。这可能会阻止某些需要模式修改(Sch-M)锁定的操作，如索引REBUILD或sp_recompile表。

4）使用场景：当对数据的实时性要求不高，且可以容忍一定程度的数据不一致时，可以考虑使用nolock。例如，在大数据量的报表查询中，为了提高查询性能，可使用nolock。

2、方法2：开启已提交读快照隔离

1)、断开库连接；

2)、开启库已提交读快照隔离级别：

ALTER DATABASE 数据库名 SET READ_COMMITTED_SNAPSHOT ON;

3、验证已提交读快照隔离下读写锁定情况：

1)、造测试数据

create  table liuwenhe (id int not null,name varchar(10));

alter table  liuwenhe ADD CONSTRAINT pk_id PRIMARY KEY (id);

CREATE INDEX idx_name ON liuwenhe(name);

insert into  liuwenhe values(1,'liu');

insert into liuwenhe values(2,'he');

insert into liuwenhe values (3,'wen');

insert into liuwenhe  values (4,'zi');

实验一：验证默认隔离级别下，更新会阻塞查询

1)、确认liu这个库没有开启读提交快照隔离级别：

确认当前隔离级别为读已提交： ReadCommitted

SELECT CASE transaction_isolation_level

WHEN 0 THEN 'Unspecified'

WHEN 1 THEN 'ReadUncommitted'

WHEN 2 THEN 'ReadCommitted'

WHEN 3 THEN 'RepeatableRead'

WHEN 4 THEN 'Serializable'

WHEN 5 THEN 'Snapshot'

END AS transaction_isolation_level

FROM sys.dm_exec_sessions

WHERE session_id = @@SPID;

session1：开启事务执行update

开启事务，执行update，不提交

begin transaction;

update  liuwenhe  set  name ='liuwenhe'  where id=3;

session 2：尝试执行查询，看是否会阻塞：

select * from liuwenhe  where id=3;

发现处于等待中，具体如下所示：

查看具体锁信息：

发现session2的select操作在等待session1的update操作；

如下所示：等待LCK_M_S锁模式，LCK_M_S：正在等待获取共享锁。

结论：在默认读提交隔离级别下，更新操作会阻塞查询操作，并且说明查询会加S共享行锁；

实验二、 默认读提交隔离级别下， 查询会阻塞更新：

方式1：显示加S共享锁，这样可以证明S锁和update使用的锁是互斥的！

session1执行查询，带with(holdlock)关键字，这样会持有S共享锁，直到事务结束；

BEGIN TRANSACTION

select * from liuwenhe   WITH (HOLDLOCK)  where id=3

session 2尝试执行update操作，发现处于阻塞：

session3查看具体的锁等待信息：发现session2被session1等待，但是session1执行的是查询语句

方式2：

session1(spid=61)执行update不提交，session2(spid=84)执行select同一行，处于等待中，被session1阻塞，session3(spid=95)尝试执行update同一行，发现处于等待中，被session2等待，

具体如下所示：

如下所示：session3(spid=95)在等待获取更新锁（正常更新锁和共享锁是可以兼容的，这里等待更新锁其实是由于session1造成的，但是这里只是想证明查询会阻塞更新），占有锁的会话是session2(spid=84)，我们知道session2执行的是查询；

结论：默认读提交隔离级别下，查询确实会阻塞更新操作；

实验三：在读提交快照隔离级别下，更新操作会阻塞查询吗？

1）开启读提交快照隔离级别：由于这个库有连接，所以处于等待中

2）、在等待spid=82的会话断开连接，才能执行成功

3）、把连接都关闭后，执行成功：

或者直接通过图形化修改，会自动关闭库连接：

4）确认读提交快照隔离级别已经开启：

5）session1 开启事务，执行update，不提交，session2执行查询发现可以正常执行，查询到了session1更新之前的数据；

session1：执行update,不提交；

session2：正常执行查询

结论：在读提交快照隔离级别下，更新不阻塞查询；并且说明查询不加S锁了，

实验四：在读提交快照隔离级别下，查询会阻塞更新吗？

session1：执行查询语句，故意延迟10秒：

session2：执行update，发现不阻塞

结论：在读提交快照隔离级别下，查询不会阻塞更新

总结：在默认隔离级别下，查询会加S锁，但是会在查询完毕后，就释放S锁，并且当查询多个数据行的时候，可能会加page级别的S锁，整个加锁过程是扫描到的行或者page加S锁，扫描结束释放该行或者page的S锁，继续扫描下一行或者page，然后加S锁，但是在读提交快照隔离级别下，查询不加S共享锁，自然不会阻塞更新操作，更新也不会阻塞查询操作，所以我们可以通过修改库的隔离级别为读提交快照隔离级别来提高库的并发处理能力，减少死锁问题；

注意事项

1、temp库空间问题：

读提交快照隔离级别下，MVCC行版本控制，是通过在读写并发时，让读会话去读取历史行版本数据，从而避免阻塞等待，那么这些历史行数据，是存储在tempdb 数据库！SQLServer会自动管理历史行版本数据，以确保版本控制的行在不再需要时被删除。

事务使用的是行版本控制而不是共享锁，tempdb 数据库必须具有足够的空间用于版本存储区。在 tempdb 已满的情况下，更新操作将停止生成版本，并继续执行，但是因为所需的特定行版本不再存在，读取操作可能会失败。只要活动事务需要访问行版本，就必须存储行版本。后台线程每隔一分钟删除一次不再需要的行版本，从而释放 tempdb 中的版本空间。

2、大事务拆分\避免长事务：

在大批量数据操作时，尽量分多批事务进行处理，并及时提交/回滚事务，避免tempdb中的行版本数据过大。

3、查看mvcc多版本数据占用的空间大小：

SELECT

SUM(version_store_reserved_page_count) AS [version store pages used]

,(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]

FROM tempdb.sys.dm_db_file_space_usage;