Understanding InnoDB locks and deadlocks

Performance Optimization
16 April 3:00PM - 3:50PM @ Ballroom A

Experience level: 
50 minutes conference

Rate This Session

InnoDB locking model is explained in the manual, numerous blog posts and presentations, and even books. Still sometimes even experienced DBA and MySQL support engineers can not properly explain the reason for lock waits or deadlocks that happened in production and affected performance and scalability. During this session we plan to describe and show all kinds of InnoDB locks in MySQL 5.7 and provide all the important details MySQL manual still misses. One of the most common problems MySQL developers and DBAs hit is related to identifying blocking locks and reasons for deadlocks. We have to read the output of SHOW ENGINE INNODB STATUS many times per day while trying to deal with locks, but do we read it properly? It seems that after years of improvements MySQL manual, numerous blog posts and presentations still had not covered all the details. For example, these are some of our recent enough findings related to InnoDB in the "missing manual" area (both are based on real life customer cases we noted and had to explain during last year): - http://bugs.mysql.com/bug.php?id=71735 - http://bugs.mysql.com/bug.php?id=71736 During this session we plan to fill the gaps, explain and show all kinds of locks InnoDB uses, including records locks and famous insert intention, next key and gap locks it uses. We'll show how to distinguish lock wait from the lock itself, explain how InnoDB checks for deadlocks and what information about locks it provides in the INNODB STATUS and output of other InnoDB monitors, in the INFORMATION_SCHEMA and in the PERFORMANCE_SCHEMA. Based on years of real life experience and source code study we plan to give you a definitive guide to InnoDB locks and different ways to monitor and study them. We also want to clarify some common misconceptions and misinterpretations related to InnoDB locks and deadlocks. The last but not the least, detailed review of all public bug reports and feature requests related to InnoDB locking model will be presented.


Principal Support Engineer, Percona
Valerii joined Percona in September 2012 as a Principal Support Engineer. Before that Valerii worked in the Bugs Verification group of MySQL Support team at MySQL AB/Sun/Oracle for more than seven years. During all this time he processed bugs at http://bugs.mysql.com. He had also worked on complex support issues, coordinated cooperation of MySQL Support with optimizer, GUI tools and some other development teams. Later he was also responsible for decision making from Support side on bug fixing in all MySQL GA versions.

Before joining MySQL AB Valerii worked as software developer, trainer, system administrator and DBA for Oracle and Informix databases.

Valerii got a degree in Mathematics from the Moscow State University back in 1992. He currently lives with his family in the small town of Vyshgorod near Kyiv, Ukraine.
Support Engineer, Percona
Nilnandan officially started with Percona as a Support Engineer 2 years ago. Before joining Percona, he worked as a MySQL Database administrator with different types of service based companies managing high-traffic websites and web applications.

Nilnandan has more than 7+ years of experience working as a MySQL DBA, He has started with ORACLE then moved to MySQL from 4.1 and going through latest releases. 

Currently he is working as a L1/L2 support engineer. He is often involved in 24x7 phone care, allocation of emergency incident management and issues. Involved in investigation and resolution of support cases in MySQL, MySQL Cluster (NDB), Percona XtraDB Cluster, XtraBackup, Percona Server and Percona Toolkit.

Also responsible for writing technical posts on www.mysqlperformanceblog.com, handling forum questions on forums.percona.com and bug verification related to Percona server bugs.