ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Error
Transaction is aborted and we need to restart the transaction. Some other transaction is blocking the transaction.
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Solution
We have two solutions for this:
1. Increase the database parameter value “innodb_lock_wait_timeout”.
2. Check the transaction which caused blocking and kill that session.
Increase the database parameter value
1. Check the database parameter value.
mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 50 | +--------------------------+-------+ 1 row in set (0.02 sec)
2. Increase the value of parameter.
-- Set at database level, effect seen during making new connection.
SET GLOBAL innodb_lock_wait_timeout = 100;
-- Set at session level
SET innodb_lock_wait_timeout = 100;
Check the transaction which caused blocking and kill that session
Follow the following link: Handle Locking and kill the session
2nd Option: We need to check the isolation level for the transaction in the Database. MariaDB / MySQL support Repeatable read. So we can also change to Read Committed.
MariaDB supports the following isolation levels:
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
Change the isolation level for the Database:
-- Check the Isolation level:
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
-- Change the Isolation level to Read Committed
SET GLOBAL TRANSACTION ISOLATION LEVELREAD COMMITTED
;
With Read Committed, we should always check for binary log setting: It is better to make it as ROW:
Three supported formats for binary log events:
- Statement-Based Logging
- Row-Based Logging
- Mixed Logging
--Check the binlog_format
select @@binlog_format
--Change to Row Based:
SET GLOBAL binlog_format = 'ROW';