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 sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 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
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?