Overly eager explicit row locking causes deadlock

Question

I'm running into deadlock problems on MariaDB using the INNODB engine.

In my use case, I've got a number of threads working on a table. Each thread first reads a number of rows and then updates those. I'm using a SELECT ... WHERE id IN (...) statement to load all relevant rows at once. I have been using LOCK IN SHARE MODE to ensure that other threads don't cause corruption.

While testing this on a table with a significantly reduced data set I have been experiencing deadlocks - although each thread works on a distinct set of rows. It seems the SELECT ... WHERE id IN (...) locks all rows when the number of ids exceeds a specific fraction of the size of the table. This causes a deadlock as threads attempt to acquire X locks on records which are S-locked by (all) other threads.

I've replicated this behaviour using 10.3.12-MariaDB-1:10.3.12+maria~xenial on a sample data set.

Setup:

CREATE TABLE items (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    some_col bigint unsigned,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO items (some_col)
    VALUES
        (12),
        (36),
        (72),
        (11),
        (81),
        (53),
        (28),
        (37),
        (58),
        (87)
;

Steps using two threads:

1. Thread A: Acquire locks

   START TRANSACTION;
   SELECT * FROM items WHERE id IN (1, 2, 3, 4, 5) LOCK IN SHARE MODE;
   

   (Using WHERE id = 1 OR id = 3 OR ... has the same effect.)

2. Thread B: Acquire locks

   START TRANSACTION;
   SELECT * FROM items WHERE id IN (6, 7, 8, 9, 10) LOCK IN SHARE MODE;
   

3. Thread A: Update row with id = 1

   UPDATE items SET some_col=0 WHERE id=1;
   

   This thread hangs waiting for the X lock on the record with id = 1.

4. Thread B: Update row with id = 6

   UPDATE items SET some_col=1 WHERE id=6;
   

   At this point MariaDB will rollback one of the transactions because it detects a deadlock.

   ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
   

   SHOW ENGINE INNODB STATUS; indicates that thread A holds S locks on all records in the items table, not just the one returned by the SELECT query in step 1.

When using separate SELECT queries for locking in step 1, this issue does not arise.

Thread A: Acquire locks

SELECT * FROM items WHERE id = 1 LOCK IN SHARE MODE;
SELECT * FROM items WHERE id = 2 LOCK IN SHARE MODE;
SELECT * FROM items WHERE id = 3 LOCK IN SHARE MODE;
SELECT * FROM items WHERE id = 4 LOCK IN SHARE MODE;
SELECT * FROM items WHERE id = 5 LOCK IN SHARE MODE;

The documentation of LOCK IN SHARE says that "… a lock is acquired on the rows read by the query …". The observed behaviour indicates that this means all rows looked at, not just the ones selected. ANALYZE format=json confirms that the access type is ALL for the SELECT query from step 1.

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 0.0562,
    "table": {
      "table_name": "items",
      "access_type": "ALL",
      "possible_keys": ["PRIMARY"],
      "r_loops": 1,
      "rows": 10,
      "r_rows": 10,
      "r_total_time_ms": 0.0373,
      "filtered": 50,
      "r_filtered": 50,
      "attached_condition": "items.`id` in (1,2,3,4,5)"
    }
  }
}

Is this correct? I.e. does LOCK IN SHARE MODE lock all rows looked at during query evaluation and not just the ones selected?

Is there a reliable way to read / lock only the rows selected (without using individual SELECT statements)?

---

Some more observations about INs locking behaviour:

• When there is only one id in the IN list, the access type is shown by ANALYZE as const and no "over-eager" locking occurs.
• When there are multiple ids (less than 50 % of the rows in the table) in the IN list, the access type is shown by ANALYZE as range and no "over-eager" locking occurs.
• When there are more ids in the IN list than 50 % of the row count of the table, the access type is shown by ANALYZE as all and "over-eager" locking occurs.

Answer 1

Is this correct? I.e. does LOCK IN SHARE MODE lock all rows looked at during query evaluation and not just the ones selected?

Yes, that looks to be the case, assuming you are using READ-REPEATABLE isolation level (the default).

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range.

Is IN considered a range type search?

If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search.

While it doesn't explicitly use the term "range", the description looks like, especially when you consider the that other option from the READ-REPEATABLE description is "unique condition", which definitely doesn't apply to multiple values.

So to answer that question, yes, it appears that LOCK IN SHARE MODE is locking more than just the rows selected.

As for your other question...

Is there a reliable way to read / lock only the rows selected (without using individual SELECT statements)?

Is there some reason you don't want to use individual SELECT statements?

You could try the following:

SELECT * FROM items WHERE id = 1 LOCK IN SHARE MODE
UNION ALL
SELECT * FROM items WHERE id = 2 LOCK IN SHARE MODE
UNION ALL
SELECT * FROM items WHERE id = 3 LOCK IN SHARE MODE
UNION ALL
SELECT * FROM items WHERE id = 4 LOCK IN SHARE MODE
UNION ALL
SELECT * FROM items WHERE id = 5 LOCK IN SHARE MODE;

That is a single query, although technically still separate statements. I do something similar in my application. Although, I use MySQL, but still InnoDB, and I use FOR UPDATE, instead of LOCK IN SHARE MODE (I've never been able to wrap my head around why I would want to use LOCK IN SHARE MODE, but I digress.)

So, it should probably work.

If that is not sufficient, can you clarify why you don't want to use multiple SELECT statements?

Answer 2

• Would it suffice to SELECT ... FOR UPDATE instead?

• Can we look at the entire transaction? Speeding it up will decrease the frequency of deadlocks.

• Even if you find a way around this deadlock, you must prepare to get deadlocks. Usually, it suffices to re-run starting with the START TRANSACTION.

Another approach might prevent the "over-eager" problem:

Put the list of ids in a table (possibly a TEMPORARY table). Then JOIN to it instead of using IN ( big list ).