The chemistry of MySQL transactions: Part 1

Atomicity and Consistency

A transaction is a way for an application to group several reads and writes together into a logical unit. Conceptually, all the reads and writes in a transaction are executed as one operation: either the entire transaction succeeds (commit) or it fails (abort, rollback). If it fails, the application can safely retry. With transactions, error handling becomes much simpler for an application, because it doesn’t need to worry about partial failure.

Not every application needs transactions, and sometimes there are advantages to weakening transactional guarantees or abandoning them completely for better performance or higher availability. Some safety properties can be achieved without transactions.

So, in order to tailor these options according to your needs, you need to know about exactly what safety guarantees transactions provide and the cost associated with them.

In this post, we will see the different aspects of transactions and the related features that Mysql offers (in particular to innoDB engine).

ACID Model: The safety guarantees provided by transactions are often described by the well known acronym ACID, which stands for Atomicity, Consistency, Isolation and Durability. Let’s see what these means and how MySQL features, in particular the InnoDB storage engine, interact with the categories of the ACID model:

Atomicity: Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single “unit”, which either succeeds completely, or fails completely: if any of the statements constituting a transaction fails to complete, the entire transaction fails and the database is left unchanged.

Without atomicity, if an error occurs partway through making multiple changes, it’s difficult to know which changes have taken affect and which haven’t. The application could try again, but that risks making the same change twice, leading to duplicate or incorrect data.

Atomicity simplifies the problem: if a transaction was aborted, the application can be sure it didn’t change anything, so it can safely be retried.

The related MySQL features which is associated with the Atomicity aspect of the ACID model are:

• Autocommit setting: By default, MySQL runs with autocommit mode enabled. This means that, when not otherwise inside a transaction, each statement is atomic, as if it were surrounded by START TRANSACTION and COMMIT. You cannot use ROLLBACK to undo the effect; however, if an error occurs during statement execution, the statement is rolled back. To disable autocommit mode implicitly for a single series of statements, use the START TRANSACTION statement:

disable autocommit mode implicitly

• Commit statement: COMMIT commits the current transaction, making its changes permanent. So, we can say this marks the success of a transaction.
• Rollback Statement: ROLLBACK rolls back the current transaction, canceling its changes. If you start an explicit transaction (autocommit = 0), perform some updates, and then roll back, InnoDB restores the original state of data. It preserves the original data by storing it in an area of the database called the rollback segment. So if you roll back, it just re-copies those pages of data to replace the ones you changed. (The rollback segment is inside the tablespace on disk)

Consistency: The idea of ACID consistency is that you have certain statements about your data (invariants) that must be true — for example, in an accounting system, credits and debits across all accounts must be balanced. If a transaction starts with a database that is valid according to these invariants, and any writes during the transaction preserve the validity, then you can be sure that the invariants are always satisfies.

Although, as per MySQL docs, InnoDB doublewrite buffer and InnoDB crash recovery are the features that are related to Consistency aspect of ACID model, these only aid and not completely cover Consistency (and so, I’ll discuss them during D of ACID). The idea of consistency depends upon the application’s notion of invariants, and it’s the application’s responsibility to define transactions correctly. This is not something the databases can guarantee: if you write bad data and violates your invariants, the database can’t stop you.

So, while Atomicity, Isolation and Durability are properties of the database, Consistency is a property of the Application. Thus, the letter C doesn’t really belong to ACID!

I would be covering Isolation and Durability in detail in following posts since they have many concepts involved. Till then, adios!

References:

• Designing Data-Intensive Applications, by Martin Kleppmann.
• InnoDB and the ACID Model
• MySQL transactions and how does Rollback works?