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:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |