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?

It shows only the SHOW FULL PROCESSLIST command, nothing else. It's a local development database. Nothing is running on it. I got the 'lock wait..' error message on the command line when I tried dropping the index from there. — Tom, May 04 '10 at 15:55
In that case you are probably creating 2 separate connections in different transactions that have to wait for eachother. — Wolph, May 04 '10 at 17:01
I didn't create any transactions afterwards. I killed the script, rebooted the machine and logged in from the command line to look around. Nothing else used the database except for the mysql command line client, so something must have been stuck in the table. — Tom, May 04 '10 at 18:24
Thanks for this - just saved me a bunch of headache to not pull my hair and reinstall the server. I thought that something were seriously wrong :) — Industrial, Dec 01 '10 at 21:49
Related question: [How to debug Lock wait timeout exceeded?](http://stackoverflow.com/q/6000336/462865) — Amir Ali Akbari, Nov 22 '14 at 07:44
I have the same problem, except all my data is backed up in XML files. None of these solutions work for me (they tend to assume a running system, not one that rebooted), so I guess I'll have to wait out the restoration of millions of records via `load xml local infile`. [sigh] — ndm13, Jun 27 '15 at 20:07
Funny how your questions has 45 upvotes yet your answer has -48 downvotes :) — John Demetriou, Jan 18 '16 at 07:41
great answer for similar question, listed at http://dba.stackexchange.com/questions/100984/mysql-lock-wait-timeout-exceeded-try-restarting-transaction — Matt, Mar 19 '16 at 13:36

Mihai Crăiță · Answer 1 · 2013-06-14T13:38:49.377

164

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.

edited Jun 14 '13 at 13:38

answered May 31 '13 at 16:13

Mihai Crăiță

3,328
3
25
37

43

TAKE NOTE! This was mentioned by someone on one of the many SO threads concerning this problem: Sometimes the process that has locked the table shows up as sleeping in the processlist! I was tearing my hair out until I killed all the threads that were open in the database in question, sleeping or not. That finally unlocked the table and let the update query run. The commenter mentioned something like "Sometimes a MySQL thread locks a table, then sleeps while it waits for something non-MySQL-related to happen." – Eric L. Dec 11 '13 at 13:50
(I added my own answer to flesh out that thought fragment [here](http://stackoverflow.com/a/20521293/1007619), on a related question) – Eric L. Dec 11 '13 at 14:10
This helped me. I had a couple of _sleeping_ threads created by PHPStorm database management/query feature. – Ejaz Sep 17 '16 at 04:36
Great! I had a hidden process since 5 hours that I could identify and kill. – Aldo Paradiso Mar 21 '17 at 16:36
5

this isnt a solution any more than duct taping over an infected wound is a solution. youre not addressing the underlying root problem. – user2914191 Aug 27 '17 at 16:10
The same happened in my case. I had query was stuck and only after I killed all 'Sleep' processes query executed. Can anybody explain why? – Volodymyr Bilovus Jan 31 '22 at 17:15

score 71 · Answer 2 · edited Jan 18 '17 at 21:21

71

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.

edited Jan 18 '17 at 21:21

Farid Movsumov

12,350
8
71
97

answered Mar 01 '16 at 10:56

nlsrchtr

770
5
7

You might need to use root account to run that SQL to see which transaction is actually blocking other from accessing table – tom10271 Jun 15 '20 at 08:45

score 54 · Answer 3 · edited May 23 '17 at 12:26

54

Check InnoDB status for locks

SHOW ENGINE InnoDB STATUS;

Check MySQL open tables

SHOW OPEN TABLES WHERE In_use > 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)

edited May 23 '17 at 12:26

Community

1
1

answered Nov 14 '16 at 10:23

martoncsukas

2,077
20
23

8

Thank you so much! Running `SELECT * FROM information_schema.innodb_trx t JOIN information_schema.processlist p ON t.trx_mysql_thread_id = p.id` revealed the culprit: The locking thread came from my on IP address ... I had forgotten to close a debug console that I had left in the middle of a transaction... – Elias Strehle Sep 03 '18 at 13:37
The `INNODB_LOCKS` table is deprecated as of MySQL 5.7.14 and is removed in MySQL 8.0. Try `SELECT * FROM performance_schema.data_locks;` instead. – Tyler Jul 20 '23 at 18:51

score 50 · Answer 4 · edited May 04 '18 at 12:53

50

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;

edited May 04 '18 at 12:53

WhyAreYouReadingThis

342
3
16

answered Aug 10 '14 at 14:41

Max D

815
7
10

2

Reference link: [innodb_lock_wait_timeout](https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout) – culix Apr 20 '16 at 05:12
1

I'm running a very busy 11GB database. This is the only thing that has worked for me, thank you! – dongemus Jun 04 '17 at 17:58
Just remember to change the values to its previous values if you don't want to keep them there forever. – Ricardo Martins Apr 12 '18 at 02:20
This means that some of my users will have a more slow experience right? – Arnold Roa May 16 '18 at 10:58

score 11 · Answer 5 · edited Oct 18 '19 at 04:12

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.

score 9 · Answer 6 · answered Mar 04 '13 at 13:41

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,...).

THANK YOU. you did not solve my issue directly but I suffered from table locks and it was so bad. That is the only post ob the whole internet, which leads me to the idea to check the foreign keys. So i found out that the key of the primary key was 11 and the foreign keys 10. I dont know how that could happen and why everything worked before. — EscapeNetscape, May 18 '18 at 13:47

score 3 · Answer 7 · edited Aug 14 '20 at 16:25

3

Goto processes in mysql.

So can see there is task still working.

Kill the particular process or wait until process complete.

edited Aug 14 '20 at 16:25

HoldOffHunger

18,769
10
104
133

answered Apr 25 '12 at 12:02

Shemeer M Ali

1,030
15
39

10

It solves the problem. But this can't be a solution for a LIVE production server...... How can we output this deadlock handling? Or how can we AVOID this deadlock from happening? – Rakib May 24 '12 at 13:14
1

well, it so happens that even with PERFECTLY well-formed and PERFECTLY escaped mysql queries which are not even written by the developer but by the active-records interface of the framework, lock wait timeout issues can STILL happen. So i don't writing proper mysql query is a factor here. – Rakib Nov 24 '12 at 01:54

Mike Lane · Answer 8 · 2012-04-04T23:28:48.873

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.

score 1 · Answer 9 · answered May 10 '11 at 13:19

1

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. :)

answered May 10 '11 at 13:19

The Science Boy

351
5
22

1

It might solve the problem. But having to do this every time such error occurs can't be a solution for a LIVE production server...... How can we output this deadlock handling? Or how can we AVOID this deadlock from happening? – Rakib May 24 '12 at 13:15

score 1 · Answer 10 · answered Aug 10 '20 at 18:49

1

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.

answered Aug 10 '20 at 18:49

Anushri HV

51
3

1

This is not answering the OP, since initially it is clear that the use case is running, in this case mistakenly, multiple updates in multiple, unrelated transactions. – GullerYA Aug 10 '20 at 20:57

score 1 · Answer 11 · answered Mar 16 '12 at 16:17

1

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.

answered Mar 16 '12 at 16:17

kriver

1,588
3
20
33

It solves the problem. But this can't be a solution for a LIVE production server...... How can we output this deadlock handling? Or how can we AVOID this deadlock from happening? – Rakib May 24 '12 at 13:14
restart Apache and its services (or at least the MySQL), no need to reboot – Amjo Jul 07 '16 at 16:59

score 0 · Answer 12 · edited Nov 02 '20 at 02:05

0

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)

edited Nov 02 '20 at 02:05

Ahmet

7,527
3
23
47

answered Nov 01 '20 at 19:54

arjit shukla

11

score 0 · Answer 13 · answered Apr 08 '22 at 15:43

This happened to me when I was accessing the database from multiple platforms, for example from dbeaver and control panels. At some point dbeaver got stuck and therefore the other panels couldn't process additional information. The solution is to reboot all access points to the database. close them all and restart.

score -3 · Answer 14 · edited Oct 18 '19 at 00:50

-3

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.

edited Oct 18 '19 at 00:50

J. Scott Elblein

4,013
15
58
94

answered Apr 09 '18 at 02:13

Hiren Raj

54
4

score -212 · Accepted Answer · edited Aug 18 '17 at 10:18

-212

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

edited Aug 18 '17 at 10:18

Ionică Bizău

109,027
88
289
474

answered Jan 25 '11 at 18:21

Tom

7,515
7
38
54

11

I've come to the conclusion that the stuck transaction finished in the background while he was waiting for an answer, and so by the time he got an answer nothing else worked. – Joshua Dec 09 '20 at 23:40
@Starship-OnStrike Does not really answer the question. – user16217248 Jun 08 '23 at 02:27
Then why isnt it flagged as not an answer? @user16217248 – Starship - On Strike Jun 08 '23 at 09:34
@Starship-OnStrike Because it's kind of an answer, but also kind of not. – user16217248 Jun 08 '23 at 16:25

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

15 Answers15

Linked