Posted by ZeldorBlat on 06/18/07 13:33

On Jun 18, 9:25 am, evanp...@gmail.com wrote:
> 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.

Single statements are atomic. Unless you need to execute multiple
statements there's no reason to use transactions. And I wouldn't be
surprised if MySQL was able to execute that particular statement in
less than a second.

[Back to original message]