0

In my magento admin panel i am getting below error.

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction, query was: UPDATE flag SET state = ?, last_update = '2019-06-12 05:30:06' WHERE (flag_id='13')

When i execute statement show engine innodb status

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-06-11 11:55:03 0x7f5b780d5700
*** (1) TRANSACTION:
TRANSACTION 130434774, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 1136, 15 row lock(s)
MySQL thread id 110548, OS thread handle 140030705755904, query id 12396415 localhost root Sending data
UPDATE `cron_schedule` AS `current`
 LEFT JOIN `cron_schedule` AS `existing` ON existing.job_code = current.job_code AND (existing.executed_at > UTC_TIMESTAMP() - INTERVAL 1 DAY OR existing.executed_at IS NULL) AND existing.status = 'running'
SET `current`.`status` = 'running'
WHERE (current.schedule_id = '6570401') AND (current.status = 'pending') AND (existing.schedule_id IS NULL)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8492 page no 13 n bits 232 index PRIMARY of table `b2b_demo`.`cron_schedule` trx id 130434774 lock mode S locks rec but not gap waiting
Record lock, heap no 158 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 4; hex 0064419f; asc  dA ;;
 1: len 6; hex 000007c646d1; asc     F ;;
 2: len 7; hex 260000016d27bb; asc &   m' ;;
 3: len 30; hex 6f757464617465645f61757468656e7469636174696f6e5f6661696c7572; asc outdated_authentication_failur; (total 40 bytes);
 4: len 7; hex 70656e64696e67; asc pending;;
 5: len 30; hex 53514c53544154455b34303030315d3a2053657269616c697a6174696f6e; asc SQLSTATE[40001]: Serialization; (total 509 bytes);
 6: len 4; hex 5cff9530; asc \  0;;
 7: len 4; hex 5cff9694; asc \   ;;
 8: SQL NULL;
 9: SQL NULL;

*** (2) TRANSACTION:
TRANSACTION 130434775, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
5 lock struct(s), heap size 1136, 16 row lock(s)
MySQL thread id 110549, OS thread handle 140030832891648, query id 12396416 localhost root Sending data
UPDATE `cron_schedule` AS `current`
 LEFT JOIN `cron_schedule` AS `existing` ON existing.job_code = current.job_code AND (existing.executed_at > UTC_TIMESTAMP() - INTERVAL 1 DAY OR existing.executed_at IS NULL) AND existing.status = 'running'
SET `current`.`status` = 'running'
WHERE (current.schedule_id = '6570399') AND (current.status = 'pending') AND (existing.schedule_id IS NULL)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 8492 page no 13 n bits 232 index PRIMARY of table `b2b_demo`.`cron_schedule` trx id 130434775 lock_mode X locks rec but not gap
Record lock, heap no 158 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 4; hex 0064419f; asc  dA ;;
 1: len 6; hex 000007c646d1; asc     F ;;
 2: len 7; hex 260000016d27bb; asc &   m' ;;
 3: len 30; hex 6f757464617465645f61757468656e7469636174696f6e5f6661696c7572; asc outdated_authentication_failur; (total 40 bytes);
 4: len 7; hex 70656e64696e67; asc pending;;
 5: len 30; hex 53514c53544154455b34303030315d3a2053657269616c697a6174696f6e; asc SQLSTATE[40001]: Serialization; (total 509 bytes);
 6: len 4; hex 5cff9530; asc \  0;;
 7: len 4; hex 5cff9694; asc \   ;;
 8: SQL NULL;
 9: SQL NULL;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8492 page no 13 n bits 232 index PRIMARY of table `b2b_demo`.`cron_schedule` trx id 130434775 lock mode S locks rec but not gap waiting
Record lock, heap no 21 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 4; hex 006441a1; asc  dA ;;
 1: len 6; hex 000007c626f4; asc     & ;;
 2: len 7; hex b4000004ae0110; asc        ;;
 3: len 30; hex 6f757464617465645f61757468656e7469636174696f6e5f6661696c7572; asc outdated_authentication_failur; (total 40 bytes);
 4: len 7; hex 70656e64696e67; asc pending;;
 5: SQL NULL;
 6: len 4; hex 5cff9530; asc \  0;;
 7: len 4; hex 5cff9694; asc \   ;;
 8: SQL NULL;
 9: SQL NULL;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 130739722
Purge done for trx's n:o < 130739393 undo n:o < 0 state: running but idle
History list length 25
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421506130444424, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421506130438904, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421506130439824, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 130739721, ACTIVE 6 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 119884, OS thread handle 140030705755904, query id 13272943 localhost root updating
UPDATE `customer_visitor` SET `customer_id` = '120', `session_id` = '46u77ku3l07qidttk9snu1s3b4', `last_visit_at` = '2019-06-12 05:40:32' WHERE (visitor_id='10071')
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8524 page no 3 n bits 104 index PRIMARY of table `b2b_demo`.`customer_visitor` trx id 130739721 lock_mode X locks rec but not gap waiting
Record lock, heap no 35
------------------
---TRANSACTION 130739711, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 119883, OS thread handle 140030833293056, query id 13272718 localhost root updating
UPDATE `customer_visitor` SET `customer_id` = '120', `session_id` = '46u77ku3l07qidttk9snu1s3b4', `last_visit_at` = '2019-06-12 05:40:30' WHERE (visitor_id='10071')
------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8524 page no 3 n bits 104 index PRIMARY of table `b2b_demo`.`customer_visitor` trx id 130739711 lock_mode X locks rec but not gap waiting
Record lock, heap no 35
------------------
---TRANSACTION 130739640, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 119882, OS thread handle 140030832891648, query id 13272521 localhost root updating
UPDATE `customer_visitor` SET `customer_id` = '120', `session_id` = '46u77ku3l07qidttk9snu1s3b4', `last_visit_at` = '2019-06-12 05:40:30' WHERE (visitor_id='10071')
------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8524 page no 3 n bits 104 index PRIMARY of table `b2b_demo`.`customer_visitor` trx id 130739640 lock_mode X locks rec but not gap waiting
Record lock, heap no 35
------------------
---TRANSACTION 130739629, ACTIVE 36 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 119880, OS thread handle 140030704752384, query id 13272172 localhost root update
INSERT INTO `cron_schedule` (`job_code`, `status`, `created_at`, `scheduled_at`) VALUES ('catalog_product_outdated_price_values_cleanup', 'pending', '2019-06-12 05:40:02', '2019-06-12 05:44:00')
------- TRX HAS BEEN WAITING 36 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8492 page no 5 n bits 792 index CRON_SCHEDULE_SCHEDULED_AT_STATUS of table `b2b_demo`.`cron_schedule` trx id 130739629 lock_mode X insert intention waiting
Record lock, heap no 1
------------------

It is generated the above deadlocks for my magento2 website. How can i resolve the issue?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.