SQL Server事务、隔离级别和并发问题解决

     版权声明：本文为博主原创文章，未经博主允许不得转载。

    首先，对于SqlServer的事务和隔离级别，与其他关系型数据库类似，事务是一个工作单元，可能包含查询和修改数据以及修改数据定义等多个活动。我们可以显式或隐式的定义事务边界。可以使用BEGIN TRAN或者BEGIN TRANSACTION语句显式的定义事务的开始。如果希望提交事务，可以使用COMMIT TRAN语句显式的定义事务结束。如果不希望提交事务（即要撤销更改），可以使用ROLLBACK TRAN或者ROLLBACK TRANSACTION语句-摘抄自SQL Server 2012基础教程。  

事务具有原子性、一致性、隔离性、持续性四个属性，缩写字母为ACID。

（1）原子性：事务是一个工作单元，事务中的所有修改要么提交、要么撤销，在事务完成之前如果系统出现故障，重新启动时SQL Server会撤销所做的修改。

（2）一致性：一致性是指数据的状态，RDMS提供了以并发事务修改和查询数据的能力。

（3）隔离性：隔离是用于控制访问数据的机制，确保事务所访问数据是在其期望的一致性级别中的数据，SQL Server支持两种不同的模式来处理隔离：基于锁的传统模式和基于行版本控制的新模式，在企业内部部署的SQL Server中，默认是基于锁的模式。

（4）持续性：数据修改写入到数据库磁盘上的数据部分之前，总是先写入到数据库的事务日志磁盘，在提交之后，指令记录在事务日志的磁盘上，在尚未修改磁盘上的数据部分之前，事务被认为是持续的，在系统正常或是出现故障启动时，SQL Server将检查每个数据库的事务日志并执行具有两个阶段的恢复过程-重做和撤销。

 ***************************************************************************************************************************    说了一些概念之后，接下来想结合事务的并发说一下锁，比较简单的我们能接触到的有三种锁，共享锁（S）、排它锁（X）、更新锁（U）。

排他锁：当试图修改数据时，事务会请求数据资源的一个排他锁，而不管其隔离级别，如果授予了锁，那么排他锁知道事务结束才会被解除，对于单语句事务意味着直到语句完成锁定才会被解除，对于多语句事务意味着直到完成所有语句并通过COMMIT TRAN或ROLLBACK TRAN命令结束才会解除锁定。排他锁之所以被称为排他，是因为如果一个事务正在修改行，直到事务完成，其他事务都不能修改相同的行，这是默认的修改行为。然而，另外一个事物能不能读取相同的行，取决于它的隔离级别。

共享锁：当试图读取数据时，事务默认请求数据资源的一个共享锁，并且一旦语句完成资源读取，会立即释放资源的共享锁。共享锁之所以被称为共享，是因为多个事务可以同时持有相同资源的共享锁。虽然在修改数据时，不能修改锁的模式和所需的持续时间，但是通过改变其隔离级别，可以在读取数据时控制锁定的处理方式。

更新锁
更新 (U) 锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S) 锁，然后修改行，此操作要求锁转换为排它 (X) 锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。

若要避免这种潜在的死锁问题，请使用更新 (U) 锁。一次只有一个事务可以获得资源的更新 (U) 锁。如果事务修改资源，则更新 (U) 锁转换为排它 (X) 锁。否则，锁转换为共享锁。

BEGIN TRAN

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

接下来我们再来读取该条记录的数据。

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

接下来我们进行查询，此时会发现一直在查询中直到达到设置的查询超时时间为止。

当更新行时会获取该资源上的排他锁，如果更新成功，SQL Server会将锁授予会话，所以直到事务完成，其排他锁会一直存在，当读取数据时需要获取该资源上的共享锁，但是更新行会话一直存在即以排他锁锁定，但是排他锁和共享锁不能兼容，此时会导致查询阻塞不得不进行等待。说明锁在并发情况下会导致阻塞。

    接下来，说一下事务并发过程中可能出现的四种问题：脏读、丢失修改、不可重复读、幻影读。

1、脏读：读取未提交的数据。一个进程更新了数据但在另一个进程读取相同数据之前未提交该更新。第二个进程所读取的数据处于一种不一致状态。

                            图1-0（t3时刻事务T2读到了脏数据）

2、丢失修改：一个进程读取了数据，并对数据执行了一些计算，然后根据这些计算更新数据。如果两个进程都是先读取数据，然后再根据他们所读取数据更新，其中一个进程可能会覆盖另一个进程的更新。

                      图1-1（本身卖完两张票后数据库中A应为0，丢失修改造成A为1）

3、不可重复读：在同一事物的两次读取中，进程读取相同的资源得到不同的值。当第二个进程在第一个进程的两次读取之间更新数据，就会发生这种情况。

                  图1-2（两次读取不一致数据）

4、幻读：当一个进程对一定范围内的行执行操作，而另外一个进程对该范围内的行执行不兼容的操作，这时会发生幻读。例如，一个进程删除的符合某筛选器的所有行，在删除事务期间，另一个进程插入符合该筛选器的新行。则新行被认为是幻影行。

                    图1-3

Repeatable Read、Serializable.这是四种基本的事务隔离级别也可以叫它们并发控制模式中的悲观控制模式。隔离级别确定了并发用户读取或写入的行为，读取者是任何选择数据的语句，默认情况下使用共享锁，写入者是任何对表进行修改的语句，并且需要一个排他锁。在获得锁和锁的持续期间，不能控制写入者的行为方式，但是可以控制读取者的行为方式，我们通过设置隔离级别来隐式的影响写入者的行为。

这四种模式就是根据取不取锁以及锁的持续时间来划分的。（主要是通过影响读取者间接影响写入者）

隔离级别逐渐变大。

READ_UNCOMMITTED：读取者不取锁，会出现脏读现象（读取者读到了写入者还没有提交的数据）。

READ_COMMITTED：它是企业内部部署的SQL Server默认隔离级别。读取者获得共享锁，但是读取完成后便不再持有锁。解决了脏读，但会出现丢失更新（丢失更新主要发生在两个事务读取一个值时，同时基于读取的值进行更新，由于在该隔离级别中读取后不会再该资源上持有锁，两个事务都可以更新其值，并且最后更新该值的事务将会覆盖另外一个事务的更新。产生的主要原因还是读取者在读取完成后便不再持有共享锁导致的）和不可重复读（在读取间隙，读取者不再持有共享锁，那么写入者便可以更改数据造成前后读取不一致的现象）的问题。

Repeatable Read：读取者直到事务结束一直持有共享锁。这意味着直到读取者事务结束都没有写入者能够修改资源。解决了丢失更新和不可重复读的问题，但是容易引发死锁（多个读取者第一次都获得了共享锁但是在接下来谁都无法获得排他锁的现象。原因就是锁的持续时间到了事务结束之后，然而事务既可以读取也可以写入导致的问题）虽然这种模式能够保证读取者前后读取的一致性，但是会出现幻读（共享锁还是互斥锁都 保留到了事务结束，但是无法阻止其他人运行新增操作，导致第一次查询时没有数据，第二次查询时却有了数据。被称为“幻读”）。

Serializable

为了防止幻影读取，需要将隔离级别提升为SERIALIZABLE，最重要的部分是SERIALIZABLE隔离级别的行为类似于REPEATABLE READ即它要求读取者获取一个共享锁来进行读取，并持有锁到事务结束，但是SERIALIZABLE隔离级别添加了另外一个方面-在逻辑上，该隔离级别要求读取者锁定查询筛选所限定的键的整个范围。这意味着读取者锁定的不仅是查询筛选限定的现有行，也包括将来行，或者准确地说，它会阻止其他事务尝试添加读取者查询筛选限定的行。下面我们来演示这种情况。

BEGIN TRAN

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

我们查询产品Id = 1的所有行，结果集如下：

接下来我们再来插入一条数据。

INSERT INTO Production.Products
        ( productname ,
          supplierid ,
          categoryid ,
          unitprice ,
          discontinued
        )
VALUES  ( N'Product ABCDE' , -- productname - nvarchar(40)
          1 , -- supplierid - int
          1 , -- categoryid - int
          20.00 , -- unitprice - money
          0  -- discontinued - bit
        )

此时尝试插入会成功，但是查询出来的数据有12条数据，实际上有13条数据也就是说导致幻影读取。当我们在查询数据时设置SERIALIZABLE如下隔离级别，此时插入语句会将处于阻塞状态

SET TRAN ISOLATION LEVEL SERIALIZABLE

通过设置隔离级别为SERIALIZABLE能够解决幻影读取情况。

随着隔离级别的升高，读取者请求的锁就越强，并且持续时间越长。因此，隔离级别越高，一致性越高并发性能越低。当处理并发问题时就需要基于行版本的隔离级别了。

**********************************************************************************************************************************

基于行版本的隔离级别

    在SQL Server中存在两种基于行版本控制技术的隔离级别：SNAPSHOT、READ COMMITTED SNAPSHOT。如果启用了任何基于快照的隔离级别，在修改tempdb之前，DELETE和UPDATE语句需要复制行的版本，对于INSERT语句则不需要再tempdb中版本化，因为它不存在早期的版本，但需要注意的是，启用任何基于行版本控制的隔离级别对于数据更新和删除的性能可能会有负面影响，由于它们不会获取共享锁，并且哎数据被以排他方式锁定或是数据版本不是所期望的版本时不需要等待，因此对于读取者的性能通常会有所改善。我们又称这两种隔离级别为乐观式并发隔离级别。

SNAPSHOT隔离级别

在SNAPSHOT隔离级别下，读取者在读取数据时， 它是确保获得事务启动时最近提交的可用行版本，这意味着，保证获得的是提交后的读取并且可重复读取，以及确保获得不是幻读，类似于SERIALIZABLE级别中一样，但是此隔离级别依赖于行版本，而不是使用共享锁，要想在企业部署的SQL Server实例中允许事务以SNAPSHOT隔离级别工作，首先需要在查询窗口执行以下代码打开快照隔离级别。如下：

ALTER DATABASE TSQL2012 SET ALLOW_SNAPSHOT_ISOLATION ON

下面来演示SNAPSHOT隔离级别行为，我们打开一个事务在当前基础上更新单价，如下：

BEGIN TRAN

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


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

此时更新尚未提交的事务，此时其单价为25。

SET TRAN ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

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

因为我们在数据库中启用了SNAPSHOT隔离级别，此时即使是在READ COMMITTED隔离级别下运行也会复制更新到tempdb之前的版本，如下我们设置隔离级别为SNAPSHOT来打开一个事务查询其行记录。

SET TRAN ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

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

如果是在SERIALIZABLE隔离级别下运行，此时肯定导致查询阻塞，但是由于在SNAPSHOT模式下，不会去获取该事务上的共享锁，而是获取事务运行时可用的上次提交的行版本。此时之前版本的unitprice = 24而不是当前版本的unitprice = 25，如下：

此时我们再将上述未提交的写入事务进行提交。此时unitprice = 25的当前版本则变为了提交版本，但是我们再来读取数据并提交事务，仍旧会获得该行事务启动时可用的最后提交版本，如下：

当我们重新打开一个事务进行查询，此时事务启动时该行可用的最后提交版本时unitprice = 25的版本，如下：

SNAPSHOT隔离级别可以防止更新冲突，但不会像REPEATABLE READ和SERIALIZABLE隔离级别那样产生死锁，SNAPSHOT隔离级别的事务失败，表明检测到了更新冲突，SNAPSHOT隔离级别通过检查存储的版本来检测更新冲突，它可以发现在事务的读取和写入之间是否有另一个事务修改了数据。

READ COMMITTED SNAPSHOT隔离级别

该隔离级别也是基于行版本控制，它与SNAPSHOT隔离级别区别在于，读取者获得是【语句】启动时可用的最后提交的行版本，而不是【事务】启动时可用的最后提交的行版本，READ COMMITTED SNAOSHOT也不会检测更新冲突，导致类似于READ COMMITTED隔离级别，但在所请求资源以排他锁锁定时，不会请求共享锁并且不会等待。在企业内部部署的SQL Server中要想启动READ COMMITTED SNAPHOST隔离级别，需要打开唯一会话来设置，否则无法进行启用（启用该隔离级别实际上是将READ COMMITTED隔离级别在语义上改变为READ COMMITTED SNAPSHOT隔离级别）。下面我们来演示下READ COMMITTED SNAPSHOT隔离级别。

ALTER DATABASE TSQL2012 SET READ_COMMITTED_SNAPSHOT ON;

我们同样是更新一个尚未提交的事务，如下：

BEGIN TRAN;

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

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

此时我们将上述写入进行提交，再来打开一个会话读取该行记录数据。

此时我们再来提交事务看看。

如果 是在SNAPSHOT隔离级别下运行上述代码，就会得到unitprice = 24，但是由于代码运行在READ COMMITTED SNAPSHOT隔离级别下，会得到语句启动时可用的最后提交的行版本unitprice = 25，而不是事务开始时的行版本unitprice = 24。