Concurrent UPDATE deadlock (updating 2 fields, selecting by PK)

Question

I have a strange deadlock case, where simple concurrent updates are causing deadlocks on MySQL (5.6.27).

The setup is with multiple workers (several different servers) processing queues and sometimes (not always) we end up with a deadlock.

Queries are fairly straightforward:

START TRANSACTION
UPDATE transactions SET
        suspended_reason = 'PoF is required',
        updated_at = '2017-08-23 06:27:10'
    WHERE id = 1711933
COMMIT

Output from SHOW ENGINE INNODB STATUS

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-08-23 06:27:10 2b4f5ff03700
*** (1) TRANSACTION:
TRANSACTION 24880629, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 91461, OS thread handle 0x2b4f772c2700, query id 3579786 10.2.10.156 staging updating
UPDATE transactions SET suspended_reason = 'PoF is required', updated_at = '2017-08-23 06:27:10' WHERE id = 1711933
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 934 page no 26924 n bits 128 index `PRIMARY` of table `maindb`.`transactions` trx id 24880629 lock_mode X locks rec but not gap waiting
Record lock, heap no 44 PHYSICAL RECORD: n_fields 64; compact format; info bits 0


*** (2) TRANSACTION:
TRANSACTION 24880628, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 91345, OS thread handle 0x2b4f5ff03700, query id 3579787 10.2.11.209 staging updating
UPDATE transactions SET suspended_reason = 'PoF is required', updated_at = '2017-08-23 06:27:10' WHERE id = 1711933
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 934 page no 26924 n bits 128 index `PRIMARY` of table `maindb`.`transactions` trx id 24880628 lock mode S locks rec but not gap
Record lock, heap no 44 PHYSICAL RECORD: n_fields 64; compact format; info bits 0


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 934 page no 26924 n bits 128 index `PRIMARY` of table `maindb`.`transactions` trx id 24880628 lock_mode X locks rec but not gap waiting
Record lock, heap no 44 PHYSICAL RECORD: n_fields 64; compact format; info bits 0


*** WE ROLL BACK TRANSACTION (1)

Looking at InnoDB status output I'm having hard time interpreting it. 2nd transaction has 2 locks? Lock modes are different - S and X.

In this case fields being updated have the same values. Sometimes both fields are different, in other cases only one of the fields is different. Primary key (id) is AUTO_INCREMENT, fields being updated don't have indexes on them.

Additional info:

CREATE TABLE `transactions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  ...
  `suspended_reason` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  ...
  PRIMARY KEY (`id`),
  KEY `transactions_to_country_id_foreign` (`to_country_id`),
  KEY `transactions_coupon_id_foreign` (`coupon_id`),
  KEY `transactions_from_country_id_foreign` (`from_country_id`),
  KEY `transactions_from_currency_id_foreign` (`from_currency_id`),
  KEY `transactions_incoming_account_id_foreign` (`incoming_account_id`),
  KEY `transactions_outgoing_account_id_foreign` (`outgoing_account_id`),
  KEY `transactions_to_currency_id_foreign` (`to_currency_id`),
  KEY `transactions_user_id_foreign` (`user_id`),
  KEY `transactions_expired_cancelled_index` (`expired`,`cancelled`),
  KEY `transactions_sent_received_index` (`sent`,`received`),
  KEY `transactions_expired_cancelled_sent_received_index` (`expired`,`cancelled`,`sent`,`received`),
  KEY `transactions_operator_id_foreign` (`operator_id`),
  KEY `transactions_receiver_data_id_foreign` (`receiver_data_id`),
  KEY `transactions_batch_id_foreign` (`batch_id`),
  CONSTRAINT `transactions_batch_id_foreign` FOREIGN KEY (`batch_id`) REFERENCES `transaction_batches` (`id`),
  CONSTRAINT `transactions_coupon_id_foreign` FOREIGN KEY (`coupon_id`) REFERENCES `promotion_coupons` (`id`),
  CONSTRAINT `transactions_from_country_id_foreign` FOREIGN KEY (`from_country_id`) REFERENCES `country_codes` (`id`),
  CONSTRAINT `transactions_from_currency_id_foreign` FOREIGN KEY (`from_currency_id`) REFERENCES `currencies` (`id`),
  CONSTRAINT `transactions_incoming_account_id_foreign` FOREIGN KEY (`incoming_account_id`) REFERENCES `internal_accounts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `transactions_operator_id_foreign` FOREIGN KEY (`operator_id`) REFERENCES `admins` (`id`),
  CONSTRAINT `transactions_outgoing_account_id_foreign` FOREIGN KEY (`outgoing_account_id`) REFERENCES `internal_accounts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `transactions_receiver_data_id_foreign` FOREIGN KEY (`receiver_data_id`) REFERENCES `transaction_recipients` (`id`),
  CONSTRAINT `transactions_to_country_id_foreign` FOREIGN KEY (`to_country_id`) REFERENCES `country_codes` (`id`),
  CONSTRAINT `transactions_to_currency_id_foreign` FOREIGN KEY (`to_currency_id`) REFERENCES `currencies` (`id`),
  CONSTRAINT `transactions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1760743 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

There are multiple FKs from other tables that reference transactions PK.

Isolation level: REPEATABLE READ.

Answer 1

Why would workers be touching the same record?? Perhaps the real problem is back where a worker grabs a task and locks it for itself??

The deadlock is valid if two workers processed the same transaction. What is your mechanism for preventing such??