Fixing Lock wait timeout exceeded try restarting transaction for a stuck Mysql table

Question

From a script I sent a query like this thousands of times to my local database   update some table set some column   some value   I forgot to add the where part  so the same column was set to the same a value for all the rows in the table and this was done thousands of times and the column was indexed  so the corresponding index was probably updated too lots of times   I noticed something was wrong  because it took too long  so I killed the script  I even rebooted my computer since then  but something stuck in the table  because simple queries take a very long time to run and when I try dropping the relevant index it fails with this message   Lock wait timeout exceeded  try restarting transaction   It s an innodb table  so stuck the transaction is probably implicit  How can I fix this table and remove the stuck transaction from it

User · Answer

You can check the currently running transactions with

SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`

Your transaction should be one of the first, because it's the oldest in the list. Now just take the value from trx_mysql_thread_id and send it the KILL command:

KILL 1234;

If you're unsure which transaction is yours, repeat the first query very often and see which transactions persist.

User · Answer

Restart MySQL  it works fine   BUT beware that if such a query is stuck  there is a problem somewhere     in your query  misplaced char  cartesian product       very numerous records to edit complex joins or tests  MD5  substrings  LIKE        etc   data structure problem foreign key model  chain loop locking  misindexed data   As  syedrakib said  it works but this is no long-living solution for production   Beware   doing the restart can affect your data with inconsistent state   Also  you can check how MySQL handles your query with the EXPLAIN keyword and see if something is possible there to speed up the query  indexes  complex tests

User · Answer

Check InnoDB status for locks  SHOW ENGINE InnoDB STATUS    Check MySQL open tables  SHOW OPEN TABLES WHERE In use  gt  0    Check pending InnoDB transactions  SELECT   FROM  information schema   innodb trx  ORDER BY  trx started      Check lock dependency - what blocks what  SELECT   FROM  information schema   innodb locks     After investigating the results above  you should be able to see what is locking what   The root cause of the issue might be in your code too - please check the related functions especially for annotations if you use JPA like Hibernate   For example  as described here  the misuse of the following annotation might cause locks in the database    Transactional propagation   Propagation REQUIRES NEW

User · Answer

When you establish a connection for a transaction  you acquire a lock before performing the transaction  If not able to acquire the lock  then you try for sometime  If lock is still not obtainable  then lock wait time exceeded error is thrown  Why you will not able to acquire a lock is that you are not closing the connection  So  when you are trying to get a lock second time  you will not be able to acquire the lock as your previous connection is still unclosed and holding the lock    Solution  close the connection or setAutoCommit true   according to your design  to release the lock

User · Answer

I ran into the same problem with an  update -statement  My solution was simply to run through the operations available in phpMyAdmin for the table  I optimized  flushed and defragmented the table  not in that order   No need to drop the table and restore it from backup for me

User · Answer

This started happening to me when my database size grew and I was doing a lot of transactions on it   Truth is there is probably some way to optimize either your queries or your DB but try these 2 queries for a work around fix    Run this   SET GLOBAL innodb lock wait timeout   5000     And then this   SET innodb lock wait timeout   5000

User · Answer

I had this problem when trying to delete a certain group of records  using MS Access 2007 with an ODBC connection to MySQL on a web server   Typically I would delete certain records from MySQL then replace with updated records  cascade delete several related records  this streamlines deleting all related records for a single record deletion    I tried to run through the operations available in phpMyAdmin for the table  optimize flush  etc   but I was getting a need permission to RELOAD error when I tried to flush  Since my database is on a web server  I couldn t restart the database  Restoring from a backup was not an option   I tried running delete query for this group of records on the cPanel mySQL access on the web  Got same error message    My solution  I used Sun s  Oracle s  free MySQL Query Browser  that I previously installed on my computer  and ran the delete query there  It worked right away  Problem solved  I was then able to once again perform the function using the Access script using the ODBC Access to MySQL connection

User · Answer

Issue in my case  Some updates were made to some rows within a transaction and before the transaction was committed  in another place  the same rows were being updated outside this transaction  Ensuring that all the updates to the rows are made within the same transaction resolved my issue

User · Answer

Goto processes in mysql  So can see there is task still working  Kill the particular process or wait until process complete

User · Answer

I had a similar problem and solved it by checking the threads that are running  To see the running threads use the following command in mysql command line interface   SHOW PROCESSLIST    It can also be sent from phpMyAdmin if you don t have access to mysql command line interface  This will display a list of threads with corresponding ids and execution time  so you can KILL the threads that are taking too much time to execute  In phpMyAdmin you will have a button for stopping threads by using KILL  if you are using command line interface just use the KILL command followed by the thread id  like in the following example   KILL 115    This will terminate the connection for the corresponding thread

User · Answer

Fixed it   Make sure you doesn t have mismatched data type insert in query  I had an issue where i was trying  user browser agent data  in VARCHAR 255  and having issue with this lock however when I changed it to TEXT 255  it fixed it   So most likely it is a mismatch of data type

User · Answer

I had the same issue  I think it was a deadlock issue with SQL  You can just force close the SQL process from Task Manager  If that didn t fix it  just restart your computer  You don t need to drop the table and reload the data

User · Answer

I solved the problem by dropping the table and restoring it from backup

User · Answer

issue resolved in my case by changing delete to truncate issue- query  delete from Survey1 sr survey generic details mycursor execute query   fix- query  truncate table Survey1 sr survey generic details mycursor execute query

[mysql] Fixing "Lock wait timeout exceeded; try restarting transaction" for a 'stuck" Mysql table?

Examples related to mysql

Examples related to transactions