미디어위키 Error: 1205 Lock wait timeout exceeded; try restarting transaction

1 개요

Error: 1205 Lock wait timeout exceeded; try restarting transaction (135.79.246.80)

[a3ce57bdfc91ea46d80257fb] /w/index.php?title=ll&action=submit JobQueueError from line 802 of /var/www/html/w/includes/jobqueue/JobQueueDB.php: DBQueryError: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading?
Query: DELETE FROM job WHERE job_cmd = 'refreshLinksPrioritized' AND job_id = '9135'
Function: JobQueueDB::doAck
Error: 1205 Lock wait timeout exceeded; try restarting transaction (135.79.246.80)
Backtrace:
... (생략)

2 확인

root@zetawiki:~# php /var/www/mediawiki/maintenance/runJobs.php
root@zetawiki:~#

MariaDB [mediawiki]> SELECT COUNT(*) FROM job;
+----------+
| count(*) |
+----------+
|      203 |
+----------+
1 row in set (0.00 sec)

MariaDB [mediawiki]> SELECT COUNT(*), job_cmd FROM job GROUP BY job_cmd;
+----------+-------------------------+
| COUNT(*) | job_cmd                 |
+----------+-------------------------+
|        7 | htmlCacheUpdate         |
|        2 | recentChangesUpdate     |
|        8 | refreshLinksDynamic     |
|      187 | refreshLinksPrioritized |
+----------+-------------------------+
4 rows in set (0.00 sec)

MariaDB [mediawiki]> UPDATE job SET job_attempts=0;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

3 조치

MariaDB [mediawiki]> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 25    |
+--------------------------+-------+
1 row in set (0.01 sec)

MariaDB [mediawiki]> set innodb_lock_wait_timeout=120;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mediawiki]> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 120   |
+--------------------------+-------+
1 row in set (0.00 sec)

MariaDB [mediawiki]> update job set job_attempts=0 limit 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

MariaDB [mediawiki]> update job set job_attempts=0 where job_attempts!=0 LIMIT 10;
Query OK, 10 rows affected, 1 warning (0.01 sec)
Rows matched: 10  Changed: 10  Warnings: 1

MariaDB [mediawiki]> update job set job_attempts=0 where job_attempts!=0 LIMIT 10;
Query OK, 10 rows affected, 1 warning (1 min 28.26 sec)
Rows matched: 10  Changed: 10  Warnings: 1

MariaDB [mediawiki]> update job set job_attempts=0 where job_attempts!=0 LIMIT 10;
Query OK, 10 rows affected, 1 warning (0.00 sec)
Rows matched: 10  Changed: 10  Warnings: 1

MariaDB [mediawiki]> update job set job_attempts=0 where job_attempts!=0 LIMIT 10;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

MariaDB [mediawiki]> update job set job_attempts=0 where job_attempts!=0 LIMIT 5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

MariaDB [mediawiki]> update job set job_attempts=0 where job_attempts!=0 LIMIT 1;
Query OK, 1 row affected, 1 warning (1 min 38.41 sec)
Rows matched: 1  Changed: 1  Warnings: 1

4 의견

• MySQL 슬로우 쿼리 로그 설정을 해보았으나, 슬로우 쿼리 로그에는 아무 것도 나오지 않음
• innodb_lock_wait_timeout을 50 → 25로 조정해보았으나, 오류 출력까지의 대기시간이 짧아질뿐 근본적인 해결은 아님
• 오히려 innodb_lock_wait_timeout를 늘려야 함

미디어위키 작업큐에 있는 작업 중에는 50초 이상 걸리는 것도 있는데, 대기시간이 짧으면 계속 실패할 뿐 해소가 안됨

innodb_lock_wait_timeout을 120으로 늘리고 job테이블의 job_attempts 컬럼을 0으로 업데이트하며 작업을 반복하여 해소함

root@zetawiki:~# crontab -l | grep runJobs
* * * * * php /var/www/mediawiki/maintenance/runJobs.php >> /var/log/runJobs.log 2>&

root@zetawiki:~# vi /var/www/mediawiki/LocalSettings.php

# End of automatically generated settings.
# Add more configuration options below.
#---------------------------------------------------------------------
$wgRunJobsAsync = false;

5 같이 보기

• ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
• MySQL innodb_lock_wait_timeout
• 미디어위키 runJobs.php
• 미디어위키 job
• Error 503 Backend fetch failed

6 참고

• https://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im