0

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.

  • 1
    Please add a tag for your DBMS. – David Browne - Microsoft Dec 8 '18 at 23:55
  • "Queued jobs"?? Multiple *nix processes? Java Threads? Multiple webservers getting multiple requests? What? – Rick James Dec 9 '18 at 1:24
  • Do you have a 'composite' INDEX(slack_channel_id, message_id) (in either order)? – Rick James Dec 9 '18 at 1:25
  • @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. – Webnet Dec 9 '18 at 18:09
  • 1
    Please provide SHOW CREATE TABLE message; I think we need to change the PRIMARY KEY and/or some secondary keys. – Rick James Dec 9 '18 at 18:27
4
+50

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.

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.