My application sends messages to Slack. On very rare occasions we may need to retract the message going out. These retractions are handled by queueing a job for each
messageId that has been created thus far.
I noticed during our last retraction about a week ago that we saw about 2k:
try restarting transaction (SQL: update `message` set `deleted_at` = 2018-12-04 04:47:44 where (`slack_channel_id` = xxxxxxxxxxxxxxxx and message_id = xxxxxxxxxxxxxxxx))"
It seems like the queue jobs (which are absolutely happening concurrently) are stepping on each other's toes. My expectation is that this query would only put a lock on the individual row it needs (we have a compound unique index on
message_id), but it seems like more than one job is locking this record.
Why are these concurrent update queries locking rows that don't match the
CREATE TABLE `messages` ( `slack_id` bigint(20) unsigned NOT NULL, `slack_channel_id` bigint(20) unsigned NOT NULL, `message_id` bigint(20) unsigned NOT NULL, `slack_message_id` bigint(20) unsigned DEFAULT NULL, `premium` tinyint(1) NOT NULL DEFAULT '0', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, UNIQUE KEY `slack_message_slack_id_channel_id_message_id_unique` (`slack_id`,`slack_channel_id `,`message_id`), KEY `slack_message_message_id_foreign` (`message_id`), CONSTRAINT `slack_message_slack_id_foreign` FOREIGN KEY (`slack_id`) REFERENCES `slacks` (`id`), CONSTRAINT `slack_message_message_id_foreign` FOREIGN KEY (`message_id`) REFERENCES `messages` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
I've added an
id auto increment primary column and it did not resolve the transaction issues.