a303cf2d53
运行必须源码：app/(controller/model/service/middleware/lib/utils/validate/router)、config/、database/(migrations/seeders/sql)、启动脚本、app/public/templates 模板。 已忽略运行非必须：node_modules/、logs/、run/、app/public 运行时上传数据、.env*、构建/缓存产物。
100 lines
4.2 KiB
JavaScript
100 lines
4.2 KiB
JavaScript
'use strict';
|
||
|
||
/**
|
||
* 迁移 021：为答题提交表增加唯一约束，杜绝并发作答重复落库（Bug #12 / B12）
|
||
*
|
||
* 背景（B12）：原 saveAnswers 使用「先 findOne 再 create」的非原子写法，且
|
||
* (aggregation_uid, question_uid, question_type) 仅为普通索引。同一用户并发提交
|
||
* 时，多个请求都 find 不到记录，于是各自 create，导致同一题被插入多行
|
||
* （实测：3 路并发产生 57 行 / 20 题，exam_submissions 出现 3 条同选手记录）。
|
||
* 重复行会污染 answeredCount、判分明细与成绩恢复，属数据一致性缺陷。
|
||
*
|
||
* 修复：
|
||
* 1) zhongzhi_submissions / gaozhi_submissions 的复合索引升级为唯一索引
|
||
* uk_{table}_submission (aggregation_uid, question_uid, question_type)；
|
||
* service.saveAnswers 改用 upsert(conflictFields=[...])，由 MySQL 的
|
||
* INSERT ... ON DUPLICATE KEY UPDATE 在并发下原子折叠为单行更新。
|
||
* 2) exam_submissions 的 contestant_id 索引升级为唯一索引
|
||
* uk_exam_submission_contestant，配合 service 中并发安全的
|
||
* _ensureExamSubmissionSafe，保证每选手仅一条提交记录。
|
||
*
|
||
* 注意：up/down 均先去重（保留最小 id）再加减索引，确保幂等且不会因历史脏数据失败。
|
||
*/
|
||
module.exports = {
|
||
up: async (queryInterface) => {
|
||
// ---- 去重：同 (aggregation_uid, question_uid, question_type) 仅留最小 id ----
|
||
await queryInterface.sequelize.query(`
|
||
DELETE t FROM zhongzhi_submissions t
|
||
WHERE t.id NOT IN (
|
||
SELECT min_id FROM (
|
||
SELECT MIN(id) AS min_id FROM zhongzhi_submissions
|
||
GROUP BY aggregation_uid, question_uid, question_type
|
||
) AS keep
|
||
)
|
||
`);
|
||
await queryInterface.sequelize.query(`
|
||
DELETE t FROM gaozhi_submissions t
|
||
WHERE t.id NOT IN (
|
||
SELECT min_id FROM (
|
||
SELECT MIN(id) AS min_id FROM gaozhi_submissions
|
||
GROUP BY aggregation_uid, question_uid, question_type
|
||
) AS keep
|
||
)
|
||
`);
|
||
// exam_submissions：每 contestant_id 仅留最小 id
|
||
await queryInterface.sequelize.query(`
|
||
DELETE t FROM exam_submissions t
|
||
WHERE t.id NOT IN (
|
||
SELECT min_id FROM (
|
||
SELECT MIN(id) AS min_id FROM exam_submissions
|
||
GROUP BY contestant_id
|
||
) AS keep
|
||
)
|
||
`);
|
||
|
||
// ---- zhongzhi ----
|
||
await queryInterface.removeIndex('zhongzhi_submissions', 'idx_zhongzhi_submissions_agg_qtype').catch(() => {});
|
||
await queryInterface.addIndex('zhongzhi_submissions', {
|
||
name: 'uk_zhongzhi_submission',
|
||
unique: true,
|
||
fields: ['aggregation_uid', 'question_uid', 'question_type'],
|
||
}).catch(() => {});
|
||
|
||
// ---- gaozhi ----
|
||
await queryInterface.removeIndex('gaozhi_submissions', 'idx_gaozhi_submissions_agg_qtype').catch(() => {});
|
||
await queryInterface.addIndex('gaozhi_submissions', {
|
||
name: 'uk_gaozhi_submission',
|
||
unique: true,
|
||
fields: ['aggregation_uid', 'question_uid', 'question_type'],
|
||
}).catch(() => {});
|
||
|
||
// ---- exam_submissions ----
|
||
await queryInterface.removeIndex('exam_submissions', 'idx_exam_submissions_contestant_id').catch(() => {});
|
||
await queryInterface.addIndex('exam_submissions', {
|
||
name: 'uk_exam_submission_contestant',
|
||
unique: true,
|
||
fields: ['contestant_id'],
|
||
}).catch(() => {});
|
||
},
|
||
|
||
down: async (queryInterface) => {
|
||
await queryInterface.removeIndex('zhongzhi_submissions', 'uk_zhongzhi_submission').catch(() => {});
|
||
await queryInterface.addIndex('zhongzhi_submissions', {
|
||
name: 'idx_zhongzhi_submissions_agg_qtype',
|
||
fields: ['aggregation_uid', 'question_uid', 'question_type'],
|
||
});
|
||
|
||
await queryInterface.removeIndex('gaozhi_submissions', 'uk_gaozhi_submission').catch(() => {});
|
||
await queryInterface.addIndex('gaozhi_submissions', {
|
||
name: 'idx_gaozhi_submissions_agg_qtype',
|
||
fields: ['aggregation_uid', 'question_uid', 'question_type'],
|
||
});
|
||
|
||
await queryInterface.removeIndex('exam_submissions', 'uk_exam_submission_contestant').catch(() => {});
|
||
await queryInterface.addIndex('exam_submissions', {
|
||
name: 'idx_exam_submissions_contestant_id',
|
||
fields: ['contestant_id'],
|
||
});
|
||
},
|
||
};
|