InnoDB – How to Resolve Deadlocks Caused by Overly Eager Explicit Row Locking

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.