Update query is locked

Question

This is probably a loaded question, but what could be the cause of our MySQL process locks? We ran into an issue where we were trying to use a feature of our software and it 'froze'. Looking into the issue, we found that a query to the MySQL database was 'locked' and the software was waiting for the lock to unlock/resolve before returning.

Some background, we're running a Spring application on Glassfish and letting GF do the DB connection pools management. Everything seemed to be working fine until we started getting these threads locking up when we made requests to perform one particular 'update' function.

• The table we're updating is innodb and has defaults set (row-locking I believe is default)
• Running the show engine innodb status; didn't report any deadlocks.
• Running the show full processlist shows some 'Locked' processes. We're not explicitly locking anything in our code.
• We have no foreign key constraints on the table in question. The ID is the only primary key.
• Running the locked 'update' query from anywhere results in another locked query. We can select from that table without issue. We can insert in other tables without issue.

The only thing I've been able to find is the first answer here - MySQL InnoDB hangs on waiting for table-level locks. We may have run a dump of the database while people were using the system. Is that the most likely cause or could there be other things at work here?

Answer 1

Certainly, mysqldump, with the default options, will lock the tables before dumping them, causing updates to completely block with InnoDB.

Use the --single-transaction option with mysqldump to avoid the issue.

See: Run MySQLDump without Locking Tables

Further, since it's a table lock, not a row lock, the timeout cannot be controlled with innodb_lock_wait_timeout.