[SOLVED] Locking a MySQL INNODB row in PHP

Locking a MySQL INNODB row in PHP

I have a table called meta, with two columns name and value.

In a php script, which is called by many clients concurrently, I do this:-

$mysqli->multi_query("SELECT id FROM links WHERE id > (SELECT value FROM meta WHERE name='scan') LIMIT 1000;UPDATE meta SET value=value+1000 WHERE name='scan';");

or this:-

$mysqli->multi_query("SELECT id FROM links WHERE id > (SELECT value FROM meta WHERE name='scan' <b>FOR UPDATE</b>) LIMIT 1000;UPDATE meta SET value=value+1000 WHERE name='scan';");

Unfortunately, this doesn't appear to work as clients are ending up with duplicate id's. The database is heavily loaded and the SELECT takes a few seconds.

Solution

$mysqli->autocommit(FALSE);
$mysqli->query("BEGIN;");
$mysqli->multi_query("SELECT id FROM links WHERE id > (SELECT value FROM meta WHERE name='scan' FOR UPDATE) LIMIT 1000;UPDATE meta SET value=value+1000 WHERE name='scan';");
$mysqli->commit();

It's a complex issue; locking and transaction levels, but the magic above was the BEGIN statement. Without it, each statement was running in its own transaction level, and the FOR UPDATE lock was being unlocked too early.