Research Paper Week: In-Memory Multi-Version Concurrency Control

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

In-Memory Multi-Version Concurrency Control

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:

Page 3

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!

Previous Post
Research Paper Week: Automatic Indexing in Azure SQL DB
Next Post
Research Paper Week: Constant Time Recovery in Azure SQL DB

3 Comments. Leave new

  • I know what I’m reading tonight. It’s gotta be more exciting than NFL pre-season…

    Thanks for the links.

    If you’re really bored and want to expand your horizons, this is pretty wild.
    https://hpi.de/en/plattner/publications/all-publications.html

    Reply
  • George Walkey
    August 23, 2019 4:13 am

    MS is almost last in all graphs, but hey its still 1.0, right?

    Reply
    • I didn’t even notice that! I’m gonna sound like a Microsoft apologist here, but I really don’t care too much about that – I don’t put a lot of faith in cross-database benchmark tests. It’s too easy to swing the results dramatically by tweaking the benchmark’s goals. I’m more interested in the explanations of how the technologies work. (But now I gotta look at the graphs again, hahaha!)

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}