Advanced MySQL Index Locking Explained @hnasr
Advanced MySQL Index Locking Explained  @hnasr
Uploaded November 2024 | Updated November 2026, 3 hours ago
Fundamentals of Database Engineering udemy course
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

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 .. uber.com/en-JO/blog/upgrading-ubers-mysql-fleet Will cover this separately

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.html




Backend Troubleshooting Course
performance.husseinnasser.com


Fundamentals of Backend Engineering (link redirects to udemy with coupon)
backend.husseinnasser.com

Fundamentals of Networking for Effective Backends udemy course (link redirects to udemy with coupon)
network.husseinnasser.com

Follow me on Medium
medium.com/@hnasr/membership


Introduction to NGINX (link redirects to udemy with coupon)
nginx.husseinnasser.com

Python on the Backend (link redirects to udemy with coupon)
python.husseinnasser.com

Become a Member on YouTube
youtube.com/channel/UC_ML5xP23TOWKUcc-oAE_Eg/join

Buy me a coffee if you liked this
buymeacoffee.com/hnasr

Arabic Software Engineering Channel
youtube.com/channel/UChWZsjdoRvZ0T9QWZOD6UpA

🔥 Members Only Content
youtube.com/playlist?list=UUMO_ML5xP23TOWKUcc-oAE_Eg



🏭 Backend Engineering Videos in Order
backend.husseinnasser.com

💾 Database Engineering Videos
youtube.com/playlist?list=PLQnljOFTspQXjD0HOzN7P2tgzu7scWpl2

🎙️Listen to the Backend Engineering Podcast
husseinnasser.com/podcast

Gears and tools used on the Channel (affiliates)

🖼️ Slides and Thumbnail Design
Canva
partner.canva.com/c/2766475/647168/10068


Stay Awesome,
Hussein
Advanced MySQL Index Locking ExplainedThe Real Hidden Cost of a RequestHow does the Kernel manage backend Connections?Sockets used to be blocking until asyncThe effect of Random UUID on database performanceyOuR DNS qUeRies wILl eVenTualLy lOok lIKe ThIs (0x20 DNS encoding)MongoDB Internal ArchitectureClustered Collections makes Mongo faster but there is a costSix stages of a good software engineerCloudflares 150ms global cache purge | Deep DiveWhy Cloudflare Ditched NGINXPrimary Key vs Secondary Key - What you probably didnt know
Hussein Nasser |

Advanced MySQL Index Locking Explained

SHARE TO X SHARE TO REDDIT SHARE TO FACEBOOK WALLPAPER