For those of you tired of this thread please excuse me, but
here is MySQL's current position statement on and discussion
about transactions:
Disclaimer: I just helped Monty write this partly in response to
some of the fruitful, to me, discussion on this list. I know
this is not crucial to mod_perl but I find the "wise men who
are enquirers into many things" to be one of the great things
about this list, to paraphrase old Heraclitus. I learn quite
a bit about quite many things by following leads and hints here
as well as by seeing others problems.
I'd love to see your criticism of the below either here or
off the list.
Ed
-----
The question is often asked, by the curious and the critical, "Why is
MySQl not a transactional database?" or "Why does MySQl not support
transactions."
MySQL has made a conscious decision to support another paradigm for
data integrity, "atomic operations." It is our thinking and experience
that atomic operations offer equal or even better integrity with much
better performance. We, nonetheless, appreciate and understand the
transactional database paradigm and plan, in the next few releases,
on introducing transaction safe tables on a per table basis. We will
be giving our users the possibility to decide if they need
the speed of atomic operations or if they need to use transactional
features in their applications.
How does one use the features of MySQl to maintain rigorous integrity
and how do these features compare with the transactional paradigm?
First, in the transactional paradigm, if your applications are written
in a way that is dependent on the calling of "rollback" instead of "commit"
in critical situations, then transactions are more convenient. Moreover,
transactions ensure that unfinished updates or corrupting activities
are not commited to the database; the server is given the opportunity
to do an automatic rollback and your database is saved.
MySQL, in almost all cases, allows you to solve for potential
problems by including simple checks before updates and by running
simple scripts that check the databases for inconsistencies and
automatically repair or warn if such occurs. Note that just by
using the MySQL log or even adding one extra log, one can normally
fix tables perfectly with no data integrity loss.
Moreover, "fatal" transactional updates can be rewritten to
be atomic. In fact,we will go so far as to say that all
integrity problems that transactions solve can be done with
LOCK TABLES or atomic updates, ensuring that
you never will get an automatic abort from the database, which is a
common problem with transactional databases.
Not even transactions can prevent all loss if the server goes down.
In such cases even a transactional system can lose data.
The difference between different systems lies in just how small
the time-lap is where they could lose data. No system is 100 % secure,
only "secure enough". Even Oracle, reputed to be the safest
of transactional databases, is reported to sometimes lose data
in such situations.
To be safe with MySQL you only need to have backups and have the update
logging turned on. With this you can recover from any situation that you could
with any transactional database. It is, of course, always good to have
backups, independent of which database you use.
The transactional paradigm has its benefits and its drawbacks. Many users
and application developers depend on the ease with which they can code around
problems where an "abort" appears or is necessary, and they may have to do
a little more work with MySQL to either think differently or write more.
If you are new to the atomic operations paradigm, or more familiar or more
comfortable with transactions, do not jump to the conclusion that MySQL
has not addressed these issues. Reliability and integrity are foremost
in our minds.
Recent estimates are that there are more than 1,000,000 mysqld servers
currently running, many of which are in production environments. We hear
very, very seldom from our users that they have lost any data, and in
almost all of those cases user error is involved. This is in our
opinion the best proof of MySQL's stability and reliability.
Lastly, in situations where integrity is of highest importance, MySQL's
current features allow for transaction-level or
better reliability and integrity.
If you lock tables with LOCK TABLES, all updates will stall until any
integrity checks are made. If you only do a read lock (as opposed to
a write lock), then reads and inserts are still allowed to happen.
The new inserted records will not be seen by any of the clients
that have a READ lock until they relaease their read locks.
With INSERT DELAYED you can queue insert into a local queue, until
the locks are released, without having to have the client to wait for
the insert to complete.
Atomic in the sense that we mean it is nothing magical, it only means
that you can be sure that while each specific update is running no other
user can interfere with it and that there will never be an automatic rollback
(which can happen on transaction based systems if you are not very careful).
MySQL also guarantees that there will not be any dirty reads.
We have thought quite a bit about integrity and performance and we believe that
our atomic operations paradigm allows for both high reliability and
extremely high performance, on the order of three to five times the
speed of the fastest and most optimally tuned of transactional databases.
We didn't leave out transactions because they are hard to do;
The main reason we went with atomic operations as opposed to
transactions is that by doing this we could apply many speed
optimizations that would not otherwise have been possible.
Many of our users who have speed foremost in their minds are not at all
concerned about transactions. For them transactions are not an issue.
For those of our users who are concerned with or have wondered about
transactions vis a vis MySQL, there is a "MySQL way" as we have
outlined above.
One final note: we are currently working on a safe replication schema that
we believe to be better than any commercial replication system we know of.
This system will work most reliably under the atomic operations,
non-transactional, paradigm. Stay tuned.
Perrin Harkins wrote:
> Greg Stark wrote:
> > For example, it makes it very hard to mix any kind of long running query with
> > OLTP transactions against the same data, since rollback data accumulates very
> > quickly. I would give some appendage for a while to tell Oracle to just use
> > the most recent data for a long running query without attempting to rollback
> > to a consistent view.
>
> I believe setting the isolation level for dirty reads will allow you to
> do exactly that.
Oh, silly me. Oracle doesn't appear to offer dirty reads. The lowest
level of isolation is "read committed" which reads all data that was
committed at the time the query began, but doesn't preserve that state
for future queries. So, if you have lots of uncommitted data or you
commit lots of data to the table being queried while the query is
running you could make your rollback segment pretty big. But, if you
can afford Oracle, you can afford RAM.
- Perrin
