mysql - 多个表上的 sequelize 查询中的竞争条件

Question

问题:

我正在开展一个项目，该项目由多项研究和一组用户组成，每个用户都参与其中一项研究。每项研究都根据使用某种随机化算法生成的列表将参与者分为两组。注册后，每个用户都会被分配到一个研究中，他们的组由注册顺序和组列表中的相应索引决定。例如，如果学习 A总座位数 4和组列表是 [0, 1, 1, 0]第一个用户被分配到组 0 ，第二个到1依此类推，直到书房已满。

项目中还定义了其他用户角色，即管理员，可以分配给多个研究，而无需在研究中占据一席之地。这意味着用户与研究的关系是 n:m .

当前实现中出现的问题是将用户分配给研究和研究组时的竞争条件。下面提供了代码，它的工作方式是覆盖 addUser的 Study模型，每当将用户添加到研究时，它都会检查研究中已有多少用户，并为用户提供组列表的当前索引，即 seatsTaken数字。只要将用户按时间间隔添加到研究中，这就会起作用。但是每当同时添加多个用户时，异步查询会导致竞争条件和 seatsTaken计数受其他用户同时注册的影响。

在下面的示例中，分配给研究的用户 A在间隔中分配了正确的组，但学习 B与同时查询有不正确的组分配。

const Sequelize = require('sequelize');
const assert = require('assert');

const sequelize = new Sequelize({
  database: 'database',
  username: 'username',
  password: 'password',
  dialect: process.env.DB_DIALECT || 'sqlite',
  storage: 'db.sqlite',
  logging: false
});

const User = sequelize.define('user', {
  id: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    primaryKey: true,
  },
  group: {
    type: Sequelize.INTEGER,
    allowNull: true,
    defaultValue: null
  }
});

// Groups list for studies 'A' and 'B'
const groupLists = {
  a: [0, 1, 1, 0],
  b: [1, 0, 1, 0]
}

const Study = sequelize.define('study', {
  id: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    primaryKey: true,
  },
  name: {
    type: Sequelize.STRING,
    allowNull: false
  },
  seatsTotal: {
    type: Sequelize.INTEGER,
    defaultValue: 0
  }
});

// n:m relation between users and studies
User.belongsToMany(Study, {through: 'UserStudy'});
Study.belongsToMany(User, {through: 'UserStudy'});

// Overridden 'addUser' method for groups assignment
Study.prototype.addUser = async function(user) {
  // Count already occupied seats
  const seatsTaken = await User.count({
    include: [{
      model: Study,
      where: {
        name: this.name
      }
    }]
  });
  // Add the user to study
  await Study.associations.users.add(this, user);
  // Assign the group of the user based on the seatsTaken
  await user.update({ group: groupLists[this.name][seatsTaken] });
}

sequelize.sync({force: true}).then(async () => {
  // Studies 'A' and 'B' with 4 seats
  await Study.bulkCreate([{name: 'a', seatsTotal: 4}, {name: 'b', seatsTotal: 4}]);
  // 8 users
  await User.bulkCreate(new Array(8).fill(0).map(() => ({})));

  const studies = await Study.findAll();
  const users = await User.findAll(); 

  // Assign half of the users to study 'A' in intervals
  users.filter((_, idx) => idx % 2 === 0).forEach((user, idx) => {
    setTimeout(() => {
      studies[0].addUser(user);
    }, 100*idx);
  });

  // Assign the other half to study 'B' at the same time
  await Promise.all(users.filter((_, idx) => idx % 2 === 1).map(user => {
    return studies[1].addUser(user);
  }));

  setTimeout(async () => {
    // Wait for all queries to finish and assert the results
    const userStudies = await User.findAll({
      include: [Study]
    });

    const studyUsersA = userStudies.filter(u => u.studies.some(s => s.name === 'a'));
    const studyUsersB = userStudies.filter(u => u.studies.some(s => s.name === 'b'));

    try {
      console.log('Group list A actual:', studyUsersA.map(u => u.group), 'expected:', groupLists['a']);
      assert.deepEqual(studyUsersA.map(u => u.group).sort((a, b) => a-b), groupLists['a'].sort((a, b) => a-b), 'Group list A is not assigned correctly');
      console.log('Group list B actual:', studyUsersB.map(u => u.group), 'expected:', groupLists['b']);
      assert.deepEqual(studyUsersB.map(u => u.group).sort((a, b) => a-b), groupLists['b'].sort((a, b) => a-b), 'Group list B is not assigned correctly');
      console.log(`Passed: Group lists are assigned correctly.`);
    } catch (e) {
      console.log(`Failed: ${e.message}`);
    }
  }, 500);
});

我能找到的相关问题要么是关于在一张表中增加一个值，要么只是提到事务和锁而没有提供示例代码:
Avoiding race condition with Nodejs Sequelize
How to lock table in sequelize, wait until another request to be complete
Addition and Subtraction Assignment Operator With Sequelize
Database race conditions

限制:

项目栈是nodejs , expressjs和 sequelize和mysql用于生产和 sqlite 的数据库用于开发和
测试。

该解决方案应该适用于 sqlite和 mysql .

优选地，组列表不存储在数据库中。这些列表是由算法和随机种子生成的，但在示例代码中它们是硬编码的。

解决方案应该是 sequelize 解决方案，而不是在 express 服务器中限制或排队用户请求。

在同时请求的情况下，并不严格要求保留用户注册的确切顺序，因为无法真正验证哪个用户首先添加到研究中，但最终结果必须具有正确的数量 0。 s 和 1 s 是指定的组。

我试过 sequelize 事务，但是我在 sqlite 兼容性方面遇到了很多问题，并且由于数据库锁定而表达请求失败，但这可能是因为我缺乏如何正确执行此操作的知识。这里的限制是请求不应该因为数据库锁而失败。

提供的代码是重现问题的最小示例。请使用它作为基础。

运行代码

npm install sequelize sqlite3 mysql2

方格:

node index.js

mysql(使用 docker ):

docker run -d --env MYSQL_DATABASE=database --env MYSQL_USER=username --env MYSQL_PASSWORD=password --env MYSQL_RANDOM_ROOT_PASSWORD=yes -p 3306:3306 mysql:5.7
DB_DIALECT=mysql node index.js

注:

示例代码仅用于演示当前实现中的问题，间隔和超时用于模拟用户与服务器的交互。请不要关注示例中的模式是错误的，而应关注问题本身以及如何在满足限制部分中提到的要求的同时以更好的方式解决问题。

这是一个相当大项目的一部分，我可能会根据实际项目需求和我在此处收到的反馈更新需求。

如果我应该提供任何其他信息，请告诉我。先感谢您。

Answer 1

恐怕这是预期的行为。

您声明 seatsTaken作为异步计算的属性。

您也可以异步插入多个用户。

您不会在其自己的事务中隔离每个用户创建。

因此，您会看到一笔交易的状态变化，而且由于您没有指定任何特定顺序，所以它的变化相当困惑。最终状态会变得一致，但是您实现该状态的方法只是等待一段时间。

我想实现一致性的最简单方法是将每个插入都包装在一个事务中。

如果每次插入的事务太慢，您可以在一个事务中批量插入所有用户记录，然后计算另一个事务中占用的席位，甚至只是同步执行所有操作。

无论如何，如果你想要一致性，你需要逻辑序列化，一个明确的“前后”关系。目前您的代码缺少它，AFAICT。