WordPress database error Deadlock found while trying to get lock;
Ever seen something like above? Chances are you are using WooCommerce or have a lot of write activity going on.
Deadlocks usually occur when you are writing more than one value to the same cell of the database. I was once working on an e-commerce website (running WooCommerce) with about 300 sales each hour. The big issue we were facing there was the lock happening on the database each time an update was triggered and to make things worse there was no indexing in place. The first solution therefore was to add indexing which tremendously helped. Since the index had high cardinality, it also improved our read and write performance.
After a lot more digging, the underlying problem however was with WooCommerce Chained Products meta data.
Suppose you link a product to another parent product but later delete it, it turns out that the metadata is not properly deleted causing a null value to be called each time that particular product was purchased.
We therefore had to remove the extra post meta directly from the database and the deadlock errors have flown away.
