5

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?

7

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/

  • 1
    superb answer, with a very clear and insightful explanation, thanks a lot! – Marco Pantaleoni May 10 '18 at 7:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.