If you’ve been doing performance tuning for several years, or graduated from my Mastering Server Tuning class, you’ve come across Read Committed Snapshot Isolation, aka RCSI, aka multi-version concurrency control, aka MVCC, aka optimistic concurrency. It’s not the way SQL Server ships by default, although it is the default for Azure SQL DB, and it’s part of the magic in how you can query an Availability Group readable secondary even when it’s applying updates to the tables behind the scenes.
Today’s paper turns it up several notches: PDF: An Empirical Evaluation of In-Memory Multi-Version Concurrency Control by Yingjun Wu, Joy Arulraj, Jiexi Lin, Ran Xian, and Andrew Pavlo (2017).
This white paper compares and contrasts the methods used by SQL Server (Hekaton In-Memory OLTP), Oracle, Postgres, MySQL, SAP HANA, and others. It doesn’t really focus on SQL Server rowstore RCSI, though – the only SQL Server thing it focuses on is Hekaton.
It’s only 12 pages, but buckle up: these are very information-dense pages. By page 2, they’re already breaking out each competitor by its protocol, version storage, garbage collection method, and index management (logical vs physical pointers.)
By page 3, you’re facing stuff like this:
Do you need this white paper’s contents in order to be a good production DBA, development DBA, or database developer? Absolutely not. This white paper is more for the curious amongst us, those folks who want to know internals work and why Microsoft made the design decisions that they did. It’s the kind of white paper I’ve only started digesting, and will probably take me several more readings before I’d even feel comfortable discussing its contents.
But the cool thing about having a blog like this is that a lot of y’all are smarter than me, so I present this white paper for your amusement. Enjoy.
People who liked this paper (uh, me) also enjoyed this one from Microsoft Research: Improving Optimistic Concurrency Control Through Batching and Operation Reordering by Bailu Ding, Lucja Kot, and Johannes Gehrke (2019). That’s right: taking groups of transactions that are happening around the same time, and reordering them to avoid problems with lock conflicts!