PHP, MySQL, Table locks, Persistent Connections

So I’ve got:

• A MySQLtable of records that I want to process
• 3 x EC2 instances behind an ELB, each running the same PHP application called by
• Cron scheduler

I want the 3 identical EC2 instances for resilience: if one of them fails, there’ll still be 2 left running.

I want any given instance of the application to “claim” a row at a time for processing.

Because there are 3 instances, concurrency’s an issue: what if the following sequence of events happens?

1. Instance #1 reads the 1st unclaimed row from the table – ID 12345
2. Before instance #1 can mark that row as “claimed,” instance #2 reads the same, not-yet-claimed row, from the table (ID 12345).
3. Instance #1 marks row ID 12345 as “claimed”
4. Instance #2 marks row ID 12345 as “claimed”
5. Both instances go off to process the same record

My current answer to this is to use MySQL transactions and table locks. Effectively, each instance needs to attempt to run the following pseudo-SQL:

SET AUTOCOMMIT = 0;
LOCK TABLES my_table WRITE;

SELECT record_id FROM my_table
WHERE is_claimed = 0
ORDER BY create_datetime ASC
LIMIT 1;

[PHP notes my_table.record_id in variable]

UPDATE my_table
SET is_claimed = 1
WHERE record_id = $record_id;

COMMIT;

UNLOCK TABLES;

SET AUTOCOMMIT = 1;

This was working OK in itself. Then, I decided to knock the script over (by having PHP exit() after the SELECT query). I’d done a bit of reading that suggested MySQL would automatically unlock the table when the script finished.

BUT that didn’t happen.

The reason was, I’m running PHP as an Apache module, and I’d configured my application to use persistent MySQL database connections. IE, the database connection persists in between PHP script requests. Because of this configuration, the MySQL table remained locked, and – when I tried a query against table my_table in my desktop MySQL client, the query was hanging. MySQL recovered when I restarted Apache – which I guess has the effect of closing the database connection, which allows MySQL to release the locks.

So, when I re-configured my application not to use persistent DB connections, the problem went away: script failure results in the DB connection being closed, which results in MySQL releasing the table locks.