介绍阻塞和锁定

12 分钟

锁定是关系数据库的关键功能，对于维护 ACID 模型的原子性、一致性和隔离属性至关重要。所有 RDBMS 都会阻止违反数据库写入的一致性和隔离的操作。 SQL 程序员必须在正确的点启动和结束事务，以确保数据一致性。数据库引擎提供锁定机制来保护受影响表的逻辑一致性，这是关系模型的基础。

在 SQL Server 中，当一个进程持有特定资源（行、页、表、数据库）上的锁时，将发生阻塞，第二个进程尝试获取同一资源上具有不兼容锁类型的锁。通常，锁只保持短暂的时间，一旦持有锁的进程释放锁，被阻塞的进程就可以获取锁并完成其事务。

SQL Server 锁定完成事务所需的最小数据量，从而允许最大并发性。例如，如果 SQL Server 锁定单个行，则表中的所有其他行仍可用于其他进程，从而启用并发工作。但是，每个锁都需要内存资源，因此一个进程在单个表上保存数千个单独的锁并不经济高效。为了平衡并发与成本，SQL Server 使用一种称为锁升级的技术。如果单个对象上超过 5,000 行需要锁定在单个语句中，SQL Server 会将多行锁升级为单个表锁。

锁定是一种正常的现象，白天经常会发生。仅当它导致无法快速解决的阻塞时，它才会变得有问题。有两种类型的性能问题是由阻止引起的：

进程会在释放资源之前长时间锁定一组资源，从而导致其他进程被阻塞，降低查询性能和并发性。
进程获取了一组资源的锁，并永远不会释放它们，需要管理员进行干预才能解决。

死锁是另一种阻塞方案，当一个事务持有资源上的锁，且另一个事务持有另一个资源上的锁时发生。然后，每个事务会尝试获取当前被另一个事务锁定的资源上的锁，从而导致无限等待，因为两个事务都无法完成。 SQL Server 引擎检测到这些死锁场景，并通过终止其中一个事务来解决死锁，其依据是哪个事务执行了需回滚的工作量最少。终止的事务称为死锁牺牲品。死锁记录在 system_health 扩展事件会话中，该会话默认处于启用状态。

了解事务的概念非常重要。 Autocommit 是 SQL Server 和 Azure SQL 数据库的默认模式，这意味着以下语句所做的更改将自动记录在数据库的事务日志中。

INSERT INTO DemoTable (A) VALUES (1);

为了使开发人员能够更精细地控制其应用程序代码，SQL Server 还允许显式地控制你的事务。以下查询将对 DemoTable 表中的某个行采用锁，在添加用于提交事务的后续命令后才会释放该锁。

BEGIN TRANSACTION

INSERT INTO DemoTable (A) VALUES (1);

编写以下查询的正确方法如下：

BEGIN TRANSACTION

INSERT INTO DemoTable (A) VALUES (1);

COMMIT TRANSACTION

COMMIT TRANSACTION 命令显式提交对事务日志的更改记录。更改的数据最终将以异步方式进入数据文件。这些事务表示数据库引擎的工作单元。如果开发人员忘记发出 COMMIT TRANSACTION 命令，事务将保持打开状态，并且不会释放锁。这是造成事务长时间运行的主要原因之一。

数据库引擎用来帮助数据库的并发的另一种机制是行版本控制。将行版本控制隔离级别启用到数据库时，引擎会维护 TempDB 中每个修改行的版本。这通常用于混合使用的工作负载，目的是防止读取查询阻止正在写入数据库的查询。

若要监视等待 commit 或 rollback 的未结事务，请运行以下查询：

SELECT tst.session_id, [database_name] = db_name(s.database_id)
    , tat.transaction_begin_time
    , transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
    , transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
        WHEN 2 THEN 'Read-only transaction'
        WHEN 3 THEN 'System transaction'
        WHEN 4 THEN 'Distributed transaction' END
    , input_buffer = ib.event_info, tat.transaction_uow     
    , transaction_state  = CASE tat.transaction_state    
        WHEN 0 THEN 'The transaction has not been completely initialized yet.'
        WHEN 1 THEN 'The transaction has been initialized but has not started.'
        WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
        WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
        WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
        WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
        WHEN 6 THEN 'The transaction has been committed.'
        WHEN 7 THEN 'The transaction is being rolled back.'
        WHEN 8 THEN 'The transaction has been rolled back.' END 
    , transaction_name = tat.name, request_status = r.status
    , tst.is_user_transaction, tst.is_local
    , session_open_transaction_count = tst.open_transaction_count  
    , s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib
ORDER BY tat.transaction_begin_time DESC;

隔离级别

SQL Server 提供多个隔离级别，用于定义数据所需的一致性和正确性级别。通过隔离级别，可以在并发性和一致性之间找到平衡。隔离级别不会影响为防止数据修改而采取的锁。事务将始终获取对正在修改的数据的独占锁。但是，隔离级别可能会影响锁定的时间长度。较低的隔离级别会增加多个用户进程同时访问数据的能力，但会增加可能发生的数据一致性风险。 SQL Server 中的隔离级别如下所示：

读取未提交 - 这是可用的最低隔离级别。允许脏读，这意味着一个事务可能会看到另一个事务做出的尚未提交的更改。
读取已提交 - 允许一个事务读取之前读取的、但未由另一个事务修改的数据，而无需等待第一个事务完成。此级别还会在所选操作执行完成后释放读取锁定。这是默认的 SQL Server 级别。
可重复读 – 该级别将对所选数据获取的读写锁定一直保留到事务结束。
可序列化 – 这是事务之间完全隔离的最高级别。读取和写入锁定在所选数据上获取，直到事务结束后才会释放。

SQL Server 还包括两个隔离级别，其中包括行版本控制。

读取已提交的快照– 在此级别的读取操作中，不需要使用行或页锁，并且引擎会为每个操作呈现查询开始时存在的数据的一致快照。当用户在 OLTP 数据库上运行频繁报告查询时，通常会使用此级别，以防止读操作阻碍写操作。
快照 - 此级别通过行版本控制提供事务级读取一致性。此级别容易出现更新冲突。如果在此级别下运行的事务读取另一个事务修改的数据，则会终止并回滚快照事务进行的更新。读取已提交的快照隔离不存在此问题。

使用 T-SQL SET 命令为每个会话设置隔离级别，如下所示：

SET TRANSACTION ISOLATION LEVEL

 { READ UNCOMMITTED

 | READ COMMITTED

 | REPEATABLE READ

 | SNAPSHOT

 | SERIALIZABLE

 }

无法为所有在数据库中运行的查询或所有由特定用户执行的查询设置全局隔离级别。它是会话级别设置。

监视阻止问题

识别阻塞问题可能是由于其零星性质而具有挑战性的。 DMV sys.dm_tran_locks 与 sys.dm_exec_requests 联接时，提供有关每个会话持有的锁的信息。监视阻塞问题的更有效方法是持续使用扩展事件引擎。

阻止问题通常分为两类：

事务设计不佳：例如，缺少 COMMIT TRANSACTION 的事务将永远无法结束。尝试在单个事务中执行太多工作或使用链接服务器连接进行分布式事务可能会导致不可预知的性能。
架构设计导致的长时间运行的事务：这通常涉及对缺少索引或设计不佳的更新查询的列进行更新。

监视与锁定相关的性能问题，可以快速识别与锁定相关的性能下降。

有关如何监视阻塞的详细信息，请参阅了解和解决 SQL Server 阻塞问题。

反馈

此页面是否有帮助？