CODEWITHSUNDEEP
mysql
Robby75
Robby75

Reputation: 3465

Find out what is locking a MySQL InnoDB table-row

I use a database with an InnoDB-table. When I try to update a row, I get the "ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction" message, which I don't get on any other row (that I've tried).

How can I debug that?

SHOW PROCESSLIST;

is only yielding sleeping processes and because it's an InnoDB table, SHOW TABLE STATUS is basically useless (seems to work only for MyISAM tables).

SHOW ENGINE INNODB STATUS

also does not contain anything useful; the LATEST DETECTED DEADLOCK section contains two transactions from 3 days ago that no longer exist (thread IDs no longer appear in SHOW PROCESSLIST) and no longer seem relevant.

Is there any way to get to know what query caused the problem? Also I would love to see the current locks on InnoDB tables, what is locking what right now - and not three days ago.

Upvotes: 1

Views: 2439

Answers (1)

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7242

Have you checked the error log file of mysql? Maybe there is more detail to which transaction it is that failed.

To show all tables that are in use, simply execute this syntax

SHOW OPEN TABLES WHERE IN_Use=1

Upvotes: 5

Related Questions