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.html
Backend Troubleshooting Course
https://performance.husseinnasser.com
Fundamentals of Backend Engineering (link redirects to udemy with coupon)
https://backend.husseinnasser.com
Fundamentals of Networking for Effective Backends udemy course (link redirects to udemy with coupon)
https://network.husseinnasser.com
Follow me on Medium
https://medium.com/@hnasr/membership
Introduction to NGINX (link redirects to udemy with coupon)
https://nginx.husseinnasser.com
Python on the Backend (link redirects to udemy with coupon)
https://python.husseinnasser.com
Become a Member on YouTube
https://www.youtube.com/channel/UC_ML5xP23TOWKUcc-oAE_Eg/join
Buy me a coffee if you liked this
https://www.buymeacoffee.com/hnasr
Arabic Software Engineering Channel
https://www.youtube.com/channel/UChWZsjdoRvZ0T9QWZOD6UpA
🔥 Members Only Content
https://www.youtube.com/playlist?list=UUMO_ML5xP23TOWKUcc-oAE_Eg
🏭 Backend Engineering Videos in Order
https://backend.husseinnasser.com
💾 Database Engineering Videos
https://www.youtube.com/playlist?list=PLQnljOFTspQXjD0HOzN7P2tgzu7scWpl2
🎙️Listen to the Backend Engineering Podcast
https://husseinnasser.com/podcast
Gears and tools used on the Channel (affiliates)
🖼️ Slides and Thumbnail Design
Canva
https://partner.canva.com/c/2766475/647168/10068
Stay Awesome,
Hussein