1.8.4.3 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 section 7 MySQL Table Types.

The other non-transactional table types (such as MyISAM) in MySQL Server 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.

With MySQL Server supporting both paradigms, the user is able to decide if he needs the speed of atomic operations or if he needs to use transactional features in his applications. This choice can be made on a per-table basis.

As noted, the trade off for transactional vs. non-transactional table types lies mostly in performance. Transactional tables have significantly higher memory and diskspace requirements, and more CPU overhead. That said, transactional table types such as InnoDB do of course offer many unique features. MySQL Server's modular design allows the concurrent use of all these different storage engines to suit different requirements and deliver optimum performance in all situations.

But how does one use the features of MySQL Server to maintain rigorous integrity even with the non-transactional MyISAM tables, and how do these features compare with the transactional table types?

1. 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, 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. MySQL Server, in almost all cases, allows 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. 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.
2. 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 you never will get an automatic abort from the server, which is a common problem with transactional database systems.
3. Even a transactional system can lose data if the server goes down. 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 database systems, is reported to sometimes lose data in such situations. To be safe with MySQL Server, whether using transactional tables or not, you only need to have backups and have the binary logging turned on. With this you can recover from any situation that you could with any other transactional database system. It is, of course, always good to have backups, independent of which database system 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. However, even if you are new to the atomic operations paradigm, or more familiar with transactions, do consider the speed benefit that non-transactional 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 non-transactional tables. 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), 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. See section 6.4.4 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 will never be an automatic rollback (which can happen with transactional tables if you are not very careful). MySQL Server also guarantees that there will not be any dirty reads.

Following are some techniques for working with non-transactional tables:

• Loops that need transactions normally can be coded with the help of LOCK TABLES, and you don't need cursors when you can update records on the fly.
• To avoid using ROLLBACK, you can use the following strategy:
  1. Use LOCK TABLES ... to lock all the tables you want to access.
  2. Test conditions.
  3. Update if everything is okay.
  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 this case, all locks will be 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 fields relative to their current value.
  • Update only those fields that actually have changed.
  For example, when we are doing updates to some 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 the user can decide which version of the customer record he should 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
    SET
      customer_date='current_date',
      address='new address',
      phone='new phone',
      money_he_owes_us=money_he_owes_us-125
    WHERE
      customer_id=id AND address='old address' AND phone='old phone';
  

  As you can see, this is very efficient and works even if another client has changed the values in the pay_back or money_he_owes_us columns.
• In many cases, users have wanted ROLLBACK and/or LOCK TABLES for the purpose of managing unique identifiers for some tables. This can be handled much more efficiently by using an AUTO_INCREMENT column and either the SQL function LAST_INSERT_ID() or the C API function mysql_insert_id(). See section 9.1.3.31 mysql_insert_id(). You can generally code around row-level locking. Some situations really need it, but they are very few. InnoDB tables support row-level locking. With MyISAM, 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 already 1 in the original row. You can think of it as though MySQL Server changed the preceding query to:

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

User Comments

Saya Pemakai Baru MySQL, sebelumnya selama bertahun-tahun
saya menggunakan SQL Server sebagai Database.
Ketika Saya Mencoba MySQL pertama kali, dibenak saya
"Inilah Database Server yang saya cari ? "

Saya telah mencoba untuk connect dengan program visual
basic saya, dan berencana untuk menggunakannya pada
proyek-proyek koputerisasi saya.

MySQL....Hebat !

I'm not sure about the claim of innodb providing full ACID transactions. The C in ACID stands for consistency. Which means you can't insert a NULL and get a default value inserted. Even if the insert checking is turned on at compile time, I'm pretty sure you have to support check constraints to qualify for the C part of ACID, and currently MySQL simply ignores check constraints.

Is there a plan to implement check constraints and turn on insert value checking by default?

Without write-ahead logging of some kind it is also hard to believe you can fulfill the D part, durability. But I'm not familiar enough with innodb tables to say one way or the other. Do they employ some form of write ahead logging that survives sudden power down safely and all that?

Add your own comment.