Search code examples
phpmysqltransactionsinnodbmyisam

Are MySQL transactions atomic?

I have read that transactions are atomic in MySQL (InnoDB) but when I test the next code in 5 threads they select the same ID:

$db->beginTransaction();

$row = $db->fetchRow("SELECT * FROM atomic WHERE selected = 0 LIMIT 1");

sleep(5);

$db->update("atomic", array('selected' => 1), "id = " . $row['id']);

$db->commit();

echo "Selected row: " . $row['id'];
Solution

  • You should have a look at the FOR UPDATE keyword in this scenario.

    A simple select will not lock the selected rows, so what you are seeing in your example is perfectly normal.