《SQL Server 2012 T-SQL基础》读书笔记

Chapter 9 Transactions and Concurrency

SQL Server默认会把每个单独的语句作为一个事务，也就是会自动在每个语句最后提交事务（可以设置IMPLICIT_TRANSACTIONS来改变此默认行为)。

事务的四个属性：

原子性 Atomicity。遇到错误时，SQL Server通常会自动回滚事务（除了一些不太严重的错误比如主键冲突、尝试获取锁超时）。（可以通过 @@TRANCOUNT 判断当前是否处于一个事务当中）
一致性 Consistency。指同时发生的事务在修改和查询数据时不会发生冲突。可以根据需要设置isolation level来保证一致性。
隔离性 Isolation。传统上是用锁。比较新的，比如Windows Azure SQL Database默认是用row versioning。
持久性 Durability。数据修改在写入硬盘上的数据区之前，总是会先将一条提交指令（commit instruction）写入数据库的事务日志，这时候事务就可以认为是持久的（durable）了，因为即使数据还没有写入硬盘数据区，当系统启动时，SQL Server会检查事务日志，并把哪些还没写入数据区的数据写入（rolling forward），把那些还没有把提交指令写入日志的事务回滚。

最主要的两种加锁模式(lock modes)：排他锁和共享锁。
排他锁(Exclusive lock)。当你尝试修改数据时，你的事务必须对应的数据资源上请求一个排他锁，成功持有后，直到ROLLBACK TRAN或COMMIT TRAN才会释放锁。如果有其他事务对资源持有任何lock mode，那么你就不能在此资源上获得排他锁；如果你在资源上持有了排他锁，那么其他事务就不能获得对此资源的任何lock mode。所以说不可能有两个事务能同时修改某行数据，但是能不能同时读取决于隔离级别（isolation level）。
共享锁(shared lock )。默认下，读取数据会在相应资源上获取一个共享锁，多个事务可以对同一数据资源同时持有shared locks。
（但是如果一个事务对一个资源已经持有了一个共享锁，然后再想对这个资源请求一个排他锁，似乎是可以的，因为我在REPEATABLE READ这种隔离级别下试了一下：一个事务对某一行先读再写是可以的，但是如果写之前有其他事务对此行持有共享锁就不行了。摘自微软官网：No other transactions can modify the data while shared (S) locks exist on the resource.）

以上总结下来就是：

	已经授予排他锁	已经授予共享锁
请求排他锁	拒绝	拒绝
请求共享锁	拒绝	同意

（注：以上行为都是对同一个资源，“已经授予xx锁”是指已经授予其他某个事务这个锁，“请求xx锁”是指现在又有一个事务过来请求这个锁）

还可以根据细粒度划分资源类型，比如rows reside within pages，所以说row比page更细，page比row更粗（更高级）。要获取某个资源的锁，必须先获取intent locks（意向锁） of the same mode on higher levels of granularity。对于意向锁，我的理解是：

假设最外面一个大的是一个Page，里面包含了很多Row。让Page持有意向排他锁的意思就是这个Page里面的某一行正持有排他锁；对Page请求意向排他锁的意思是想对这个Page里面的某一行请求排他锁。为了更好地记忆和理解，我们姑且把排他锁和写当成一回事儿，把共享锁和读当成一回事儿（于是根据两种锁的定义可得：你写的时候别人不能读也不能写；你读的时候别人能读但是不能写）。假设你已经正在对整个Page写，那么别人当然不能在这时对它里面的某一行进行写或读。同理，假设你已经正在对整个Page读，那么别人当然能对它里面的某一行进行读，但不能对它里面的某一行进行写。当你正在对Page里的某一行进行写，那么别人不能对整个Page进行写或读，但是别人可以对Page里的某一行进行写或读（如果刚好是你正在写的那一行，那么对这一行的锁会起隔离效果）。如果你正在对Page里的某一行进行读，那么别人不能对整个Page进行写，但可以对Page进行读，也可以对Page里的某一行进行读或写。以上总结一下就是下表：

	已经授予排他锁(X)	已经授予共享锁(S)	已经授予意向排他锁(IX)	已经授予意向共享锁(IS)
请求排他锁	拒绝	拒绝	拒绝	拒绝
请求共享锁	拒绝	同意	拒绝	同意
请求意向排他锁	拒绝	拒绝	同意	同意
请求意向共享锁	拒绝	同意	同意	同意

（注：以上行为都是对同一个资源，X代表排他锁，S代表共享锁，I代表intent）

当事务请求一个暂时无法获得的锁的时候，请求阻塞并进入等待状态。下面举个例子（假设都是默认设置），假设我们先在一个SSMS的查询窗口输入（注意没有COMMIT TRAN）：

BEGIN TRAN;

  UPDATE Production.Products
    SET unitprice += 1.00
  WHERE productid = 2;

然后在另一个查询窗口里输入以下就会被block住：

--SET LOCK_TIMEOUT 5000; --如果五秒后都还没有获取锁，那就放弃（默认值是-1，也就是不设置timeout时间）
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;

然后我们可以查询sys.dm_tran_locks这个dynamic management view (DMV)来获取锁的信息，输出如下（没有select所有字段）：

每一个会话（session）都由一个唯一的server process ID（SPID）标示。可以通过 @@SPID 来查询当前的会话的SPID。上面的restype字段是指资源类型，其中KEY可以理解成是row。剩下的字段看名字都很明了。
通过查询sys.dm_exec_connections来获取与某些process相关的连接的信息：

SELECT ...
FROM sys.dm_exec_connections
WHERE session_id IN(52, 53);

结果：

结果中包括：连接建立的时间；连接中最后一次发生读和写的时间；一个二进制的handle（句柄？），代表连接最近一次的SQL batch run。你可以把这个handle作为输入交给sys.dm_exec_sql_text，然后得到a batch of code（应该就是指最近一次批处理对应的SQL代码）。但注意：得到的代码不一定就是出问题的地方，因为当你得到代码后，程序还有可能继续执行下去。
你也可以通过查询sys.dm_exec_sessions得到与session相关的信息：

sys.dm_exec_requests对每一个活动的request都有对应的一行，你可以通过以下SQL来查询被block住的request：

SELECT ...
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;  --主要是这个过滤条件起的作用

从而得到是被哪个session阻塞的，请求的资源，已经等待的时间等等信息。
最后，新开一个查询窗口，输入KILL 52;，会导致刚才那个update的事务进行回滚，于是释放锁。

两种设置隔离级别（isolation level）的方式：

SET TRANSACTION ISOLATION LEVEL <isolation name>;
SELECT ... FROM <table> WITH (<isolationname>);

1.READ UNCOMMITTED Isolation Level
在这种隔离级别下，读不需要申请锁。所以可能会“脏读”，比如：我先在事务1里面更新一个数据但不Commit，然后又跑到事务2里面去查这个数据，会查出来是你刚才在事务1里面更新后的值，但是这时候你也可以再回到事务1里回滚。所以事务2读到的就是脏数据。

2.The READ COMMITTED Isolation Level（SQL Server默认的隔离级别）
在这种隔离级别下，读需要申请共享锁，但是只要“读完了”就会把锁释放掉了。比如在同一事务中有两句对同一列的SELECT，在这两句之间的地方是没有锁的，可能刚好这时候就有另一个事务对数据进行了写，所以导致两次读可能读到的不一样。

3.The REPEATABLE READ Isolation Level
在这种隔离级别下，读不仅需要申请共享锁，而且只有当这个事务结束的时候才会释放锁。这种隔离级别还能防止另一种现象“a lost update”。意思就是在比REPEATABLE READ更低的隔离级别下，如果有两个事务读了一个值，(根据这个值做了些计算)，然后再更新这个值。这就会导致后更新的那个人wins（覆盖掉先更新的那个人的值）。在REPEATABLE READ级别下，上述情况会导致死锁（两个持有共享锁的事务都在因为申请排他锁不成功而等待）。

4.The SERIALIZABLE Isolation Level
有一种上述的REPEATABLE READ无法解决的情景：你打开了一个事务，读取一些数据（注意此时会锁住运行时找到的那些行），然后此时恰好有别人在这个表里ADD了一些数据，又正好满足你的SELECT里的WHERE条件，那么当你第二次读取“这些数据”的时候会发现 “怎么多了几行？”，这种现象叫幻读（phantom read）。
而在SERIALIZABLE这种隔离级别下，首先会保证REPEATABLE READ的那些要求，还意味着读操作不仅会锁住满足查询条件的现有的那些行，还会锁住未来可能满足查询条件的行，换句话说，其他事务这时候想ADD一些满足你的搜索条件的行的话，会被block住。

5.The SNAPSHOT Isolation Level
DELETE或UPDATE的时候，会把要修改或删除的值先拷一份到tempdb去。然后读操作会读到最近一次提交的数据。
需要在数据库层面设置一下：
ALTER DATABASE TSQL2012 SET ALLOW_SNAPSHOT_ISOLATION ON;
然后你可以先开个事务1进行更新操作。然后再开一个事务2，设置一下隔离级别：
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
然后SELECT（读），这时候你会读到更新前的值。然后在事务1里面提交，这时候再在事务2里面查一下，发现还是老的值。但如果你再开一个新的事务3，就会发现读到了新的值。为什么呢？因为在这种隔离级别下，当你读的时候，保证会把当你的事务开始时的最近提交的数据给你。
最后把所有没提交的事务都提交干净，于是没有事务需要那个老值了，一个清理线程下次执行的时候会把tempdb里没用的数据清理掉。
其次，在这种隔离级别下，不像REPEATABLE和SERIALIZABLE会引发一个死锁来避免更新冲突，而是能判断出在这种隔离级别下的事务中的一次读操作和一次写操作之间是否有其他事务修改过数据，如果有，就报错。（为什么非得要一次读和一次写？因为你的写入的数值是基于你读出来的值决定的，这两者相当于一个不可分割的过程。）

6.The READ COMMITTED SNAPSHOT Isolation Level
需要在数据库层面设置一下：
ALTER DATABASE TSQL2012 SET READ_COMMITTED_SNAPSHOT ON;
READ COMMITTED SNAPSHOT和刚才讲的SNAPSHOT不同的地方就在于：刚才是“当你读的时候，保证会把当你的事务开始时的最近提交的数据给你”，而现在是：你读的时候，保证会把当the statement开始时的最近提交的数据给你（不需要获取共享锁）。同样是刚才SNAPSHOT里面那个例子的话，唯一不同的就是在事务2里面第二次查询的时候，会查到新值。另外，这种隔离级别不会检查出更新冲突（update conflicts）。所以说在逻辑上，READ COMMITTED SNAPSHOT和READ COMMITTED有点类似（non-repeatable read和会产生lost update）；而SNAPSHOT在逻辑上和SERIALIZABLE 类似。

总结：

Uncomitted Read就是读到别人修改后但还没commit的值。
虽然书上没有明说，但我推理：隔离级别是对于某个事务而言的，因为开始一个事务前要设置隔离级别。

如果下面的语句中的productid没有索引，那么SQL Server就必须扫描（锁定）表中所有行，所以更容易导致死锁。

SELECT productid, unitprice FROM Production.Products WHERE productid = 2;