SQL Server 并发控制 第二篇：隔离级别和锁（1）
SQL Server 并发控制 第三篇：隔离级别和行版本（2）
隔离级别定义事务处理数据读取操作的隔离程度，在悲观并发模式下，隔离级别只会影响读操作申请的共享锁（Shared Lock），而不会影响写操作申请的互斥锁（Exclusive Lock），隔离级别控制读操作的行为：
- 在读数据时是否使用共享锁，申请何种类型的锁；
- 事务持有共享锁的时间；
- 读操作引用被其他事务更新，但尚未提交的数据行时，控制读操作的行为：
- 被阻塞，等待其他事务释放互斥锁；
- 读没有提交的数据，获取更新之后的数据值；
在乐观并发模式下，读操作引用被其他事务更新，但尚未提交的数据行时，控制读操作的行为：
- 获取更新之前的数据值，从tempdb中读取行版本（row version），该行版本在事务开始时已经提交；
在执行写操作时，事务总是持有互斥锁，直到事务结束才释放，互斥锁不受事务隔离级别的影响。在悲观并发模式中，互斥锁和任意锁都不兼容，在同一时刻，同一个数据行上，只能有一个事务持有互斥锁，就是说，写操作是顺序进行的，完全隔离的，不能并发执行。隔离和并发，此消彼长。
一，事务运行时的隔离级别
SQL Server 数据库级别默认的事务隔离级别是Read Committed，用户不能修改Database-Level默认的隔离级别，但是，用户能够修改Session-Level默认的事务隔离级别。
每一个事务都运行在一个特定的隔离级别内，该隔离级别是由会话（session）决定的。SQL Server定义的五个隔离级别，都是为了定义读数据的行为：
- READ UNCOMMITTED ：允许脏读、数据不可重复读和数据范围不可重复读
- READ COMMITTED：防止脏读（读取未提交的数据更新），允许数据不可重复读和数据范围不可重复读
- REPEATABLE READ：防止脏读和数据不可重复读，允许数据范围不可重复读
- SERIALIZABLE：防止脏读、数据不可重复读和数据范围不可重复读
- SNAPSHOT：快照隔离级别，主要是为了避免读写相互阻塞
事务的隔离级别共有5个，使用SET命令修改Session-Level的隔离级别，使用DBCC UserOptions 查看当前Session的隔离级别：
SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }
注意：隔离级别只能定义读操作的行为，无法定义写操作的行为，写操作跟写操作与读操作都是互斥的。
在任何隔离级别下，事务在执行写操作时都申请互斥锁（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）操作；
二，悲观并发模式下的隔离级别
在悲观并发模式下，可以设置4个隔离级别，分别是READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE，其中READ UNCOMMITTED、REPEATABLE READ和SERIALIZABLE是悲观并发模式独有的。
1，脏读
当事务运行在 READ UNCOMMITTED 隔离级别下时，有可能会读取到其他事务未提交的数据更新。
- 例如，事务A修改了数据，把数据由1修改2，但是还没有提交，
- 之后，另一个事务B读取了该数据，结果是2，
- 在事务B读取数据之后，事务A回滚，那么该数据的最终值是1。
出现脏读的原因是读操作不加共享锁，读操作不会被写操作阻塞，使得读操作可能会读取到写事务未提交的值。
2，数据不可重复读
当事务运行在 READ COMMITTED 隔离级别下时，在同一个事务内，两次读取同一个数据，可能读取到不同的结果，这就是数据不可重复读现象。
- 例如，事务A读取了一个数据，值是1，事务未提交
- 之后，事务B修改了该数据，把数据值由1修改为2，并提交事务。
- 在事务B提交之后，事务A重新读取该书，值为2
出现数据不可重复读的原因是事务在读取数据时申请了共享锁，但是在语句执行完成之后，就立马释放了共享锁。要想避免出现数据不可重复读的现象，事务必须一直持有共享锁，直到事务提交或回滚。
3，数据范围不可重复读
当使用where子句限定了数据范围之后，事务在两次执行相同的查询，获得的数据范围不同，也就是说，在相同的数据范围内，增加了新的数据。
- 例如，事务A查询Age在10和20之间的男生，共有7个，事务未提交
- 事务B向数据表中插入一条新的数据，Age是15，事务提交
- 事务A重新查询Age在10和20之间的男生，共有8个
出现数据范围不可重复读的原因是事务只申请了共享锁，没有申请范围锁，这就导致其他写事务可以向特定的数据范围内新增数据。
三，锁
在悲观并发模式下，事务使用锁来实现数据的一致性。锁是一种机制，锁施加在资源上，表示锁定该资源。锁的所有者是事务，事务的特性决定了锁的特性。
1，锁定的资源
事务可以在数据行、数据页、键范围、分区和表上加锁，
锁定的资源类型，按照粒度的级别，从低向高分别是：
- RID：堆中的一行
- KEY：聚集索引中的键
- KEY Range：键范围
- PAGE：堆表的数据页或B-Tree的索引页
- EXTENT：分区，连续的8个Page
- HoBT：堆或B-Tree， 用于保护堆（没有聚集索引的表）和堆中的B 树
- IDX：索引中的数据行
- OBJECT：表（整个表）
2，锁模式
共享锁（S锁）：读操作在读取的数据上施加共享锁，用于select子句中
互斥锁（X锁）：写操作在更新的数据上施加互斥锁，一个数据上，只能施加一个互斥锁，X锁和任意锁都是互斥的。
更新锁（U锁）：U锁和S锁是兼容的，在写操作中，数据的更新过程分为两步，第一步是找到更新的数据，对于找到的数据施加U锁；第二步是在真正执行数据更新时，把U锁转换为X锁。
意向锁（I锁）：意向锁有 IS、IX和IU 三种类型，当事务申请低粒度锁时，事务会在相同的对象上，依次申请高粒度资源的意向锁，直到表上的意向锁。例如，事务A申请在数据行R上施加X锁，同时，事务A会在包含该数据行R的数据页上申请IX锁，同时申请表级的IX锁，也就是说，在一行上申请X锁，那么该行所在的页和表都会申请IX锁。在这种情况下，其他事务就很容易探测到有事务在更新表中的数据，避免锁住整个表。
键范围锁（Key-Range）：键范围锁出现在SERIALIZABLE隔离级别，如果事务需要扫描一个范围的数据，事务使用键范围锁，锁定表中的特定范围，避免其他事务向范围中插入新的数据，键范围锁与特定的索引键关联。
3，锁持续的时间
X锁持续到事务结束，S锁的持续时间受到事务隔离级别的影响。
- READ UNCOMMITTED ：不申请S锁
- READ COMMITTED：申请S锁，S锁在读取操作完成时就立马释放，S锁持续的时间是语句执行的时间
- REPEATABLE READ：申请S锁，当事务结束之后，立马释放S锁，S锁持续的时间是事务执行的时间
- SERIALIZABLE：申请S锁，锁定的资源是键范围，当事务结束之后，立马释放S锁，S锁持续的时间是事务执行的时间
4，锁的兼容性
自行百度
5，锁升级
锁升级是指锁施加的资源的粒度增加，从低粒度升级到高粒度，这会使数据库系统并发度降低，增加阻塞和死锁的风险，但是带来的好处是降低锁管理的开销、保证查询执行的速度。
锁升级的第一种方式是锁占用的内存达到阈值。通常来说，一个锁结构大概需要96B的内存空间，当数据库中存在大量的锁结构时，锁结构会占用较多的内存空间。当SQL Server使用超过24%的Buffer Poll用于存储锁结构时，SQL Server 引擎会选择一些正在持有的锁的会话，把锁升级到高层次的级别，使锁锁定的资源粒度变大，降低锁的数量。
锁升级的第二种方式是锁的数量达到阈值。在一个表上，当一个会话申请的锁的数量达到一定的阈值，阈值的默认值是5000，也就是说，当一个会话持有锁的数量超过5000时，SQL Server就自动把锁升级。
锁升级是自动进行的，也可以使用LOCK HINT，手动控制锁的粒度。
参考文档：
