Transactions and Atomic Operations

MySQL Server (version 3.23-max and all versions 4.0 and above) supports transactions with the InnoDB and BDB transactional storage engines. InnoDB provides full ACID compliance. See Chapter 14, Storage Engines. For information about InnoDB differences from standard SQL with regard to treatment of transaction errors, see Section 14.2.12, “InnoDB Error Handling”.

The other nontransactional storage engines in MySQL Server (such as MyISAM) follow a different paradigm for data integrity called atomic operations. In transactional terms, MyISAM tables effectively always operate in autocommit = 1 mode. Atomic operations often offer comparable integrity with higher performance.

Because MySQL Server supports both paradigms, you can decide whether your applications are best served by the speed of atomic operations or the use of transactional features. This choice can be made on a per-table basis.

As noted, the tradeoff for transactional versus nontransactional storage engines lies mostly in performance. Transactional tables have significantly higher memory and disk space requirements, and more CPU overhead. On the other hand, transactional storage engines such as InnoDB also offer many significant features. MySQL Server's modular design enables the concurrent use of different storage engines to suit different requirements and deliver optimum performance in all situations.

But how do you use the features of MySQL Server to maintain rigorous integrity even with the nontransactional MyISAM tables, and how do these features compare with the transactional storage engines?

  • If your applications are written in a way that is dependent on being able to call ROLLBACK rather than COMMIT in critical situations, transactions are more convenient. Transactions also 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.

    If you use nontransactional tables, MySQL Server in almost all cases enables you to resolve 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 an inconsistency occurs. You can normally fix tables perfectly with no data integrity loss just by using the MySQL log or even adding one extra log.

  • More often than not, critical transactional updates can be rewritten to be atomic. Generally speaking, all integrity problems that transactions solve can be done with LOCK TABLES or atomic updates, ensuring that there are no automatic aborts from the server, which is a common problem with transactional database systems.

  • To be safe with MySQL Server, regardless of whether you use transactional tables, you only need to have backups and have binary logging turned on. When that is true, you can recover from any situation that you could with any other transactional database system. It is always good to have backups, regardless of which database system you use.

The transactional paradigm has its advantages and disadvantages. Many users and application developers depend on the ease with which they can code around problems where an abort appears to be necessary, or is necessary. However, even if you are new to the atomic operations paradigm, or more familiar with transactions, do consider the speed benefit that nontransactional tables can offer on the order of three to five times the speed of the fastest and most optimally tuned transactional tables.

In situations where integrity is of highest importance, MySQL Server offers transaction-level reliability and integrity even for nontransactional tables. If you lock tables with LOCK TABLES, all updates stall until integrity checks are made. If you obtain a READ LOCAL lock (as opposed to a write lock) for a table that enables concurrent inserts at the end of the table, reads are permitted, as are inserts by other clients. The newly inserted records are not be seen by the client that has the read lock until it releases the lock. With INSERT DELAYED, you can write inserts that go into a local queue until the locks are released, without having the client wait for the insert to complete. See Section 8.11.3, “Concurrent Inserts”, and Section, “INSERT DELAYED Syntax”.

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 can never be an automatic rollback (which can happen with transactional tables if you are not very careful). MySQL Server also guarantees that there are no dirty reads.

Following are some techniques for working with nontransactional tables:

  • Loops that need transactions normally can be coded with the help of LOCK TABLES, and you don't need cursors to update records on the fly.

  • To avoid using ROLLBACK, you can employ the following strategy:

    1. Use LOCK TABLES to lock all the tables you want to access.

    2. Test the conditions that must be true before performing the update.

    3. Update if the conditions are satisfied.

    4. Use UNLOCK TABLES to release your locks.

    This is usually a much faster method than using transactions with possible rollbacks, although not always. The only situation this solution doesn't handle is when someone kills the threads in the middle of an update. In that case, all locks are released but some of the updates may not have been executed.

  • You can also use functions to update records in a single operation. You can get a very efficient application by using the following techniques:

    • Modify columns relative to their current value.

    • Update only those columns that actually have changed.

    For example, when we are updating customer information, we update only the customer data that has changed and test only that none of the changed data, or data that depends on the changed data, has changed compared to the original row. The test for changed data is done with the WHERE clause in the UPDATE statement. If the record wasn't updated, we give the client a message: Some of the data you have changed has been changed by another user. Then we show the old row versus the new row in a window so that the user can decide which version of the customer record to use.

    This gives us something that is similar to column locking but is actually even better because we only update some of the columns, using values that are relative to their current values. This means that typical UPDATE statements look something like these:

    UPDATE tablename SET pay_back=pay_back+125;
    UPDATE customer
        address='new address',
        phone='new phone',
        customer_id=id AND address='old address' AND phone='old phone';

    This is very efficient and works even if another client has changed the values in the pay_back or money_owed_to_us columns.

  • In many cases, users have wanted LOCK TABLES or ROLLBACK for the purpose of managing unique identifiers. This can be handled much more efficiently without locking or rolling back by using an AUTO_INCREMENT column and either the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. See Section 12.13, “Information Functions”, and Section, “mysql_insert_id()”.

    You can generally code around the need for row-level locking. Some situations really do need it, and InnoDB tables support row-level locking. Otherwise, with MyISAM tables, you can use a flag column in the table and do something like the following:

    UPDATE tbl_name SET row_flag=1 WHERE id=ID;

    MySQL returns 1 for the number of affected rows if the row was found and row_flag wasn't 1 in the original row. You can think of this as though MySQL Server changed the preceding statement to:

    UPDATE tbl_name SET row_flag=1 WHERE id=ID AND row_flag <> 1;

Download this Manual
User Comments
  Posted by David Phillips on August 1, 2005
MySQL "atomic" transactions are not guaranteed if a thread is killed or the server crashes. For example, an UPDATE might not update all of the requested rows if the thread is killed during the update.

You seem to be describing MyISAM behavior. Remember, only
InnoDB and BDB tables support transactions.
  Posted by Douglass Davis on May 19, 2004
Atomic means that all of the operations are executed or none of them are. This page should make it clear that "atomic" operations as they see them are not really atomic, due to the fact that the DB or thread can crash. Otherwise, this documentation can be very confusing.
  Posted by Don Mitchell on June 26, 2006
Although MySQL does not like the industry standard TPC benchmarks, a number of people have been testing the MySQL atomic transactions with the TPC-C kit. Numbers up to 560 transactions per minute are being reported for MySQL, and about 60 tpm for PostgreSQL.

Note that commercial database systems are a lot faster, but of course more expensive. On a single proc PC, Microsoft SQL Server's TPC-C benchmark is 38622 tpm, on a 2.8 GHz Dell, and on serious big iron, over 3 million tpm have been clocked by IBM DB2).
  Posted by Pete Wilson on June 8, 2010
Mark Callaghan's bug report at http://bugs.mysql.com/bug.php?id=51193&thanks=3&notify=199 amplifies what actually goes on.
  Posted by Kouber Saparev on December 3, 2010
Keep in mind that MySQL transactions are not fully atomic in the sense of the "all or nothing" rule. You can commit a transaction with half of the statements done, and the other half failed.
  Posted by Dinoop p on August 30, 2014
Reply to Kouber Saparev
The "all or nothing" rule is true for a single operation. But the transactions are normally used as a series of different dependent application business logic operations. In this context the "all or nothing" is decided by the application logic.
Sign Up Login You must be logged in to post a comment.