事务和锁

客官°小女子只卖身不卖艺 2021-12-15 07:39 330阅读 0赞

本文并不全是原创，我参考的原文[在这里][Link 1]

--------------------

背景：

当用户并发尝试访问同一数据的时，SQL Server尝试用锁来隔离不一致的数据和使用隔离级别查询数据时控制一致性(数据该如何读取)，说起锁就会联想到事务，事务是一个工作单元，包括查询／更新数据和数据定义。

## 锁 ##

### 锁类型 ###

在SQL Server中，根据资源的不同，锁分为以下三种类型：  
    行锁：是SQL Server中数据级别中粒度最小的锁级别，行锁根据表是否存在聚集索引，分为键值锁和标识锁  
    页锁：针对某个数据页添加的锁，在T-SQL语句中，使用了页锁就不会在使用相同类型的行锁，反之依然，在对数据页加锁后，无法在对其添加不兼容的锁  
    表锁：添加表锁则无法添加与其不兼容的页å锁和行锁

### 锁模式 ###

共享锁(S)：发生在数据查找之前，多个事务的共享锁之间可以共存  
   排他锁(X)：发生在数据更新之前，排他锁是一个独占锁，与其他锁都不兼容  
   更新锁(U)：发生在更新语句中，更新锁用来查找数据，当查找的数据不是要更新的数据时转化为S锁，当是要更新的数据时转化为X锁  
   意向锁：发生在较低粒度级别的资源获取之前，表示对该资源下低粒度的资源添加对应的锁，意向锁有分为：意向共享锁(IS) ，意向排他锁(IX)，意向更新锁(IU)，共享意向排他锁(SIX)，共享意向更新锁(SIU)，更新意向排他锁(UIX)  
   共享锁／排他锁／更新锁一般作用在较低级别上，例如数据行或数据页，意向锁一般作用在较高的级别上，例如数据表或数据。锁是有层级结构的，若在数据行上持有排他锁的时候，则会在所在的数据页上持有意向排他锁. 在一个事务中，可能由于锁持有的时间太长或个数太多，出于节约资源的考虑，会造成锁升级  
   除了上述的锁之外，还有几个特殊类型的锁，例如架构锁，架构锁包含两种模式，架构稳定锁(Sch-S)和架构更新锁(Sch-M) ,架构稳定锁用来稳定架构，当查询表数据的时候，会对表添加架构稳定锁，防止架构发生改变。当执行DDL语句的时候，会使用架构更新锁，确保没有任何资源对表的占用。大数据量的表避免执行DDL操作，这样会造成架构更新锁长时间占用资源，影响其他操作,除非必要不然不要执行DDL语句，如在必要的情况下添加字段，需要先给字段初始化，在设置为非空。

### 锁的兼容性 ###

![1163931-20190521150759746-664567771.png][]

如何查看一个事务中所请求的锁类型和锁的顺序，可使用SQL Profiler 查看 Mode 属性

#### 数据准备 ####

IF OBJECT_ID('dbo.Nums','u') IS NOT NULL
        DROP TABLE dbo.Nums;
    GO
    CREATE TABLE dbo.Nums
    (
         ID INT PRIMARY KEY,
         NUM INT
    );
    GO
    IF EXISTS(SELECT * FROM SYS.SEQUENCES WHERE OBJECT_ID=OBJECT_ID('dbo.NumSequence'))
        DROP SEQUENCE dbo.NumSequence;
    GO
    CREATE SEQUENCE dbo.NumSequence
        MINVALUE 1
        MAXVALUE 1000
        NO CYCLE
    GO
    DECLARE @num AS INT = NEXT VALUE FOR dbo.NumSequence
    INSERT INTO dbo.Nums VALUES(@num,@num);
    GO 1

## 事务的隔离级别 ##

### 事务 ###

事务是一个工作单元，包含查询／修改数据以及修改数据定义的多个活动的组合，说起事务就需要提起事务的四个基本特性ACID：  
   原子性：事务要么全部成功，要么全部失败。  
   一致性：事务为提交前或者事务失败后，数据都和未开始事务之前一致  
   隔离性：事务与事务之间互不干扰  
   持久性：事务成功后会被永久保存起来，不会在被回滚

### 隔离级别 ###

事务的隔离级别控制并发用户的读取和写入的行为，即不同的隔离界别对锁的控制方式不一样，隔离级别主要分为两种类型：悲观并发控制和乐观并发控制，悲观并发控制有：READ UNCPOMMITTED ／ READ COMMITTED (会话默认) ／REPEATABLE READ ／ SERIALIZABLE . 乐观并发控制主要以在Tempdb中创建快照的方式来实现，有：SNAPSHOT 和 READ COMMITTED SHAPSHOT,也被称为基于行版本的控制的隔离级别。

#### READ UNCOMMITTED ####

这个就相当于在查询语句的后面加上with(nolock)，此隔离级别的主要特点是可以读取其他事务中未提交更改的数据（该语句 READ UNCOMMITTED是对整个批处理或SP不加锁，因此 后面所有的T-SQL查询语句都是不加S锁的），该隔离级别下请求查询的数据不需要共享锁（例如 tranA是一个delete TestTable操作，同时你查询TestTable的时候就需要先在TestTable的查询行上面加S锁，因此就要等待delete的操作结束，X锁释放掉，但是你使用 READ UNCOMMITTED之后，你在查询的时候就不加S锁了，所以就能访问放了X锁的语句），这样对于请求的行正在被更改，不会出现阻塞，这就造成了脏读.此隔离级别是最低的隔离级别，并发性良好，但是对于数据的一致性方面有缺陷，在一些不重要的查询中可以采用这种方式  
以上面的表为例，开始两个会话，在会话1中运行如下代码：

BEGIN TRAN
        UPDATE  dbo.Nums SET NUM = 10
        WHERE ID = 1

开启会话2并且运行如下代码：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    GO
    SELECT * FROM dbo.Nums WHERE ID = 1

查看运行结果

![1163931-20190521151418555-796428838.png][]

在事务未提交成情况下，却读取到了数据，这就是脏读。

另一种情形：

--会话1
    BEGIN TRAN
    UPDATE dbo.Nums SET NUM+=1 WHERE ID = 1  
    --会话2
    SELECT * FROM dbo.Nums WHERE ID=1

你会发现会话2一直在等待，因为会话1在 ID = 1 的语句上面放了X锁，并且处于事务中，并且事务没有commit导致 ID = 1 的行一直被X锁锁住，而导致会话2的S锁没办法放到ID = 1  的行上，所以一直在等待会话1事务的提交。，作为对比，看下面的语句：

--会话1
    BEGIN TRAN
    SELECT * FROM dbo.Nums WHERE ID=1
    --会话2
    UPDATE dbo.Nums SET NUM+=1 WHERE ID = 1

你会发现 会话2可以执行成功，疑问：会话1在ID=1的行上面放了S锁，并且会话1的事务也没有结束，会话2要更新就必须放X锁在ID=1的行上面，但是X锁是互斥锁，不可能放的上去啊？其实 读取操作一完成，资源上的共享锁（S 锁）就会被立即释放（除非将事务隔离级别设置为可重复读或更高级别，或者在事务持续时间内用锁定提示保留共享锁（S 锁），而X锁则是要等到事务提交之后才会释放），即使该读取操作是在一个事务中，并且该事务未完成。

#### READ COMMITTED ####

这个是系统默认的一种情况。同样的，这个语句作用的是整个批处理，即下面所有的TSQL语句都具备这一点。此隔离级别可以看作是对READ UMCOMMITTED 隔离级别的升级，解决带了脏读的问题，主要因为当你查询的时候需要先请求共享锁定，由于锁之间的兼容性，造成阻塞（当你查询的语句上面放了X锁的时候），但是该模式也会带来一个问题那就是不可重复读（不理解看下面的例子），在同一事务中的两个相同的查询 查出来的结果不一致，主要是因为该隔离级别对应共享锁并不会一直保持（也就是说两条连续的查询语句，每一条都会加锁，但是两条之间是没有锁的，因此update和delete语句会在两条之间执行，导致两条查询语句显示的结果不一样），在两条查询语句之间是没有锁存在的，这样其他事务就是更新数据  
以上面的表为例，开始两个会话，在会话1中运行如下代码：

BEGIN TRAN
        UPDATE  dbo.Nums SET NUM = 10
        WHERE ID = 1

在会话2中运行如下代码，该会话会被阻塞

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    GO
    SELECT * FROM dbo.Nums WHERE ID = 1

打开会话3运行如下语句，查看当前阻塞状态，连接信息，阻塞语句等其他信息

SELECT request_session_id,resource_type,resource_database_id,DB_NAME(resource_database_id) AS dbname,
    resource_associated_entity_id,request_mode,request_status FROM sys.dm_tran_locks

根据上面得到的session\_id ，可以用下面的语句查询更具体的信息：

SELECT session_id,most_recent_session_id,connect_time,last_read,last_write, most_recent_sql_handle 
    FROM sys.dm_exec_connections WHERE session_id IN (54,55)

可以具体的查看到执行语句，要想知道具体某个会话阻塞原因，即正在等待哪个会话的资源，运行如下语句

SELECT session_id,blocking_session_id,command,text,database_id,wait_type,wait_resource,wait_time 
    FROM sys.dm_exec_requests cross apply sys.dm_exec_sql_text(sql_handle)
    WHERE blocking_session_id > 0

会话 session_id 正在等待会话 blocking_session_id

下面我们来说说不可重复读的问题,新建会话1运行如下代码

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    GO
    BEGIN TRAN
        SELECT * FROM dbo.Nums WHERE ID=1　　--A语句
        WAITFOR DELAY '00:00:10'
        SELECT * FROM dbo.Nums WHERE ID=1　　--B语句

新建会话2并运行如下代码

BEGIN TRAN
        UPDATE dbo.Nums SET NUM+=1 WHERE ID = 1  
        COMMIT TRAN

查看会话1的运行结果如图，从图中可以看出两次读取出来的数据不一致，这就是不可重复读，意思就是：会话1的A语句能够正常执行得到结果集，然后释放S锁，然后会话2就开始了，由于ID = 1

![1163931-20190521155339356-397859336.png][]

#### REPEATABLE READ ####

此隔离级别可以看作的是READ COMMITTED 的升级，该模式可以解决READ COMMITTED 的不可重复读的问题，主要是因为该级别下对共享锁的占用时间较长，会一直持续到事务的结束（也就是说两条连续的查询语句，每一条都会加锁，并且两条之间也是有锁的，并且锁一直持续到事务结束）。但是该模式也会存在一个叫做幻读的缺陷，幻读指的是在查找一定范围内的数据时，其他事务对该范围的数据进行INSERT操作，导致再次执行相同的查询语句，查询的结果可能多或者是和第一句不一致，造成幻读的原因是因为被锁定的数据行是在第一次查询数据时确定的，对未来的数据并没有锁。此隔离级别不建议在更新频率较高的环境下使用，会造成性能不佳  
以上面的表为例，打开两个会话，在会话1中运行下面的代码：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
    GO
    BEGIN TRAN
        SELECT * FROM dbo.Nums WHERE ID=1
        WAITFOR DELAY '00:00:10'
        SELECT * FROM dbo.Nums WHERE ID=1
    COMMIT TRAN

打开会话2并且运行如下代码

BEGIN TRAN
        UPDATE dbo.Nums SET NUM+=1 WHERE ID = 1  
    COMMIT TRAN

结果如下：

![1163931-20190523080610241-1204214720.png][]

运行过程中可以发现UPDATE的DML会一直等待会话1中事务的提交，并不会造成不可重复读，下面来演示下幻读的问题，重新打开两个会话，在会话1中运行下面的代码：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
    GO
    BEGIN TRAN
        SELECT * FROM dbo.Nums 
        WAITFOR DELAY '00:00:10'
        SELECT * FROM dbo.Nums 
    COMMIT TRAN

打开会话2运行如下代码：

BEGIN TRAN
        INSERT INTO dbo.Nums VALUES(2,2)
    COMMIT TRAN

运行结果：

![1163931-20190523081047269-1337669210.png][]

由于会话2在会话1延时的10s内增加了一笔（不会被阻塞，因为insert不会放锁在表上面），导致两个相同的查询结果却不一致，这就是幻读，当然，这也是

REPEATABLE READ和READ UNCOMMITTED共同的缺点。

#### SERIALIZABLE ####

此隔离级别可以看作是 REPEADTABLE READ 的升级，解决了幻读的问题，因为该模式下不仅可以锁定第一次查询的数据行，还可以锁定未来满足条件的数据行，是一个区间锁的概念，该级别不会出现上述的问题，但是相对的代价就是一致性强牺牲了并发性  
以上表为例，修改会话1的隔离级别为 SERIALIZABLE，代码如下：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

从结果可以看到会话2一直在等待会话1的完成，关于锁的请求类型和顺序请打开SQL Profiler 自行查看.

分看两种情况，

第一种：查询中指定了主键id，那么就只是锁定了键值，会话2不需要等待，可以直接执行

--会话1
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRAN
        SELECT * FROM dbo.Nums where id=2
        WAITFOR DELAY '00:00:10'
        SELECT * FROM dbo.Nums where id=2
    COMMIT TRAN

--会话2
    INSERT INTO dbo.Nums VALUES(14,5)

查询的条件不是主键，那么锁定的就是整个表：

--会话1
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRAN
        SELECT * FROM dbo.Nums where num=2
        WAITFOR DELAY '00:00:10'
        SELECT * FROM dbo.Nums where num=2
    COMMIT TRAN

--会话2
    INSERT INTO dbo.Nums VALUES(12,3)

事实是会话2一直在等待会话1执行完之后才执行，但是会话2插入的也不在会话1的区域内

当然，如果会话2改成 delete和update语句情况和insert也是一样的

#### SNAPSHOT ####

当前隔离级别和接下来要介绍的隔离级别都是乐观并发控制的两种模式，又称行版本控制的隔离级别，在tempdb中存储事务未提交之前的数据行，使用基于行版本的控制隔离级别不会请求共享锁，对于查询数据的请求直接从快照读取，但是这种快照方式还是很消耗性能的，尤其是对于更新或删除操作,仍然会出现阻塞. SNAPSHOT级别对快照的读取是以事务为单位的，同一个事务中的读取操作都会读取同一快照，无论其他事务是否更新了快照。在 READ COMMITTED 的隔离级别下还是会从快照读取，但是其他模式就按照本身的控制方式进行控制,目标是源表，只有SNAPSHOT隔离级别可以检测冲突。  
要使用该隔离级别需要在数据库中打开任意会话执行如下代码：

ALTER DATABASE TEST  SET ALLOW_SNAPSHOT_ISOLATION ON

以上面的表为例，打开两个会话，在会话1中运行如下代码：

BEGIN TRAN
        UPDATE dbo.Nums set NUM +=1
        WHERE ID = 1

打开会话2并运行如下代码：

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
    GO
    BEGIN TRAN
        SELECT * FROM dbo.Nums  
        WHERE ID = 1

此时会话2并没有被阻塞，而是返回了之前的版本，结果如下：

![1163931-20190524142720139-1811000167.png][]

切换会会话1运行 COMMIT TRAN ,紧接着继续在会话2中在执行一遍相同的查询，执行结果如下

![1163931-20190524142727859-282442096.png][]

发现与上次的结果相同，但是会话1明明已经提交了，为什么还是原来的数据呢，这是因为该模式的特点，要是想读取新的数据需要，需要提交本次事务，继续在会话2中运行如下代码：

COMMIT TRAN
    BEGIN TRAN
        SELECT * FROM dbo.Nums  
        WHERE ID = 1
    COMMIT TRAN

结果如图所示：

![1163931-20190524142815310-1658281342.png][]

下面看一个冲突检测的例子  
重新打开两个会话，在会话1中运行如下代码：

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
    GO
    BEGIN TRAN
        SELECT * FROM dbo.Nums  
        WHERE ID = 1

打开会话2运行如下代码：

BEGIN TRAN 
        UPDATE dbo.Nums SET NUM =10000
        WHERE ID =1

回到会话1，继续运行如下代码：

UPDATE dbo.Nums SET NUM =100
        WHERE ID =1

此时会话1出现阻塞，可以通过执行如下语句：

从图中可以看出竞争的资源是源表的数据行，并不是快照的，这就说明对于UPDATE 或者是DELETE 最终的目标是源表，切换会话2 运行 COMMIT TRAN 发现会话1中出现了错误：

![1163931-20190524155532282-1820985904.png][]

READ COMMITTED SNAPSHOT 模式对于冲突检测这一案例结果是不支持，会话1中的更新操作会成功，读者可以自行实验。

#### READ COMMITTED SNAPSHOT ####

同SNAPSHOT很像，但对于快照的读取是以语句为单位的，同一个事务中的查询数据的语句每次都读取快照的最新版  
要使用该隔离级别需要在数据库中打开任意会话执行如下代码：

ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT ON

以上表为例，打开2个会话，在会话1运行如下代码：

BEGIN TRAN 
        UPDATE dbo.Nums SET NUM +=1
        WHERE ID =1

打开会话2，并运行如下代码：

BEGIN TRAN 
        SELECT * FROM dbo.Nums 
        WHERE ID =1

运行结果为：

![1163931-20190524155711431-87515553.png][]

是从快照中读取出来的，继续在会话1中运行 COMMIT TRAN ，之后在会话2中的当前事务中继续执行相同的查询，结果如下：

![1163931-20190524155738766-833883599.png][]

这就是之前所说的语句为单位的读取快照，在这里有一个很有趣的现象就是，在会话2中并未设置隔离级别，这是因为默认情况下的隔离级别为 READ COMMITTED 由于运行了如上语句修改数据库标记，故，会话的默认的隔离级别变成了 READ COMMITTED SNAPSHOT，当显示修改为其他隔离级别是，则会按照修改后的隔离级别运行。若修改会话2的隔离级别为 READ UNCOMMITTED 时，并不会进行快照查询，仍然出现了脏读。

对于解决脏读／不可重复读／幻读等问题，可以通过升级隔离级别的方式解决问题。

![1163931-20190524155824630-676367802.png][]

## 死锁 ##

说起锁的问题，那当然少不了谈起死锁这种现象，主要发生于两个或多个事务之间存在相互阻塞，造成死锁，在SQL Server 中会牺牲工作最少的事务，SQL Server 可以设置一个`DEADLOCK_PRIORITY`的会话选项设置事务的在发生死锁的情况下牺牲的顺序，值在-10～10之间，在发生死锁的情况下，会优先牺牲数值最低的事务，不管其做的工作有多么的重要，当存在平级的时候，将根据工作数量进行牺牲。  
下面来演示一个死锁的例子，以上面的表为例，并创建一个Nums副本表取名CopyNums,并添加(1,1)记录，打开两个会话，在会话1中执行如下代码：

SET DEADLOCK_PRIORITY 0
    BEGIN TRAN
        UPDATE dbo.Nums SET NUM=100
        WHERE ID = 1

打开会话2运行如下代码：

SET DEADLOCK_PRIORITY 1
    BEGIN TRAN
        UPDATE  dbo.CopyNums SET NUM = 100
        WHERE ID = 1

切换回会话1 继续运行如下代码：

SELECT * FROM dbo.CopyNums 
        WHERE ID = 1

此时会发生阻塞，等待排他锁(X)释放，切换会话2运行如下代码：

SELECT * FROM dbo.Nums
        WHERE ID = 1

此次也会发生阻塞，但是阻塞一会你就会发现，会话1终止了，并出现如下错误：  
![1163931-20190524160048710-400687389.png][]

为什么会终止的是会话1呢？可以发现在会话中我们设置了 DEADLOCK\_PRIORITY，会牺牲数值低的那个会话事务，查看SQL Profiler 可以发现，确实有死锁现象发生(为了清晰仅显示死锁)

![1163931-20190524160147937-794596000.png][]

那么既然死锁会发生，就要有对应的避免死锁的对策：  
   1. 事务时间越长，保持锁的时间就越长，造成死锁的可能性就越大，检查事务中是否放置了过多的不应该属于同一工作单元的逻辑，有的话请移除到，从而缩短事务的时间  
   2. 上述死锁发生的关键在于访问顺序的问题，将两个会话中的语句变成一个顺序(都先操作Nums 或者 CopyNums )，就没有了死锁现象，所以在没有逻辑的单元中，调换顺序也会减少死锁的发生  
   3. 考虑选择隔离级别，不同隔离级别对锁的控制方式不一样，例如：行版本控制就不会请求共享锁(S)

转载于:https://www.cnblogs.com/ziqiumeng/p/10900557.html

[Link 1]: https://www.cnblogs.com/retop/p/7073432.html
[1163931-20190521150759746-664567771.png]: /images/20211214/da43eeb1d70047b18da11a3e94de7a70.png
[1163931-20190521151418555-796428838.png]: /images/20211214/2a62921d2d344357bc31ff57965066b1.png
[1163931-20190521155339356-397859336.png]: /images/20211214/586a5b69bceb4209b2070c4c65c5c1a1.png
[1163931-20190523080610241-1204214720.png]: /images/20211214/395404da18084202bbc358b8b4b509dc.png
[1163931-20190523081047269-1337669210.png]: /images/20211214/c88fbb2ec7124e1f9fb9b01d9dd6574e.png
[1163931-20190524142720139-1811000167.png]: /images/20211214/34fe6256fe9f4882a91230fc1a95b27e.png
[1163931-20190524142727859-282442096.png]: /images/20211214/19dc163139ba4aa4ad066d0fc344815a.png
[1163931-20190524142815310-1658281342.png]: /images/20211214/a0b492d98a50409b98bb42bfab0ce0f7.png
[1163931-20190524155532282-1820985904.png]: /images/20211214/008ca003ef714e9282569ba20aa212bf.png
[1163931-20190524155711431-87515553.png]: /images/20211214/8518d1de263740439dda17c20d9f5dc5.png
[1163931-20190524155738766-833883599.png]: /images/20211214/31a5141a37124db98dd04df59a8799c3.png
[1163931-20190524155824630-676367802.png]: /images/20211214/66f041878ae94f59a186c25890621f9d.png
[1163931-20190524160048710-400687389.png]: /images/20211214/8c98a93712d34c54a6b9e2c1ecbec88d.png
[1163931-20190524160147937-794596000.png]: /images/20211214/6e77ea1d9fa14faaaf0632196639c53e.png