#Wordpress mysql deadlock update#

It internally creates an instance of a CommittableTransaction So I looked under the hood of TransactionScope and found that: The default isolation level in MySql is REPEATABLE READ so I would assume that the default isolation level in TransactionScope is also REPEATABLE READ which makes the deadlock even weirder because the SQL script execution doesn’t deadlock. The plot thickens!!! Does transaction isolation level affect locking? 🤔 If I run 2 transactions almost simultaneously ( in two separate tabs/sessions and with slightly different data values), the output of the diagnostic query shows that one transaction has got the X lock whilst the other one is waiting for it, but still no S lock in sight*:Īfter a while waiting, I get a LOCK WAIT TIMEOUT error, not a DEADLOCK: It doesn’t seem to affect other locks or transactions!

#Wordpress mysql deadlock update#

Ok! just the X lock taken by the UPDATE statement (as expected) but still no S lock! Ignore the IX lock its an intention lock that always seems to be granted to all the transactions that request it. The way these locks work is documented very well in MySql docs if you want to understand more! This is a very deep subject and it’s hard to keep all edge cases in your head at all times.īut why the S lock in the first place? I wondered if a simple SELECT statement was resulting in the S lock by any chance so I also ran just the SELECT statement in a transaction in SQL without committing/rolling back the transaction so that I can run diagnostic queries with the transaction still in RUNNING state: This means each transaction is waiting for a lock to write to the record but they each hold another lock that at least one of them needs to release before they can get the X lock, therefore, DEADLOCK!!! Although I left this setting enabled in our production database and haven’t noticed any significant impact on transaction processing.Īnyway, in this case, I can see both transaction 1 and 2 hold an S (shared) lock and are waiting for X (exclusive) lock to update the row. MySql docs recommend enabling it only for debugging and then disabling it once the problem is resolved, probably because deadlock logging could adversely affect normal transaction performance. This will mostly produce the same output but it will log all the deadlocks. setting it to “ON”, default is “OFF”) the innodb_print_all_deadlocks setting in system variable.

Log all deadlocks to MySql logs by enabling ( i.e. This will list the most recent deadlock and show what locks the transactions held and what locks they were waiting for at the time of deadlock.ī. RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `TrxDb`.`Products` trx id 2632 lock_mode X locks rec but not gap waiting *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `TrxDb`.`Products` trx id 2632 lock mode S locks rec but not gap Update TrxDb.Products set stock = 357 where Id = 1000 and Version = 1 TRANSACTION 2632, ACTIVE 0 sec starting index read RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `TrxDb`.`Products` trx id 2631 lock_mode X locks rec but not gap waiting *** (1) WAITING FOR THIS LOCK TO BE GRANTED: Record lock, heap no 2 PHYSICAL RECORD: n_fields 5 compact format info bits 0 RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `TrxDb`.`Products` trx id 2631 lock mode S locks rec but not gap Update TrxDb.Products set stock = 495 where Id = 1000 and Version = 1 LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s) TRANSACTION 2631, ACTIVE 0 sec starting index read Show the most recent deadlocks by running show engine innodb status query against the database.

There are at least 2 basic ways to see what’s going on with locks in MySql:Ī. But how do I see what’s really happening? Debugging Locking in MySql

My first hunch was that may be the transactions are just taking too long due to the fact that we’re SELECTing and then either INSERTing or UPDATing and that’s causing contention with locks being held for too long. So I decided to investigate and address this. An increase in message volume, could very well make this problem worse in the future. In our case although data loss wasn’t much of an issue but repeated deadlocks were still worrisome. Its only a problem if it happens so regularly that it makes it difficult to make forward progress and/or results in data loss in other ways. Note that a deadlock in and of itself is not the end of the world, because you can re-issue the deadlocked transaction because it gets rolled back by MySql. (0x80004005): Deadlock found when trying to get lock try restarting transaction… It didn’t take too many iterations to replicate the error: