Moodle 4.0访问题库分类时出现数据库死锁错误求助

问题详情 #

访问题库特定分类时触发数据库死锁错误，错误信息及堆栈跟踪如下：

错误日志

Error writing to database:

Debug information:  Deadlock found when trying to get lock; try restarting transaction
DELETE FROM mdl_question_response_count WHERE analysisid IN (
SELECT id
FROM mdl_question_response_analysis
WHERE hashcode= ? AND whichtries = ? AND questionid = ?
)
[array (
0 => '80ff80a084acf661db8978c57dabc0e88c0793f2',
1 => 'alltries',
2 => '7057133',
)]
Error code: dmlwriteexception

堆栈跟踪

line 489 of /lib/dml/moodle_database.php: dml_write_exception thrown
line 291 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
line 1686 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->query_end()
line 216 of /question/classes/statistics/responses/analysis_for_question.php: call to mysqli_native_moodle_database->delete_records_select()
line 140 of /question/classes/statistics/responses/analyser.php: call to core_question\statistics\responses\analysis_for_question->cache()
line 753 of /mod/quiz/report/statistics/report.php: call to core_question\statistics\responses\analyser->calculate()
line 722 of /mod/quiz/report/statistics/report.php: call to quiz_statistics_report->analyse_responses_for_questions()
line 675 of /mod/quiz/report/statistics/report.php: call to quiz_statistics_report->analyse_responses_for_all_questions_and_subquestions()
line 950 of /mod/quiz/report/statistics/report.php: call to quiz_statistics_report->get_all_stats_and_analysis()
line 2492 of /mod/quiz/lib.php: call to quiz_statistics_report->calculate_questions_stats_for_question_bank()
line 8053 of /lib/moodlelib.php: call to mod_quiz_calculate_question_stats()
line 149 of /question/classes/local/statistics/statistics_bulk_loader.php: call to component_callback()
line 65 of /question/classes/local/statistics/statistics_bulk_loader.php: call to core_question\local\statistics\statistics_bulk_loader::load_statistics_for_place()
line 1037 of /question/classes/local/bank/view.php: call to core_question\local\statistics\statistics_bulk_loader::load_aggregate_statistics()
line 984 of /question/classes/local/bank/view.php: call to core_question\local\bank\view->load_required_statistics()
line 768 of /question/classes/local/bank/view.php: call to core_question\local\bank\view->display_question_list()
line 59 of /question/edit.php: call to core_question\local\bank\view->display()

已尝试操作：

• 重启Linux服务器及数据库
• 更新服务器系统
• 清理moodledata缓存及本地缓存
• 生产库复制到本地测试环境可正常访问，排除数据库数据本身问题

针对性解决办法 #

1. 增加事务重试次数

死锁属于并发资源竞争，Moodle默认事务重试次数不足。修改/lib/dml/moodle_database.php：
找到$this->transactionretrycount变量，将默认值3调整为5-10；同时将$this->transactionretrydelay设置为100-200毫秒，避免立即重试加剧锁竞争。

2. 调整MySQL隔离级别

生产环境默认的REPEATABLE-READ隔离级别易引发统计类操作死锁，临时切换到READ-COMMITTED测试：

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

验证问题缓解后，修改my.cnf（或my.ini）永久生效：

transaction-isolation = READ-COMMITTED

3. 临时禁用自动统计加载

错误触发点为统计缓存清理，可先绕过该逻辑：
修改/question/classes/local/bank/view.php，注释掉load_required_statistics()调用：

// $this->load_required_statistics(); // 临时禁用自动统计加载

访问题库正常后，手动进入测验统计报告页面重新生成统计数据，再恢复代码。

4. 排查生产环境并发负载

• 执行SHOW ENGINE INNODB STATUS;查看死锁详情，定位竞争的资源和事务
• 监控数据库连接数、CPU负载，高峰时段限制非必要的批量统计操作

5. 升级Moodle补丁版本

Moodle 4.0存在统计模块并发死锁的已知问题，升级到4.0.x最新补丁版本（如4.0.10及以上），官方已修复部分事务处理逻辑漏洞。

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