#mysql #innodb #database-deadlocks
Вопрос:
Я пытаюсь понять информацию из ВЫВОДА МОНИТОРА INNODB, когда произошла тупиковая ситуация.
Версия MySQL: 5.7.17 , Уровень изоляции: ПОВТОРЯЕМЫЙ ПРОЧИТАЙТЕ
вот мою таблицу. столик с эмипти. В нем нет никаких данных.
CREATE TABLE `player_club` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `modifiedBy` bigint(20) DEFAULT NULL, `timeCreated` datetime NOT NULL, `account_id` bigint(20) DEFAULT NULL, `currentClubId` bigint(20) DEFAULT NULL, `endingLevelPosition` int(11) NOT NULL, `nextClubId` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `UK_cagoa3q409gsukj51ltiokjoh` (`account_id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1; Я регистрирую выходные данные show engine innodb status после каждого DML.
| ИДЕНТИФИКАТОР ТРАНЗАКЦИИ 2331 | ИДЕНТИФИКАТОР ТРАНЗАКЦИИ 2332 | выход монитора innodb |
|---|---|---|
| удалить из player_club, где идентификатор учетной записи = 561; | вывод № 1 | |
| удалить из player_club, где account_id = 563; | вывод № 2 | |
| вставьте в player_club (измененные, созданные во времени, текущий клуб, конечный уровень, следующий клуб, идентификатор учетной записи) значения (0, ‘2014-12-23 15:47:11.596’, 180, 4, 181, 561); | вывод № 3 | |
| вставьте в player_club (измененные, созданные во времени, текущий клуб, конечный уровень, следующий клуб, идентификатор учетной записи) значения (0, ‘2014-12-23 15:47:11.596’, 180, 4, 181, 563); |
вот вывод № 1:
------------ TRANSACTIONS ------------ Trx id counter 2332 Purge done for trx's n:o lt; 2329 undo n:o lt; 0 state: running but idle History list length 36 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 283298692306128, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283298692304384, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283298692303512, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283298692302640, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 2331, ACTIVE 5 sec 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 8, OS thread handle 22488, query id 151 localhost ::1 root TABLE LOCK table deadlock.player_club trx id 2331 lock mode IX RECORD LOCKS space id 31 page no 4 n bits 72 index UK_cagoa3q409gsukj51ltiokjoh of table deadlock.player_club trx id 2331 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; Вопрос 1:Означает ли это, что ТРАНЗАКЦИЯ 2331 имеет 2 блокировки?
- lock 1.
TABLE LOCK table deadlock.player_club trx id 2331 lock mode IXrepresent a exclusive intension lock; - lock 2. following part represent a next-key lock.
RECORD LOCKS space id 31 page no 4 n bits 72 index UK_cagoa3q409gsukj51ltiokjoh of table deadlock.player_club trx id 2331 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; here is output #2:
----------- TRANSACTIONS ------------ Trx id counter 2333 Purge done for trx's n:o lt; 2329 undo n:o lt; 0 state: running but idle History list length 36 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 283298692304384, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283298692303512, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283298692302640, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 2332, ACTIVE 4 sec 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 9, OS thread handle 14552, query id 157 localhost ::1 root TABLE LOCK table deadlock.player_club trx id 2332 lock mode IX RECORD LOCKS space id 31 page no 4 n bits 72 index UK_cagoa3q409gsukj51ltiokjoh of table deadlock.player_club trx id 2332 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; ---TRANSACTION 2331, ACTIVE 33 sec 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 8, OS thread handle 22488, query id 151 localhost ::1 root TABLE LOCK table deadlock.player_club trx id 2331 lock mode IX RECORD LOCKS space id 31 page no 4 n bits 72 index UK_cagoa3q409gsukj51ltiokjoh of table deadlock.player_club trx id 2331 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; now,TRANSACTION 2 owns 2 locks too.
here is output #3:
----------- TRANSACTIONS ------------ Trx id counter 2333 Purge done for trx's n:o lt; 2329 undo n:o lt; 0 state: running but idle History list length 36 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 283298692304384, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283298692303512, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283298692302640, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 2332, ACTIVE 31 sec 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 9, OS thread handle 14552, query id 157 localhost ::1 root TABLE LOCK table deadlock.player_club trx id 2332 lock mode IX RECORD LOCKS space id 31 page no 4 n bits 72 index UK_cagoa3q409gsukj51ltiokjoh of table deadlock.player_club trx id 2332 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; ---TRANSACTION 2331, ACTIVE 60 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 8, OS thread handle 22488, query id 163 localhost ::1 root update insert into player_club (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, 4, 181, 561) ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 31 page no 4 n bits 72 index UK_cagoa3q409gsukj51ltiokjoh of table deadlock.player_club trx id 2331 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; ------------------ TABLE LOCK table deadlock.player_club trx id 2331 lock mode IX RECORD LOCKS space id 31 page no 4 n bits 72 index UK_cagoa3q409gsukj51ltiokjoh of table deadlock.player_club trx id 2331 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; RECORD LOCKS space id 31 page no 4 n bits 72 index UK_cagoa3q409gsukj51ltiokjoh of table deadlock.player_club trx id 2331 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; Q2: what does following part mean?
Does TRANSACTION 2331 own 3 lock which is one IX lock, one Next-key lock and one Insert intention lock?
Is TRANSACTION 2331 waiting an insert intension lock when it owns an insert intension lock?
---TRANSACTION 2331, ACTIVE 60 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 8, OS thread handle 22488, query id 163 localhost ::1 root update insert into player_club (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, 4, 181, 561) ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 31 page no 4 n bits 72 index UK_cagoa3q409gsukj51ltiokjoh of table deadlock.player_club trx id 2331 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; ------------------ TABLE LOCK table deadlock.player_club trx id 2331 lock mode IX RECORD LOCKS space id 31 page no 4 n bits 72 index UK_cagoa3q409gsukj51ltiokjoh of table deadlock.player_club trx id 2331 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; RECORD LOCKS space id 31 page no 4 n bits 72 index UK_cagoa3q409gsukj51ltiokjoh of table deadlock.player_club trx id 2331 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; Q3: Does LOCK WAIT 3 lock struct(s) mean it own 3 locks and it’s waiting another lock?