MariaDB - "ERROR 1205 ... Lock wait timeout exceeded; ..." when doing multiple parallel UPDATEs affecting different rows

We are using MariaDB 10.1.19 hosting a legacy database. We have an lpr table (on InnoDB) where we want to move from a text column gate to a normalized foreign key gate_id. Our code is doing the UPDATE in parallel, like this:

UPDATE lpr SET gate_id=1 WHERE gate_id IS null AND gate LIKE '%[1]%'
UPDATE lpr SET gate_id=2 WHERE gate_id IS null AND gate LIKE '%[2]%'
UPDATE lpr SET gate_id=3 WHERE gate_id IS null AND gate LIKE '%[3]%'
UPDATE lpr SET gate_id=4 WHERE gate_id IS null AND gate LIKE '%[4]%'
...

since the rows affected by all the UPDATEs are disjoint, and the table is on InnoDB, we wouldn't expect lock contention, but we get the error:


ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

for all the UPDATEs. Increasing innodb_lock_wait_timeout to ~100 doesn't change anything, as well as setting FOREIGN_KEY_CHECKS to 0.

The gate column never contains more than one [VALUE] string.

If we do a single UPDATE by hand, no such error occurs.

Doing a SHOW FULL PROCESSLIST doesn't show any lock during the UPDATEs.

What are we doing wrong? Is there a table-level lock even if using InnoDB?
Or do the UPDATEs lock more rows than those strictly selected by the WHERE clause?

What are we doing wrong? Is there a table-level lock even if using InnoDB?

Your problem is the lack of a proper index to use. InnoDB does next-key locking, meaning it will lock only the rows that it will update, but also the gaps in-between using the look up index. Because no proper index can be used for the given filter gate LIKE '%[1]%' it doesn't technically do a table lock, but it setups a lock on every single row gap according to the query plan (locking all rows).

I've recreated your structure, and SHOW ENGINE INNODB STATUS gives us all the information we need:

mysql> create table lpr (gate_id int, gate varchar(10));
Query OK, 0 rows affected (0.21 sec)

mysql> insert into lpr values (1, 'wqer[1]sd');
Query OK, 1 row affected (0.11 sec)

mysql> insert into lpr values (2, 'wqer[2]sd');  
Query OK, 1 row affected (0.06 sec)

mysql> insert into lpr values (3, 'wqer[2]sd'); 
Query OK, 1 row affected (0.03 sec)

mysql> insert into lpr values (4, 'wqer[4]sd');  
Query OK, 1 row affected (0.05 sec)

mysql> insert into lpr values (5, 'wqer[5]sd');  
Query OK, 1 row affected (0.09 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE lpr SET gate_id=1 WHERE gate_id IS null AND gate LIKE '%[1]%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SHOW ENGINE INNODB STATUS\G
...
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 4126, ACTIVE 8 sec
2 lock struct(s), heap size 1136, 6 row lock(s)
MySQL thread id 8, OS thread handle 140628280551168, query id 21 localhost root starting
SHOW ENGINE INNODB STATUS

On another session:

mysql> UPDATE lpr SET gate_id=2 WHERE gate_id IS null AND gate LIKE '%[2]%'
    -> ;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

---TRANSACTION 4127, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 9, OS thread handle 140628236515072, query id 28 localhost root updating
UPDATE lpr SET gate_id=2 WHERE gate_id IS null AND gate LIKE '%[2]%'
------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4 page no 4 n bits 72 index GEN_CLUST_INDEX of table `enwiki`.`lpr` trx id 4127 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000000200; asc       ;;
 1: len 6; hex 000000001011; asc       ;;
 2: len 7; hex 82000001060110; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 9; hex 777165725b315d7364; asc wqer[1]sd;;

You have several options:

-
Increase your concurrency by relaxing your transaction isolation level (but ghost reads can happen):

mysql> SET SESSION transaction_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE lpr SET gate_id=1 WHERE gate_id IS null AND gate LIKE '%[1]%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

On another session, now the session succeeds:

mysql> SET SESSION transaction_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE lpr SET gate_id=2 WHERE gate_id IS null AND gate LIKE '%[2]%'
-> ;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

-
Improve your design and/or queries so you only use properly indexed queries for improved concurrency.

More about concurrency and gap locking: https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/

StackExchange Database Administrators Q#206270, answer score: 12