SQL Server表被锁定：排查与处理-猿码集

1. 前言

在使用SQL Server进行开发和运维的过程中，很可能会遇到表被锁定的情况。这种情况将导致许多问题，例如资源瓶颈、应用程序无响应等等。在这篇文章中，我们将详细介绍如何排查和处理SQL Server表被锁定的情况。

2. 锁定类型

在了解如何排查和处理锁定之前，我们需要了解不同类型的锁定。

2.1 共享锁定

共享锁定是一种读锁定，通过这种锁定，可以允许多个进程同时读取某个资源，而不会发生冲突。当一个进程获得一个共享锁定时，其他进程也可以获得共享锁定，但是如果一个进程获得了一个独占锁定，则其他进程必须等待该进程释放锁定。

2.2 独占锁定

独占锁定是一种写锁定，通过这种锁定，可以保证只有一个进程可以修改资源，其他进程必须等待该进程释放锁定。

2.3 行锁定

行锁定是一种在行级别上进行锁定的机制，它仅在对行进行读取和编辑时才锁定该行。行锁定在访问数据时非常常见，因为它通常对系统的性能影响最小。

2.4 表锁定

表锁定是一种在表级别上进行锁定的机制，它会锁定整个表，无论对表进行读取还是编辑都会受到影响。当多个进程试图同时访问同一个表时，会出现表锁定的情况。

3. 排查SQL Server表被锁定的原因

通常情况下，当一个表被锁定时，我们需要查找以下几个方面，以确定锁定的原因：

3.1 查询是否正在执行

首先，我们需要确定是否有查询正在执行，导致表被锁定。我们可以使用以下查询语句查询当前正在执行的查询：


SELECT r.session_id, r.blocking_session_id, db_name(r.database_id) AS dbname, 
t.resource_type, t.resource_associated_entity_id, 
r.start_time, r.status, r.command, r.cpu_time, r.total_elapsed_time / 1000 AS seconds_running,
(SELECT text FROM sys.dm_exec_sql_text(r.sql_handle)) AS sql_text
FROM sys.dm_exec_requests r 
INNER JOIN sys.dm_tran_locks AS t ON t.lock_owner_address = r.lock_owner_address
WHERE r.command != 'AWAITING COMMAND' AND r.status NOT IN ('background', 'sleeping')
ORDER BY r.start_time ASC;

此查询语句可以返回当前正在执行的查询，并列出了会话的相关详细信息，例如会话ID、数据库名、开始时间、查询的命令等等。

3.2 死锁

死锁是指两个或更多的进程都在等待对方释放锁定资源，导致它们都无法继续执行。在这种情况下，我们需要确定哪些进程参与了死锁。可以使用以下查询语句查询所有参与死锁的进程：


SELECT 
    tl.resource_type,
    tl.resource_database_id,
    tl.resource_associated_entity_id,
    tl.request_mode,
    tl.request_session_id,   
    wt.blocking_session_id,
    wt.wait_duration_ms,    
    es.[host_name],
    es.[program_name],
    es.[login_name],
    st.[text]
FROM 
    sys.dm_tran_locks as tl
    INNER JOIN sys.dm_os_waiting_tasks as wt
        ON tl.lock_owner_address = wt.resource_address
    INNER JOIN sys.dm_exec_sessions es
        ON es.session_id = tl.request_session_id
    CROSS APPLY sys.dm_exec_sql_text(wt.sql_handle) as st
WHERE 
    tl.deadlock_priority = 0
    AND es.is_user_process = 1;

此查询语句可以返回当前死锁的详细信息，包括会话ID、资源类型、等待时间、锁定资源等等。

4. 处理被锁定的表

一旦确定了造成表锁定的原因，我们可以采取以下措施解锁表：

4.1 杀掉被阻塞的进程

如果查询正在被一个或多个进程阻塞，则可以尝试杀死阻塞进程来解锁表。可以使用以下命令杀死进程：


KILL {SESSION ID}

其中{SESSION ID}是被杀死进程的会话ID。

4.2 使用WITH(NOLOCK)选项

如果我们需要执行一个只读操作，可以使用WITH(NOLOCK)选项，以避免锁定表。例如：


SELECT * FROM myTable WITH(NOLOCK)

然而，该选项可能导致不可重复读、脏读、幻读等问题，所以应该权衡使用。

4.3 修改锁定级别

我们还可以修改锁定级别，以避免表被锁定。可以在查询的开头使用以下命令：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

该命令将把锁定级别设置为“未提交读”级别，而不是默认的“可重读”级别。

5. 结论

本文中，我们讨论了如何排查和处理SQL Server表被锁定的情况。了解不同类型的锁定，可以帮助我们识别问题，并采取适当的措施解锁表。如果不幸遇到表被锁定的情况，请按照本文中的步骤进行排查。

SQL Server表被锁定：排查与处理