A Guide to ACID In MySQL

ACID is a set of four crucial database properties that ensure data integrity and consistency even in the event of a catastrophic server failure. Learn all about how the concept works in MySQL here!

What Is ACID?

ACID has been a thing ever since databases have entered the spotlight. ACID stands for four properties crucial to properly manage databases and these are as follows:

• A stands for Atomicity.

• C stands for Consistency.

• I stands for Isolation.

• D stands for Durability.

In each database management system ACID works differently, but nonetheless, its main concepts remain unchanged.

ACID in MySQL

In MySQL, ACID works in the following way:

• Atomicity is ensured by making sure that all statements in a transaction operate as a single unit and their results are seen as a whole or not seen at all.

• Consistency is ensured by making sure that MySQL always keeps handy of log files (ib_logfile0 and ib_logfile1) and can read through them when needed.

• Isolation is ensured when row-level locking in in use.

• Durability is ensured by making sure that MySQL always maintains a log file that tracks all of the changes to its infrastructure.

ACID principles are a known cornerstone of database functionality, yet they‘re not set in stone at all – some of them can be exchanged for speed if we modify certain parameters within my.cnf (we‘ll get into that a little later.)

It‘s also worth noting that the only storage engine that support ACID principles in MySQL is InnoDB and its counterpart – XtraDB developed by Percona. Both storage engines support the ACID principles by default.

ACID in MySQL – Configuration

All settings in MySQL can be set and / or modified by modifying the contents of the main file relevant to the functionality of MySQL – my.cnf (the file will be called my.ini if we‘re using a Windows infrastructure.) The file can be found in various locations depending on your operating system – the most likely location of the file in Linux infrastructure will be the /var/lib/mysql/ folder, however, you can also find the file hiding in the /etc/mysql/ folder. For Windows users, everything is simpler: my.ini will most likely be found in the /bin/mysql/mysql*.*.** folder – ..** refers to your MySQL version (and yes, you can have a copy of the file in the same directory as well – only the original my.ini file will be scanned through.)

Files in the MySQL Directory.

Once you open the file up, you will see a lot of settings related to the inner workings of MySQL – Windows users got it lucky because the file also comes with a lot of comments within itself to help users understand how everything works, while those of you using Linux will have to read through the docs (or this blog) to understand how everything works.

Here‘s how some of the parameters within the file will look like for those using Windows:

The parameters relevant to MySQL

As you can see, there are rather a lot of settings and in Windows MySQL really does its best to walk us through them and even provides links to the documentation – my.cnf isn‘t so generous, so the only settings you will see when using the Windows counterpart of my.cnf are these:

The configuration of MySQL - InnoDB Parameters.

Well, not exactly all of them – sysadmins would go crazy – however, you will certainly see some of the following settings:

• innodb-flush-method

• innodb-file-per-table

• innodb-flush-log-at-trx-commit

• innodb-log-file-size

Some of them are relevant to optimizing MySQL for high performance, some of them (the file-per-table parameter) are best to leave at default values, some increase the log file size, and some – the innodb-flush-log-at-trx-commit parameter – deal with ACID within MySQL and its friends like Percona Server and MariaDB.

innodb-flush-log-at-trx-commit

ACID within MySQL is primarily controlled by this parameter. The purpose of this parameter is to control the balance between ACID and speed within MySQL – since InnoDB (the primary storage engine in MySQL) by default has this parameter set to 1, ACID is always being maintained. However, this parameter can also be set to different values, such as 0 or 2. The bottom line is this – if you aren‘t necessarily dealing with millions of rows and don‘t necessarily need more speed to complete insertion operations (INSERT or LOAD DATA INFILE queries), leave the value at default. If you do, however, keep the following in mind:

A value of 0 or 2 will make your queries faster at the expense of letting MySQL lose up to one second‘s worth of transactions in the event of query failure (electricity going out, etc.)

• The value of 0 tells MySQL that when data is written to the log file and a flushing (saving) operation is performed, MySQL should do nothing once the transaction commits thus saving us time in the process.

• The value of 1 will tell MySQL that once the data is written to the database, data should be committed and saved into the log file – that means ACID compliance wherever we go.

• A value of 2 will tell MySQL that the data that has been written to the database within the last couple of seconds can only be erased when the OS crashes or due to the complete power outage within the server cluster.

For most, leaving the setting at its default value – 1 – will be enough, however, for those who are into data analysis or perhaps just experimenting with the capabilities of the RDBMS, changing this setting can be beneficial. Choose wisely!

ACID vs. BASE

Some might say that ACID is a competitor to BASE – the ACID equivalent in non-relational database management systems. And that‘s not exactly false – BASE stands for Basically Available and Eventually Consistent data. For most non-relational (NoSQL) databases, the ACID model is overkill and that‘s why most of them go into a different direction. The BASE model is a softer model offering the flexibility for data, but at the same time sacrificing integrity.

We won‘t get into all of the details here, but the principle of BASE is the following:

• Basically Available means that data should be available even in the event of a failure (data is spread across many different mediums.)

• Eventually Consistent means that at sometime in the future data should enter a consistent state. This doesn‘t always happen, hence the problem itself – but that offers NoSQL databases a lot more flexibility than their ACID counterparts.

NoSQL databases are also known for their Soft State capabilities – Soft State essentially says „data integrity is the problem of the developer, not the database, do what you want.“ To sum up, BASE isn‘t exactly the best friend of ACID.

Problems Related to ACID and BASE

Both ACID and BASE solve distinct problems – ACID ensures data integrity in the event of a server failure, while BASE offers more flexibility for those working with data. However, both of those approaches come with their distinct problems – BASE doesn‘t ensure data integrity, while ACID is only available in some aspects of relational database management systems (SQL Server, PostgreSQL and TimescaleDB, also in MySQL and all of its flavors like Percona Server and MariaDB.)

The primary requirement for those working with ACID in MySQL is InnoDB or its counterpart Percona XtraDB – no other MySQL storage engines support such a model: MyISAM is obsolete (the only real reason to use it is if we want faster COUNT(*) query performance because MyISAM stores the number inside of its metadata and other storage engines don‘t), MEMORY stores all of the data in the memory of the database, the BLACKHOLE storage engines acts as a blackhole where everything that we write to it dissapears, etc. – if you‘re curious about what other storage engines can offer, just ask MySQL:

A list of available storage engines in MySQL.

If you‘re curious how to solve other problems related to your relational or non-relational database infrastructure, grab a free trial and ask DbVisualizer (seriously – the tool is used by NASA, AMD, Netflix, Twitter, Tesla, Uber, and a whole lot of other companies), come back to our blog after a while to learn more news in the database space, and until next time!

About the author

Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.