Error:

Lock wait timeout exceeded; try restarting transaction

Cause: Transaction waiting for the other transaction which holds the locks on the same objects. Error waiting for the specified time period and throw the error: Time period wait is defined by parameter ‘innodb_lock_wait_timeout’.

Check the value of the current parameter:

show variables like 'innodb_lock_wait_timeout';

For fixed the issue so that another transaction can wait for that, you may increase the value of parameter but its important to know the root cause. So i preferred to check which transaction or table the problem occurred. For tracing, Created the event which will monitor the process table every 30 seconds and capture the query or table which is locked by another session.

Following are the steps to capture the query or table which locked.

1. Check you event scheduler service is enabled, otherwise enabled it.

-- Check its enabled or disabled
show variables like '%event_scheduler%'
 
Select * from information_schema.processlist where USER = 'event_scheduler';

-- For neable the Event scheduler service  
SET GLOBAL event_scheduler=on
  

2. Create a table which capture the data every 15 seconds

create table capture_Error ( id bigint(4), user varchar(128),HOST varchar(64),DB varchar(64),time int(7),state varchar(64),INFO longtext,capt_datetime datetime);
     

3. Scheduled the event every 15 seconds which will capture the output of information_Schema.processlist table for a query running and we will match the timing from the capture_error table with when the original error occurred.

--This event record data from both table PROCESSLIST or INNODB_TRX which will help to get more detail about locking issue

CREATE OR REPLACE EVENT Checking_locking
   ON SCHEDULE
      EVERY 30 second 
      STARTS CURRENT_TIMESTAMP
DO
Begin
Declare v_row_count int;
select count(*) into v_row_count from information_schema.processlist where info is not null and time > 15;

if v_row_count > 0 then 
  INSERT INTO capture_error (id,user,host,db,time,state,info,capt_Datetime)
      select id,user,host,db,time,state,info,now() from information_schema.processlist where info is not null and time > 15;

insert into capture_Error
select trx_mysql_thread_id,'INNODB_TRX','RunningSESSION','',0,trx_State,trx_query,trx_started from information_schema.INNODB_TRX b
where trx_State in ('RUNNING','LOCK WAIT');
end if;
end

2nd Event which will gather information from Processlist only

---------------------------------------------------------------------------
--- We can create this event which will record only from information_schema.processlist 
---------------------------------------------------------------------------
-- Create the event caputre the query taking more than 15 seconds time.
CREATE OR REPLACE EVENT Checking_locking
   ON SCHEDULE
      EVERY 30 second 
      STARTS CURRENT_TIMESTAMP
DO
   INSERT INTO capture_error (id,user,host,db,time,state,info,capt_Datetime)
      select id,user,host,db,time,state,info,now() from information_schema.processlist where info is not null and time > 15;
  

4. Match the data when you received the error from the application with capt_datetime column of table capture_error. It will help to identify the locking error caused by which table.

5. If the general_log setting is enabled for the database to trace the exact query. Then we will match the trx_started with the event_time of the database.

select * from mysql.general_log where event_time = ''