Backend Engineering (Advanced)

Currently Playing: 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.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