如何在数据库中实现解锁查询

使用 master;GOIF @@TRANCOUNT > 0ROLLBACK TRAN;GO-- ================== ====================-- 创建测试数据库-- a.删除测试数据库（如果已存在） IF DB_ID(N'db_xlock_test') IS NOT NULLBEGIN; ALTER DATABASE db_xlock_testSET SINGLE_USERWITHROLLBACK AFTER 0;DROP DATABASE db_xlock_test;END;-- b．创建测试数据库 CREATE DATABASE db_xlock_test;-- c．关闭 READ_COMMITTED_SNAPSHOT 以保持 SELECT ALTER DATABASE db_xlock_testSET READ_COMMIT TED_SNAPSHOT OFF;GO-- === ============================ 的默认锁定模式==========-- 创建测试表 USE db_xlock_test;GOCREATE TABLE dbo.tb( id int IDENTITYPRIMARY KEY,name sysname);INSERT dbo.tbSELECT TOP(50000)O1.name + N'.' + O2.名称 +N'。' + O3.nameFROM sys.objects O1WITH(NOLOCK),sys.objectsO2WITH(NOLOCK),sys.objectsO3WITH(NOLOCK);GO

然后，建立连接并执行以下脚本来实现锁定。

-- ======================== ================-- 测试连接1 -Locking BEGIN TRAN--测试的初衷是通过SELECT来加锁。原来 UPDATE 无法锁定 UPDATE dbo.tb SET name = name--SELECT COUNT(*) FROM dbo.tb WITH(XLOCK)WHERE id <= 2 ;SELECTspid = @@SPID,tran_count = @@TRANCOUNT,database_name = DB_NAME(),object_id = OBJECT_ID(N'dbo.tb', N'Table');--显示锁 EXEC sp_lock@@SPID;

通过执行结果，可以看到对象被锁定：IX在表级和页级锁定，在记录级锁定。 X 锁定。

-- ======= ===== ==========================-- 测试连接 2 - 被阻止（在执行测试连接 1 后执行） SET TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT * FROM dbo。 tbWHERE id <= 2;

上面的查询会很快返回结果，不会被查询 1阻止它。

根据我们的理解（在线帮助中也有说明），在READ COMMITTED 事务隔离级别下，查询使用共享锁（S），并根据兼容级别S span>S锁与 < /span>X加锁，所以正常情况下，查询连接到2 需要等待连接1执行完成。然而测试结果却违反了这一规定原则。

了解为什么连接2不会被阻止。我对连接2进行了跟踪，发现了一个更令人沮丧的问题。 追踪结果如下：

6 - IS

Trace 前两行是Trace的结果 连接 2。从结果来看，Connection2仅使用了意向共享锁（IS），并且仅在表级别且页面级别。根据锁兼容性原则，IS和IX（连接1 仅在表级和页级使用IX锁）并且不存在冲突，因此连接2查询不会被阻止。加大查询数据量后，Trace结果显示查询仍然只用在表级和页级IS锁定（跟踪结果的最后 4 行）。

对于这个问题，解决办法当然是加大连接的粒度1< span> lock ，使用PAGLOCK表提示将锁粒度增加到页级别，使得IS <如果span>X冲突，则可以成功阻塞连接2。

但问题是为什么查询只打算在表级和页级共享锁( IS)而不是在行级别共享锁（X），这似乎与中的说明不同在线帮助（这还是理解上的偏差）。

附件：在线帮助中锁定模式说明

共享锁

共享锁（S Lock）允许并发事务在封闭并发控制下进行读取 (SELECT) 资源<跨度>。

更新锁定

更新锁（U 锁）可以防止常见的死锁。 在可重复读或可序列化事务中，此事务读取数据 [获取共享锁（S 锁定）]，以及然后修改数据 [该操作需要将锁转换为排他锁(X lock)< /span>]。 如果两个事务获取资源上的共享模式锁，然后尝试同时更新数据，则一个事务会尝试将该锁转换为独占锁 (X <跨度>锁定）。 从共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其他事务的共享模式锁不兼容；发生锁等待。 第二个事务尝试获取更新的独占锁（X 锁）。 由于两个事务都转换为排他锁（X 锁），并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。

要避免这种潜在的死锁问题，请使用更新锁（U 锁）。 一次只有一个事务可以获得资源的更新锁（U 锁）。 如果事务修改了资源，则更新锁（U 锁）会转换为排他锁（X 锁）。

独占锁

排他锁（X 锁）可以防止并发事务访问资源。 使用排它锁（X 锁）时，其他事务都不能修改数据；仅当使用 NOLOCK 提示或未提交读隔离级别时才会执行读操作。

数据修改语句（如 INSERT、UPDATE 和 DELETE）合并修改和读取操作。 语句首先执行读取操作来获取数据，然后再执行所需的修改操作。 因此，数据修改语句通常会请求共享锁和排它锁。 例如，UPDATE 语句可能会根据与另一个表的联接来修改一个表中的行。 在这种情况下，除了请求更新行上的独占锁外，UPDATE语句还将请求连接表中读取的行上的共享锁。

意图锁

数据库引擎使用意向锁来保护共享锁（S 锁）或排它锁（X 锁）放置在锁层次结构的底层资源上。 意向锁的命名意向锁，因为它们可以在较低级别的锁之前获取，从而通知将锁放在较低级别的意图。

感谢您仔细阅读本文。希望小编分享的如何实现可解锁查询内容对大家有所帮助。也希望感谢您的支持，关注行业信息频道，遇到问题及时查找。详细解决方案等你学习！