Foreign Key Locks on Inserts Causing Deadlocks

[Shawn Snider]

Hey everyone - 

We've started migrating our application to support Galera Cluster with MySQL, and it's mostly working flawlessly. We're really impressed with the platform and how well it worked out of the box.

We've run into one specific edge case that I can't fully explain. I've done extensive reading on how Galera does locking and optimistic replication, and how deadlocks are just part of the conflict resolution process (including how to expect them on select and commit type statements depending on the transaction behavior).

Here's the scenario.

We have three tables, that look as follows:

USER (ID, NAME)

MESSAGE (ID, TEXT, USER_ID [FK])

ALERT (ID, TEXT, USER_ID [FK])

All ID's are auto_increment, and we've confirmed that Galera is properly setting and managing the auto_increment gaps.

We have Galera setup with three nodes, but for the purposes of this example, we are using only two database servers, A, and B. We also have the wsrep_sync_wait set to 1 as critical reads are a big part of our application design's caching layer.

Scenario 1

I have a pool of threads inserting MESSAGEs on connections to A. I have a different pool of threads inserting MESSAGESs on connections to B. These are done as simple INSERT statements in a transaction, with no selects.

If the USER_ID is set to NULL, it all works perfect, no deadlocks. A and B synchronize perfectly and there are no conflicts.

If the USER_ID is set to say, 1, we get deadlocks about 50% of the time. It seems that as part of this commit on the insert InnoDB is grabbing a lock on the USER ID, which is causing the insert to fail. I can sort of understand this behavior, and was curious if anyone had any solution or workaround to avoid this.

Scenario 2

Same setup as Scenario 1, but one pool of threads is inserting MESSAGEs on connections to A, and the other pool of threads is inserting ALERTs on connections to B. Same, simple insert commands in the transaction with no selects.

Same behavior as in scenario 1, if USER_ID is null, no deadlocks, but if we have USER_ID set to anything else, like 1, it deadlocks about 50% of the time.

Scenario 3

This one is similar to 1 and 2, but is for UPDATES to existing rows.

One thread pool updating MESSAGEs on connections to A, another thread pool updating MESSAGEs to connections on B. We are ensuring for this test that A and B are updating different rows by PK, so no conflicts. Setting USER_ID from 1 to 1, works everytime (doesn't seem to actually grab the lock). I sort of expected this, but wanted to confirm it.

Scenario 4

This is the one I can't explain. I expected it to fail, but it works perfectly, which is counterintuitive as it's not much different than scenario 1.

One thread pool updating MESSAGEs on connections to A, another thread pool updating MESSAGEs to connections on B. We are ensuring for this test that A and B are updating different rows by PK, so no conflicts. 

Changing the USER ID from 1 to say, 4 on the row in both tables does NOT generate any deadlocks, given it's not much different than inserting a new row in the first place for an FK lookup, I can't explain this behavior.

Any thoughts, or insights as to how I can ideally fix or address scenario 1 (and why scenario 4 is passing?)

Thanks,

Shawn