SEO

#1 (**permalink**) 02-28-2008, 10:03 AM

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.

#2 (**permalink**) 02-28-2008, 10:03 AM

== Quote from evanpeck ( evanpeck@gmail.com)'s article
> 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.

you don't need to consider locks; however, you do need to look at your isolation
level. can you tell us what is the isolation level of mysql server. it can be
found in the my.cnf .

--
POST BY: lark with PHP News Reader

#3 (**permalink**) 02-28-2008, 10:03 AM

Unfortunately, I wasn't the person who originally set up the server,
as I am continuing work from last year. I also don't have access to
that file. I'm assuming it is the default setting, however.

#4 (**permalink**) 02-28-2008, 10:03 AM

== Quote from evanpeck ( evanpeck@gmail.com)'s article
> Unfortunately, I wasn't the person who originally set up the server,
> as I am continuing work from last year. I also don't have access to
> that file. I'm assuming it is the default setting, however.

an obvious question i forgot to ask is "are you sure you're committing?" and if
not, maybe you should do an explicit commit to ensure that the updates take place.
--
POST BY: lark with PHP News Reader

#5 (**permalink**) 02-28-2008, 10:03 AM

Thank you for the help.

I found a solution, however. I thought that UPDATE would 'fail' if it
did not update anything. Instead, it only fails if there is an error.

On Jun 18, 10:37 am, lark <ham...@sbcglobal.net> wrote:
> == Quote from evanpeck ( evanp...@gmail.com)'s article
>
> > Unfortunately, I wasn't the person who originally set up the server,
> > as I am continuing work from last year. I also don't have access to
> > that file. I'm assuming it is the default setting, however.
>
> an obvious question i forgot to ask is "are you sure you're committing?" and if
> not, maybe you should do an explicit commit to ensure that the updates take place.
> --
> POST BY: lark with PHP News Reader

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Is UPDATE Atomic?