More DBA job interview questions and answers at
(Continued from previous question...)
Functionality Missing from MySQL - Transactions
As MySQL does nowadays support transactions, the following discussion is only valid if you are only using the
non-transaction-safe table types.
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
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
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.
``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.
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 tables for all their critical data.
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.
(Continued on next question...)