PHP and mySQL concurrency problem

[evanpeck]

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.

[ZeldorBlat]

On Jun 18, 9:25 am, [e-mail] 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.

[Jerry Stuckle]

ev******@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.

Not finding any rows to update is not a failure. It's a success with no
rows updated.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Jerry Stuckle]

ev******@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.

P.S. Please crosspost - don't multipost.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[evanpeck]

I don't have the whole code pasted, but there is an INSERT that is
dependent on whether the UPDATE succeeds or fails - that is why I'm
using transactions.

[evanpeck]

Thank you.

So I'm assuming I'll have to run a following SELECT statement to test
whether the UPDATE was successful?

[evanpeck]

Nevermind. I found a better solution.

Thank you again.

[Toby A Inkster]

evanpeck wrote:

Nevermind. I found a better solution.

It's always nice to post what that "better solution" is for the benefit of
those who find this thread in a few months time.

I'm guessing it involves executing the UPDATE and then asking MySQL how
many rows were changed.

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 114 days, 23:33.]

dict, thes & ency
http://tobyinkster.co.uk/blog/2007/0...ict-thes-ency/