intro
ACID are four of the most important properties for any database management system – ACID is a concept known to many people, however, not everyone knows the concept inside out. Dig into it with us and familiarize yourself!
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:
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:
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.)
If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.
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:
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!