Microsoft SQL Server中的事务与并发详解

本篇索引：

1、事务

2、锁定和阻塞

3、隔离级别

4、死锁

一、事务

1.1 事务的概念

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

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

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

1.2 事务的ACID特性

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

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

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

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

在极端断电或者系统崩溃的情况下，一个发生在事务未提交之前，数据库应该记录了这个事务的"ID"和部分已经在数据库上更新的数据。供电恢复数据库重新启动之后，这时完成全部撤销和回滚操作。如果在事务提交之后的断电，有可能更改的结果没有正常写入磁盘持久化，但是有可能丢失的数据会通过事务日志自动恢复并重新生成以写入磁盘完成持久化。

1.3 如何定义事务　

　　（1）显示定义：以BEGIN TRAN开始，提交的话则COMMIT提交事务，否则以ROLLBACK回滚事务。

--定义事务
BEGIN TRAN;
  INSERT INTO dbo.T1(keycol, col1, col2) VALUES(4,101,'C');
  INSERT INTO dbo.T1(keycol, col1, col2) VALUES(4,201,'X');
COMMIT TRAN;

　　（2）隐式定义：SQL Server中默认把每个单独的语句作为一个事务。

　　换句话说，SQL Server默认在执行完每个语句之后就自动提交事务。当然，我们可以通过IMPLICIT_TRANSACTIONS会话选项来改变SQL Server处理默认事务的方式，该选项默认情况下是OFF。如果将其设置为ON，那么就不必用BEGIN TRAN语句来表明事务开始，但仍然需要以COMMIT或ROLLBACK来标明事务完成。　

二、锁定和阻塞

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）：

三、隔离级别

　　隔离级别用于决定如何控制并发用户读写数据的操作。前面说到，读操作默认使用共享锁，写操作需要使用排它锁。对于操作获得的锁，以及锁的持续时间来说，虽然不能控制写操作的处理方式，但可以控制读操作的处理方式。作为对读操作的行为进行控制的一种结果，也会隐含地影响写操作的行为方式。

　　为此，可以在会话级别上用会话选项来设置隔离级别，也可以在查询级别上用表提示（Table Hint）来设置隔离级别。

　　在SQL Server中，可以设置的隔离级别有6个：READ UNCOMMITED（未提交读）、READ COMMITED（已提交读）、REPEATABLE READ（可重复读）、SERIALIZEABLE（可序列化）、SNAPSHOT（快照）和READ COMMITED SNAPSHOT（已经提交读隔离）。最后两个SNAPSHOT和READ COMMITED SNAPSHOT是在SQL Server 2005中引入的。

　　要设置整个会话级别的隔离级别，可以使用以下语句：

SET TRANSACTION ISOLATION LEVEL <isolation name>;

　　也可以使用表提示来设置查询级别的隔离级别：

SELECT ... FROM <table> WITH <isolation name>;

3.1 READ UNCOMMITED 未提交读

　　未提交读是最低的隔离级别，读操作不会请求共享锁。换句话说，在该级别下的读操作正在读取数据时，写操作可以同时对这些数据进行修改。

　　同样，使用两个会话来模拟：

　　Step1.在Connection A中运行以下代码，更新产品2的单价，为当前值（19.00）增加1.00，然后查询该产品：

-- Connection A
BEGIN TRAN;

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

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

　　Step2.在Connection B中运行以下代码，首先设置隔离级别为未提交读，再查询产品2所在的记录：

-- Connection B
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

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

　　因为这个读操作不用请求共享锁，因此不会和其他事务发生冲突，该查询返回了如下图所示的修改后的状态，即使这一状态还没有被提交：

　　Step3.在Connection A中运行以下代码回滚事务：

ROLLBACK TRAN;

这个回滚操作撤销了对产品2的更新，这时它的价格被修改回了19.00，但是读操作此前获得的20.00再也不会被提交了。这就是脏读的一个实例！

3.2 READ COMMITED 已提交读

　　刚刚说到，未提交到会引起脏读，能够防止脏读的最低隔离级别是已提交读，这也是所有SQL Server版本默认使用的隔离级别。如其名称所示，这个隔离级别只允许读取已经提交的修改，它要求读操作必须获得共享锁才能操作，从而防止读取未提交的修改。

　　继续使用两个会话来模拟：

　　Step1.在Connection A中运行以下代码，更新产品2的价格，再查询显示价格：

BEGIN TRAN;

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

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

　　Step2.再在Connection B中运行以下代码，这段代码将会话的隔离级别设置为已提交读，再查询产品2所在的行记录：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

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

　　这时该会话语句会被阻塞，因为它需要获取共享锁才能进行读操作，而它与会话A的写操作持有的排它锁相冲突。这里因为我设置了默认会话阻塞超时时间，所以出现了以下输出：

　　Step3.在Connection A中运行以下代码，提交事务：

COMMIT TRAN;

　　Step4.回到Connection B，此时会得到以下输出：

在已提交读级别下，不会读取脏数据，只能读取已经提交过的修改。但是，该级别下，其他事务可以在两个读操作之间更改数据资源，读操作因而可能每次得到不同的取值。这种现象被称为 不可重复读。　　

3.3 REPEATABLE READ 可重复读

　　如果想保证在事务内进行的两个读操作之间，其他任何事务都不能修改由当前事务读取的数据，则需要将隔离级别升级为可重复读。在该级别下，十五中的读操作不但需要获得共享锁才能读数据，而且获得的共享锁将一直保持到事务完成为止。换句话说，在事务完成之前，没有其他事务能够获得排它锁以修改这一数据资源，由此来保证实现可重复的读取。

　　Step1.为了重新演示可重复读的示例，首先需要将刚刚的测试数据清理掉，在Connection A和B中执行以下代码：

-- Clear Test Data
UPDATE Production.Products
SET unitprice = 19.00
WHERE productid = 2;

View Code

　　Step2.在Connection A中运行以下代码，将会话的隔离级别设置为可重复读，再查询产品2所在的行记录：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRAN;

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

　　这时该会话仍然持有产品2上的共享锁，因为在该隔离级别下，共享锁要一直保持到事务结束为止。

　　Step3.在Connection B中尝试对产品2这一行进行修改：

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

　　这时该会话已被阻塞，因为修改操作锁请求的排它锁与前面会话授予的共享锁有冲突。换句话说，如果读操作是在未提交读或已提交读级别下运行的，那么事务此时将不再持有共享锁，Connection B尝试修改改行的操作应该能够成功。

　　同样，由于我设置了超时释放时间，因此会有以下输出：

　　Step4.回到Connection A，运行以下代码，再次查询茶品2所在的行，提交事务：

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

COMMIT TRAN;

　　这时的返回结果仍然与第一次相同：

　　Step5.这时再执行Connection B中的更新语句，便能够正常获得排它锁了，于是执行成功，价格变为了20.00。

可重复读隔离级别不仅可以防止不可重复读，另外还能防止丢失更新。丢失更新是指两个事务读取了同一个值，然后基于最初读取的值进行计算，接着再更新该值，就会发生丢失更新的问题。这是因为在可重复读隔离级别下，两个事务在第一次读操作之后都保留有共享锁，所以其中一个都不能成功获得为了更新数据而需要的排它锁。但是，负面影响就是会导致死锁。

在可重复读级别下运行的事务，读操作获得的共享锁将一直保持到事务结束。因此可以保证在事务中第一次读取某些行后，还可以重复读取这些行。但是，事务只锁定查询第一次运行时找到的那些行，而不会锁定查询结果范围外的其他行。因此，在同一事务进行第二次读取之前，如果其他事务插入了新行，而且新行也能满足读操作额查询过滤条件，那么这些新行也会出现在第二次读操作返回的结果中。这些新行称之为幻影，这种读操作也被称为幻读。

3.4 SERIALIZEABLE 可序列化

　　为了避免刚刚提到的幻读，需要将隔离级别设置为可序列化。可序列化级别的处理方式与可重复读类似：读操作需要获得共享锁才能读取数据并一直保留到事务结束，不同之处在于在可序列化级别下，读操作不仅锁定了满足查询条件的那些行，还锁定了可能满足查询条件的行。换句话说，如果其他事务试图增加能够满足操作的查询条件的新行，当前事务就会阻塞这样的操作。

　　同样，继续来模拟：

　　Step1.在Connection A中运行代码，设置隔离级别为可序列化，再查询产品分类等于1的所有产品：

-- Connection A
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRAN

  SELECT productid, productname, categoryid, unitprice
  FROM Production.Products
  WHERE categoryid = 1;

　　Step2.在Connection B中运行代码，尝试插入一个分类等于1的新产品：

-- Connection B
INSERT INTO Production.Products
    (productname, supplierid, categoryid,
     unitprice, discontinued)
  VALUES('Product ABCDE', 1, 1, 20.00, 0);

　　这时，该操作会被阻塞。因为在可序列化级别下，前面的读操作不仅锁定了满足查询条件的那些行，还锁定了可能满足查询条件的行。

　　同样，由于我设置了超时释放时间，因此会有以下输出：

　　Step3.回到Connection A，运行以下代码，再次查询分类1的产品，最后提交事务：

  SELECT productid, productname, categoryid, unitprice
  FROM Production.Products
  WHERE categoryid = 1;

COMMIT TRAN;

　　Step4.回到Connection B，这时Connection B就已经获得了等候已久的排它锁，插入了新行。

INSERT INTO Production.Products
    (productname, supplierid, categoryid,
     unitprice, discontinued)
  VALUES('Product ABCDE', 1, 1, 20.00, 0);

SELECT productid, productname, categoryid, unitprice
FROM Production.Products
WHERE categoryid = 1;

　　Step5.为了后面的演示，运行以下代码清理测试数据：

-- Cleanup
DELETE FROM Production.Products
WHERE productid > 77;

DBCC CHECKIDENT ('Production.Products', RESEED, 77);

View Code

3.5 SNAPSHOT 快照

　　首先解释一下什么是快照？事务已经提交的行的上一个版本存在tempdb数据库中，这是SQL Server引入的一个新功能。

　　以这种行版本控制技术为基础，SQL Server增加了两个新的隔离级别：SNAPSHOT和READ COMMITED SNAPSHOT。如果启用任何一种基于快照的隔离级别，DELETE和UPDATE语句在做出修改前都会把行的当前版本复制到tempdb数据库中；INSERT语句则不会，因为这时还没有行的旧版本。

　　在SNAPSHOPT（快照）隔离级别下，当读取数据时，可以保证读操作读取的行是事务开始时可用的最后提交的版本。

　　下面来模拟一下该隔离级别下的场景：

　　Step1.还是打开两个会话窗口，在其中一个执行以下代码，设置隔离级别为SNAPSHOT：

-- Allow SNAPSHOT isolation in the database
ALTER DATABASE TSQLFundamentals2008 SET ALLOW_SNAPSHOT_ISOLATION ON;

　　Step2.在Connection A中运行以下代码，更新产品2的价格，然后再查询该产品的价格：

-- Connection A
BEGIN TRAN;

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

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

　　Step3.在Connection B中运行以下代码，设置隔离级别为SNAPSHOT，并查询产品2的价格：

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRAN;

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

　这时的返回结果如下所示，可以看到这个结果是在该事务启动时可用的最后提交的版本。

　　Step4.回到Connection A提交这一修改的行：

COMMIT TRAN;

　　Step5.在Connection B中运行以下代码，再次读取数据，然后提交事务：

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

　　然后我们会得到跟之前一样的结果，奇了个怪了：

　　但是如果我们再次在Connection B中运行以下完整语句：

BEGIN TRAN;

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

　　这时结果便会同步，这个事务开始时可用的上一个提交的版本是价格=20.00

　　为什么两个事务得到结果会不同？这是因为快照清理线程每隔一分钟运行一次，现在由于没有事务需要为价格=20.00的那个行版本了，所以清理线程下一次运行时会将这个行版本从tempdb数据库中删除掉。

　　最后，为了下一次演示，清理测试数据：

-- Clear Test Data
UPDATE Production.Products
SET unitprice = 19.00
WHERE productid = 2;

View Code

这一隔离级别使用的不是共享锁，而是行版本控制。如前所述，不论修改操作（主要是更新和删除数据）是否在某种基于快照的隔离级别下的会话执行，快照隔离级别都会带来性能上的开销。

　　另外，在SNAP快照级别下，可以通过检查的行版本，检测出更新冲突。它能判断出在快照事务的一次读操作和一次写操作之间是否有其他事务修改过数据。如果SQL Server检测到在读取和写入操作之间有另一个事务修改了数据，则会让事务因失败而终止，并返回以下错误信息：

　　冲突检测完整实例如下：

---------------------------------------------------------------------
-- Conflict Detection 冲突检测实例
---------------------------------------------------------------------

-- Connection A, Step 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRAN;

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

-- Connection A, Step 2
  UPDATE Production.Products
    SET unitprice = 20.00
  WHERE productid = 2;
  
COMMIT TRAN;

-- Cleanup
UPDATE Production.Products
  SET unitprice = 19.00
WHERE productid = 2;

-- Connection A, Step 1
BEGIN TRAN;

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

-- Connection B, Step 1
UPDATE Production.Products
  SET unitprice = 25.00
WHERE productid = 2;

-- Connection A, Step 2
  UPDATE Production.Products
    SET unitprice = 20.00
  WHERE productid = 2;

-- Cleanup
UPDATE Production.Products
  SET unitprice = 19.00
WHERE productid = 2;

-- Close all connections

View Code

3.6 READ COMMITED SNAPSHOT 已经提交读隔离

　　已提交读隔离也是基于行版本控制，但与快照不同之处在于：在已提交读级别下，读操作读取的数据行不是食物启动之前最后提交的版本，而是语句启动前最后提交的版本。

　　此外，该级别不会像快照隔离级别一样进行更新冲突检测。这样一来，它就跟SQL Server默认的READ COMMITED级别非常类似了，只不过读操作不用获得共享锁，当请求的资源被其他事务的排它锁锁定时，也不用等待。

　　下面继续通过案例来模拟：

　　Step1.运行以下代码，设置隔离级别：

-- Turn on READ_COMMITTED_SNAPSHOT
ALTER DATABASE TSQLFundamentals2008 SET READ_COMMITTED_SNAPSHOT ON;

　　执行该查询需要一定的时间，并且要注意：要成功运行，当前连接必须是指定数据库的唯一连接，请关掉其他连接，只保留一个会话来执行。

　　可以看到它跟我们之前设置隔离级别所使用的的语句不同，这个选项其实就是把默认的READ COMMITED的寒意变成了READ COMMITED SNAPSHOT。意味着打开这个选项时，除非显式地修改会话的隔离级别，否则READ COMMITED SNAPSHOT将成为默认的隔离级别。

　　Step2.在Connection A中运行以下代码，更新产品2所在的行记录，再读取这一行记录，并且一直保持事务打开：

-- Connection A
USE TSQLFundamentals2008;

BEGIN TRAN;

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

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

　　Step3.在Connection B中读取产品2所在的行记录，并一直保持事务打开：

-- Connection B
BEGIN TRAN;

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

　　得到的结果是语句启动之前最后提交的版本（19.00）：

　　Step4.回到Connection A，提交事务：

COMMIT TRAN;

　　Step5.回到Connection B，再次读取产品2所在的行，并提交事务：

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

COMMIT TRAN;

　　这时结果如下，可以看到跟SNAPSHOT不同，这次的结果是在语句执行之前最后提交的版本而不是事务执行之前最后提交的版本，因此得到了20.00：

回想一下，这种现象是不是我们常听见的 不可重复读？也就是说，该级别下，无法防止不可重复读问题。

　　最后，按照国际惯例，清理测试数据：

-- Clear Test Data
UPDATE Production.Products
SET unitprice = 19.00
WHERE productid = 2;

View Code

　　然后，关闭所有连接，然后在一个新的连接下运行以下代码，以禁用指定数据库的基于快照的隔离级别：（执行ALTER DATABASE TSQLFundamentals2008 SET READ_COMMITTED_SNAPSHOT OFF;这一句时可能需要花费一点时间，请耐心等候；）

-- Make sure you're back in default mode
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Change database options to default
ALTER DATABASE TSQLFundamentals2008 SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE TSQLFundamentals2008 SET READ_COMMITTED_SNAPSHOT OFF;

View Code

3.7 隔离级别总结

　　下表总结了每种隔离级别能够解决各种逻辑一致性的问题，以及隔离级别是否会检测更新冲突，是否使用了行版本控制。

　　这时再回顾以下各个问题的描述及结果，我们来看另一个表：

并发事务引起的问题
问题	描述	结果	解决
丢失更新	A读—B读—A改—B改	A更改丢失	READ UNCOMMITTED
脏读	A改—B读—A回滚	B读无效值	READ COMMITTED
不可重读	A读—B改—A读	A读不一致	REPEATABLE READ
不可重读	A读—B改—A读	A读不一致	SNAPSHOT
幻读	A读—B增删—A读	A读或多或少	SERIALIZABLE

四、死锁

4.1 死锁是个什么鬼?

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

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

4.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会选择工作量较小的一个事务作为牺牲品）另外，解除死锁需要一定的系统开销，因为这个过程会涉及撤销已经执行过的处理。

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

4.3 避免死锁

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

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

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

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

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

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

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

　　最后，按照国际惯例清理掉测试数据：

-- Cleanup
UPDATE Production.Products
  SET unitprice = 19.00
WHERE productid = 2;

UPDATE Sales.OrderDetails
  SET unitprice = 19.00
WHERE productid = 2
  AND orderid >= 10500;

UPDATE Sales.OrderDetails
  SET unitprice = 15.20
WHERE productid = 2
  AND orderid < 10500;

View Code

五、小结

　　本篇介绍了事务和并发，重点解释了事务是个什么鬼，以及在SQL Server中如何管理事务。演示了在SQL Server中如何把一个事务访问的数据和其他事务的不一致性使用进行隔离，以及如何处理死锁的情况。相信随着这些内容的理解，我们对事务和并发的认知不再停留在数据库基础的教材里边，也希望对大家有所帮助。最后推荐各位.NET程序员都阅读一下《MS SQL Server 2008技术内幕：T-SQL语言基础》这本书，真的是值得阅读的一本。

　　后续我会阅读《MS SQL Server 2008技术内幕：T-SQL查询》，会带来更多的分享给大家！

参考资料

TSQLFundenmantals

（1）[美] Itzik Ben-Gan 著，成保栋译，《Microsoft SQL Server 2008技术内幕：T-SQL语言基础》

考虑到很多人买了这本书，却下载不了这本书的配套源代码和示例数据库，特意上传到了百度云盘中，点此下载

（2）BIWORK，《SQL Server 中的事务与事务隔离级别以及如何理解脏读, 未提交读,不可重复读和幻读产生的过程和原因》

（3）Jackson，《30分钟全面解析-SQL事务+隔离级别+阻塞+死锁》

作者：周旭龙

出处：http://www.cnblogs.com/edisonchou/

本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文链接。

posted @ 2016-12-04 12:58 EdisonZhou 阅读(16212) 评论(2) 编辑收藏举报

刷新页面返回顶部

Edison Zhou

To be a happy Programmer and Blogger!

Microsoft SQL Server中的事务与并发详解

一、事务

1.1 事务的概念

1.2 事务的ACID特性

1.3 如何定义事务

二、锁定和阻塞

2.1 锁

2.2 阻塞

三、隔离级别

3.1 READ UNCOMMITED 未提交读

3.2 READ COMMITED 已提交读

3.3 REPEATABLE READ 可重复读

3.4 SERIALIZEABLE 可序列化

3.5 SNAPSHOT 快照

3.6 READ COMMITED SNAPSHOT 已经提交读隔离

3.7 隔离级别总结

四、死锁

4.1 死锁是个什么鬼?

4.2 死锁实例

4.3 避免死锁

五、小结

参考资料

公告