SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction. This issue is called the Deadlock issue. Sometimes it is caused by the database.
The reason behind this issue is that you are running a lot of custom scripts and killing the scripts before the database connection gets a 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 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 follows.
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 respond as soon as possible
