使用Sequelize的INSERT和UPDATE查询中出现意外顺序

Question

首先，我将简要解释我尝试做什么以及相关的模型。

有一个trackingId数组(10个元素(，用这个trackingId和一个"免费"调色板创建一个染色体

考虑：

Project.hasMany(models.Palette);
Project.hasMany(models.Chromosome);
Chromosome.hasOne(models.Palette);
Palette.belongsTo(models.Project, {foreignKey: 'projectId', as: 'project'});
Palette.belongsTo(models.Chromosome, {foreignKey: 'chromosomeId', as: 'chromosome'});

现在，我的代码看起来像这样：

freeTrackingIds.forEach(async (trackingId) => {
// Since the project has many palettes, I want to assign to the chromosome one palette that is free (this means, that has not been assigned to any chromosome yet).
// I tried to reload() the project to fetch the changes in its palettes in previous iterations
const availablePalettes = (await project.reload()).palettes.filter((palette) => !palette.chromosomeId);
// Choosing a random palette from my available palettes. Yes, there might be better ways to achieve this. 
const randomPalette = availablePalettes[Math.floor(Math.random() * availablePalettes.length)];
// Creating the new chromosome for the project with the trackingId 
const chromosome = await Chromosome.create({ projectId: project.id, trackingId: trackingId });
// Linking the Palette to the chromosome
randomPalette.chromosomeId = chromosome.id;
await randomPalette.save();
});

我注意到availablePalettes语句会检索在forEach的前几次迭代中分配的调色板(这就是我决定每次重新加载项目实体的原因(。

即使在重新加载项目(返回时有免费可用的调色板(时，我也经历过这种情况。检查我的日志，我注意到：

Executing (default): INSERT INTO "Chromosomes" ("id","trackingId","elements","timesRequested","generation","createdAt","updatedAt","projectId") VALUES (DEFAULT,$1,$2,$3,$4,$5,$6,$7) RETURNING *;
Executing (default): INSERT INTO "Chromosomes" ("id","trackingId","elements","timesRequested","generation","createdAt","updatedAt","projectId") VALUES (DEFAULT,$1,$2,$3,$4,$5,$6,$7) RETURNING *;
Executing (default): INSERT INTO "Chromosomes" ("id","trackingId","elements","timesRequested","generation","createdAt","updatedAt","projectId") VALUES (DEFAULT,$1,$2,$3,$4,$5,$6,$7) RETURNING *;
Executing (default): INSERT INTO "Chromosomes" ("id","trackingId","elements","timesRequested","generation","createdAt","updatedAt","projectId") VALUES (DEFAULT,$1,$2,$3,$4,$5,$6,$7) RETURNING *;
Executing (default): INSERT INTO "Chromosomes" ("id","trackingId","elements","timesRequested","generation","createdAt","updatedAt","projectId") VALUES (DEFAULT,$1,$2,$3,$4,$5,$6,$7) RETURNING *;
Executing (default): INSERT INTO "Chromosomes" ("id","trackingId","elements","timesRequested","generation","createdAt","updatedAt","projectId") VALUES (DEFAULT,$1,$2,$3,$4,$5,$6,$7) RETURNING *;
Executing (default): INSERT INTO "Chromosomes" ("id","trackingId","elements","timesRequested","generation","createdAt","updatedAt","projectId") VALUES (DEFAULT,$1,$2,$3,$4,$5,$6,$7) RETURNING *;
Executing (default): INSERT INTO "Chromosomes" ("id","trackingId","elements","timesRequested","generation","createdAt","updatedAt","projectId") VALUES (DEFAULT,$1,$2,$3,$4,$5,$6,$7) RETURNING *;
Executing (default): INSERT INTO "Chromosomes" ("id","trackingId","elements","timesRequested","generation","createdAt","updatedAt","projectId") VALUES (DEFAULT,$1,$2,$3,$4,$5,$6,$7) RETURNING *;
Executing (default): UPDATE "Palettes" SET "chromosomeId"=$1,"updatedAt"=$2 WHERE "id" = $3
Executing (default): UPDATE "Palettes" SET "chromosomeId"=$1,"updatedAt"=$2 WHERE "id" = $3
Executing (default): UPDATE "Palettes" SET "chromosomeId"=$1,"updatedAt"=$2 WHERE "id" = $3
Executing (default): UPDATE "Palettes" SET "chromosomeId"=$1,"updatedAt"=$2 WHERE "id" = $3
Executing (default): UPDATE "Palettes" SET "chromosomeId"=$1,"updatedAt"=$2 WHERE "id" = $3
Executing (default): UPDATE "Palettes" SET "chromosomeId"=$1,"updatedAt"=$2 WHERE "id" = $3
Executing (default): UPDATE "Palettes" SET "chromosomeId"=$1,"updatedAt"=$2 WHERE "id" = $3
Executing (default): UPDATE "Palettes" SET "chromosomeId"=$1,"updatedAt"=$2 WHERE "id" = $3
Executing (default): UPDATE "Palettes" SET "chromosomeId"=$1,"updatedAt"=$2 WHERE "id" = $3

我不确定这是否是基于时间的，但它看起来像是sequelize(当然是出于性能原因(在我分配托盘之前完成了我所有的染色体创作。这可能是available调色板返回在forEach((的前几次迭代中分配给染色体的调色板的原因吗？

以上就是全部内容，提前谢谢！

Answer 1

forArray对象的Each函数不支持异步语法。使用"for of"：

forof (const trackingId of freeTrackingIds) {
// Since the project has many palettes, I want to assign to the chromosome one palette that is free (this means, that has not been assigned to any chromosome yet).
// I tried to reload() the project to fetch the changes in its palettes in previous iterations
const availablePalettes = (await project.reload()).palettes.filter((palette) => !palette.chromosomeId);
// Choosing a random palette from my available palettes. Yes, there might be better ways to achieve this. 
const randomPalette = availablePalettes[Math.floor(Math.random() * availablePalettes.length)];
// Creating the new chromosome for the project with the trackingId 
const chromosome = await Chromosome.create({ projectId: project.id, trackingId: trackingId });
// Linking the Palette to the chromosome
randomPalette.chromosomeId = chromosome.id;
await randomPalette.save();  
}

此外，我建议您将事务用于此类批处理操作。