Concurrent update queries locking rows when `where` clause doesn't match

Question

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 slack_server_id, slack_channel_id and 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 where clause?

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;

Update

I've added an id auto increment primary column and it did not resolve the transaction issues.

• David Browne - Microsoft: Please add a tag for your DBMS.
• Rick James: "Queued jobs"?? Multiple *nix processes? Java Threads? Multiple webservers getting multiple requests? What?
• Rick James: Do you have a 'composite' INDEX(slack_channel_id, message_id) (in either order)?
• Ben: @RickJames Yes, that's what I meant by compound. It's a unique index on those columns. "queued job" is a term used by the framework, at the end of the day that verbiage is intended to communicate the concurrency.
• Rick James: Please provide SHOW CREATE TABLE message; I think we need to change the PRIMARY KEY and/or some secondary keys.

Answer 1

A lesson to learn about indexing. INDEX(a,b,c) is not optimal for WHERE a=1 AND c=2. This is because only the leftmost column(s) of an index will be used; there cannot be any skipping over columns (b, in this example).

Back to your question.

INDEX(slack_channel_id, message_id)

is needed, and

INDEX(`slack_id`,`slack_channel_id `,`message_id`)

is not useful at all. slack_id is in the way.

KEY `slack_message_message_id_foreign` (`message_id`),

will be partially useful -- namely for narrowing the search down to those rows with the desired message_id.

A Cookbook on building optimal indexes.