ACID in Database Systems: All You Need to Know

Acid. For those who make food, it‘s a known substance – citric acid is vitamin C, fizzy drinks contain acid, lemon juice contains citric acid, and so on. For some, though, acid has a different meaning – enter the world of software engineering. For software engineers working with databases, acid has a completely different meaning. In these circles, acid is known as ACID – it‘s a set of four distinct database properties that make work with databases what it is today. Software engineers working on ACID-compliant databases spare themselves from problems occurring to their data when a transaction gets interrupted.

What Is ACID?

In the database circles, ACID transactions are broken down in the following way:

• A stands for Atomicity – atomicity ensures that the statements in a given transaction operate as an indivisible unit and that their effects are either seen collectively or not at all.
• C stands for Consistency – consistency is usually handled by the logging mechanisms inside of the database which records all of the changes to the database.
• I stands for Isolation – isolation means that one transaction is “isolated” (cannot be affected) by the results of other transactions.
• D stands for Durability – durability in many database management systems refers to a log file that tracks all of the changes to the database.

In other words, ACID in database management systems is a set of database properties that guarantee data integrity in the event of interruptions that affect data-related operations. Ever been in a situation where you‘re importing data into a table and your internet goes out for a couple of minutes? What about electricity going out for a minute or two? Without ACID compliance, such things would disrupt your database and the data within – with ACID, you would simply have to cancel (terminate) the transaction in question, then start anew until it‘s finished – no damage to your database instances would be inflicted.

The way ACID works, though, depends on the database management system that is being used – different database management systems deal with the concept in their own ways. For the purposes of this blog, we‘re going to focus on MySQL and its associates (Percona Server and MariaDB.) Don’t get discouraged, though – ACID works in pretty much the same way in all database management systems that support it – be it SQL Server, SQLite, PostgreSQL, or MySQL and the advice contained in this blog can be twisted and applied to other database management systems if necessary.

Before moving on to the more detailed explanation of the ACID properties, though, know this – the only storage engines that support the ACID model within MySQL are InnoDB and XtraDB (which is the same as InnoDB, but with a couple of Percona-made enhancements. XtraDB is available in Percona Server.)

How Does It Work in MySQL?

Atomicity

MySQL offers three features to ensure that statements operate as an indivisible unit and these features are as follows:

1. The COMMIT query – most engineers look into committing data as means to make import operations faster. Running a SET autocommit=0; query, then importing data, and then COMMITing changes to MySQL will make the insert operation perform faster because commits would only be committed at the end of the transaction and not in the middle of it. Everything would look something along those lines (notice the lines 2 and 8):

1. The ROLLBACK query – this query is pretty self-explanatory as it simply „rolls back“ the changes made by a previous transaction. Think of it as an undo button – if you set autocommit to off (0) as in the example above, and then instead of COMMIT write ROLLBACK, nothing will happen.
2. The ability to fiddle with autocommit and turn it on or off – as already mentioned above, MySQL provides us with the ability to turn off automatic commits of data in order to commit it later on – this may have implications on speed when importing larger data sets, but not for many other use cases.

Consistency

Ever heard of the log files provided by MySQL? If you did, you already know the drill – they look like so:

Consistency is very closely related to these two files (in other database management systems, they may look different.) The reason why is that because in MySQL, consistency is handled by MySQL‘s logging mechanisms (two files – ib_logfile0 and ib_logfile1 that can be seen above) which record all of the changes to a database. When a database is restored, these files are scanned and consistency is once again in place – data is right there where it previously was.

Isolation

In MySQL and many other database management systems, isolation refers to row-level locking: row-level locking makes it possible to lock a single row for longer a time period, and row-level locking also makes it possible to lock entire tables if a query cannot use an index. In most cases, row-level locking is used when one transaction waits for another one to commit (finish.)

Isolation in MySQL is only supported by XtraDB or InnoDB, and other storage engines (MyISAM, MEMORY, and MERGE) are based on table-level locking. Learn more about row-level locking available in MySQL’s InnoDB here.

Durability

Durability is a brother of consistency. No, really – who wants durable data that isn’t consistent? Or consistent data that cannot be protected (cannot be durable) in the long term? Noone. Durability is also ensured via the log files that track all of the changes to the system – when log files are scanned and the data is restored, the data is both consistent and durable. Woohoo!

BASE vs. ACID in Databases

As you might be able to tell if you’ve read this far, MySQL is ACID compliant. That can’t be said about all database management systems, though – some database management systems are based upon the BASE model where:

• B and A stand for Basically Available – data is ensured to be available by replicating it inside of the database.
• S stands for Soft State – such a feature essentially means that the values of data inside of the DBMS are able to change over time.
• And E stands for Eventually Consistent – and that means exactly what we think it does: the data may not be consistent at first, but database management systems that support the BASE model will eventually put data into the consistency realm.

ACID is supported by MySQL, PostgreSQL, SQL Server, and SQLite while BASE is supported by NoSQL-based database management systems, such as MongoDB. Both of these appliances have their own use cases, however, before deciding to use any database management system that supports either ACID transactions or BASE principles, be aware of the possible consequences of your actions and choose wisely.

We hope you’ve enjoyed this blog, come back to the DbVisualizer article section to learn more about how DbVisualizer can help you in achieving your database goals, and until next time!