servermanfail servermanfail - 2 months ago 13
MySQL Question

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.

Answer
$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.

Comments