Posted by evanpeck on 06/18/07 13:25

I'm not really sure if my problem lies in the PHP code or the SQL...
but here it is:

begin();

$query = "UPDATE Nodes "
."SET NodeStatus = 1 "
."WHERE NodeID = $NodeID AND NodeStatus = 0";

$result = mysql_query($query);

if ($result == null || $result == false) {
rollback();
return;
}


In the Nodes table, there should be only one instance of a node with a
particular NodeID.

So, presumably, user1 will hit the UPDATE and change the NodeStatus to
1. Then, when user2 hits the UPDATE, it should fail, since the
NodeStatus =1 (and a condition of the update is that the NodeStatus
=0).

However, when multiple users simultaneously (at least down to the
second, according to logs) access the method, they ALL succeed in the
UPDATE query. I don't understand how this can happen. I'm assuming
that the UPDATE operation is atomic in mySQL.

I feel like I shouldn't have to use locks.. but I'm considering it
with the terrible results so far.

[Back to original message]