Sequelize插入数据后无法查询到新增数据的技术问题

问题现象 #

后端插入数据后，前端无法立即检索到新增内容。React应用中，插入数据的函数在收到响应后会发起查询请求更新组件状态，所有逻辑均使用await/async，后端事务也已按顺序正常提交。

客户端调用接口：

• POST: api/ticket（插入数据）
• GET: api/ticket（获取所有数据）

后端日志显示流程正常，但SELECT语句无法检索到刚插入的数据。两个接口使用不同事务，但预期不应影响数据查询。另外在addMessage函数内部调用与GET方法相同的findAll语句能返回正确数据，但分开调用两个接口时就无法获取全部数据，必须刷新页面才能看到新增项。

后端日志 #

START TRANSACTION;
Executing (a9d14d5c-c0ac-4821-9b88-293b086debaa): INSERT INTO `messages` (`id`,`message`,`createdAt`,`updatedAt`,`ticketId`,`userId`) VALUES (DEFAULT,?,?,?,?,?);
Executing (a9d14d5c-c0ac-4821-9b88-293b086debaa): COMMIT;
Executing (9ee9ddaa-294e-41d1-9e03-9f02a2737030): START TRANSACTION;
Executing (9ee9ddaa-294e-41d1-9e03-9f02a2737030): SELECT `ticket`.`id`, `ticket`.`subject`, `ticket`.`status`, `ticket`.`createdAt`, `ticket`.`updatedAt`, `ticket`.`deletedAt`, `ticket`.`userId`, `messages`.`id` AS `messages.id`, `messages`.`message` AS `messages.message`, `messages`.`sender` AS `messages.sender`, `messages`.`createdAt` AS `messages.createdAt`, `messages`.`updatedAt` AS `messages.updatedAt`, `messages`.`deletedAt` AS `messages.deletedAt`, `messages`.`ticketId` AS `messages.ticketId`, `messages`.`userId` AS `messages.userId`, `messages->user`.`id` AS `messages.user.id`, `messages->user`.`firstname` AS `messages.user.firstname`, `messages->user`.`surname` AS `messages.user.surname`, `messages->user`.`email` AS `messages.user.email`, `messages->user`.`password` AS `messages.user.password`, `messages->user`.`stripeId` AS `messages.user.stripeId`, `messages->user`.`token` AS `messages.user.token`, `messages->user`.`birthDate` AS `messages.user.birthDate`, `messages->user`.`status` AS `messages.user.status`, `messages->user`.`confirmationCode` AS `messages.user.confirmationCode`, `messages->user`.`createdAt` AS `messages.user.createdAt`, `messages->user`.`updatedAt` AS `messages.user.updatedAt`, `messages->user`.`deletedAt` AS `messages.user.deletedAt` FROM `tickets` AS `ticket` LEFT OUTER JOIN `messages` AS `messages` ON `ticket`.`id` = `messages`.`ticketId` AND (`messages`.`deletedAt` IS NULL) LEFT OUTER JOIN `users` AS `messages->user` ON `messages`.`userId` = `messages->user`.`id` AND (`messages->user`.`deletedAt` IS NULL) WHERE (`ticket`.`deletedAt` IS NULL);
Executing (9ee9ddaa-294e-41d1-9e03-9f02a2737030): COMMIT;

接口代码 #

POST /api/ticket 接口 #

exports.addMessage = async (req, res) => {
  try {
    const result = await sequelize.transaction(async (t) => {
      var ticketId = req.body.ticketId;
      const userId = req.body.userId;
      const message = req.body.message;
      const subject = req.body.subject;

      // 校验输入 - 新建工单必须提供主题
      if (!ticketId && !subject) {
        return res
          .status(400)
          .send({ message: "新建工单必须填写主题" });
      }
      // 校验输入 - 已有工单必须提供用户ID和消息内容
      if (!userId && !message && ticketId) {
        return res
          .status(400)
          .send({ message: "必须提供用户ID和消息内容" });
      }
      // 若无工单ID则创建工单
      if (!ticketId) {
        const [ticket, created] = await Ticket.findOrCreate({
          where: {
            subject: subject,
            userId: userId,
          },
          transaction: t,
        });
        ticketId = ticket.id;
      }
      // 创建新消息对象
      const messageObject = await db.message.create(
        {
          message: message,
          userId: userId,
          ticketId: ticketId,
        },
        { transaction: t }
      );
      // 返回消息对象
      return res.send(messageObject);
    });
  } catch (err) {
    console.log(err);
    return res.status(500).send({
      message:
        err.message || "创建工单消息时发生错误。",
    });
  }
};

GET /api/ticket 接口 #

exports.findAll = async (req, res) => {
  try {
    const result = await sequelize.transaction(async (t) => {
      const tickets = await db.ticket.findAll(
        {
          include: [{ model: db.message, include: [db.user] }],
        },
        { transaction: t }
      );
      tickets.forEach((ticket) => {
        console.log(JSON.stringify(ticket.messages.length));
      });

      return res.send(tickets);
    });
  } catch (err) {
    console.log(err);
    res.status(500).send({
      message: err.message || "查询工单时发生错误。",
    });
  }
};

排查与解决方案 #

1. 事务提交时机错误 #

问题：原POST接口在事务回调内部直接返回res.send，虽然Sequelize会自动提交事务，但响应发送给前端的时机可能早于事务真正持久化到数据库的时间，导致前端立即发起的查询无法获取到新数据。

修复：将res.send移到事务回调外部，确保事务完全提交后再响应前端：

exports.addMessage = async (req, res) => {
  try {
    const messageObject = await sequelize.transaction(async (t) => {
      var ticketId = req.body.ticketId;
      const userId = req.body.userId;
      const message = req.body.message;
      const subject = req.body.subject;

      // 原有校验逻辑不变
      if (!ticketId && !subject) {
        throw new Error("新建工单必须填写主题");
      }
      if (!userId && !message && ticketId) {
        throw new Error("必须提供用户ID和消息内容");
      }

      if (!ticketId) {
        const [ticket, created] = await Ticket.findOrCreate({
          where: { subject, userId },
          transaction: t,
        });
        ticketId = ticket.id;
      }

      return db.message.create(
        { message, userId, ticketId },
        { transaction: t }
      );
    });
    // 事务提交完成后再响应
    res.send(messageObject);
  } catch (err) {
    console.log(err);
    res.status(err.message.includes("新建工单") ? 400 : 500).send({
      message: err.message || "创建工单消息时发生错误。",
    });
  }
};

2. 查询接口不必要的事务包裹 #

问题：GET接口仅做查询操作，无需事务包裹，多余的事务可能引入不必要的隔离限制，导致无法读取刚提交的数据。

修复：移除GET接口的事务逻辑：

exports.findAll = async (req, res) => {
  try {
    const tickets = await db.ticket.findAll({
      include: [{ model: db.message, include: [db.user] }],
    });
    tickets.forEach((ticket) => {
      console.log(JSON.stringify(ticket.messages.length));
    });
    res.send(tickets);
  } catch (err) {
    console.log(err);
    res.status(500).send({
      message: err.message || "查询工单时发生错误。",
    });
  }
};

3. 验证数据过滤条件 #

检查插入的ticket和message的deletedAt字段是否为NULL，确保查询语句中的WHERE (ticket.deletedAt IS NULL)和JOIN条件不会过滤掉新增数据。可以在插入后打印数据的deletedAt值，或直接在数据库中查询确认。

4. 数据库隔离级别检查 #

如果使用MySQL，默认隔离级别是REPEATABLE READ，理论上不会影响提交后的事务读取，但可以临时调整为READ COMMITTED测试是否解决问题。不过此操作需谨慎，避免影响其他业务逻辑。

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