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();

$r开发者_JAVA技巧ow = $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'];

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.