I Regret Everything: Episode 1 - Foreign Key Constraints

It's about time I really evaluated my stance on FK constraints on InnoDB tables in MySQL. Here was my position on the matter up till now: judicious use of foreign keys for some people can improve performance for certain queries and help maintain data integrity.

This is great because as a programmer, you probably want those things. Probably the thing you're writing reads much more than it writes. That's just the way the world works these days. Additionally, who's going to say no to data with integrity. The only way this could be better is if it also gave the data honor and humility.

So let's take a really shallow look at how foreign key constraints work before we go about properly criticizing them. First, let's talk about locks. Skip past the following paragraphs about chickens and Will Smith if you already know how they work.

Let's say you have a number that represents the total number of chickens you have wrangled into a children's ball pit. You and your buddy are tasked with maintaining this number at 25. You put chickens in, and when there are too many, you have to dispatch some. Before you start your task for the day, there are 23 chickens flapping about. You take the time to count them, and you go and get two more chickens to put in.

While you're off gathering said chickens, Will Smith, who you have convinced to partner with you for the day, also counts 23 and also decides to put two chickens in. He disappears to summon the birds, and you put yours in, not knowing he's doing the same. He returns some time later, throws his chickens in, and calls it a job well done. When you check your work two hours later, you have 27 chickens, and while you appreciate that you got to spend time with the Man In Black, you're beginning to question your line of work, and honestly, who's paying you to do this.

It's a little like the heisenburg uncertainty principle, but with chickens. Between observing a value and acting on it, there is a short but distinct amount of time in which someone else can muck it all up for you. That's where locks come in. Imagine you each have a padlock to the ball pit. When there's a lock on the door, no one else may mess with the chickens until you have removed it. As long as you can't remove someone else's lock, everything's dandy.

So... computers.

So imagine your database table to be a thing filled with chickens.

Right, let's just forget that.

Here's the point. To maintain integrity between two tables, B and A, in a scenario where a row in B depends on a row in A, InnoDB will lock both tables. If you are updating the child B, it will lock A to make sure no one deletes the parent row while you're not looking. We expect the B lock, but while it makes sense, we don't usually think that there would be a lock on A.

Now we've reached the crux: all roads from here on lead to deadlock, a scenario where a process has table A locked and is waiting for a lock on table B to free up while another process has B locked and is waiting for the lock on A to go away. Both processes wait forever, and you end up crying.

The real problem is that there are so many ways this can happen, and even as I write this post, I find more. A SELECT on the parent will lock the child, a SELECT on a child will lock the parent. If you have an S lock due to a foreign key constraint, it can't automatically be upgraded to an X lock. To avoid the 'phantom problem', InnoDB uses next-key locking and gap locking when using FK constraints.

Frankly, I don't understand that last paragraph, and the phantom problem sounds downright scary. I want to deal with data storage, not ghosts (although only just barely). This sounds like a fight where everyone's slinging locks instead of bullets, and I want none of that. I am not smart enough to deal with it.

Let's say for a second that you are. That you're very careful about your constraints, and how you lock, and the order in which you acquire your locks. You're diligent about deadlocks and it shows. Now, your entire codebase is migrated over to SqlAlchemy or ActiveRecord or any other fancy ORM. Where is your god-of-locking now? You have next to no control over locking in these environments, and the queries become problematically complex. Difficult enough that analysis may not be worth it.

Where does that leave us? Well, you have data. It has integrity. It might even have conviction, and after going through so many locks, some character. But it's awfully lonely, since the database frequently times out on lock-waits when more than one person is using it. All because you used a foreign key.

The ironic bit, of course, is that your app is already structured to avoid the real scenario that having foreign keys saves you from. Chances are, you will write your code so you never modify existing primary keys, and you won't accidentally delete records and orphan rows. Even if you do, orphaned rows may be acceptable; you can just clean them up later, no harm no foul.

So I amend my position here with finality, for all who come after me, now until eternity, world without end: judicious use of foreign keys for some people can improve performance for certain queries and help maintain data integrity, but you are not one of those people.