事务隔离级别

什么是事务？

事务（Transaction）由作为包含执行的单个命令或一组命令组成。

通过事务可以将多个操作合并为单个工作单元。

事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。

如果某一事务成功，则在该事务中进行的所有数据修改均会提交(commit transaction)，成为数据库中的永久组成部分。 如果事务遇到错误且必须取消或回滚(rollback transaction)，则所有数据修改均被清除。

如果锁定持续时间过长，则涉及多个资源的事务可能会降低并发性。 因此，事务应尽量保持简短。

事务的4个特性

事务必须符合 ACID 属性（原子性、一致性、隔离性和持久性）才能保证数据的一致性。

• 原子性（Atomicity）
  事务必须只执行一次，并且必须是原子的，即所有工作都已完成，或者没有任何工作。原子化消除了只处理部分操作的机会。

• 一致性（Consistency）
  事务必须保持数据一致性，将数据的一个一致状态转换为另一种一致的数据状态。

• 隔离性（Isolation）
  事务必须为隔离单位，一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

• 持久性（Durability）
  事务必须可恢复，因此必须具有持久性。 如果事务已提交，则系统保证其更新可以保留，即使在提交后计算机立即崩溃。 使用专用日志记录，系统重新启动过程可以完成事务所需的未完成操作，使事务持久。

事务的分类

事务分为三类：显式事务、隐式事务、自动提交事务

• 显式事务
  用 begin transaction 明确指定事务的开始，由 commit transaction 提交事务、rollback transaction 回滚事务到事务结束。

• 隐式事务
  通过设置 set implicit_transactions on 语句，将隐式事务模式设置为打开。
  当以隐式事务模式操作时，不必使用 begin transaction 开启事务，当一个事务结束后，这个模式会自动启用下一个事务，只需使用 commit transaction 提交事务或 Rollback Transaction 回滚事务即可。

• 自动提交事务
  这是 SQL Server 的默认模式，它将每条单独的 T-SQL 语句视为一个事务。如果成功执行，则自动提交。如果错误，则自动回滚。

事务的隔离等级

image.png

事务的隔离级别 - 未提交的读取

允许读其他事务已修改未提交的数据行，相当于WITH(NOLOCK)提示，会引发脏读的问题。

image.png

事务的隔离级别 - 已提交的读取

SQL Server 默认的隔离级别。当事务在执行读操作时，会放置共享锁，以防止其他事务修改数据，当读完成时，会立即自动释放共享锁，有利于事务并发。可避免脏读，但会引发不可重复读的问题。

image.png

事务的隔离级别 - 可重复的读取

所有的共享锁（查询数据行的读行为）均保留至交易结束为止，而不是读取完毕后就释放共享锁，避免了不可重复读的问题。因没有申请范围锁，会产生幻读问题。

image.png

事务的隔离级别 - 可序列化

事务隔离级别的最高级，最严谨的等级，会锁定整个范围的索引键，事务之间完全隔离，基本无并发。与SELECT搭配HOLDLOCK的效果相同，事务之间完全是串行化执行。

image.png

事务的隔离级别-行版本控制隔离级别

image.png

事务的隔离级别-行版本控制隔离级别-读取已提交的快照

执行下面的语句开启

ALTER DATABASE [DBName] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE

在RCSI隔离级别下，事务有两个特性：

• 事务使用行版本（Row Version）代替共享锁，读操作不会阻塞其他事务的写操作；

• RCSI隔离级别保证语句级别的事务一致性，查询语句只能读取在该语句执行时已经提交的数据，如果在该语句执行时数据更新尚未提交，该语句读取不到。

存在不可重复读的问题

事务的隔离级别-行版本控制隔离级别-快照

执行下面的语句开启

ALTER DATABASE [DBName] SET ALLOW_SNAPSHOT_ISOLATION ON

把会话的隔离级别设置为SNAPSHOT，事务才能访问行版本的数据。

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

如果不把会话的隔离级别设置为SNAPSHOT，会话的隔离级别是悲观模式下的READ COMMITTED。

SNAPSHOT隔离级别不会阻塞其他事务的写操作，该隔离级别忽略数据的修改操作，只读取事务开启前已提交的行版本数据，事务运行期间由其他事务修改的数据不会被读取到。当SNAPSHOT事务尝试修改由其他事务修改的数据时，产生更新冲突，更新操作会异常终止。

事务的隔离级别 - 读取已提交与行版本控制隔离级别的对比

image.png

锁的介绍

锁模式

image.png

共享锁
共享锁（S 锁）允许并发事务在封闭式并发控制下读取 (SELECT) 资源。 资源上存在共享锁（S 锁）时，任何其他事务都不能修改数据。 读取操作一完成，就立即释放资源上的共享锁（S 锁），除非将事务隔离级别设置为可重复读或更高级别，或者在事务持续时间内用锁定提示（HOLDLOCK）保留共享锁（S 锁）。

更新锁
更新锁（U 锁）可以防止常见的死锁。共享模式到排他锁的转换必须等待一段时间，因为一个事务的排他锁与其他事务的共享模式锁不兼容；发生锁等待。若要避免这种潜在的死锁问题，请使用更新锁（U 锁）。 一次只有一个事务可以获得资源的更新锁（U 锁）。 如果事务修改资源，则更新锁（U 锁）转换为排他锁（X 锁）。

排他锁
排他锁（X 锁）可以防止并发事务对资源进行访问。 使用排他锁（X 锁）时，任何其他事务都无法修改数据；仅在使用 NOLOCK 提示或未提交读隔离级别时才会进行读取操作。
数据修改语句（如 INSERT、UPDATE 和 DELETE）通常请求共享锁和排他锁。

架构锁
在执行依赖于表架构的操作时使用。架构锁的类型是架构修改 (Sch-M) 和架构稳定性 (Sch-S)。

意向锁
建立锁层次结构，这些锁指示事务正在处理层次结构中较低级别的某些资源，而不是所有资源，较低级别的资源将具有共享锁、更新锁或排他锁。意向锁说明SQL Server有在资源的低层获得共享锁或排它锁的意向。

锁的兼容性

image.png

事务是为了解决并发情景下，数据变更过程的一致性。
事务隔离级别为了解决并发情景下不同程度的数据读取异常。
事务隔离级别由锁来实现。

可锁定的资源

image.png

锁升级

锁升级是将许多较细粒度的锁转换成数量更少的较粗粒度的锁的过程，这样可以减少系统开销，但却增加了并发争用的可能性。

当 SQL Server 数据库引擎 获取低级别的锁时，它还将在包含更低级别对象的对象上放置意向锁。如获取了行上的共享锁，还会在页和表上放置意向共享锁。

数据库引擎 不会将行锁或键范围锁升级到页锁，而是将它们直接升级到表锁。 同样，页锁始终升级到表锁。

锁升级阈值

单个 Transact-SQL 语句在单个未分区表或索引上获得至少 5,000 个锁，将触发锁升级。

如何从表中删除超过5000条的记录？

DELETE FROM LogMessages WHERE LogDate < '2/1/2021'  --会导致锁升级引起阻塞

将大批操作分成多个小批操作，可以显著减少每个事务累积的锁数量，并防止锁升级。

WHILE EXISTS (SELECT TOP 1 1 FROM LogMessages WHERE LogDate < '2/1/2021')
BEGIN
    DELETE TOP(500) FROM LogMessages WHERE LogDate < '2/1/2021'
END

表提示应用

• 在对数据的准确性要求不太高时，或查询不会修改的数据时，尽量使用WITH(NOLOCK)

• 在取数时，不希望同一条记录被多个进程同时获取到，在查询时应开启事务并使用WITH(ROWLOCK,XLOCK,READPAST)

• 在高并发对同一条数据的值进行修改的情况下，为了保证数据的准确性，在查询时应开启事务并使用WITH(UPDLOCK)

NOLOCK一定不会产生阻塞吗？

READUNCOMMITTED 和 NOLOCK 提示仅适用于数据锁。 所有查询（包括那些带有 READUNCOMMITTED 和 NOLOCK 提示的查询）都会在编译和执行过程中获取 Sch-S（架构稳定性）锁。 因此，当并发事务持有表的 Sch-M（架构修改）锁时，将阻塞查询。 例如，数据定义语言 (DDL) 操作在修改表的架构信息之前获取 Sch-M 锁。 所有并发查询（包括那些使用 READUNCOMMITTED 或 NOLOCK 提示运行的查询）都会在尝试获取 Sch-S 锁时被阻塞。 相反，持有 Sch-S 锁的查询将阻塞尝试获取 Sch-M 锁的并发事务。

阻塞与死锁

阻塞

是指当一个数据库会话中的事务，正在锁定其他会话事务想要读取或修改的资源，造成这些会话发出的请求进入等待的状态。SQL Server 默认会让被阻塞的请求无限期地一直等待，直到原来的事务释放相关的锁，或直到它超时 (根据 SET LOCK_TIMEOUT参数)、服务器关闭、进程被杀死。一般的系统中，偶尔有短时间的阻塞是正常且合理的；但若设计不良的程序，就可能导致长时间的阻塞，这样就不必要地锁定了资源，而且阻塞了其他会话欲读取或更新的需求。

image.png

阻塞查看

查看阻塞方法1

SELECT session_id,wait_duration_ms,wait_type,blocking_session_id,resource_description 
FROM sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL

会话55的共享锁请求被会话51的排他锁阻塞了

image.png

查看阻塞方法2

SELECT SP.* FROM SYS.SYSPROCESSES SP 
WHERE (SP.status = N'suspended' AND SP.blocked<>0)
   OR (SP.spid IN (SELECT DISTINCT BLOCKED FROM SYS.SYSPROCESSES WHERE BLOCKED<>0) AND SP.BLOCKED=0)

blocked字段中的值就是引起阻塞的会话ID，blocked=0表示是阻塞源

image.png

查看阻塞锁定的资源的情况

SELECT * FROM sys.dm_tran_locks 
WHERE request_session_id IN (51,55) 
ORDER BY request_session_id,resource_type

image.png

减少阻塞的建议

• 尽可能让事务轻薄短小、让锁定的时间尽量短，例如把不必要的命令移出事务外，或把一个大量更新的事务，切成多个更新较少的事务，以改善并发性。

• 将组成事务的 SQL 语句，放在一个「批 (batch) 处理」，以避免不必要的延迟。这些延迟常由 BEGIN TRAN ... COMMIT TRAN 命令之间的网络 I/O 所引起。

• 若对数据的准确性要求不高，可以在SELECT语句中加WITH (NOLOCK)。

• 避免在事务执行期间，等待用户的反馈或交互，这样可能会造成无限期的持有锁定。

• 避免事务开启后查询大量的数据，尽可能在事务开启前先将数据插入临时表。

• 关注SQL语句的性能，提前给表建立合适的索引，避免因缺失索引导致的阻塞。

• 对于有大量并发的数据库，建议在充分测试的情况下，开启已提交读快照隔离级别READ_COMMITTED_SNAPSHOT，消除读取数据时的阻塞问题。

死锁

指当两个进程各自拥有一个锁，而这个锁是对方继续运行所需要的，这样就会出现两个进程相互阻止对方运行的情况，这就会出现死锁，如果对这种情况不进行处理的话，那么就将无限期的等待下去，在SQL Server中内置了死锁探测，每5秒钟锁监视器就会检查死锁状态，如果发现死锁，SQL Server通常选择终止回滚开销最小的进程来解决死锁问题。

image.png

在示例中，对于 Part 表锁资源，事务 T1 依赖于事务 T2。 同样，对于 Supplier 表锁资源，事务 T2 依赖于事务 T1。 因为这些依赖关系形成了一个循环，所以在事务 T1 和事务 T2 之间存在死锁。

查询死锁信息的工具

跟踪标志 1204 和跟踪标志 1222

发生死锁时，跟踪标志 1204 和跟踪标志 1222 会返回在 SQL Server 错误日志中捕获的信息。
跟踪标志 1204 会报告由死锁所涉及的每个节点设置格式的死锁信息。
跟踪标志 1222 会设置死锁信息的格式，顺序为先按进程，然后按资源。
可以同时启用这两个跟踪标志，以获取同一个死锁事件的两种表示形式。

重要
避免在导致死锁的工作负载密集型系统上使用跟踪标志 1204 和 1222。 使用这些跟踪标志可能会导致性能问题。 改用死锁扩展事件(#deadlock_xevent)。

事件探查器（SQL Profiler）死锁图形事件

image.png

死锁扩展事件
自 SQL Server 2012 (11.x) 起，应使用 xml_deadlock_report 扩展事件 (xEvent)，而不使用 SQL 跟踪或 SQL 探查器中的死锁图事件类。

image.png

将死锁减至最少

• 按同一顺序访问对象。

• 避免事务中的用户交互。

• 保持事务简短并处于一个批处理中。

• 使用较低的隔离级别。

• 使用基于行版本控制的隔离级别。

• • 将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON，以使读取提交的事务可以使用行版本控制。
• • 使用快照隔离。

• 使用绑定连接，在活动事务中执行sp_getbindtoken返回令牌，在新会话中运行sp_bindsession绑定令牌，将新会话绑定到同一事务上，以共享同一锁空间。

常见死锁类型 - 书签查找死锁

会话52查询表数据使用了非聚集索引(NCL)查找，在该索引上加了共享锁，同一时间，会话55更新该表在聚集索引(CL)上加了排他锁，因更新的数据也在NCL中，故会话55请求NCL的排他锁，而会话52需要的数据NCL中没有，需回CL中查找，会请求CL的共享锁，此时，产生死锁。

image.png

常见死锁类型 - 锁转换引起死锁

两个进程在各自的事务中都获取了表中某行(id=7)的共享锁，且都需要对该行做修改，那么两个事务都要将共享锁转换成排他锁，因排他锁与其他事务的共享锁不兼容，发生锁等待，因此每个事务都等待另一个事务释放共享锁，从而产生死锁。

image.png