0

I'm encountering transaction error like this.

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-12-25 08:45:10 2b128be88700
*** (1) TRANSACTION:
TRANSACTION 112497037, ACTIVE 8 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 17 lock struct(s), heap size 376, 319 row lock(s)
MySQL thread id 2637970, OS thread handle 0x2b1276a40700, query id 
298000288 10.104.40.10 kit_dev_user Sending data
update `mt_section` inner join `mt_contract` on 
`mt_contract`.`mt_section_id` = `mt_section`.`id` inner join 
`mt_contract_partner` on `mt_contract_partner`.`mt_contract_id` = 
`mt_contract`.`id` set `mt_section`.`status` = 1 where 
`mt_contract`.`status` in (1, 2) and `mt_contract_partner`.`start_date` 
= '2018-12-25'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 939 page no 3 n bits 122 index `PRIMARY` of table 
`kit_dev_db01`.`mt_section` trx id 112497037 lock_mode X waiting
Record lock, heap no 122 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len=4; bufptr=0x2b1260619af3; hex= 00037969; asc   yi;;
 1: len=6; bufptr=0x2b1260619af7; hex= 000006b492c4; asc       ;;
 2: len=7; bufptr=0x2b1260619afd; hex= 8e000004e301fa; asc        ;;
 3: len=30; bufptr=0x2b1260619b04; hex= 
654d745777647a7a3442556d6d704b634e75635058494868316653745739; asc 
eMtWwdzz4BUmmpKcNucPXIHh1fStW9; (total 32 bytes);
 4: len=1; bufptr=0x2b1260619b24; hex= 02; asc  ;;
 5: len=1; bufptr=0x2b1260619b25; hex= 03; asc  ;;
 6: len=4; bufptr=0x2b1260619b26; hex= 806f4718; asc  oG ;;
 7: len=4; bufptr=0x2b1260619b2a; hex= 00091c46; asc    F;;
 8: len=5; bufptr=0x2b1260619b2e; hex= 99a044a9f4; asc   D  ;;
 9: len=5; bufptr=0x2b1260619b33; hex= 99a10c711e; asc    q ;;
 10: SQL NULL;

*** (2) TRANSACTION:
TRANSACTION 112497348, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 29 lock struct(s), heap size 376, 31 row lock(s), undo log     entries 51
MySQL thread id 2637971, OS thread handle 0x2b127a344700, query id     298002973 172.31.30.249 kit_dev_user update
insert into `mt_contract` (`status`, `mt_section_id`, `mt_room_id`, `registered_at`, `updated_at`, `deleted_at`) values (5, 227689, 251710, '2018-04-30 08:40:36', '2018-09-25 03:16:27', NULL)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 939 page no 3 n bits 122 index `PRIMARY` of table     `kit_dev_db01`.`mt_section` trx id 112497348 lock_mode X locks rec but not gap
Record lock, heap no 122 PHYSICAL RECORD: n_fields 11; compact format;     info bits 0
 0: len=4; bufptr=0x2b1260619af3; hex= 00037969; asc   yi;;
 1: len=6; bufptr=0x2b1260619af7; hex= 000006b492c4; asc       ;;
 2: len=7; bufptr=0x2b1260619afd; hex= 8e000004e301fa; asc        ;;
 3: len=30; bufptr=0x2b1260619b04; hex= 654d745777647a7a3442556d6d704b634e75635058494868316653745739; asc eMtWwdzz4BUmmpKcNucPXIHh1fStW9; (total 32 bytes);
 4: len=1; bufptr=0x2b1260619b24; hex= 02; asc  ;;
 5: len=1; bufptr=0x2b1260619b25; hex= 03; asc  ;;
 6: len=4; bufptr=0x2b1260619b26; hex= 806f4718; asc  oG ;;
 7: len=4; bufptr=0x2b1260619b2a; hex= 00091c46; asc    F;;
 8: len=5; bufptr=0x2b1260619b2e; hex= 99a044a9f4; asc   D  ;;
 9: len=5; bufptr=0x2b1260619b33; hex= 99a10c711e; asc    q ;;
 10: SQL NULL;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1382 page no 5 n bits 1 index     `mt_contract_mt_section1` of table `kit_dev_db01`.`mt_contract` trx id     112497348 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len=8; bufptr=0x2b1263f7c070; hex= 73757072656d756d; asc supremum;;

update sql is only change the status of the mt_section table.
why mt_contract table inserting sql be locking?

i don't understand below error...

`kit_dev_db01`.`mt_section` trx id 112497348 lock_mode X locks rec but not gap

Second SQL target table is mt_contract. but error is mt_section table row locking error.

Table schema is

CREATE TABLE `mt_contract` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '[ID]',
  `status` tinyint(3) unsigned NOT NULL COMMENT '[status]',
  `mt_section_id` int(10) unsigned DEFAULT NULL COMMENT '[sectionID]
  PRIMARY KEY (`id`),
  KEY `mt_contract_mt_section1` (`mt_section_id`),
  CONSTRAINT `mt_contract_mt_section1` FOREIGN KEY (`mt_section_id`) REFERENCES `mt_section` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=215081 DEFAULT CHARSET=utf8mb4

CREATE TABLE `mt_section` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '[ID]',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '[status]',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=229747 DEFAULT CHARSET=utf8mb4

Thank you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.