Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks

Percona XtraDB Cluster (PXC) and the technology it uses (Galera) is an exciting alternative to traditional MySQL replication.  For those who don’t know, it gives you:

• Fully Synchronous replication with a write latency increase equivalent to a ping RTT to the furthest node
• Automatic cluster synchronization, both incremental and full restores
• The ability to read and write on every node without needing to worry about replication delay

However, good engineers know there is always a trade-off.  If someone tries to sell you on a technology that sounds amazing without telling you the tradeoffs, be careful.

One of the tradeoffs in Galera is how multi-node writing is implemented.  This is highly simplified, but locking inside of an RDBMS can either be optimistic or pessimistic.