How to fix the error "Lock wait timeout exceeded; try restarting transaction"?

A lock wait timeout exceeded try restarting transaction error code happens when a transaction fails to acquire a lock on a resource within a certain amount of time. This can happen when the resource is locked by another transaction, or when the transaction is waiting for a lock on a different resource that is held by another transaction. In either case, the transaction will be rolled back and the locks released.

You are here because you have a question about:

• error code 1205 lock wait timeout exceeded try restarting transaction
• lock wait timeout exceeded try restarting transaction

• Check the query that is causing the lock wait timeout and see if it can be optimized
• Try increase the innodb_lock_wait_timeout setting
• Try setting innodb_rollback_on_timeout to 1
• Try setting innodb_print_all_deadlocks to 1
• Check your transaction isolation level and make sure it is not set to SERIALIZABLE

1. Check the query that is causing the lock wait timeout and see if it can be optimized

1. Check the query that is causing the lock wait timeout.
2. If the query can be optimized in order to fix the lock wait timeout, then the transaction can be restarted.

2. Try increase the innodb_lock_wait_timeout setting

To increase the innodb_lock_wait_timeout setting, follow these steps:

1. Navigate to the [innodb_lock_wait_timeout] section of your MySQL server configuration file (usually located at C:\Program Files\MySQL\MySQL Server 6.5\bin\mysqld.exe.mysql).
2. Change the setting to a higher value if necessary.
3. Restart the MySQL server.

3. Try setting innodb_rollback_on_timeout to 1

1. Open the mysql client and connect to the database
2. In the mysql client, issue the following command to change the innodb_rollback_on_timeout setting SET GLOBAL INNODB_ROLLBACK_ON_TIMEOUT = 1;
3. Exit the mysql client and restart the database server
4. Verify that the lock wait timeout has been corrected

4. Try setting innodb_print_all_deadlocks to 1

To try and fix the lock wait timeout exceeded problem, you will need to set the innodb_print_all_deadlocks option to 1. This option will print out all the deadlocks that have occurred in the database. Next, you will need to restart the transaction that is experiencing the lock wait timeout problem.

5. Check your transaction isolation level and make sure it is not set to SERIALIZABLE

1. Login to the MySQL server using the root account
2. Change to the mysqld directory
3. Run the following command to check the transaction isolation level select @@transaction_isolation;
4. If the transaction isolation level is set to SERIALIZABLE, then the lock wait timeout exceeded try restarting transaction message will be displayed.
5. To fix the issue, change the transaction isolation level to REPEATABLE READ or READ UNCOMMITTED.