SQL Server 2022 脏读问题排查与思考

发布于：2025-04-06 ⋅ 阅读:(12) ⋅ 点赞:(0)

总结sqlserver的使用，总是会回想起很多开发过程当中加班努（拼）力（命）的场景，今天，就把之前一个由于数据库脏读到这的OA系统员工请假流程状态不一致问题和解决思路分享一下。

业务场景描述

由于企业项目原因，这里对业务细节进行了模糊，还请各位大佬担待。

1. 业务背景

某企业OA系统正在处理年度高峰期的大量请假申请，人力资源部门同时有多个审批人员在处理请假流程。系统使用SQL Server 2022作为数据库，部分报表查询使用了WITH(NOLOCK)提示以提高性能。

2. 脏读发生过程

时间线：

  1. 09:00:00 - 员工A提交请假申请（初始状态：待审批）

    INSERT INTO LeaveRequests (EmployeeID, StartDate, EndDate, Status)
VALUES (1001, '2023-12-25', '2023-12-28', 'Pending')

  2. 09:00:05 - 审批人B开始审批该申请（事务1）

    BEGIN TRANSACTION
UPDATE LeaveRequests 
SET Status = 'Approving'  -- 状态变为"审批中"
WHERE RequestID = 2345
-- 尚未提交，审批人正在核对日历

  3. 09:00:10 - 员工A同时查询申请状态（事务2，使用NOLOCK）

    -- 员工客户端执行的查询
SELECT Status FROM LeaveRequests WITH(NOLOCK) 
WHERE RequestID = 2345
-- 返回结果："Approving"

  4. 09:00:15 - 审批人B发现冲突，回滚审批

    ROLLBACK TRANSACTION
-- 状态回滚到"Pending"

  5. 09:00:20 - 员工A看到系统显示"已开始审批"，于是取消出差计划

    • 但实际申请仍处于"待审批"状态

    • 导致员工错误决策

3. 业务影响分析

影响维度 具体表现
数据一致性 员工看到不存在的中途状态
业务流程 基于错误状态的业务决策
用户信任度 对系统可靠性产生怀疑
法律风险 若涉及薪资计算可能引发纠纷

4. 相关数据表结构

CREATE TABLE LeaveRequests (
    RequestID INT PRIMARY KEY IDENTITY,
    EmployeeID INT NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL,
    Status VARCHAR(20) NOT NULL CHECK (Status IN ('Pending', 'Approving', 'Approved', 'Rejected')),
    ApproverID INT NULL,
    ApprovalTime DATETIME NULL
);
​
-- 状态变更日志表
CREATE TABLE LeaveStatusLog (
    LogID INT IDENTITY PRIMARY KEY,
    RequestID INT NOT NULL,
    OldStatus VARCHAR(20),
    NewStatus VARCHAR(20),
    ChangeTime DATETIME DEFAULT GETDATE()
);

5. 典型错误代码示例

// C# 数据访问层错误实现
public LeaveRequest GetLeaveRequest(int requestId)
{
    // 使用NOLOCK提示读取数据
    string sql = "SELECT * FROM LeaveRequests WITH(NOLOCK) WHERE RequestID = @RequestId";
    
    using (var connection = new SqlConnection(connectionString))
    {
        return connection.QueryFirst<LeaveRequest>(sql, new { RequestId = requestId });
    }
}

6. 第一次思考后的修改

技术方案：
-- 启用数据库快照隔离
ALTER DATABASE OASystem SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE OASystem SET READ_COMMITTED_SNAPSHOT ON;
代码改造：
// 正确实现方式1：使用默认的已提交读
public LeaveRequest GetLeaveRequest(int requestId)
{
    string sql = "SELECT * FROM LeaveRequests WHERE RequestID = @RequestId";
    
    using (var connection = new SqlConnection(connectionString))
    {
        return connection.QueryFirst<LeaveRequest>(sql, new { RequestId = requestId });
    }
}
​
// 正确实现方式2：显式设置事务隔离级别
public LeaveRequest GetLeaveRequestWithSnapshot(int requestId)
{
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (var transaction = connection.BeginTransaction(IsolationLevel.Snapshot))
        {
            string sql = "SELECT * FROM LeaveRequests WHERE RequestID = @RequestId";
            var result = connection.QueryFirst<LeaveRequest>(sql, new { RequestId = requestId }, transaction);
            transaction.Commit();
            return result;
        }
    }
}

7. 对接产品和大佬优化业务流程

对接顶头上司和产品，大家积（甩）极（锅）响应，最后达成一致进行业务流程优化，其实好多东西在上一步修改的时候，站在开发的角度上会感觉产品莫名其妙，但是站在产品的角度，也会觉得很委屈，所以，理解万岁吧。

  1. 状态机设计：

  1. 用户界面提示：

    • 对中间状态显示"处理中，请勿依赖当前状态"

    • 提供状态变更历史记录查看功能

  2. 审批超时机制：

    -- 自动回滚超过30分钟的审批中状态
UPDATE LeaveRequests 
SET Status = 'Pending'
WHERE Status = 'Approving' 
AND ApprovalTime < DATEADD(MINUTE, -30, GETDATE())

思考总结

在上面的基础上，真好假期，结合之前自己的思考然后查询了一些资料，做以下总结，也是为了给自己一个回忆的凭证吧。

一、脏读现象识别

1.1 典型脏读表现

  • 事务A读取到事务B未提交的修改

  • 读取的数据随后被事务B回滚

  • 报表显示"幽灵数据"（实际不存在的数据）

  • 数据前后不一致

1.2 监控脏读发生

-- 查看当前隔离级别下发生的脏读
SELECT 
    t.text AS [SQL语句],
    s.session_id,
    s.read_uncommitted_transactions,
    s.open_transaction_count
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.read_uncommitted_transactions > 0;

二、根本原因分析

2.1 常见脏读成因

原因类型 具体场景 风险等级
使用READ UNCOMMITTED隔离级别 显式设置或NOLOCK提示 高
快照隔离未正确配置 未启用ALLOW_SNAPSHOT_ISOLATION 中
应用程序设计缺陷 错误处理事务边界 高
ORM框架默认配置 某些框架默认低隔离级别 中

三、排查诊断流程

3.1 隔离级别检查

-- 检查当前会话隔离级别
DBCC USEROPTIONS;
​
-- 检查数据库默认隔离级别
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
​
-- 查找使用NOLOCK提示的查询
SELECT q.text, cp.objtype, cp.usecounts
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) q
WHERE q.text LIKE '%NOLOCK%';

3.2 活动事务监控

-- 查看当前活动事务
SELECT 
    tat.transaction_id,
    tat.name,
    tat.transaction_begin_time,
    tst.session_id,
    es.host_name,
    es.program_name,
    es.login_name
FROM sys.dm_tran_active_transactions tat
JOIN sys.dm_tran_session_transactions tst ON tat.transaction_id = tst.transaction_id
JOIN sys.dm_exec_sessions es ON tst.session_id = es.session_id;

四、解决方案

4.1 隔离级别调整方案

方案1：启用读已提交快照（推荐）
-- 启用数据库快照隔离
ALTER DATABASE YourDatabase 
SET ALLOW_SNAPSHOT_ISOLATION ON;
​
-- 启用读已提交快照
ALTER DATABASE YourDatabase 
SET READ_COMMITTED_SNAPSHOT ON;
方案2：显式设置隔离级别
-- 应用程序连接字符串
"Server=...;Database=...;ApplicationIntent=ReadWrite;Transaction Isolation Level=Read Committed;"
​
-- T-SQL中设置
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

4.2 代码层修复

修复NOLOCK使用
-- 不安全的写法
SELECT * FROM Orders WITH(NOLOCK) WHERE CustomerID = 1001;
​
-- 修改为安全写法
BEGIN TRANSACTION
    SELECT * FROM Orders WHERE CustomerID = 1001;
COMMIT TRANSACTION
ORM框架配置示例（Entity Framework Core）
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(
        "Server=...",
        options => options.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)
        .UseSnapshots()  // 启用快照隔离
    );
}

4.3 架构优化方案

读写分离实现
-- 配置Always On可用性组
ALTER AVAILABILITY GROUP [AG_Name]
MODIFY REPLICA ON 'Replica_Server' 
WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = 'TCP://Replica_Server:1433'));
​
-- 应用程序连接字符串
"Server=AG_Listener;Database=...;ApplicationIntent=ReadOnly;"

五、验证与监控

5.1 脏读测试脚本

-- 会话1（模拟未提交事务）
BEGIN TRANSACTION
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 123;
    -- 不提交，保持事务开放
​
-- 会话2（检查是否读取到未提交数据）
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT Balance FROM Accounts WHERE AccountID = 123;  -- 应返回原值

5.2 长期监控方案

-- 创建扩展事件会话监控脏读
CREATE EVENT SESSION [DirtyReadMonitoring] ON SERVER 
ADD EVENT sqlserver.lock_acquired(
    WHERE ([mode] = 1 AND [resource_type] = 2) -- 模式1=S锁，资源类型2=PAGE
),
ADD EVENT sqlserver.lock_timeout(
    WHERE ([resource_type] = 2)
ADD TARGET package0.event_file(SET filename=N'DirtyReadMonitoring')
WITH (MAX_MEMORY=4096 KB, MAX_DISPATCH_LATENCY=30 SECONDS);

六、性能与一致性平衡建议

  1. 关键业务数据：

    • 使用READ COMMITTED SNAPSHOT

    • 避免NOLOCK提示

  2. 报表查询：

    • 使用快照隔离(SNAPSHOT)

    • 或路由到只读副本

  3. 高并发场景：

    • 考虑内存优化表

    • 缩短事务持续时间

  4. 历史数据分析：

    • 使用数据库快照

    • 或列存储索引

七、紧急情况处理

当生产环境出现脏读导致数据错误时：

  1. 立即识别问题会话：

SELECT session_id, transaction_isolation_level 
FROM sys.dm_exec_sessions
WHERE transaction_isolation_level = 1;  -- 1=READ UNCOMMITTED

  1. 终止危险会话：

KILL [session_id];

  1. 临时提升隔离级别：

-- 数据库级别
ALTER DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布