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 확인[ | ]
Console
Copy
root@zetawiki:~# php /var/www/mediawiki/maintenance/runJobs.php
root@zetawiki:~#
Console
Copy
MariaDB [mediawiki]> SELECT COUNT(*) FROM job;
+----------+
| count(*) |
+----------+
| 203 |
+----------+
1 row in set (0.00 sec)
Console
Copy
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)
Console
Copy
MariaDB [mediawiki]> UPDATE job SET job_attempts=0;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
3 조치[ | ]
Console
Copy
MariaDB [mediawiki]> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 25 |
+--------------------------+-------+
1 row in set (0.01 sec)
Console
Copy
MariaDB [mediawiki]> set innodb_lock_wait_timeout=120;
Query OK, 0 rows affected (0.00 sec)
Console
Copy
MariaDB [mediawiki]> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 120 |
+--------------------------+-------+
1 row in set (0.00 sec)
Console
Copy
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
Console
Copy
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
Console
Copy
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
Console
Copy
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
Console
Copy
MariaDB [mediawiki]> update job set job_attempts=0 where job_attempts!=0 LIMIT 10;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Console
Copy
MariaDB [mediawiki]> update job set job_attempts=0 where job_attempts!=0 LIMIT 5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Console
Copy
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으로 업데이트하며 작업을 반복하여 해소함
Console
Copy
root@zetawiki:~# crontab -l | grep runJobs
* * * * * php /var/www/mediawiki/maintenance/runJobs.php >> /var/log/runJobs.log 2>&
Console
Copy
root@zetawiki:~# vi /var/www/mediawiki/LocalSettings.php
PHP
Copy
# 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 참고[ | ]
편집자 Jmnote Jmnote bot
로그인하시면 댓글을 쓸 수 있습니다.