Fun MySQL fact of the day: the phantom record menace
Yesterday, we created an index to prevent a full table scan from locking the entire list_of_things
table during a single UPDATE
. I mentioned it was an improvement to the problem, but not a complete fix. Today, we'll see why.
Databases aren't incredibly useful if you can't INSERT
data into them, so let's have a few other accounts try to insert some data into the same table as yesterday:
account 5> BEGIN; UPDATE list_of_things SET modified = now() WHERE account_id = 5;
account 3> BEGIN; INSERT INTO list_of_things VALUES(0, 3, now(), 'account 3''s thing');
account 6> BEGIN; INSERT INTO list_of_things VALUES(0, 6, now(), 'account 6''s thing');
account 9> BEGIN; INSERT INTO list_of_things VALUES(0, 9, now(), 'account 9''s thing');
What happens here? Well, the INSERT
for account_id=9
completes normally, but the INSERT
for account_id={3,6}
are stalled until the UPDATE
on account_id=5
commits. This may again seem somewhat surprising, but this is once again how InnoDB is attempting to prevent phantom reads: all records with account_id=5
must be updated, with no exception. This means that no new records with account_id=5
may be added until the transaction commits or rolls-back.
"But 3
and 6
aren't 5
", you protest! Indeed, they aren't. This is the result of gap locks, which InnoDB uses to prevent new rows with account_id=5
when updating all existing rows with account_id=5
. More specifically, this is actually the result of two gap locks: a "next-key lock", which locks account_id=(1,5]
, and a "gap lock" for account_id=[5,9)
. We'll consider the technical reasons tomorrow, but in any case, the net result for us is that as many as 6
accounts are impacted by the actions of 1 account.
Now, since this is a gap lock, we can say that the less sparse the data are, the less coarse the locks will be, but that's a variable often out of our control. Thankfully, some things are in our control and we can address this in a few ways:
* Add a unique constraint to account_id
, which will generally prevent gap locks. This, naturally, has broad implications on your schema design and may not be applicable.
* Use READ-COMMITTED
isolation, which will generally prevent gap locks. This may have subtle implications on your data access patterns and should be reasoned-out.
* Use multiple transactions to find all of the records where account_id=5
(preferably from a replica) and update them in small batches (remember about undo logs) by primary key. This is most preferable for a number of reasons we have already and will discuss in the future.
Either of the last 2 solutions will result in possible phantom reads, so you just have to decide which is more important to you for your specific use case: preventing phantom reads or higher throughput and predictable latency by means of narrower locks.