CODEWITHSUNDEEP
phpmysqltransactionsinnodbmyisam
Wiliam
Wiliam

Reputation: 3754

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'];

Upvotes: 3

Views: 2802

Answers (1)

Mat
Mat

Reputation: 206879

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.

Upvotes: 5

Related Questions