> Which mailing list? Did the user get any respones to the above?
> The above clearly indicates that he doesn't have a clue of what
> he is talking about, so you can safely ignore him :)
well, it would be a shame to let a gauntet like that go to
waste.. ;-)
> > UPDATE $table SET votes=votes+1 WHERE choice = '$f{choice}'
> >
> > and more than one person clicks update at the same time, what
> > will happen?
>
> > Response from creator of MySQL:
>
> > All commands in MySQL are atomic; The above is safe to use
> > without any problems!
confident, but questionable. it's thinking like that which
made the Therac 25 famous.
the query above is composed of three parts.. an implicit read, a
calculation, and an explicit write. i don't know the internals
of the database well enough to judge the granularity of those
operations, but it's easy enough to find out with a stress test.
launch 50 processes whose main loop is:
for (0..99999) {
UPDATE $table SET x=x+1
}
in parallel with another 50 whose main loop is:
for (0..99999) {
UPDATE $table SET x=x-1
}
assuming the whole update is truly atomic, x will end with
exactly the same value as it started. if the implicit query on
the right hand side of the assignment is actually a separate
operation, and not an atomic part of the UPDATE, what you have
is an effective, if costly, random number generator.
assuming the version above works as it should, this one probably
won't:
for (0..99999) {
$x = SELECT $table x;
$x++;
UPDATE $table SET $x;
}
--------
for (0..99999) {
$x = SELECT $table x;
$x--;
UPDATE $table SET $x;
}
because the three operations are now explicitly non-atomic.
eventually, one process is bound to swap out between reading and
writing. the value in the database will be invalid, but other
processes will still be able to read it as they swap past. by
the time the first process comes back into context and completes
its write, any number of other processes can be chewing on
inaccurate data.
the voting model you outlined doesn't need record locking,
because you're working with simple values, and the order in
which votes arrive doesn't matter. each vote is fundamentally
independent of the others, so what's to lock? a guestbook
would be roughly equivalent. the individual entries are more
complex, but they're still independent of each other. MySQL
could support the biggest usenet flamewar you can imagine
without ever breaking a sweat.
OTOH, the place where you need locking is in a read-edit-write
cycle. the prosecution submits that MySQL would really suck as
the revision control system for the linux source tree.
the voting model in your example was write-only, so let's change
it a little.. instead of just ticking over an odometer every
time a vote comes in, let's set up a two-stage affair:
first, the user queries a script that creates a set of links
like so:
<br><a href="v2.pl?i1=XXX&i2=yyy&i3=zzz">vote for item 1</a>
<br><a href="v2.pl?i1=xxx&i2=YYY&i3=zzz">vote for item 2</a>
<br><a href="v2.pl?i1=xxx&i2=yyy&i3=ZZZ">vote for item 3</a>
where xxx is the current number of votes for item 1, and XXX is
the next larger increment. the script which catches the
request simply reads the values for each item and plugs them
back into the database.
now set the users on it and see what happens. the information
which goes to the user is no longer independent of any other
submission. recording what i chose erases what you chose, and
a system without locks has no way of preventing that.
yes, the example is trivial, but you can draw analogies between
that model and an inventory-linked e-commerce system. if
you're selling concert tickets, and a thousand people are trying
to locate, reserve, and purchase the twenty seats at front row
center, you have some important synchronization issues to deal
with.
in its proper domain of application MySQL is a useful and
powerful tool. that domain happens to be high read volume data
access with purely sequential or purely independent writes to
any given record. it's not good in domains which involve high
concurrent write volumes of interdependent data, because it
doesn't have the built in tools to support that.
the fact that you can do some pretty cool things with
transaction handling doesn't make MySQL useless.. you don't need
sequential rollback capacity for a guestbook. OTOH, the fact
that you can write a really kickass guestbook with MySQL doesn't
make record locking or transaction handling useless either.
mike stone <[EMAIL PROTECTED]> 'net geek..
been there, done that, have network, will travel.
____________________________________________________________________
--------------------------------------------------------------------
Join The NEW Web Consultants Association FORUMS and CHAT:
Register Today at: http://just4u.com/forums/
Web Consultants Web Site : http://just4u.com/webconsultants
Give the Gift of Life This Year...
Just4U Stop Smoking Support forum - helping smokers for
over three years-tell a friend: http://just4u.com/forums/
To get 500 Banner Ads for FREE
go to http://www.linkbuddies.com/start.go?id=111261
---------------------------------------------------------------------
