1.8.5.3. Transaction and Atomic Operation Differences

MySQL Server (version 3.23-max and all versions 4.0 and above) supports transactions with the InnoDB transactional storage engine. In MySQL 5.5 and up, newly created tables use InnoDB by default, as explained in Section 14.2.1.1, "InnoDB as the Default MySQL Storage Engine". By default, InnoDB provides full ACID compliance; see Section 14.2.8, "MySQL and the ACID Model" for ways that you can adjust settings to balance ACID compliance with raw performance. For information about InnoDB differences from standard SQL with regard to treatment of transaction errors, see Section 14.2.3.14, "InnoDB Error Handling".

The nontransactional storage engines in MySQL Server (such as MyISAM) follow a different paradigm for data integrity called "atomic operations". MyISAM tables effectively always operate in autocommit = 1 mode. Because changed data is written to disk one statement at a time, it is harder to guarantee the consistency of a sequence of related DML operations, which could be interrupted partway through. Thus, this mode is suitable for read-mostly workloads. In transactional terms, while each specific update is running, no other user can interfere with it, there can never be an automatic rollback, and there are no dirty reads. However, these features apply to single operations, not related updates that succeed or fail as a unit. Workarounds such as the LOCK TABLES statement limit concurrent write access to nontransactional tables.

You can choose which paradigm to use, even for different tables within the same application: transactional features for reliability combined with high performance, or atomic operations for non-critical, read-mostly data (for example, on replication slave servers).

Transactional storage engines such as InnoDB offer many significant features to support high reliability for heavy read/write workloads. As a result, transactional tables can have higher memory and disk space requirements, and more CPU overhead. MySQL Server's modular design enables the concurrent use of different storage engines to suit different requirements and deliver optimum performance in all situations.

Workarounds for Reliability with Non-Transactional Tables

But how do you use the features of MySQL Server to maintain 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, you must resolve potential problems at the application level by including checks before updates and by running scripts that check the databases for inconsistencies and automatically repair or warn if such an inconsistency occurs. You can normally fix tables with no data integrity loss by using the MySQL log or even adding one extra log.
Sometimes, critical transactional updates can be rewritten to be atomic. Multiple DML operations can be done with LOCK TABLES or atomic updates, ensuring that there are no deadlocks by limiting concurrent write access. 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.10.3, "Concurrent Inserts", and Section 13.2.5.2, "INSERT DELAYED Syntax".
To be safe with MySQL Server, regardless of what kinds of tables you use, make regular backups and have binary logging turned on. It is always good to have backups, regardless of which database system you use.

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.
Note

This solution does not handle the situation 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, using the following techniques:
- Modify columns relative to their current value. This makes the update correct even if another client has changed the column values in the meantime.
- Update only those columns that actually have changed. This is a good database practice in general.
When managing unique identifiers, you can avoid statements such as LOCK TABLES or ROLLBACK 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.14, "Information Functions", and Section 22.8.7.37, "mysql_insert_id()".

For situations that require row-level locking, use InnoDB tables. 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;
```