Description:
Looks like simple parallel record update by unique index cause deadlock.
LATEST DETECTED DEADLOCK
------------------------
2021-08-25 09:54:22 0x2b9b3efde700
*** (1) TRANSACTION:
TRANSACTION 20162672428, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 14846562, OS thread handle 47945785005824, query id 1094567037 10.17.75.14 wp_user updating
UPDATE `abwp_5_options` SET `option_value` = '1629885262.3089809417724609375000' WHERE `option_name` = '_transient_doing_cron'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 268870 page no 54 n bits 216 index option_name of table `wordpress`.`abwp_5_options` trx id 20162672428 lock_mode X waiting
Record lock, heap no 147 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 21; hex 5f7472616e7369656e745f646f696e675f63726f6e; asc _transient_doing_cron;;
1: len 8; hex 00000000001670c2; asc p ;;
*** (2) TRANSACTION:
TRANSACTION 20162672405, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 14846542, OS thread handle 47945776752384, query id 1094566519 10.17.23.73 wp_user updating
UPDATE `abwp_5_options` SET `option_value` = '1629885261.3153219223022460937500' WHERE `option_name` = '_transient_doing_cron'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 268870 page no 54 n bits 216 index option_name of table `wordpress`.`abwp_5_options` trx id 20162672405 lock_mode X locks rec but not gap
Record lock, heap no 147 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 21; hex 5f7472616e7369656e745f646f696e675f63726f6e; asc _transient_doing_cron;;
1: len 8; hex 00000000001670c2; asc p ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 268870 page no 54 n bits 216 index option_name of table `wordpress`.`abwp_5_options` trx id 20162672405 lock_mode X waiting
Record lock, heap no 147 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 21; hex 5f7472616e7369656e745f646f696e675f63726f6e; asc _transient_doing_cron;;
1: len 8; hex 00000000001670c2; asc p ;;
*** WE ROLL BACK TRANSACTION (1)
CREATE TABLE `abwp_2_options` (
`option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`option_name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`option_value` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`autoload` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'yes',
PRIMARY KEY (`option_id`),
UNIQUE KEY `option_name` (`option_name`),
KEY `autoload` (`autoload`)
) ENGINE=InnoDB AUTO_INCREMENT=14459 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ |
+------------------------+
How to repeat:
I can't easily reproduce it. It happens on live cluster time after time.
