Peter Schoenster wrote:
> On 5 Feb 99, at 11:54, Jack Killpatrick wrote:
>
> > Thanks very much for your post. I was thinking about record locking this
> > morning, as a matter of fact, and couldn't really figure out *why* it
> > would apply to my situation. AFAIK, all http requests are processed
> > sequentially (even if a nonosecond apart) and will be queued up, so it
> > would make sense that MySQL is going to get the commands in a sequence,
>
> Here is something pertinent to our thread posted by Greenspun:
>
> > I note with horror that many of the responses above imply that
> > supporting transactions is somehow a minor extra feature that you
> > don't really need. Actually, support for atomic transactions is the
> > whole point of installing a relational database. If you don't need
> > transactions, then you probably don't need a database management
> > system at all. If you do need transactions, you are courting disaster
> > if the power should fail while your machine is in the middle of doing
> > something. I wrote a whole book chapter about this...
> > http://photo.net/wtr/dead-trees/53011.htm
> >
> > -- Philip Greenspun, February 11, 1998
Yeah, actually I quoted that in an earlier part of this thread. The reason I
quoted it, though, was because I was making a sarcastic remark about "If you
don't need transaction, then you probably don't need a database management
system at all". Pfeh, tell that to my 87 tables.
> I infer that Greenspun is saying that atomicity cannot exist outside
> of transaction: that without the transactional control you do not
> have "atomic".
I must admit that I'm confused on the terminology. From what I've reasoned
out, atomicity refers to record level locking and transactions refer to a
sequence of queries where each query must execute successfully or all the
changes that were made are rolled back. Such as:
<start transaction>
query 1 pulls money out of checking account
query 2 puts money into savings account
<end transaction>
In a transaction, if query 2 fails, then the changes to query 1 are
reversed. Or, more likely, the changes to query 1 are not committed until
query 2 has been completed. Transactions such as this put extra overhead on
the db and on RAM because the queries in the sequence have to be kept in
cache or temp tables until the transaction is fully completed.
Can anyone correct me if I'm wrong?
> On another page Greenspun says:
>
> > Atomicity
> >
> > Results of a transaction's execution are either all committed or all
> > rolled back. All changes take effect, or none do. That means, for Joe
> > User's money transfer, that both his savings and checking balances are
> > adjusted or neither are.
So perhaps atomicity is really just a term to define the elements of the
transaction and not to refer to record-level locking (although record-level
locking may be a part of the transaction funtion)? This definition would not
quite make sense when compared to the way Monty as MySQL used it:
Peter wrote:
> > What happens if more than one person votes for the same choice
> > at the same time?
> >
> > Will there be a queue? If I do the following:
> >
> > UPDATE $table SET votes=votes+1 WHERE choice = '$f{choice}'
> >
> > and more than one person clicks update at the same time, what
> > will happen?
> __end of my questions
>
> > Response from creator of MySQL:
>
> > All commands in MySQL are atomic; The above is safe to use without
> > any problems! This is documented in the MySQL manual; Search after
> > 'locking'!
So what is it? Does atomicity refer to locking?
> I would have to agree,given that definition,that mysql does not
> fullfill that requirement.
Yes, given Greenspan's definition, but there is a confusion between the
terms "transaction" and "atomicity", as far as I can tell. Either way, MySQL
does not do transaction handling (rollback) and does not do record-level
locking (which I won't need/can't use since the interface is web based), but
it *does* que up updates and do them in order. So my decision really comes
down to two things now: do I need transactions (as defined by the rollback
method above) and (from earlier in the thread) can I workaround the lack of
INNER JOIN's? Mike Stone gave me some ideas on implementing the workaround,
but I have not had time to do tests yet.
> But this lack of atomicity as defined above does not mean that you
> need fear multiple simultaneous updates of the database with MySQL.
Wonder if Greenspan and Monty can get together and define the terms so we're
clear? Heh. Or am I just the one who's confused?
BTW, do you know of a searchable MySQL listserve archive, other than the one
at organic.com?
Thanks,
Jack
____________________________________________________________________
--------------------------------------------------------------------
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
---------------------------------------------------------------------
