Subject: RE: Mysql is atomic



> 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 gauntlet 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.