4

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.

  • 1
    Please provide SHOW CREATE TABLE transactions -- other indexes may be critical to this discussion. And you are sure the UPDATE is the only SQL in the transaction on both servers? – Rick James Dec 13 '17 at 16:51
  • @RickJames I added constrains part of CREATE TABLE. To answer your question, this table can be quite busy with other processes doing other tasks - selecting, updating, but not deleting. – arnaslu Dec 14 '17 at 8:11
  • Discussion moved to chat. – ypercubeᵀᴹ Dec 14 '17 at 10:14
4

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??

  • I agree with @Rick James. Question worth replying is: What prevents a second worker to pick the same record while it's being processed by another? – thatsaru Dec 18 '17 at 14:05

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.