magento 2 errors : General error: 1205 Lock wait timeout exceeded; try restarting transaction

Question

I am getting below error every second or third day.

Exception #0 (Magento\Framework\DB\Adapter\LockWaitException): SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction, query was: INSERT INTO customer_visitor (customer_id, session_id, last_visit_at) VALUES (?, ?, ?) Exception #1 (Zend_Db_Statement_Exception): SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction, query was: INSERT INTO customer_visitor (customer_id, session_id, last_visit_at) VALUES (?, ?, ?) Exception #2 (PDOException): SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

I see table customer_visitor have above 22000 records. Can we clear this table?

I also check in my staging site for same table having 12 records and it never increase records of customer visitor.

How can I cleaning up customer_visitor table log. is it creates any problems if cleaning up this table

Answer 1

I also faced the same issue and found the solution.

First, enter in Mysql using terminal/cli with below commands

mysql -u [db username]_user -p
use [db username]_db

View running processes by following command

SHOW PROCESSLIST;

Then kill the process in 'command' sleep in my case the process in the first row with id 22023836

KILL 2202836;

Then refresh the page and the issue is solved now.

Answer 2

Do you want to clear the tables on schedule?

If so you need an extension. check this:

https://github.com/rakeshmagento/magento2-db-log-cleaner

This will truncate the tables in readme though so check that's what you want.