Sfrgttk

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)?

• 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 ).