We have the following database specifications:

version: 10.1.47-MariaDB-0ubuntu0.18.04.1
innodb_version: 5.6.49-89.0
tx_isolation: READ-COMMITTED
innodb_strict_mode: ON
autocommit: ON

We have the problem that we lock too many data entries in the table, which causes deadlocks. Here is our example.

The first query we run:

UPDATE a SET col4 = 3 WHERE col1 = 1 AND col2 = 2 AND col3 = 3;

We have single column indexes on all the columns. According to the docs: this will set an exclusive next-key lock on every record the search encounters.

So the next UPDATE statement will be waiting for the previous one, although the matching final result records are not overlapping. This is because the index on col1.

UPDATE a SET col4 = 2 WHERE col1 = 1 AND col2 = 9999;

I would like to lock only the final result records and not "every record the search encounters". Splitting the query into a SELECT and an UPDATE by primary key gives me that result, but then a racing condition can happen between the SELECT and the UPDATE. Combining those in a transaction also doesn't help, as every SELECT is a consistent read.

How would you design a process like this, where I can run an UPDATE statement, without locking every record the search encounters?

  • While the final result sets might not overlap, your two UPDATE queries do overlap within the shared index for col1 = 1 (aka "every record the search encounters"), and therefore potentially at the page level. Therefore changes you make in the first UPDATE could affect the physical location of the data referenced in the second UPDATE on disk. This is likely why the locking occurs as it does, and I'm not sure there's a reliable way around it (but I can't say for sure).
    – J.D.
    Jan 15, 2021 at 15:52
  • That makes sense, yes. Like you said, I am also curious if there are people that have a smart way around it.
    – Daan
    Jan 15, 2021 at 15:54
  • "there is an exclusive next-key lock on the col1 column" -- locks are on rows, not columns. "without locking every record the search encounters" -- you need to help the statement encounter fewer rows, by supplying appropriate indices.
    – mustaccio
    Jan 15, 2021 at 16:09
  • 1
    Can you replace your index on col1 with a composite index on col1,col2? If there's many rows that share the same col1 value then this will also obviously speed up locating the rows that need updating too - (ditto if you add col3) Jan 15, 2021 at 16:14
  • 1
    @Daan , if the system recognises that the composite index will give you better selectivity against the table and index then it should use that instead. I'm not sure how decent MySQL would be and identifying this and if it's only locking the index key of the index used to access the rows (I wouldn't expect it to lock the index key for every index for the rows modified as that would be pretty bad) Jan 15, 2021 at 16:38

1 Answer 1

UPDATE a SET col4 = 3 WHERE col1 = 1 AND col2 = 2 AND col3  = 3;

Needs this composite index:

INDEX(col1, col2, col3)  -- in any order

So, if you start it with col1, col2 (in either order), that index will also work nicely for

UPDATE a SET col4 = 2 WHERE col1 = 1 AND col2 = 9999;

If, by "We have indexes on all the columns" you mean single-column indexes, then those are wasted and in the way. MySQL will use only one index. It will pick among (col1), (col2), (col3) looking for the most selective. Then it will scan all the rows with, say col1 = 1. This is likely to be a lot more rows than you need to UPDATE.

With the composite index I recommend, it will go straight to the row(s) that match all of the WHERE clause. This will be many fewer rows, thereby decreasing the likelihood of a deadlock. Also, it will be much faster, which also decreases the chance of a deadlock.

Regardless, your could should check for errors and re-run any transaction that hits a deadlock.

  • Thanks for your solution. Do you mean with "it will pick the most selective" that an index on 1 column will always be picked before an index with multiple columns? And do you know any documentation there is about this behavior?
    – Daan
    Jan 15, 2021 at 18:14
  • @Daan - A 2-column index will always be picked before a 1-column index where that 1 column is the first column of the other index. Furthermore, you should (in most situations) drop INDEX(a) when you have INDEX(a, ...) since the 1-col index has become useless and there is no need to tempt the Optimizer.
    – Rick James
    Jan 15, 2021 at 18:17
  • @Daan - And before you ask, "selectivity" is based on the whole index, not each column.
    – Rick James
    Jan 15, 2021 at 18:18
  • tnx, that makes sense. So can I safely conclude that if you have an index set (one-column or composite) that is completely covered by another index, the first one should be removed?
    – Daan
    Jan 18, 2021 at 10:32
  • 1
    @Daan - Yes; if "left part". That is INDEX(c,b,a) handles whatever INDEX(c,b) and INDEX(c) handle. But no other cases are handled -- not (a), (a,b), (c,d), (c,a), etc.
    – Rick James
    Jan 18, 2021 at 18:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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