Advanced MySQL Index Locking Explained

Fundamentals of Database Engineering udemy course https://database.husseinnasser.com A detailed comparison of MySQL InnoDB 5.6 and 8.0 B+Tree index locking We know that a write to the index may cause a structure change (tree rebalance) which can cause leaf pages, internal pages and the ROOT to split and update. Allowing a read while the structure is being changed can cause corruptions, so we need to protect the structure and readers via physical locks or a mutexes) I talk about those in my OS course) Now in 5.6, MySQL InnoDB opted to do a global exclusive lock (X lock) on entire index when a rebalance is triggered, preventing reads from happening (reads take a shared S lock), even when the reads are going to a different part of the tree. In 8.0 only the pages being restructured or written are X Locked (not the entire index) also a snapshot of those pages are saved so concurrent reads to those pages are allowed. This index is instead locked via a new intent SX Lock which allows shared S locks but prevents X Lock. I really enjoyed this write up, much more details here by Zongzhi Chen 0:00 Intro 3:00 What are B+Trees? 12:00 S Lock vs E Lock 15:00 InnoDB Index vs Page Locks 17:00 MySQL 5.6 Index Reads 19:48 MySQL 5.6 Index Writes 27:20 Simplicity Design Philosophy 29:15 MySQL 8.0 Index Reads 33:22 MySQL 8.0 Index Writes 38:00 Summary I also try to remember crediting the Devs behind the work, they are the people behind the work and their work are rarely acknowledged. This work is done by Zongzhi Chen http://baotiao.github.io/2024/06/09/english-btree.html After updating the video Uber announced they moved to MySQL 8.0 and saw 94% database lock time decrease .. https://www.uber.com/en-JO/blog/upgrading-ubers-mysql-fleet/ Will cover this separately https://www.linkedin.com/posts/hnaser_a-detailed-comparison-of-mysql-innodb-56-activity-7223248379995111424-W33S?utm_source=share&utm_medium=member_iosv https://www.cs.usfca.edu/~galles/visualization/BPlusTree