Mysql – Concurrent update queries locking rows when `where` clause doesn’t match

concurrencylockingMySQL

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.

Best Answer

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.

Related Solutions

Mysql – Finding rows for a specified date range

Another way to get the result is this. It finds first all groups that the teacher has surely taught (or is going to) by checking that she has started within the month and then in another subquery it finds - for every group - the last teacher that started at the first day of the month or earlier.

With the unique index you have on the table, the second subquery should be quite efficient. The first subquery would benefit from an index on (teacherid, startdate, groupid):

SELECT groupid
FROM pupilgroupteacher
WHERE teacherid = @teacher 
  AND startdate >= @month + INTERVAL 1 DAY
  AND startdate < @month + INTERVAL 1 MONTH

UNION DISTINCT

SELECT gg.groupid
FROM 
    ( SELECT DISTINCT groupid
      FROM pupilgroupteacher
    ) AS gd
  JOIN pupilgroupteacher AS gg
    ON  gg.groupid = gd.groupid
    AND gg.startdate =
        ( SELECT MAX(gi.startdate)
          FROM pupilgroupteacher AS gi 
          WHERE gi.groupid = gd.groupid
            AND gi.startdate < @month + INTERVAL 1 DAY
        )
WHERE gg.teacherid = @teacher ;

Postgresql – Improve performance on concurrent UPDATEs for a timestamp column in Postgres

Would a different kind of column be faster? For example an integer

No. timestamp and timestamptz are just unsigned 64-bit integers internally anyway.

Is there some way to not lock the column?

It doesn't lock the column. It takes weak table lock that doesn't really block anything except DDL, and takes a row level lock on the row you're updating.

There is no way to prevent the row level lock. It exists because without it behaviour and ordering concurrent updates would be undefined. We don't like undefined behaviour in RDBMSs.

It only blocks concurrent updates of the same row anyway.

Any other tips to improve this?

Not with the detail provided. There's likely a better way to do what you're trying to do, but it'll probably involve taking a few steps back and looking for a different strategy for solving the underlying problem.

In the specific case of cache invalidation I think you might want to look into LISTEN and NOTIFY. Again though, there just isn't enough info here to go on.

Best Answer

Related Solutions

Mysql – Finding rows for a specified date range

Postgresql – Improve performance on concurrent UPDATEs for a timestamp column in Postgres

Related Question