sql server查看锁表及解锁方法(示例详解)

一、查看锁表信息

1. 使用系统视图查看锁信息

-- 查看当前所有锁
SELECT 
    t.request_session_id AS [会话ID],
    t.resource_type AS [资源类型],
    t.request_mode AS [锁模式],
    t.request_status AS [请求状态],
    DB_NAME(resource_database_id) AS [数据库名],
    CASE 
        WHEN resource_type = 'OBJECT' 
            THEN OBJECT_NAME(resource_associated_entity_id)
        WHEN resource_type IN ('KEY', 'PAGE', 'RID') 
            THEN OBJECT_NAME(p.object_id)
        ELSE 'N/A'
    END AS [对象名],
    resource_description AS [资源描述],
    wt.blocking_session_id AS [阻塞会话ID],
    st.text AS [SQL语句],
    qp.query_plan AS [执行计划]
FROM sys.dm_tran_locks t
LEFT JOIN sys.partitions p ON p.hobt_id = t.resource_associated_entity_id
LEFT JOIN sys.dm_os_waiting_tasks wt ON t.request_session_id = wt.session_id
LEFT JOIN sys.dm_exec_requests r ON t.request_session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE t.resource_database_id = DB_ID()
ORDER BY t.request_session_id;

2. 查看阻塞链

-- 查看阻塞链（哪些会话阻塞了其他会话）
SELECT 
    blocking.session_id AS [阻塞会话ID],
    blocked.session_id AS [被阻塞会话ID],
    wait.wait_type AS [等待类型],
    wait.wait_duration_ms AS [等待时长(ms)],
    blocked_text.text AS [被阻塞SQL],
    blocking_text.text AS [阻塞SQL]
FROM sys.dm_exec_connections AS blocking
INNER JOIN sys.dm_exec_requests AS blocked
    ON blocking.session_id = blocked.blocking_session_id
INNER JOIN sys.dm_os_waiting_tasks AS wait
    ON blocked.session_id = wait.session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) AS blocked_text
OUTER APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) AS blocking_text;

3. 查看特定表的锁信息

-- 查看特定表的锁信息
SELECT 
    l.request_session_id AS [会话ID],
    l.resource_type AS [资源类型],
    l.request_mode AS [锁模式],
    l.request_status AS [状态],
    OBJECT_NAME(p.object_id) AS [表名],
    st.text AS [SQL语句]
FROM sys.dm_tran_locks l
LEFT JOIN sys.partitions p ON p.hobt_id = l.resource_associated_entity_id
LEFT JOIN sys.dm_exec_requests r ON l.request_session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE l.resource_type = 'OBJECT' 
    AND OBJECT_NAME(p.object_id) = 'YourTableName'
ORDER BY l.request_session_id;

二、常用的锁查看脚本

快速查看锁概况

-- 查看锁的概况
SELECT 
    COUNT(*) AS [锁总数],
    resource_type AS [资源类型],
    request_mode AS [锁模式],
    request_status AS [状态]
FROM sys.dm_tran_locks
GROUP BY resource_type, request_mode, request_status
ORDER BY resource_type, request_mode;

查看长时间运行的会话

-- 查看运行时间超过30秒的会话
SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    r.status,
    r.command,
    r.start_time,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    t.text AS [SQL语句]
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status IS NOT NULL
    AND DATEDIFF(SECOND, r.start_time, GETDATE()) > 30
ORDER BY r.start_time;

三、解锁方法

1. KILL 会话（强制终止）

-- 查看会话信息
SELECT 
    session_id,
    login_name,
    host_name,
    program_name,
    status
FROM sys.dm_exec_sessions
WHERE session_id = <会话ID>;

-- 终止特定会话
KILL <会话ID>;

-- 终止所有阻塞会话
DECLARE @session_id INT;
DECLARE session_cursor CURSOR FOR
SELECT DISTINCT blocking_session_id
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;

OPEN session_cursor;
FETCH NEXT FROM session_cursor INTO @session_id;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Killing session: ' + CAST(@session_id AS VARCHAR(10));
    EXEC('KILL ' + @session_id);
    FETCH NEXT FROM session_cursor INTO @session_id;
END

CLOSE session_cursor;
DEALLOCATE session_cursor;

2. 设置事务超时

-- 设置查询超时（在连接字符串中设置）
-- Connection Timeout=30;  -- 连接超时30秒

-- 在查询中设置锁超时
SET LOCK_TIMEOUT 5000;  -- 设置锁超时为5秒（5000毫秒）
-- 执行查询...

3. 使用 NOLOCK 提示（慎用）

-- 在查询中使用 NOLOCK 提示
SELECT * FROM YourTable WITH (NOLOCK) WHERE ...

-- 但要注意：NOLOCK 可能导致脏读，不适合财务等关键数据

4. 使用 READ UNCOMMITTED 隔离级别

-- 设置会话隔离级别
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 执行查询...
-- 恢复默认隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

四、预防锁表的方法

1. 优化查询和事务

-- 1. 保持事务简短
BEGIN TRANSACTION
-- 尽量减少事务中的操作
COMMIT TRANSACTION

-- 2. 按相同顺序访问表
-- 3. 使用合适的索引
-- 4. 避免长时间运行的操作

2. 使用行版本控制

-- 启用快照隔离级别（需要先启用数据库选项）
ALTER DATABASE YourDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON;

-- 使用快照隔离
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

3. 监控脚本

-- 创建锁监控存储过程
CREATE PROCEDURE sp_MonitorLocks
AS
BEGIN
    SELECT 
        GETDATE() AS CheckTime,
        COUNT(*) AS LockCount,
        SUM(CASE WHEN request_status = 'WAIT' THEN 1 ELSE 0 END) AS WaitingLocks
    FROM sys.dm_tran_locks;

    SELECT 
        session_id,
        blocking_session_id,
        wait_type,
        wait_time,
        DB_NAME(database_id) AS DatabaseName,
        SUBSTRING(text, 
            CASE 
                WHEN statement_start_offset = 0 OR statement_start_offset IS NULL 
                THEN 1 
                ELSE statement_start_offset/2 + 1 
            END,
            CASE 
                WHEN statement_end_offset = 0 OR statement_end_offset IS NULL 
                THEN LEN(text) 
                ELSE statement_end_offset/2 - statement_start_offset/2 + 1 
            END
        ) AS SQL_Text
    FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
    WHERE blocking_session_id > 0;
END

五、实用工具和扩展

1. SQL Server Profiler

使用 SQL Server Profiler 跟踪锁事件
事件类：Locks: Lock:Acquired, Locks: Lock:Released

2. 动态管理视图(DMV)汇总

-- 汇总锁信息
SELECT 
    DB_NAME(database_id) AS DatabaseName,
    resource_type,
    request_mode,
    COUNT(*) AS LockCount,
    SUM(CASE WHEN request_status = 'WAIT' THEN 1 ELSE 0 END) AS WaitingCount
FROM sys.dm_tran_locks
GROUP BY database_id, resource_type, request_mode
ORDER BY WaitingCount DESC;