SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction Magento

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction. This issue is called Deadlock issue. Some times it is caused on the database.

The reason behind this issue is if you are running a lot of custom scripts and killing the scripts before the database connection gets chance to close.

If you can login to MySQL from CLI and run the following command

SHOW PROCESSLIST;

you will see the following output

+———+—————–+——————-+—————–+———+——+——-+——————+———–+—————+———–+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read |
+———+—————–+——————-+—————–+———+——+——-+——————+———–+—————+———–+
| 162 | db_user| 111.11.0.65:21532 | db_name| Sleep | 3850 | | NULL | 0 | 0 | 0 |
| 175 | db_user| 111.11.0.65:27488 | db_name| Sleep | 3757 | | NULL | 0 | 0 | 0 |
| 176 | db_user| 111.11.0.65:32670 | db_name| Sleep | 3731 | | NULL | 0 | 0 | 0 |
| 190 | db_user| 111.11.0.65:47424 | db_name | Sleep | 3639 | | NULL | 0 | 0 | 0 |
| 210 | db_user| 111.11.0.65:56029 | db_name| Sleep | 3591 | | NULL | 0 | 0 | 0 |
| 211 | db_user| 111.11.0.65:59201 | db_name| Sleep | 3567 | | NULL | 0 | 0 | 0 |
| 225 | db_user| 111.11.0.65:2390 | db_name| Sleep | 3529 | | NULL | 0 | 0 | 0 |
| 227 | db_user| 111.11.0.65:10125 | db_name | Sleep | 3473 | | NULL | 0 | 0 | 0 |
| 230 | db_user| 111.11.0.65:18407 | db_name| Sleep | 3424 | | NULL | 0 | 0 | 0 |
| 280 | db_user| 111.11.0.65:35679 | db_name| Sleep | 3330 | | NULL | 0 | 0 | 0 |
| 287 | db_user| 111.11.0.65:57815 | db_name| Sleep | 1860 | | NULL | 0 | 0 | 0 |
| 291 | db_user| 111.11.0.67:20650 | db_name| Sleep | 188 | | NULL | 1 | 0 | 0 |
| 325 | db_user| 111.11.0.65:36618 | db_name| Query | 0 | NULL | SHOW PROCESSLIST | 0 | 0 | 0 |
| 330 | db_user| 111.11.0.75:38717 | db_name| Sleep | 0 | | NULL | 0 | 0 | 0 |
| 426 | db_user| 111.11.0.75:38819 | db_name| Sleep | 0 | | NULL | 61 | 61 | 61 |
+———+—————–+——————-+—————–+———+——+——-+——————+———–+—————+———–+
15 rows in set (0.00 sec)

You can see as an above example
162 the command is sleep and time is 3850 . This is preventing other operations.
These sleep command processes should be killed 1 by 1 using the command as following.

KILL 162;

Once you have killed all the sleep connections, things should start working as normal again.

If you have any query in SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction Magento, leave it in the comment segment, I will response as soon as possible

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s