innodb_lock_wait_timeout timeout is ignored

Question

I'm trying to do MySQL locks implementation in PHP (I need both shared and exclusive, so I can't use GET LOCK).

<?php
$conn = new \Pdo('mysql:host=localhost;dbname=locks', 'root', 'root', [\PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$conn->exec('INSERT IGNORE INTO locksPoc (lockName) VALUES ("mylock")');
$conn->exec('set session innodb_lock_wait_timeout = 1');
$this->dump('Lock exists in DB');
$conn->beginTransaction();
$rows = $conn->query('SELECT * FROM locksPoc WHERE lockName="mylock" FOR UPDATE');
$row = $rows->fetchAll();
$this->dump('Got the lock');
sleep(30);
$conn->commit();

When I run it in parallel like this:

$ php db.php &
$ php db.php &

It does not work as expected. The second one outputs "Lock exists in DB" immediately but then proceeds to wait 30s before the other script is finished. My expectation was that it will fail after 1s with exception because of the lock timeout.

Immunotherapy

SHOW VARIABLES LIKE '%timeout'; – Glittery

Please provide SHOW CREATE TABLE locksPoc -- we need to see that lockName has a unique index (or PK). – Glittery

Please add sleep(10); after the INSERT`. This should make sure of how the processes are aligned. – Glittery

Answer 1

The answer is revealed if you use SHOW PROCESSLIST:

mysql> show processlist;
+----+-----------------+-----------+-------+---------+--------+------------------------+----------------------------------------------------------+
| Id | User            | Host      | db    | Command | Time   | State                  | Info                                                     |
+----+-----------------+-----------+-------+---------+--------+------------------------+----------------------------------------------------------+
| 24 | root            | localhost | test2 | Query   |      6 | update                 | INSERT IGNORE INTO locksPoc (lockName) VALUES ("mylock") |

The second invocation of the script is waiting on the INSERT, which is executed by your script before you change the innodb_lock_wait_timeout to 1 second. The default is 50 seconds.

INSERT IGNORE does not change locking behavior or timeouts. It only makes errors downgrade to warnings. The timeout is still 50 seconds when you run your INSERT.

I have to observe that it looks like you're trying to implement a feature for named advisory locking that is already built-in to MySQL with the GET_LOCK() function. That function already has a timeout argument, so you don't have to rely on changing the InnoDB row-level locking timeout.

Gnostic

Thanks for the explanation. I'm trying to implement the locking. Unfortunatelly GET_LOCK does not support shared locks, which are a must-have in my situation. – Smirk