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 LEVEL READ 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';