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.
- 如果next key存在：
如果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;
引用
- 《SQL Server 2017 Query Performance Tuning 5th Edition》
- dotnettutorials: sql-server-concurrent-transactions
- stackoverflow: what are range locks
- techcommunity.microsoft: SQL Server Range Lock
- Microsoft: Transaction Isolation Levels