The next step is searching for the transactions in the application code, usually by editor’s search engine.
#Mysql deadlock code
Locate the offending transactions in the source code This lock holding/waiting information is necessary to pinpoint the location of these transactions in the application source code. The same explanation applies for transaction 2.
#Mysql deadlock update
It means that prior to update `table1`, transaction 1 already did something to user table, hence the X lock it is holding. It might seem weird at first, but the reason is because the query showing up here is only a part of the whole transaction. Note that although the log does not specify the lock transaction 1 is holding, we can however infer that transaction 1 is holding the lock on `db`.`user`, for which transaction 2 is waiting.Īlso note that transaction 1 is only updating table1, but it is holding a lock on table user the same thing goes for transaction 2: it is inserting into table2 but hold a lock on table1 and wait for a lock on table user. Enabling this does not cause any downtime.Īnyway, after retrieving the log will look something like this: *** (1) TRANSACTION: TRANSACTION 450913541522, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 7 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2 MySQL thread id 93608210, OS thread handle 47321957033728, query id 10802490531 10.0.64.165 db updating update `table1` set `scroll_to` = 3901, `updated_at` = ‘2021–08–17 11:30:53’ where `id` = 668126442 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1455 page no 5719543 n bits 0 index PRIMARY of table `db`.`table1` trx id 450913541522 lock_mode X locks rec but not gap waiting Record lock, heap no 95 PHYSICAL RECORD: n_fields 24 compact format info bits 0 *** (2) TRANSACTION: TRANSACTION 450913541519, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 9 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 4 MySQL thread id 93608214, OS thread handle 47339872995072, query id 10802490581 10.0.63.235 admin update insert into `table2` (`user_id`, `company_id`, `date`, `steps`, `updated_at`, `created_at`) values (491031, 1, ‘2021–08–17’, 359, ‘2021–08–17 11:30:53’, ‘2021–08–17 11:30:53’) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 1455 page no 5719543 n bits 0 index PRIMARY of table `db`.`table1` trx id 450913541519 lock_mode X locks rec but not gap Record lock, heap no 95 PHYSICAL RECORD: n_fields 24 compact format info bits 0 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 5817 page no 4486 n bits 0 index PRIMARY of table `db`.`user` trx id 450913541519 lock mode S locks rec but not gap waiting Record lock, heap no 99 PHYSICAL RECORD: n_fields 29 compact format info bits 0Ī lot of information there, but since I am only interested in which transaction/query is holding and waiting for which lock, I can summarize as follows: Transaction 1: update `table1` set `scroll_to` = 3901, `updated_at` = ‘2021–08–17 11:30:53’ where `id` = 668126442 -> Holding an X lock on `db`.`user` -> Wait for an X lock on `db`.`table1` Transaction 2: insert into `table2` (`user_id`, `company_id`, `date`, `steps`, `updated_at`, `created_at`) values (491031, 1, ‘2021–08–17’, 359, ‘2021–08–17 11:30:53’, ‘2021–08–17 11:30:53’) -> Hold an X lock on `db`.`table1` -> Wait for an S lock on `db`.`user` A simple SHOW ENGINE INNODB STATUS will do, but it shows only the latest deadlock, which is not very helpful to feel the whole picture of deadlocks across a period of time.įortunately, from mysql 5.5.30 there is a setting called innodb_print_all_deadlocks that helps printing out all deadlocks into mysql error log. The first step is getting deadlock information. Locate the offending transactions in the source codeġ.The strategy for dealing with deadlocks includes 3 steps: Wherever there is concurrency, there is deadlock! The strategy Usually mysql can detect and resolve this on its own by rolling back the transactions, unless deadlock detection is turned off.ĭeadlock is not limited to database system. Deadlock is a situation when multiple (usually 2) transactions (processes) wait for each other’s lock.
0 kommentar(er)
