Sequelize中READ UNCOMMITTED/WITH NO LOCK配置无效问题求助

开发查询测试数据库数据的项目，需要在读取数据时实现WITH NOLOCK。尝试通过Sequelize事务设置READ_UNCOMMITTED隔离级别，但日志显示操作开启了事务提交，配置未达到预期效果，求助解决。

原代码 #

const DEFAULT_PAGE_SIZE = 1001;
const MAX_PAGE_SIZE = 1001;
const DEFAULT_PAGE_SIZE2 = 91;
const MAX_PAGE_SIZE2 = 91;
function calculateOffset(page, pageSize) {
    return (page - 1) * pageSize;
}

router.get('/production-data/pagination', async (req, res) => {
    try {
        const page = parseInt(req.query.page) || 1;
        let pageSize = parseInt(req.query.pageSize) || DEFAULT_PAGE_SIZE;
        pageSize = Math.min(pageSize, MAX_PAGE_SIZE);

        const offset = calculateOffset(page, pageSize);
        const productionData = await ProductionData.findAndCountAll({
            order: [['SaveTime', 'DESC']],
            limit: pageSize,
            offset: offset,
        });

        const nextPage = page + 1;

        res.json({
            data: productionData.rows,
            totalItems: productionData.count,
            currentPage: page,
            pageSize: pageSize,
            nextPage: nextPage,
        });
    } catch (error) {
        console.error('Error fetching order data:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
});

原代码日志 #

Executing (default): SELECT 1+1 AS result
Connection has been established successfully.
Executing (default): SELECT count(*) AS [count] FROM [dbo].[tGapHistory] AS [ProductionData];
Executing (default): SELECT [SaveTime], [LineCode], [GAP], [PEND], [MKCNT] FROM [dbo].[tGapHistory] AS [ProductionData] ORDER BY [ProductionData].[SaveTime] DESC OFFSET 91 ROWS FETCH NEXT 91 ROWS ONLY;

尝试实现READ_UNCOMMITTED的代码 #

const productionData = await sequelize.transaction(async (t) => {
    return await ProductionData.findAndCountAll({
        order: [['SaveTime', 'DESC']],
        limit: pageSize,
        offset: offset,
        transaction: t,
        isolationLevel: Sequelize.Transaction.ISOLATION_LEVELS.READ_UNCOMMITTED
    });
});

尝试后的日志 #

Connection has been established successfully.
Executing (7e4567741ff6d10cf0fb): BEGIN TRANSACTION;
Executing (7e4567741ff6d10cf0fb): SELECT count(*) AS [count] FROM [dbo].[tGapHistory] AS [ProductionData];
Executing (7e4567741ff6d10cf0fb): SELECT [SaveTime], [LineCode], [GAP], [PEND], [MKCNT] FROM [dbo].[tGapHistory] AS [ProductionData] ORDER BY [ProductionData].[SaveTime] DESC OFFSET 
91 ROWS FETCH NEXT 91 ROWS ONLY;
Executing (7e4567741ff6d10cf0fb): COMMIT TRANSACTION;

解决方案 #

问题根源 #

使用sequelize.transaction()会强制开启显式事务，即使设置了READ_UNCOMMITTED隔离级别，本质是在事务内执行查询。而WITH NOLOCK是SQL Server的表级读取提示，不需要依赖事务，显式事务反而会增加不必要的开销，无法达到预期的无锁读取效果。

实现方式 #

方式1：全局设置隔离级别（适合测试库全量查询）

在Sequelize初始化时全局配置READ_UNCOMMITTED，所有查询默认使用该隔离级别，等效于自动应用WITH NOLOCK：

const sequelize = new Sequelize('your_db', 'your_user', 'your_pwd', {
  dialect: 'mssql',
  dialectOptions: {
    options: {
      isolationLevel: Sequelize.Transaction.ISOLATION_LEVELS.READ_UNCOMMITTED
    }
  }
});

方式2：单查询手动添加WITH NOLOCK（灵活控制）

针对findAndCountAll会生成count和select两个查询的特性，分别为两个查询添加WITH NOLOCK提示：

// 处理count查询，添加NOLOCK
const count = await ProductionData.count({
  hooks: {
    beforeCount(options) {
      options.sql = options.sql.replace(/FROM \[dbo\]\.\[tGapHistory\]/, 'FROM [dbo].[tGapHistory] WITH (NOLOCK)');
    }
  }
});

// 处理select查询，添加NOLOCK
const rows = await ProductionData.findAll({
  order: [['SaveTime', 'DESC']],
  limit: pageSize,
  offset: offset,
  hooks: {
    beforeFind(options) {
      options.sql = options.sql.replace(/FROM \[dbo\]\.\[tGapHistory\]/, 'FROM [dbo].[tGapHistory] WITH (NOLOCK)');
    }
  }
});

// 组装成findAndCountAll的返回格式
const productionData = { rows, count };

方式3：使用原生SQL查询（最直接）

直接手写带WITH NOLOCK的SQL，完全控制查询逻辑：

// 执行count查询
const countSql = `SELECT count(*) AS count FROM [dbo].[tGapHistory] WITH (NOLOCK)`;
const [countResult] = await sequelize.query(countSql, { type: Sequelize.QueryTypes.SELECT });
const count = countResult.count;

// 执行分页查询
const selectSql = `
  SELECT [SaveTime], [LineCode], [GAP], [PEND], [MKCNT] 
  FROM [dbo].[tGapHistory] WITH (NOLOCK) 
  ORDER BY [SaveTime] DESC 
  OFFSET ${offset} ROWS FETCH NEXT ${pageSize} ROWS ONLY
`;
const rows = await sequelize.query(selectSql, { 
  model: ProductionData, 
  mapToModel: true // 映射到模型实例
});

const productionData = { rows, count };

内容的提问来源于stack exchange，提问作者user24189560