Transaction, Lock, Isolation Level

隔离级别 Isolation Level
隔离级别用于解决事物的并发性问题，概念如下：

脏读（Read uncommitted）：可以读取到其他事物未提交的数据，因为这些数据可能被其他事物回滚，并不会最终提交到数据库，因此读取出来的数据不可靠，叫做脏读

不可重复读（Nonrepeatable read）：在确保不会发生脏读之后，接下来还会面临重复读取问题，例如：

连接1	连接2	说明
SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN SELECT * FROM TranTest WHERE Col2 = 2		连接1第一次查询
	UPDATE TranTest SET Col2 = 2	连接2执行更新。假设连接2没有显示开启事物，则这个语句执行完后自动提交，TranTest表的数据被更新了
SELECT * FROM TranTest WHERE Col2 = 2 COMMIT		连接1第二次执行同样的查询，返回的结果可能与第一次查询的结果不一样了

连接1在同一个事物中执行相同的语句，但返回的结果不一样，说明并发的事物之间隔离的程度不够，造成相互影响
解决重复读的方法是对读取到的数据加锁，阻止其他并发的事物在期间更新这些数据。例如上面例子，连接1在第一次查询之后对读取到的数据加锁，接下来连接2的更新操作将被阻塞住，直到连接1的事物完成之后才可继续执行

幻象读（Phantom read）：
上面解决重复读问题的方法，虽然锁住了被读取的数据，这些数据无法被其他事务并发update、delete，但仍然没有阻止insert。例如将上面连接2的update改成insert语句，仍然可能造成连接1两次执行的查询结果不一致，某些数据可能像幻影一样突然间冒出来，因此叫做幻象读
解决幻象读的方法，一般是锁住一个索引范围，禁止在其间插入新的键值，不可利用索引时则采用表锁等方式

SQL-92规定的4种隔离级别：Read uncommitted、Read committed、Repeatable read、Serializable

	Dirty read	Nonrepeatable read	Phantom read
Read uncommitted	Yes	Yes	Yes
Read committed	No	Yes	Yes
Repeatable read	No	No	Yes
Serializable	No	No	No

可以加锁的资源类型
RID：对heap表数据的行锁
Key：对索引或者聚集索引表中数据的行锁
Page：数据页或者索引页的页级锁
Extent：中文翻译为块级锁？8个连续的page，可以是数据页或者或者索引页
Table：表锁，针对整个表的数据和索引
DB：数据库级的锁，例如备份恢复时

锁的类型 Lock Mode
Shared (S)：共享锁，读取数据
Read Committed隔离级别在读取数据期间加S锁，读取完毕立刻释放
Repeatable Read和Serializable隔离级别在整个事物期间给读取的数据加S锁，事物完成时释放

Update (U)：更新锁。将要更新数据，实际更新数据时升级为X，不更新数据则降级为S
U锁用于解决一个普遍存在的死锁情况，例如Repeatable Read和Serializable隔离级别下，2个事务并发读取某个数据，都加了S锁，然后2个事物都要更新这个数据，都需要将S锁升级为X锁，但在这个隔离级别下他们都需要等对方释放S锁，因此死锁在这个资源上
U锁之间是互斥的，在读取数据时显示指定使用U锁可以避免类似上面的情况
另外执行带有where的update语句时，sql server首先需要根据条件找到要更新的数据，在找这些数据时先使用U锁，找到数据之后再将U锁更新为针对这些数据的X锁，从而避免update语句执行期间遭遇类似上面场景的死锁

Exclusive (X)：排他锁、独占锁。更新数据

Intent：意向锁，有Intent shared (IS)意向共享锁、Intent exclusive (IX)意向排他锁、Shared with intent exclusive (SIX)共享意向排他锁
意向锁是sql server用于改善性能的东西，他表示sql server将要对加意向锁对象的下一个层级的某些（而不是全部）数据执行某种操作
例如执行update语句更新某条数据时，sql server先对表加意向排他锁（没有表级别的IU，所以update情况下对表加的是IX），表示他正在更新这个表中的某条数据，对具体更新的数据加的是U锁（其中可能还有对相应页加IU锁）。如果此时另外的事物需要对这个表加X锁，他只需要查询这个表级的锁对象就可以确定做出决定，而不需要去查下一层级的Page、RID、Key等对象是否有其他不相容的锁

Schema：数据库结构相关的锁，有Sch-M和Sch-S
Sch-M：正在修改数据库对象，例如表、索引等时加的锁
Sch-S：编译中的查询语句涉及到的对象会加Sch-S锁，他只会阻塞Sch-M锁，对其他锁都不会阻塞

Bulk Update (BU）：批量更新锁

各种锁之间的兼容性如下：

请求的锁类型	资源上已经存在的锁类型
	IS	S	U	IX	SIX	X
IS	Yes	Yes	Yes	Yes	Yes	No
S	Yes	Yes	Yes	No	No	No
U	Yes	Yes	No	No	No	No
IX	Yes	No	No	Yes	No	No
SIX	Yes	No	No	No	No	No
X	No	No	No	No	No	No

只有相互之间兼容的锁才能同时加在相同的资源上

场景说明
表结构：

create table heap_table( 
    id int,
    val varchar(20) null
);
create unique index ix_heap_table on heap_table(id);
insert into heap_table(id,val) values(1063,'AAAAAA');
insert into heap_table(id,val) values(1064,'BBBBBB');

场景一：
执行的sql语句：

set transaction isolation level repeatable read;
begin tran;
update heap_table set val=val+'W' where id=1064;

然后用sp_lock可以看到当前的锁分配情况如下：

1. 查找数据，因为执行的是update语句，所以查找过程中加IU锁
1.1. 对表heap_table加IX锁（行6，因为sql server不存在表级的IU锁）
1.2. 使用索引ix_heap_table进行查找。加载id=1064数据对应的索引页后加IU锁（行2，IndId=2表明是索引页），索引页中对应的索引键加U锁（行5）。因为事物隔离级别为repeatable read，因此语句执行完毕之后索引页和索引键上的IU、U锁在语句执行完毕之后不会释放，而是在整个事物期间持有
1.3. 根据从索引上得到的信息加载目标数据。目标数据页加IU锁（行3，IndId=0表明是数据页），目标数据加U锁（行4）
2. 更新数据
2.1. 目标数据页需要更新，所以其上的IU锁升级为IX锁（行3）；目标数据需要更新，所以其上的U锁升级为X锁（行4）
2.2. 索引页和键值不需要更新，所以上面的IU锁、U锁没有升级，sql server也没有回过头将其降级为IS、S锁

场景二：

set transaction isolation level read committed;
begin tran;
update heap_table set val=val+'X' where val='AAAAAA';

再另外开一个窗口，执行select * from heap_table将被阻塞，改成select id from heap_table可以立刻返回，锁的分配情况如下：

1. Update语句使用全表扫描查找要更新的数据，表加IX锁，目标数据页加IU锁，目标数据加U锁；
2. 更新数据时目标数据页升级为IX锁，目标数据升级为X锁；因为没有更新索引字段，索引不加锁；
3. 新开的窗口中事物隔离级别为默认的read committed，select *语句做全表扫描，被更新语句的锁阻塞了；select id语句做索引扫描，索引上没有加锁，因此可以通过