第十四周学习笔记+思维导图

Microsoft SQL Server中的事务的并发控制

一、事务

1.1 事务的概念

　　事务是作为单个工作单元而执行的一系列操作，比如查询和修改数据等。

　　事务是数据库并发控制的基本单位，一条或者一组语句要么全部成功，对数据库中的某些数据成功修改; 要么全部不成功，数据库中的数据还原到这些语句执行之前的样子。

       举例：

       比如网上订火车票，要么你定票成功，余票显示就减一张; 要么你定票失败获取取消订票，余票的数量还是那么多。不允许出现你订票成功了，余票没有减少或者你取消订票了，余票显示却少了一张的这种情况。这种不被允许出现的情况就要求购票和余票减少这两个不同的操作必须放在一起，成为一个完整的逻辑链，这样就构成了一个事务。

1.2 事务的ACID特性

　　原子性(Atomicity)：事务的原子性是指一个事务中包含的一条语句或者多条语句构成了一个完整的逻辑单元，这个逻辑单元具有不可再分的原子性。这个逻辑单元要么一起提交执行全部成功，要么一起提交执行全部失败。

　　一致性(Consistency)：可以理解为数据的完整性，事务的提交要确保在数据库上的操作没有破坏数据的完整性，比如说不要违背一些约束的数据插入或者修改行为。一旦破坏了数据的完整性，SQL Server 会回滚这个事务来确保数据库中的数据是一致的。

　　隔离性(Isolation)：与数据库中的事务隔离级别以及锁相关，多个用户可以对同一数据并发访问而又不破坏数据的正确性和完整性。但是，并行事务的修改必须与其它并行事务的修改相互独立，隔离。 但是在不同的隔离级别下，事务的读取操作可能得到的结果是不同的。

　　持久性(Durability)：数据持久化，事务一旦对数据的操作完成并提交后，数据修改就已经完成，即使服务重启这些数据也不会改变。相反，如果在事务的执行过程中，系统服务崩溃或者重启，那么事务所有的操作就会被回滚，即回到事务操作之前的状态。

二、并发控制

并发操作带来的问题：

1、丢失修改：修改的数据被覆盖

2、不可重复读：数据被修改或者插入删除，结果不能复现

3、读脏数据: 脏数据是未提交的数据。

可串行化调度：

     如果多个事务的并发执行和某一串行执行顺序结果相同，则称为可串行化调度。

冲突可串行化调度：

     冲突操作：不同事务的读写操作和写写操作。

     不同事务的冲突操作和同一事务的两个操作都不能交换执行顺序。

并发控制的技术：

1、时间戳：

     给每个事务分配一个全局的唯一的时间戳，唯一性且单调增长性。时间戳可以为系统时钟值，也可以为逻辑计数器，同一事务中的数据操作有相同的时间戳。此方法需要大量的系统资源。

2、乐观控制法：

     假设不会发生冲突。读阶段——确认阶段——写阶段。

3、锁：

     排它锁（X锁）：写锁，一个事务获取了写锁，则其他事务都不能再加任何类型的锁，直到锁释放。

     共享锁（S锁）：读锁，一个事务获取了读锁，其他事务能加读锁，不能加写锁。

     

    

2.1 锁

　　（1）锁是什么鬼？

　　锁是事务获取的一种控制资源，用于保护数据资源，防止其他事务对数据进行冲突的或不兼容的访问。

　　（2）锁模式及其兼容性

　　主要有两种主要的锁模式—排它锁（Exclusive Lock） 和 共享锁（Shared Lock）。

　　当试图修改数据时，事务会为所依赖的数据资源请求排它锁，一旦授予，事务将一直持有排它锁，直至事务完成。在事务执行过程中，其他事务就不能再获得该资源的任何类型的锁。

　　当试图读取数据时，事务默认会为所依赖的数据资源请求共享锁，读操作一完成，就立即释放共享锁。在事务执行过程中，其他事务仍然能够获得该资源的共享锁。

排它锁和共享锁的兼容性

请求模式	已经授予排它锁（X）	已经授予共享锁（S）
授予请求的排它锁？	否	否
授予请求的共享锁？	否	是

　　（3）可锁定资源的类型

　　SQL Server可以锁定不同类型或粒度的资源，这些资源类型包括RID或KEY（行），PAGE（页）、对象（例如：表）及数据库等。

2.2 阻塞

　　（1）阻塞是个什么鬼？

　　如果一个事务持有某一数据资源上的锁，而另一事务请求相同资源上的不兼容的锁，则对新锁的请求将被阻塞，发出请求的事务进入等待状态。默认情况下，被阻塞的请求会一直等待，直到原来的事务释放相关的锁。

  

　　（2）近距离观测阻塞

　　Step1.打开两个独立的查询窗口，这里称之为Connection A,Connection B

　　Step2.在Connection A中运行以下代码（这里productid=2的unitprice本来为19）

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

　　为了更新这一行，会话必须先获得一个排它锁，如果更新成功，SQL Server会向会话授予这个锁。

　　Step3.在Connection B中运行以下代码

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

　　默认情况下，该会话需要一个共享锁，但因为共享锁和排它锁是不兼容的，所以该会话被阻塞，进入等待状态。

　　

　　（3）如何检测阻塞

　　假设我们的系统里边出现了阻塞，而且被阻塞了很长时间，如何去检测和排除呢？

　　① 继续上例，打开一个新的会话，称之为Connection C，查询动态管理视图（DMV）sys.dm_tran_locks:

-- Lock info
SELECT -- use * to explore
  request_session_id            AS spid,
  resource_type                 AS restype,
  resource_database_id          AS dbid,
  DB_NAME(resource_database_id) AS dbname,
  resource_description          AS res,
  resource_associated_entity_id AS resid,
  request_mode                  AS mode,
  request_status                AS status
FROM sys.dm_tran_locks;

　　② 运行上面的代码，可以得到以下输出：

　　

　　③ 每个会话都有唯一的服务器进程标识符（SPID），可以通过查询@@SPID函数来查看会话ID。另外，当前会话的SPID还可以在查询窗口的标题栏中找到。

　    

　　④ 在前面查询的输出中，可以观察到进程53正在等待请求TSQLFundamental2008数据库中一个行的共享锁。但是，进程52持有同一个行上的排它锁。沿着52和53的所层次结构向上检查：（查询sys.dm_exec_connections的动态管理视图，筛选阻塞链中涉及到的那些SPID）

-- Connection info
SELECT -- use * to explore
  session_id AS spid,
  connect_time,
  last_read,
  last_write,
  most_recent_sql_handle
FROM sys.dm_exec_connections
WHERE session_id IN(52, 53);

　　查询结果输出如下：

　　

　　⑤ 借助交叉联接，和sys.dm_exec_sql_text表函数生成查询结果：

-- SQL text
SELECT session_id, text 
FROM sys.dm_exec_connections
  CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST 
WHERE session_id IN(52, 53);

　　查询结果如下，我们可以达到阻塞链中涉及到的每个联接最后调用的批处理代码：

　　

　　以上就显示了进程53正在等待的执行代码，因为这是该进程最后执行的一个操作。对于阻塞进程来说，通过这个例子能够看到是哪条语句导致了问题。

　　（4）如何解除阻塞

　　① 设置超时时间

　　首先取消掉原来Connection B中的查询，然后执行以下代码：这里我们限制会话等待释放锁的时间为5秒

-- Session B
SET LOCK_TIMEOUT 5000;

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

　　然后5秒之后我们可以看到以下执行结果：

　　

　　注意：锁定超时不会引发事务回滚。

　　② KILL掉引起阻塞的进程

　　在Connection C中执行以下语句，终止SPID=52中的更新事务而产生的效果，于是SPID=52中的事务的回滚，同时释放排它锁。

--KILL SPID=52
KILL 52;

　　这时再在Connection B中执行查询，便可以查到回滚后的结果（仍然是19）：

　　

三、死锁

3.1 死锁是个什么鬼?

　　死锁是指一种进程之间互相永久阻塞的状态，可能涉及到两个或者多个进程。两个进程发生死锁的例子是：进程A阻塞了进程B，进程B又阻塞了进程A。在任何一种情况下，SQL Server都可以检测到死锁，并选择终止其中一个事务以干预死锁状态。如果SQL Server不干预，那么死锁涉及到的进程将会永远保持死锁状态。

　　默认情况下，SQL Server会选择终止做过的操作最少的事务，因为这样可以让回滚开销降低到最低。当然，在SQL Server 2005及之后的版本中，可以通过将会话选项DEADLOCK_PRIORITY设置为范围（-10到10）之间的任一整数值。

3.2 死锁实例

　　仍然打开三个会话：Connection A、B和C：

　　Step1.在Connection A中更新Products表中产品2的行记录，并保持事务一直打开：

-- Connection A
USE TSQLFundamentals2008;

BEGIN TRAN;

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

　　这时Connection A对产品表的产品2请求了排它锁。

　　Step2.在Connection B中更新OrderDetails表中产品2的订单明细，并保持事务一直打开：

-- Connection 2
BEGIN TRAN;

  UPDATE Sales.OrderDetails
    SET unitprice = unitprice + 1.00
  WHERE productid = 2;

　　这时Connection A对订单明细表的产品2请求了排它锁。

　　Step3.回到Connection A中，执行以下语句，请求查询产品2的订单明细记录：

-- Connection A

  SELECT orderid, productid, unitprice
  FROM Sales.OrderDetails
  WHERE productid = 2;

COMMIT TRAN;

　　由于此时实在默认的READ COMMITED隔离级别下运行的，所以Connection A中的事务需要一个共享锁才能读数据，因此这里会一直阻塞住。但是，此时并没有发生死锁，而只是发生了阻塞。

　　Step4.回到Connection B中，执行以下语句，尝试在Products表查询产品2的记录：

-- Connection 2

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

COMMIT TRAN;

　　这里由于这个请求和Connection A中的事务在同一个资源上持有的排它锁发生了冲突，于是相互阻塞发生了死锁。SQL Server通常会在几秒钟之内检测到死锁，并从这两个进程中选择一个作为牺牲品，终止其事务。所以我们还是得到了以下结果：

　　

　　Step5.刚刚提到了SQL Server会选择一个作为牺牲品，我们回到Connection A会看到以下的错误信息提示：

　　

　　在这个例子中，由于两个事务进行的工作量差不多一样，所以任何一个事务都有可能被终止。（前面提到，如果没有手动设置优先级，那么SQL Server会选择工作量较小的一个事务作为牺牲品）另外，解除死锁需要一定的系统开销，因为这个过程会涉及撤销已经执行过的处理。

        显然，事务处理的时间越长，持有锁的时间也就越长，死锁的可能性也就越大。应该尽量保持事务简短，把逻辑上可以属于同一工作单元的操作移到事务之外。

3.3 避免死锁

　　（1）改变访问资源的顺序可以避免死锁

　　继续上面的例子，Connection A先访问Products表中的行，然后访问OrderDetails表中的行；Connection B先访问OrderDetails表中的行，然后访问Products表中的行。

　　这时如果我们改变一下访问顺序：两个事务按照同样的顺序来访问资源，则不会发生这种类型的死锁。

      通过交换其中一个事务的操作顺序，就可以避免发生这种类型的死锁（假设交换顺序不必改变程序的逻辑）。　

　　（2）良好的索引设计也可以避免死锁

　　如果查询筛选条件缺少良好的索引支持，也会造成死锁。例如，假设Connection B中的事务有两条语句要对产品5进行筛选，Connection A中的事务要对产品2进行处理，那么他们就不应该有任何冲突。但是，如果在表的productid列上如果没有索引来支持查询筛选，那么SQL Server就必须扫描（并锁定）表中的所有行，这样当然会导致死锁。

        总之，良好的索引设计将有助于减少这种没有真正的逻辑冲突的死锁。