SQL Server-聚焦事务、隔离级别详解（二十九）

素颜马尾好姑娘i 2022-07-11 19:24 152阅读 0赞

# 前言 #

事务一直以来是我最薄弱的环节，也是我打算重新学习SQL Server的出发点，关于SQL Server中事务将分为几节来进行阐述，Always to review the basics。

## 事务简介 ##

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

如果不显式的标记事务的边界，默认情况下，SQL Server将把每个单独语句作为一个事务，换句话说，默认情况下，每个单独语句结束后SQL Server自动提交事务。可以通过一个叫做IMPLICIT\_TRANSACTIONS的回话选项修改SQL Server处理隐式事务的方式，此选项默认为OFF。当此选项为ON时，不需要指定BEGIN TRAN语句标记事务的开始，但是必须以COMMIT TRAN或者ROLLBACK TRAN语句标记事务的结束-摘抄自SQL Server 2012基础教程。

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

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

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

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

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

![589642-20170127170928566-1558732354.png][]

图片来源：https://blog.sqlauthority.com/2007/12/09/sql-server-acid-atomicity-consistency-isolation-durability/

说到事务就联想到并发，为了解决事务中的并发我们则不得不讨论下锁，所以接下来我们首先熟悉一下锁的模式-排他锁和共享锁。

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

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

讲述了锁的两种重要的模式，那么问题来了，锁的存在会导致什么问题？请继续往下看。我们试图去更新一条数据，此时并未进行提交

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

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

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

![589642-20170127175201175-560583876.png][]

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

![589642-20170127175603050-898986750.gif][]

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

## 锁的隔离级别 ##

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

SQL Server支持4个基于悲观并发控制的传统隔离级别：READ UNCOMMITTED、READ COMMITTED（企业内部部署的SQL Server实例的默认方式）、REPEATABLE READ、SERIALIZABLE。SQL Server还支持两种基于并发控制（行版本）的隔离级别：SNAPSHOT和READ COMMITTED SNAPSHOT（SQL Database的默认方式）在某种意义上，SNAPSHOT和READ COMMITTED SNAPSHOT分别是READ COMMITTED和SERIALIZABLE的乐观并发对应方式。

我们使用如下命令来设置整个会话的隔离级别

SET TRANSACTION ISOLATION LEVEL <isolation name>

或者间接在表查询中设置查询的隔离级别。

SELECT ....  FROM TABLE WITH(<isolationname>)

对于以上四个隔离级别，隔离级别越高，读取者请求的锁就越强，并且持续时间越长。因此，隔离级别越高，一致性越高并且性越低，当然，反过来也是如此。对于两个基于快照的隔离级别，SQL Server能够在tempdb中存储之前提交的行版本，读取者不请求共享锁。相反，如果当前的行版本不是他们应该看到的，SQL Server将提供给他们一个较旧的版本。

### READ UNCOMMITTED隔离级别 ###

READ COMMITTED是最低隔离级别，在该隔离级别中，读取者不需要请求共享锁，不要求共享锁的读取者从不会与持有排他锁的写入者发生冲突，这意味着读取者可以读取未提交的更改即脏读，也就是说，读取者不会干扰要求了排他锁的写入者，在该隔离级别下运行的读取者读取数据时，写入者可以更改数据。

![589642-20170128145248894-1983716256.png][]

上述我们圈出此时productid = 2的行记录，此时我们来更新该条行记录的uniprice列数据，如下

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

![589642-20170128145524331-955295030.png][]

此时我们清楚看到上述单价（unitprice）更新为了25，没毛病，此时我们再设置隔离级别为READ UNCOMMITTED运行如下代码（我们保持上述更新会话一直打开，此时将保持排他锁一直存在，虽然排他锁和共享锁不兼容，但是在READ COMMITTED隔离级别下查询不会去请求共享锁，所以并不会与上述更新事务冲突）

SET TRAN ISOLATION LEVEL READ UNCOMMITTED
    
    SELECT 
        productid, unitprice
    FROM Production.Products
    WHERE productid = 2

![589642-20170128150741425-1422605443.png][]

此时我们再将上述未提交的值进行回滚，如下

BEGIN TRAN
    
    UPDATE Production.Products
        SET unitprice += 1.00
    WHERE productid = 2;
    
    ROLLBACK TRAN
    
    SELECT 
        productid, unitprice
    FROM Production.Products
    WHERE productid = 2

![589642-20170128175243784-986536482.png][]

到这里我们想必知道了脏读的由来，当我们在一个会话中更新指定行记录时，此时我们并未进行提交，此时unitprice更新为25，接着我们在READ COMMITTED隔离级别下去查询同一行记录此时查询unitprice为25（即使上述修改并未进行提交），最后我们在某一时刻通过回滚对更新事务进行了撤销，此时数据库中的该行记录依然是24，但是我们读取的结果却是25，所以 读取者获得的是从未提交过的值，也就是我们说的脏读。到这里我们可以下一个结论：

READ UNCOMMITTED：在该隔离级别下会导致数据脏读。

我们通过设置隔离级别为READ COMMITTED来解决数据脏读，请继续往下看。

### READ COMMITTED隔离级别 ###

如果想阻止读取者未提交的修改，则需要使用更高的隔离级别，防止脏读的最低的隔离级别为READ COMMITTED，它是企业内部部署的SQL Server默认隔离级别，如名称所述，该隔离级别仅允许读取者已提交的更改。它通过要求读取者获得一个共享锁来防止未提交的读取，也就是说，如果一个写入者持有了排他锁，读取者的共享锁请求将会与写入者冲突，此时必须等待，一旦写入者提交了事务，读取者就可以获得它的共享锁，所以它必然是只读取提交后的修改。关于READ COMMITTED隔离级别的示例上述我们已经演示。

问题1：与READ UNCOMMITTED隔离级别不同的是，在READ COMMITTED隔离级别中，不会获得脏读，因为它只能读取已提交的修改，但是写入者未进行提交此时会导致持续等待，对于读取者直到完成，读取者都仅持有共享锁，它不会到事务结束一直持有锁，它甚至不会到语句结束，换句话说，在同一事务中的两次相同数据资源的读取之间，不会持有该资源的锁，所以其他事务可以在这两次读取的间隙修改资源，并且读取者每次读取到的值可能会有所不同，这种现象被称为不可重复读取或不一致解析。此时我们就必须通过更高的隔离级别来解决不可重复读取的问题。

问题2：同时我们需要注意的是在READ COMMITTED隔离级别中可能出现【丢失更新】现象，丢失更新主要发生在两个事务读取一个值时，同时基于读取的值进行更新，由于在该隔离级别中读取后不会再该资源上持有锁，两个事务都可以更新其值，并且最后更新该值的事务将会覆盖另外一个事务的更新。

### REPEATABLE READ隔离级别 ###

如果我们希望确保在同一事务中的多次读取之间没有其他事务能够修改其值，需要提升隔离级别到REPEATABLE READ。在该隔离级别中，读取者不仅需要一个共享锁才能够进行读取，而且直到事务结束都持有锁，这意味着只要读取者获得了数据资源上的共享锁，直到读取者结束事务，都没有其他事务可以获取一个排他锁来修改资源，这样才能保证可重复读取或者是一致的解析。我们来演示下该隔离级别，如下：

SET TRAN ISOLATION LEVEL REPEATABLE READ
    
    BEGIN TRAN
    
    SELECT 
        productid, unitprice
    FROM Production.Products
    WHERE productid = 2

![589642-20170128182928175-1437994267.png][]

此时返回productid = 2的单价。接下来我们再来进行更新。

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

![589642-20170128183211284-1010447452.gif][]

由于写入者请求的排他锁与授予读取者的共享锁冲突，此时写入者事务会被阻塞，如果读取者是运行在READ UNCOMMITTED或者READ COMMITTED隔离级别下，此时它将不会持有共享锁，并且试图修改该行就会成功。当我们在查询事务中添加COMMIT TRAN，此时读取者的事务已经提交并且释放了共享锁，如下：

SET TRAN ISOLATION LEVEL REPEATABLE READ
    
    BEGIN TRAN
    
    SELECT 
        productid, unitprice
    FROM Production.Products
    WHERE productid = 2
    
    COMMIT TRAN

此时再来写入者就能获取等待它的排他锁并且成功更新行，此时unitprice = 25；

问题1：虽然REPEATABLE READ隔离级别能够确保同一事务中的多次读取没有其他事务来修改值即解决了不可重复读取或不一致解析的问题，但是在第一次读取后双方都会保持它们的共享锁，因此对于稍后的更新都不会获得一个排他锁，这样就很有可能导致死锁，并且阻止更新冲突。

问题2：虽然REPEATABLE READ隔离级别可以确保在事务中第一次读取的行能够重复读取，但是事务锁定的资源（如行）是查询第一次运行时发现的，在查询运行时那里并没有行，因此，同一事务中的第二次读取可能会返回新行，这些新行被称为幻影，这种读取称为幻影读取，如果在读取之间，另一个事务添加了读取者查询筛选限定的新行，就会导致幻影读取。

既然REPEATABLE READ容易导致幻影读取，我们则需要更高的隔离级别来解决这个问题，请继续往下看。

## SERIALIZABLE隔离级别 ##

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

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

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

![589642-20170128202649894-419334703.png][]

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

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中，SNAPSHOT隔离级别在逻辑上类似于SERIALIZABLE隔离级别，READ COMMITTED SNAPSHOT隔离级别类似于READ COMMITTED隔离级别，但是，读取者使用基于行版本控制的隔离级别并不不会发出共享锁，所以在请求的数据以排他锁锁定时它们不会等待，读取者仍旧会获得类似于SERIALIZABLE和READ  COMMITTED的一致性级别，如果当前版本不是它们希望看到的版本，那么SQL Server会给读取者提供一个较旧的版本。

如果启用了任何基于快照的隔离级别，在修改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

![589642-20170128222354331-1901155666.png][]

此时更新尚未提交的事务，此时其单价为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，如下：

![589642-20170128223143566-2126939032.png][]

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

![589642-20170128223436206-1382665553.png][]

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

![589642-20170128223829284-1805044081.png][]

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

![589642-20170128230352362-1359238506.png][]

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

![589642-20170128230516519-1896159240.png][]

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

![589642-20170128230722941-2013800174.png][]

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

## 总结 ##

本节比较详细的讨论了事务、四种悲观式并发隔离级别和两种乐观式并发隔离级别，下节我们开始谈论一些细枝末节。

[589642-20170127170928566-1558732354.png]: /images/20220711/f2b6860a17a448d48137accf398b81f6.png
[589642-20170127175201175-560583876.png]: /images/20220711/cd70b7249e184e87be4afe684c7146b5.png
[589642-20170127175603050-898986750.gif]: /images/20220711/8f83b95faca5434f81ccf0bf08e48dd4.png
[589642-20170128145248894-1983716256.png]: /images/20220711/c9f14264eeb945e49306aab9afa6e8be.png
[589642-20170128145524331-955295030.png]: /images/20220711/f854314c452b465f80d53470f8f2a4d5.png
[589642-20170128150741425-1422605443.png]: /images/20220711/0a6c1975c5e941f49cd765d9cadad3ff.png
[589642-20170128175243784-986536482.png]: /images/20220711/dd7f21af8c16448591cb010977636331.png
[589642-20170128182928175-1437994267.png]: /images/20220711/df510f0c58b342ff811ada7f776204c5.png
[589642-20170128183211284-1010447452.gif]: /images/20220711/53c9fa03291c41f58a64cdeeb47e9f3d.png
[589642-20170128202649894-419334703.png]: /images/20220711/8d8c928834a64e65a25cd03c02919b9e.png
[589642-20170128222354331-1901155666.png]: /images/20220711/86220f4cf1c94ee29631ba297ecd8d9e.png
[589642-20170128223143566-2126939032.png]: /images/20220711/4c122778daa1448fbf87a22f979fc2a2.png
[589642-20170128223436206-1382665553.png]: /images/20220711/944a4bb2d41447078e9d095febb71261.png
[589642-20170128223829284-1805044081.png]: /images/20220711/412ecfb2cbe94bd78835267f92a8e46d.png
[589642-20170128230352362-1359238506.png]: /images/20220711/0ec5a1f9c8624a1aad3ba2a8b0c38ed1.png
[589642-20170128230516519-1896159240.png]: /images/20220711/668fd313267a4cb0a8b3fb5514e23df0.png
[589642-20170128230722941-2013800174.png]: /images/20220711/27cee52bcd9349e7b55d88da700fd1fd.png