Tags: MySQL

MySQL is always painful for developer when its comes to Stored Procedures, Function, PL/SQL, etc... This may be different for you and may not resolve problem however if you've situation similar to mine than it might help you.

Error
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Scenario
We've Stored Procedure that performs Insert/Update/Delete in about 45 tables. This Stored Procedure working fine in local databases with windows environment. It was not working on production server with Linux environment. I've followed below steps to find out the culprit query.

Steps
Find out the query that executing for long time. This information also contains process_id.

SHOW ENGINE INNODB STATUS

You can also execute following query to find out the process_id(id column) and query on particular database schema.

SHOW PROCESSLIST;

Now if the query is still executing you'll have to kill the process.

KILL process_id;

Culprit Query

INSERT INTO ...
SELECT 
FROM mytable
WHERE column_name
IN(SELECT mytable2_col_name 
   FROM mytable2 
   WHERE id IN(SELECT mytable3_col_name
               FROM mytable3 
               WHERE type = 'GRID' and m_id IN(SELECT DISTINCT column_name
                                               FROM mytable 
                                               WHERE nt_id = NewNtID)) 
   AND fk_col IS NOT NULL)

The query contains long inner query plus I'm using IN for column and same column I used as last inner query.

Solution
I chopped all inner queries and used temporary table to store records of each query and I used final output of all inner query in main query and it worked for me. 

Tags: MySQL