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










