Sql Server 数据库事务与锁，同一事务更新又查询锁的变化，期望大家来解惑

r囧r小猫 2024-03-22 18:03 44阅读 0赞

我有一个People表，有三行数据：

![7e7353be17a7da283a287d42ee1f47a8.png][]

如果我们没详细了解数据库事务执行加锁的过程中，会不会有这样一个疑问：如下的这段 SQL 开启了事务，并且在事务中进行了更新和查询操作。

BEGIN TRAN 
    	update People set Name='张三' where id=1;
    	
    	select * from People where id=1;
    commit tran

我们知道sql server数据库的默认事务级别是READ COMMITTED（已提交的读取），我们再看一下已提交读事务隔离级别描述：

> 允许事务读取另一个事务以前读取（未修改）的数据，而不必等待第一个事务完成。 SQL Server数据库引擎将保留 (对所选数据) 获取的写入锁，直到事务结束，但读取锁将在执行 SELECT 操作后立即释放。 这是SQL Server数据库引擎默认级别。

那么我们在READ COMMITTED 隔离级别下更新People表数据库，按照这个逻辑在id=1的数据行上添加`排它锁（X锁）`并等到事务提交后才会释放锁。  
但是事务继续执行查询，在READ COMMITTED隔离级别下 Select 会对查询数据施加`共享锁（S锁）`。因为有排它锁，所以查询无法获得共享锁需要等待排它锁释放，如果按照这个逻辑的话这个事务自身就死锁无法执行了。

但这个事务还是会正常执行完成，针对这个疑问，那么我们看下数据库的事务和锁：

### 数据库引擎隔离级别 ###

ISO 标准定义了以下隔离级别，SQL Server数据库引擎支持所有这些隔离级别：

<table> 
 <thead> 
  <tr> 
   <th>隔离级别</th> 
   <th>定义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>未提交的读取</td> 
   <td>隔离事务的最低级别，只能保证不读取物理上损坏的数据。 在此级别上，允许脏读，因此一个事务可能看见其他事务所做的尚未提交的更改。</td> 
  </tr> 
  <tr> 
   <td>已提交的读取</td> 
   <td>允许事务读取另一个事务以前读取（未修改）的数据，而不必等待第一个事务完成。 SQL Server数据库引擎将保留 (对所选数据) 获取的写入锁，直到事务结束，但读取锁将在执行 SELECT 操作后立即释放。 这是SQL Server数据库引擎默认级别。</td> 
  </tr> 
  <tr> 
   <td>可重复的读取</td> 
   <td>SQL Server数据库引擎会保留对所选数据获取的读取和写入锁定，直到事务结束。 但是，因为不管理范围锁，可能发生虚拟读取。</td> 
  </tr> 
  <tr> 
   <td>可序列化</td> 
   <td>隔离事务的最高级别，事务之间完全隔离。 SQL Server数据库引擎保留对所选数据获取的读取和写入锁定，这些锁将在事务结束时释放。 SELECT 操作使用分范围的 WHERE 子句时获取范围锁，主要为了避免虚拟读取。 注意： 请求可序列化隔离级别时，复制的表上的 DDL 操作和事务可能失败。 这是因为复制查询使用的提示可能与可序列化隔离级别不兼容。</td> 
  </tr> 
 </tbody> 
</table>

![4d1755400cfd6d9036659dfb42268d68.png][]

SQL Server数据库引擎使用不同的锁模式锁定资源，这些模式确定并发事务如何访问资源。

T-SQL 设置事务隔离级别，只对当前会话连接一直有效

SET TRANSACTION ISOLATION LEVEL
        { READ UNCOMMITTED
        | READ COMMITTED
        | REPEATABLE READ
        | SNAPSHOT
        | SERIALIZABLE
        }

### 锁模式 ###

下表显示了SQL Server数据库引擎使用的资源锁模式。

<table> 
 <thead> 
  <tr> 
   <th>锁模式</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>共享 (S)</td> 
   <td>用于不更改或不更新数据的读取操作，如 SELECT 语句。</td> 
  </tr> 
  <tr> 
   <td>更新 (U)</td> 
   <td>用于可更新的资源中。 防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。</td> 
  </tr> 
  <tr> 
   <td>排他 (X)</td> 
   <td>用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。 确保不会同时对同一资源进行多重更新。</td> 
  </tr> 
  <tr> 
   <td>意向</td> 
   <td>用于建立锁的层次结构。 意向锁包含三种类型：意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。</td> 
  </tr> 
  <tr> 
   <td>架构</td> 
   <td>在执行依赖于表架构的操作时使用。 架构锁包含两种类型：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。</td> 
  </tr> 
  <tr> 
   <td>大容量更新 (BU)</td> 
   <td>在将数据大容量复制到表中且指定了 TABLOCK 提示时使用。</td> 
  </tr> 
  <tr> 
   <td>键范围</td> 
   <td>当使用可序列化事务隔离级别时保护查询读取的行的范围。 确保再次运行查询时其他事务无法插入符合可序列化事务的查询的行。</td> 
  </tr> 
 </tbody> 
</table>

### 锁兼容性 ###

锁兼容性控制多个事务能否同时获取同一资源上的锁。 如果资源已被另一事务锁定，则仅当请求锁的模式与现有锁的模式相兼容时，才会授予新的锁请求。 如果请求锁的模式与现有锁的模式不兼容，则请求新锁的事务将等待释放现有锁或等待锁超时间隔过期。 例如，没有与排他锁兼容的锁模式。 如果具有排他锁（X 锁），则在释放排他锁（X 锁）之前，其他事务均无法获取该资源的任何类型（共享、更新或排他）的锁。 另一种情况是，如果共享锁（S 锁）已应用到资源，则即使第一个事务尚未完成，其他事务也可以获取该项的共享锁或更新锁（U 锁）。 但是，在释放共享锁之前，其他事务无法获取排他锁。

下表显示了最常见的锁模式的兼容性。

![fcb73eb8a378027db3cfaba717dcfa44.png][]

### 查看执行时锁的情况 ###

通过锁的兼容性模式我们知道在id=1的行上添加了排它锁，那么它就无法再接收任何锁，那我们调试这个事务看看锁的情况。

![5fe5b639f7e83731cf5e914eeee0677d.png][]

我们调试到第3行，这个时候看下锁的情况，此时事务添加了key(行）排它锁`X锁`，page（页）和object（表）添加了意向排它锁`IX锁`。

SELECT 
    resource_type,
    resource_database_id,
    resource_description,
    request_mode,
    request_type,
    request_session_id,
    request_owner_type,
    request_owner_id,
    lock_owner_address
    FROM sys.dm_tran_locks where request_owner_type='transaction'

![6e17e183427be68c33d9c305166845e8.png][]

然后我们再继续调试到第4行，此时还没提交事务，排它锁X依然存在，但是没有S锁。

![4b345bf45c1c14af6e5a0ca6b98c0fad.png][]

我们知道在读提交事务隔离级别下，S锁是使用完了就释放的，所以我们用SQL Server Profiler来监视下锁的情况，设置监控的项为lock，然后设置筛选条件。

![d0a5d07b24793a3e2864d48472ac7793.png][]

上面我已经将 *张三1* 改为了 *张三*，我们再将 *张三* 改回 *张三1*，并启动监控。

BEGIN TRAN 
    	update People set Name='张三1' where id=1;
    	select * from People where id=1;
    commit tran

![e1a8c66fff294423033926280b83801f.png][]

**可以看到事务 transactionid=30010685 的锁监控 :**

*  首先申请IX更新意向锁(object,page) 准备更新，然后获得行上的X排它锁进行更新，更新后释放了行锁和page锁（EventClass= Lock:released,Mode=0-null）。
 *  等查询时申请page页IS意向读取锁，并获得行S锁读取数据后释放行锁和page页锁。
 *  最后还有几个顺序释放，依次是key、page、Object，这里恰好和上面调试还没提交事务时查询sys.dm\_tran\_locks的锁情况一样，也就是说事务提交后依次又进行了一遍释放。

**通过上面我们得出结论，事务里面并不是取得了X锁要等事务结束后才释放，在事务执行过程中也是有释放的，只是事务还保持着对于锁在事务层面的记录，防止其它事务并发（这里是我推断的，没找到相关文献说明）。  
所以事务是在锁上更宏观的逻辑隔离，事务隔离级别只是在业务上保证数据符合隔离级别预期，至于事务中如何控制锁是基于数据库内在设计，而不能通过事务的描述去推断锁过程。**

我查阅网上很多博文和官方资料都是讲事务和锁概念，有时候结合两种也是模棱两可看不出什么强联系，没有讲事务执行过程中锁是如何变化的，不知道我这篇推论是否正确，欢迎指正。

### 再次验证 ###

我将事务隔离级别设置为REPEATABLE READ（可重复读），然后调试到commit行还没提交，我们看跟踪的锁和事务锁表dm\_tran\_locks查询的结果，按照REPEATABLE READ描述，select查询的S锁会在事务提交后释放，我们看看截图情况

![394c05c31fcfc57b30d11da38f6e4aab.png][]

开启了SQL Server Profiler结果，查询id=3后S锁已经释放。

![e18755feb1ec994a5540defc46de9ee6.png][]

再查dm\_tran\_locks表，表中依然显示事务获取了S锁，并且 resource\_description=98ec012aa510 资源描述和上面跟踪是对应的。

![6d23dfe39735f6a1625e30b0cae11468.png][]

最后我们执行完调试，跟踪锁显示又按照顺序释放了一遍锁

![eedb61a8844fb9362397bb64dd9849c1.png][]

[7e7353be17a7da283a287d42ee1f47a8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/35b1e50e631f44bf94a42a29ea36445b.png
[4d1755400cfd6d9036659dfb42268d68.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/ee5f4501e0df4251998b10a9d105eebe.png
[fcb73eb8a378027db3cfaba717dcfa44.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/aa1d86fedfc942099afa817b011a3683.png
[5fe5b639f7e83731cf5e914eeee0677d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/d2565b5c8196453eac7d01925cfc2be4.png
[6e17e183427be68c33d9c305166845e8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/696cb67b6e7d496f985ea5124ea7398b.png
[4b345bf45c1c14af6e5a0ca6b98c0fad.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/0be909f7003943b0805301912166c0a2.png
[d0a5d07b24793a3e2864d48472ac7793.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/b23bb615685747b888777f84762128ea.png
[e1a8c66fff294423033926280b83801f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/d919fbf528334e6cb3312364aa0c160a.png
[394c05c31fcfc57b30d11da38f6e4aab.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/28b6b43c359a458ca0bf96dca77287ea.png
[e18755feb1ec994a5540defc46de9ee6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/315abf88ee844151bc9d4c92b5c42277.png
[6d23dfe39735f6a1625e30b0cae11468.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/bf2e300d835d45da9bd8ba76e60eabc3.png
[eedb61a8844fb9362397bb64dd9849c1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/cdd27bd4c38f41538c8c78eae3b9698a.png