SQL Server事务及隔离级别

事务的并发操作可能出现的问题

中文	英文	描述
脏读	Dirty Reads	事务2读到了事务1未提交的事务，事务1随后回滚，但事务2读到了事务1的“中间数据”。 在Read Uncommitted隔离级别下会发生，其它级别不会。 (update&read)
丢失更新	Lost Updates	两个事务对同一个行分别进行更新，其中一个更新覆盖了另一个，导致丢失了一个更新。 在Read Committed的隔离级别下仍能发生，Repeatable Read能够避免它发生。 为什么官方文档里没有提到Lost Updates这个现象？ 官方文档：Transaction Isolation Levels (read&update)
不可重复读	Non-repeatable Reads	事务2在事务1的两次读取之间更新了数据导致事务1两次读到不一样的数据。 在Repeatable Read隔离级别下解决，和Lost Update一样，本质都是因为在此隔离级别下S锁持有到事务结束使其它事务无法在本事务执行过程中更新数据。 (read&update)
幻读	Phantom Reads	当一个事务执行一个query两次, 但得到不同行数的结果集。 幻读和不可重复读不一样地方在于，解决不可重复读问题时针对的是已有数据，因此可以持有它们的S锁，使其它事务请求X锁时等待；而幻读是新插入的数据。 (insert\delete)

事务

• 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。

• 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。

我一直纳闷这个“一致”是什么意思，然后在#引用.1.这本书里看到了解释：数据库在某一时刻的状态反应的是真实世界在这个时刻的数据，真实世界被抽象成数据，然后真实世界不停运行，数据库中的数据就像真实世界的一个切面，只不过真实世界是连续的，而数据库的数据是真实世界某一个时刻的状态，是离散的。随着真实世界的运行，当数据库中的数据从一个时刻到下一个时刻且数据都是“正确的”的时候，就称数据库从一个一致性状态到了另一个一致性状态，保证这种“一致性”的手段就是“数据库约束”，它对数据的正确性进行校验。不过，这种校验是片面的，数据的正确性也取决于业务逻辑（后台代码），但在数据库层面能做的就是“数据库约束”。

• 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。

• 持久性（Durability）：即便发生断电等意外情况，已被提交的事务对数据库的修改应该永久保存在数据库中（硬盘上）。这点一般通过日志来保证。

事务的隔离级别

隔离级别影响锁模式的表现。

Read Uncommitted

• 不要求SELECT时请求S锁，因此它不会阻塞X锁或者被X锁阻塞，因此可以发生脏读。

• The intention of this isolation level is for systems primarily focused on reporting and business intelligence, not online transaction processing.

• 别用。

Read Committed

• 要求SELECT时请求S锁，因此它会阻塞X锁或者被X锁阻塞，因此可以避免脏读。

• 它是SQL Server默认的隔离级别。

• 由于S锁不会持有到事务结束，而是在SELECT完成后就释放了， 因此可能发生幻读和不可重复读。

  • 幻读：INSERT, DELETE

  • 不可重复读：UPDATE

Repeatable Read

• 要求SELECT时请求S锁，并持续到事务结束，因此避免了事务过程中其它事务对数据的修改，因此可以避免幻读和不可重复读。

• 可重复读：在当前的事务中可再次读取（R），读取的结果没有被修改（CUD）。

Serializable

• 最高级别的隔离级别

• 相比直接在所需的行上加锁，可串行化隔离级别在所需的行和下一行（索引顺序）上获得一个range lock。因此它可以避免插入新的数据，从而避免幻读现象。

• 上图是SQLite中的页的结构，但DBMS设计思路是类似的。在一个页中，存储着多个Key，这些Key按顺序排列在一个数组中，所谓的“下一个”就是这个索引的下一个。

• 如果key存在

  • 如果next key存在：
    • 在non-unique index的情况下，在当前请求的key和next key上范围锁。
    • 在unique index的情况下，理论上在key上获取S锁就足够，但SQL Server仍会获取范围锁
  • 如果next key不存在：taken on the ‘infinity’ value.

• 如果key不存在

  • 如果next key存在：taken on the next key.
  • 如果next key不存在： taken on the ‘infinity’ value.

• 在无限大上的范围锁会锁定 >= 当前key的范围

• BETWEEN 在请求的key上和next key上获取范围锁

• WHERE 在请求的key及前后获取范围锁

create table foo (c1 int)

go

insert into foo values (1)

insert into foo values (2)

insert into foo values (3)

insert into foo values (4)

insert into foo values (5)

create unique clustered index foo_ci on foo(c1)

set tran isolation level serializable

begin tran

select * from foo where c1 between 2 and 4 

SELECT dtl.request_session_id,
 dtl.resource_database_id,
 dtl.resource_associated_entity_id,
 dtl.resource_type,
 dtl.resource_description,
 dtl.request_mode,
 dtl.request_status
FROM sys.dm_tran_locks AS dtl
WHERE dtl.request_session_id = @@SPID;

Snapshot

• 与可串行化隔离级别一样，快照隔离级别也可以避免幻读。
• 与其它的隔离级别不同的是，其它的隔离级别是基于锁机制的，而快照的实现方式不是基于锁的，而是基于行版本（row versioning）的。
• 如果快照事务尝试提交对自事务开始以来已更改的数据的修改，则事务将回滚并引发错误。

ALLOW_SNAPSHOT_ISOLATION

开启并使用快照隔离级别：

-- Enable the snapshot isolation for the database
-- Provide your database name
ALTER DATABASE TESTDB SET ALLOW_SNAPSHOT_ISOLATION ON

-- Set the transaction isolation level to snapshot
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT * FROM Products WHERE Id = 1001

事务2将引发更新冲突异常，导致事务2的修改回滚：

-- 执行1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
  UPDATE Products SET Quantity = 5 WHERE Id=1001
-- 执行3
COMMIT TRANSACTION


-- 执行2
ALTER DATABASE TESTDB SET ALLOW_SNAPSHOT_ISOLATION ON
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
UPDATE Products SET Quantity = 5 WHERE Id=1001

READ_COMMITTED_SNAPSHOT

• SQL Server中的读已提交快照隔离级别不是一种单独的隔离级别。它只是在SQL Server中实现读已提交隔离级别的另一种方式。
• 读已提交隔离级别的问题是，如果它试图读取同时由另一个事务更新的数据，它会阻塞事务。
• 更改默认的读已提交隔离级别的行为到快照读已提交：

ALTER DATABASE TEST_DB SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE TEST_DB SET READ_COMMITTED_SNAPSHOT ON

在事务2的数据库连接上开启快照读已提交，并使用读已提交隔离级别，不会阻塞，事务2读取到事务2开始时（事务1提交前）的数据：

--Transaction 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION

  UPDATE Products SET Quantity = 5 WHERE Id = 1001
  WAITFOR DELAY '00:00:15'

COMMIT TRANSACTION

--Transaction 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
  SELECT * FROM Products WHERE Id = 1001
COMMIT TRANSACTION

上面的结果与如下事务2使用快照隔离级别是一样的：

--Transaction 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION

  UPDATE Products SET Quantity = 5 WHERE Id = 1001

COMMIT TRANSACTION

--Transaction 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
  SELECT * FROM Products WHERE Id = 1001 
COMMIT TRANSACTION

READ COMMITTED SNAPSHOT和SNAPSHOT的区别

• 根本的区别在于，快照使用乐观读和乐观写，读已提交快照使用乐观读和悲观写（锁）；当在两个隔离级别下分别使用两个事务修改同一数据时，快照隔离级别会发生更新冲突，而读已提交快照级别会阻塞。
• 读已提交快照隔离级别并不需要额外的隔离级别声明，只需要打开快照开关以启用版本记录，并打开开关替换默认的读已提交隔离级别为读已提交快照隔离级别的开关；而快照隔离级别需要打开快照开关，同时设置隔离级别为SNAPSHOT。
• 读已提交快照仍可能发生不可重复读和幻读，但写事务不会阻塞读事务，写事务会阻塞写事务（锁）；快照隔离级别能够避免不可重复读和幻读，同时写事务也不会阻塞读事务，写事务也不阻塞写事务（写冲突回滚）。

引用

1. 《SQL Server 2017 Query Performance Tuning 5th Edition》
2. dotnettutorials: sql-server-concurrent-transactions
3. stackoverflow: what are range locks
4. techcommunity.microsoft: SQL Server Range Lock
5. Microsoft: Transaction Isolation Levels
6. youtube: Snapshot isolation level in SQL server
7. Microsoft: Snapshot isolation in SQL Server
8. dotnettutorials: Snapshot Transaction Isolation Level in SQL Server
9. Stackoverflow: Read committed Snapshot VS Snapshot Isolation Level