O'Reilly logo

High Performance MySQL, 2nd Edition by Vadim Tkachenko, Arjen Lentz, Peter Zaitsev, Baron Schwartz, Derek J. Balling, Jeremy D. Zawodny

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Appendix D. Debugging Locks

Any system that uses locks to control shared access to resources can be hard to debug when a lock contention issue crops up. Perhaps you’re trying to add a column to a table, or just trying to run a query, when suddenly you find that your queries are blocked because something else is locking the table or rows you’re trying to use. This appendix shows you what to do when you encounter these situations in MySQL. Often all you will want to do is find out why your query is blocked, but sometimes you will want to know what’s blocking it, so you know which process to kill. This appendix shows you how to achieve both goals.

Lock Waits at the Server Level

A lock wait can happen at either the server level or the storage engine level. [133] (Application-level locks could be a problem too, but we’re focusing on MySQL.)

The MySQL server itself uses several types of locks. If a query is waiting for a lock at the server level, you can see evidence of it in the output of SHOW PROCESSLIST. In addition to server-level locks, any storage engine that supports row-level locks, such as InnoDB, implements its own locks, at least at the time of this writing. In MySQL 5.0 and earlier versions, the server is unaware of such locks, and they’re mostly hidden from users and database administrators. Future versions may expose more of these locks at the server level, probably through pluggable INFORMATION_SCHEMA tables.

Here are the kinds of locks the MySQL server uses:

Table locks

Tables ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required