Recently, I saw many interest in understanding and getting information about database locking.
MySQL InnoDB’s locking can be complex and having an overview not always simple.
For more information about how InnoDB locking is working, I can only recommend this excellent series of articles by Kuba:
- https://dev.mysql.com/blog-archive/innodb-data-locking-part-1-introduction/
- https://dev.mysql.com/blog-archive/innodb-data-locking-part-2-locks/
- https://dev.mysql.com/blog-archive/innodb-data-locking-part-2-5-locks-deeper-dive/
- https://dev.mysql.com/blog-archive/innodb-data-locking-part-3-deadlocks/
- https://dev.mysql.com/blog-archive/innodb-data-locking-part-4-scheduling/
- https://dev.mysql.com/blog-archive/innodb-data-locking-part-5-concurrent-queues/
I’ve extended my MySQL Shell plugins to provide an overview of the locks.
Disclaimer: the output can be inconsistent with short locking waits
Let’s have a look at the output of the plugin:
As you can see, the plugin returns the thread and connection ID. It also returns if a thread is blocking or blocked by another thread. The latest or current statement is also available and finally a summary of the locking information including which indexes and keys might be locked.
If you want to learn more about InnoDB locks of if you have some locking issue to solve, I encourage you to test this MySQL Shell plugin.
As usual, enjoy MySQL, InnoDB and MySQL Shell !
