事务、锁与并发控制

5.1 事务基础与 ACID 属性

5.1.1 ACID 特性详解

5.1.2 事务类型与控制语句

-- ① 显式事务（最常用，推荐在应用层使用）
BEGIN TRANSACTION;   -- 或 BEGIN TRAN

    -- 事务内的操作
    UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountID = 101;
    UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountID = 202;

    -- 检查是否有错误
    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRANSACTION;
        RETURN;
    END;

COMMIT TRANSACTION;  -- 或 COMMIT TRAN / COMMIT

-- ② 推荐：TRY...CATCH 事务模式
BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountID = 101;
    UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountID = 202;

    -- 人为测试回滚：RAISERROR('测试错误', 16, 1);

    COMMIT TRANSACTION;
    PRINT '转账成功';

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    -- 记录错误日志
    INSERT INTO dbo.ErrorLog (ErrorMsg, ErrorLine, ErrorProc, ErrorTime)
    VALUES (ERROR_MESSAGE(), ERROR_LINE(), ERROR_PROCEDURE(), GETDATE());

    -- 重新抛出错误（SQL Server 2012+，保留原始错误信息）
    THROW;
    -- 旧写法：RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE());
END CATCH;

-- ③ 自动提交（默认模式，每条语句自动构成一个事务）
UPDATE Accounts SET Balance = 5000 WHERE AccountID = 101;  -- 自动 COMMIT

-- ④ 隐式事务（不建议使用，容易忘记 COMMIT）
SET IMPLICIT_TRANSACTIONS ON;
-- 之后的 DML/DDL 自动 BEGIN TRAN，需手动 COMMIT/ROLLBACK
UPDATE Accounts SET Balance = 5000 WHERE AccountID = 101;
COMMIT;   -- 必须手动提交！
SET IMPLICIT_TRANSACTIONS OFF;

-- ⑤ 保存点（Savepoint）：部分回滚
BEGIN TRANSACTION;
    INSERT INTO Orders (CustomerID, Amount) VALUES (1, 100);
    SAVE TRANSACTION sp1;   -- 保存点

    INSERT INTO OrderItems (OrderID, ProductID) VALUES (SCOPE_IDENTITY(), 999);
    -- 假设 ProductID=999 不存在，违反外键约束

    IF @@ERROR <> 0
        ROLLBACK TRANSACTION sp1;  -- 只回滚到保存点，保留 Orders 的插入

COMMIT TRANSACTION;  -- 提交 Orders 的插入（OrderItems 已回滚）

5.1.3 事务日志工作机制（WAL）

-- 查看事务日志使用情况
SELECT
    name,
    log_size_mb     = SIZE * 8.0 / 1024,
    log_used_mb     = FILEPROPERTY(name, 'SpaceUsed') * 8.0 / 1024,
    log_used_pct    = CAST(FILEPROPERTY(name, 'SpaceUsed') * 100.0 / SIZE AS DECIMAL(5,2)),
    recovery_model  = DATABASEPROPERTYEX(name, 'Recovery')
FROM sys.databases
WHERE database_id > 4;  -- 排除系统数据库

-- 查看活跃日志
DBCC SQLPERF(LOGSPACE);

-- 查看事务日志 VLF 状态（VLF=Virtual Log File）
DBCC LOGINFO('YourDatabase');

-- 日志增长管理：避免日志文件过度增长
-- 问题：事务日志 VLF 碎片化会导致性能下降
-- 建议：初始分配足够大；增量设置为固定值（非百分比）
ALTER DATABASE YourDatabase
MODIFY FILE (NAME = 'YourDatabase_log', SIZE = 4096MB, FILEGROWTH = 512MB);

5.2 锁机制详解

5.2.1 锁类型层次

SQL Server 的锁体系按粒度从细到粗：

行锁（Row）
  ↓  当行数超过阈值时自动升级
页锁（Page = 8KB）
  ↓  当页数超过阈值时自动升级
表锁（Table）

主要锁模式：

-- 查看当前锁请求
SELECT
    request_session_id AS SPID,
    resource_type,
    resource_description,
    request_mode,
    request_status,
    resource_associated_entity_id
FROM sys.dm_tran_locks
WHERE request_session_id > 50  -- 排除系统会话
ORDER BY request_session_id, resource_type;

-- 查看活跃事务
SELECT
    t.transaction_id,
    t.name,
    t.transaction_begin_time,
    DATEDIFF(SECOND, t.transaction_begin_time, GETDATE()) AS DurationSec,
    t.transaction_type,
    t.transaction_state,
    s.session_id,
    s.login_name,
    s.host_name,
    r.command,
    SUBSTRING(txt.text, (r.statement_start_offset/2)+1, 200) AS CurrentSQL
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_tran_session_transactions st ON t.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions s             ON st.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r        ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) txt
WHERE t.transaction_begin_time IS NOT NULL
ORDER BY t.transaction_begin_time;

5.2.2 死锁分析与解决

死锁发生在两个或多个会话互相等待对方持有的锁，SQL Server 会自动选择”代价最小的”事务作为死锁牺牲品（Deadlock Victim）。

-- 模拟死锁场景：
-- 会话1：先锁 A，再锁 B
-- 会话2：先锁 B，再锁 A

-- 会话1 执行：
BEGIN TRAN;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
-- 等待会话2...（手动在会话2中先运行）
WAITFOR DELAY '00:00:05';
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
COMMIT;

-- 会话2 执行（同时）：
BEGIN TRAN;
UPDATE Accounts SET Balance = Balance - 200 WHERE AccountID = 2;
WAITFOR DELAY '00:00:05';
UPDATE Accounts SET Balance = Balance + 200 WHERE AccountID = 1;  -- 死锁！
COMMIT;

-- 捕获死锁信息（系统健康监测会话记录）
WITH SystemHealthDeadlocks AS (
    SELECT CAST(target_data AS XML) AS HealthData
    FROM sys.dm_xe_session_targets t
    JOIN sys.dm_xe_sessions s ON t.event_session_address = s.address
    WHERE s.name = 'system_health' AND t.target_name = 'ring_buffer'
)
SELECT
    event_data.value('(event/@timestamp)[1]', 'DATETIME2') AS DeadlockTime,
    event_data.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS DeadlockGraph
FROM SystemHealthDeadlocks
CROSS APPLY HealthData.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(event_data)
ORDER BY DeadlockTime DESC;

-- 应用层防止死锁的最佳实践
-- 1. 统一访问顺序（所有事务按相同顺序访问表）
-- 2. 缩短事务持续时间（避免在事务中等待用户输入）
-- 3. 设置死锁优先级
SET DEADLOCK_PRIORITY LOW;   -- 让当前会话优先成为牺牲品（保护重要事务）
SET DEADLOCK_PRIORITY HIGH;  -- 让当前会话不被牺牲（用于重要批处理）

-- 4. 使用 NOLOCK/READ COMMITTED SNAPSHOT 减少读锁
-- 5. 应用层捕获错误1205并重试

5.2.3 锁升级与锁超时

-- 锁超时设置（避免无限等待）
SET LOCK_TIMEOUT 5000;   -- 等待5秒，超时后抛出错误1222
-- SET LOCK_TIMEOUT -1;  -- 无限等待（默认）
-- SET LOCK_TIMEOUT 0;   -- 立即超时（不等待）

BEGIN TRY
    SET LOCK_TIMEOUT 5000;
    UPDATE Orders SET Status = 2 WHERE OrderID = 1001;
    COMMIT;
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 1222  -- Lock request time out
    BEGIN
        PRINT '锁等待超时，请稍后重试';
    END
    ELSE THROW;
END CATCH;

-- 锁升级阈值（默认：当单个表的锁数量超过5000时升级为表锁）
-- 可以在表级别禁用锁升级（适合大表高并发场景）
ALTER TABLE dbo.Orders SET (LOCK_ESCALATION = DISABLE);
-- 或限制只升级到分区锁（分区表）
ALTER TABLE dbo.OrdersPartitioned SET (LOCK_ESCALATION = AUTO);

5.3 隔离级别详解

5.3.1 传统隔离级别

-- SQL Server 支持的隔离级别
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;    -- 读未提交（脏读）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;      -- 读已提交（默认）
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;     -- 可重复读
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;        -- 串行化

-- 查看当前会话隔离级别
SELECT
    CASE transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'READ UNCOMMITTED'
        WHEN 2 THEN 'READ COMMITTED'
        WHEN 3 THEN 'REPEATABLE READ'
        WHEN 4 THEN 'SERIALIZABLE'
        WHEN 5 THEN 'SNAPSHOT'
    END AS IsolationLevel
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

各隔离级别的并发问题：

-- 演示：脏读（READ UNCOMMITTED）
-- 会话1
BEGIN TRAN;
UPDATE Products SET Price = 9999 WHERE ProductID = 1;
-- 不提交，等待...

-- 会话2（可以读取到未提交的 9999）
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT Price FROM Products WHERE ProductID = 1;  -- 读到 9999（脏读！）

-- NOLOCK 提示等效于 READ UNCOMMITTED
SELECT Price FROM Products WITH (NOLOCK) WHERE ProductID = 1;
-- ⚠️ 不推荐在生产环境对业务表使用 NOLOCK，可能读到不一致数据

5.3.2 行版本控制隔离（RCSI 与 Snapshot）

行版本控制不依赖锁来实现读隔离，而是读取行的历史版本（存储在 TempDB 的版本存储区），读不阻塞写，写不阻塞读，可显著提升并发。

-- 启用 READ_COMMITTED_SNAPSHOT（RCSI）
-- 将默认的 READ COMMITTED 级别由锁方式改为行版本方式
-- 生产建议：维护窗口执行，此操作需要数据库独占访问
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE YourDatabase SET MULTI_USER;

-- 验证 RCSI 是否开启
SELECT name,
       is_read_committed_snapshot_on,
       snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();

-- 启用快照隔离（Snapshot Isolation，SI）
-- SI 与 RCSI 的区别：
-- RCSI：语句级别快照（每条语句看到自己开始时的快照）
-- SI：事务级别快照（整个事务看到事务开始时的快照）
ALTER DATABASE YourDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;

-- 使用快照隔离读取
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
SELECT * FROM Products;  -- 读取事务开始时的数据版本
-- 期间其他事务的修改对本事务不可见
COMMIT;

-- 版本存储监控（版本存储在 TempDB）
SELECT
    version_store_reserved_page_count * 8.0 / 1024 AS VersionStoreMB,
    version_store_start_time,
    version_cleanup_estimate_ms
FROM sys.dm_tran_version_store_space_usage;

-- 查找长时间运行的快照事务（影响版本存储增长）
SELECT
    s.session_id,
    s.login_name,
    t.transaction_begin_time,
    DATEDIFF(MINUTE, t.transaction_begin_time, GETDATE()) AS DurationMin,
    s.host_name,
    r.command
FROM sys.dm_tran_active_snapshot_database_transactions t
JOIN sys.dm_exec_sessions s ON t.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
ORDER BY t.transaction_begin_time;

5.4 阻塞分析与实战

-- 完整的阻塞分析查询（生产环境常用）
WITH BlockingChain AS (
    SELECT
        r.session_id                AS BlockedSPID,
        r.blocking_session_id       AS BlockerSPID,
        r.wait_type,
        r.wait_time / 1000          AS WaitSec,
        r.status,
        s.login_name                AS BlockedLogin,
        s.host_name                 AS BlockedHost,
        bs.login_name               AS BlockerLogin,
        bs.host_name                AS BlockerHost,
        SUBSTRING(t.text,
            (r.statement_start_offset/2)+1,
            ((CASE r.statement_end_offset
              WHEN -1 THEN DATALENGTH(t.text)
              ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1
        )                           AS BlockedSQL,
        bt.text                     AS BlockerSQL
    FROM sys.dm_exec_requests r
    JOIN sys.dm_exec_sessions s  ON r.session_id = s.session_id
    JOIN sys.dm_exec_sessions bs ON r.blocking_session_id = bs.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
    OUTER APPLY (
        SELECT TOP 1 st.text
        FROM sys.dm_exec_connections c
        CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) st
        WHERE c.session_id = r.blocking_session_id
    ) bt(text)
    WHERE r.blocking_session_id > 0
)
SELECT * FROM BlockingChain ORDER BY WaitSec DESC;

-- 强制终止阻塞会话（谨慎使用！）
-- KILL {session_id};
-- KILL {session_id} WITH STATUSONLY;  -- 查看回滚进度

5.5 小结

下一章：存储过程、函数与动态 SQL