GNU Info

(mysql.info)Missing Transactions

---

Next: Missing Triggers Prev: Missing SELECT INTO TABLE Up: Missing functions

---

Enter node , (file) or (file)node

Transactions
............

As MySQL does nowadays support transactions, the following discussion
is only valid if you are only using the non-transaction-safe table
types. Note: COMMIT.

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, within the next few releases,
to introduce 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 committed 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 to be, 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
indicate 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 obtain 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 release their read
locks.  With `INSERT DELAYED' you can queue inserts into a local queue,
until the locks are released, without having the client wait for the
insert to complete. Note: INSERT DELAYED.

"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 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.  You can find some example of how to write atomic
updates in the commit-rollback section. Note: Commit-rollback.

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.  For those where safety is more important than speed,
we recommend them to use the `BDB', or `InnoDB' tables for all their
critical data. Note: Table types.

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.