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?
Best Answer
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:On another session:
You have several options:
Increase your concurrency by relaxing your transaction isolation level (but ghost reads can happen):
On another session, now the session succeeds:
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/