Pretty recently, I was troubleshooting a performance issue in a production Rails app backed by Postgres. There was this one class of query that would get slower and slower over the course of about an hour. The exact pathology is a tale for another time, but the investigation led me down the rabbit hole of a particular data structure in Postgres internals called a MultiXact.
Let’s look at what a MultiXact is, why it exists, and under what circumstances one comes into existence.
What the hell is a MultiXact?
In Postgres, it’s possible for two threads (or indeed, even three!) to lock the same row at the same time, as long as their locks don’t conflict with one another. The rules for what kinds of locks conflict with what other kinds of locks are admirably documented.
When you lock a row in Postgres, what you do is, you put your transaction ID in the xmax field of the corresponding tuple in storage (more on xmax on the Cybertec blog). This way, anyone who comes along looking for this row will know that you already have it locked. The latecomer can then wait for the lock:
Transaction 768 | Transaction 769 | Notes |
SELECT * | Transaction 768 now has a row lock. The row’s xmax field contains the value 768. | |
SELECT * | Transaction 769 retrieves the current row, sees that Transaction 768 already holds a lock that conflicts with the lock it wants, and waits for Transaction 768 to be over. |
But what if two threads both want to lock the same row simultaneously? For example:
Transaction 772 | Transaction 773 | Notes |
SELECT * | Afterward, transaction 772 has a row lock. The row’s xmax field contains the value 772. | |
SELECT * | What happens now? |
Transaction 773 can’t just write its transaction ID into the xmax field. That would amount to preempting Transaction 772‘s lock… which would defeat the whole point of locking. To solve this problem, Postgres creates a MultiXact. A MultiXact essentially bundles together some set of transactions so that those transactions can all lock the same row at the same time. Instead of a transaction ID, a new MultiXact ID is written to the row’s xmax.
Transaction 772 | Transaction 773 | Notes |
SELECT * | ||
SELECT * | Now both transactions have the row locked. The row’s xmax field is set to 14, which is a MultiXact ID. MultiXact 14 refers to transactions 772 and 773 by their transaction IDs. | |
COMMIT; | Transaction 773 is now over, but the row’s xmax value is still 14. Since MultiXacts are immutable, MultiXact 14 still refers to the now-defunct Transaction 773 as well as the ongoing Transaction 772. | |
COMMIT; | With both transactions over, there are no remaining locks active on the row. Its xmax value is still 14, and will remain 14 until another thread locks the row or the table is vacuumed. |
It bears repeating that MultiXacts are immutable. If transactions 104 and 108 both have row R locked as part of MultiXact 19, and transaction 117 locks row R too, transaction 117 can’t just join MultiXact 19. Instead, a new MultiXact with ID 20 is created, which contains 104, 108, and 117.
This means that, every time an additional transaction wants to lock a row, Postgres has to write the whole new MultiXact to a buffer. For large MultiXacts, the time-cost of all this reading and writing can become quite significant. Especially since access to the underlying data region is subject to a set of global mutexes.
Counterintuitively, a MultiXact doesn’t always involve multiple transactions.
When I first encountered MultiXacts in production, it was extra confusing, because I was seeing MultiXacts get created within a single transaction. “What gives?” I thought! “They’re supposed to be multi.”
Turns out, we were doing something like this:
BEGIN;
SELECT * FROM queue_jobs
WHERE id = 4
FOR SHARE;
SAVEPOINT foo;
SELECT *
FROM queue_jobs
WHERE id = 4
FOR UPDATE;A savepoint doesn’t technically create a new transaction (I don’t think), but nevertheless, Postgres needs to keep track of the fact that the FOR UPDATE lock was taken after the savepoint, so that that lock can be relinquished in case of a subsequent ROLLBACK TO foo command. As a result, a new MultiXact gets created and its ID gets placed in the row’s xmax field.
I hope I’ve contributed to your understanding of MultiXacts in Postgres! It’s the sort of thing where you don’t ever need to think or know about it, until suddenly you do.
Dan Slimmon 