The early days of computing saw the creation of databases, as developers needed to store and manage data. However, there was a problem — transactions were not always trustworthy and data integrity was at stake as they were not acid-compliant yet. A group of computer scientists took on the task of creating a set of principles to ensure reliable transactions, aiming to solve these problems. They came up with what would eventually become ACID compliance and ACID properties, defining the future of ACID-compliant databases and relational databases as a whole. ACID (Atomicity, Consistency, Isolation, and Durability) compliance is a set of properties that guarantees reliability and consistency in database transactions. Today, it is a principle that has become the watchword in building banking applications. ACID transactions are not just for banks, though. All applications can benefit from ACID guarantees. In MySQL, ACID compliance ensures data integrity by enforcing rules and constraints defined by the database schema. It ensures reliability by committing all the changes within a transaction or committing none of the changes. It guarantees that concurrent transactions do not interfere with each other. It also ensures data durability by permanently saving transactions so that they would survive any subsequent failures and reduces the risks of errors, data corruption, and inconsistencies.
Atomicity treats a transaction as a single, indivisible unit of work. This means that a transaction either passes or fails, ensuring that incomplete data is not passed into the database. Statements such as BEGIN, COMMIT, and ROLLBACK control atomicity in MySQL. For example, imagine transferring money from one bank account to another (consider it as a single transaction with just two steps or changes: debit and credit). Atomicity ensures that the changes of debiting one account (step one) and crediting the other (step two) happens as a single transaction, or no change occurs (no step). This way, there won’t be an error in crediting the other account without debiting the source account or vice versa. This is an example of an ACID-compliant transaction.
Consistency guarantees that a transaction brings the database management systems from one valid state to another. This means that any modification of data in a transaction will follow the predefined rules and constraints of the database schema. If the transaction fails, the operation will not go through. The developer can define these constraints, which encompass PRIMARY KEY, FOREIGN KEY, NOT NULL, CHECK, and UNIQUE constraints. Think of what a librarian does. They ensure that all the books are properly categorized, labeled, and placed on the correct shelves according to the library's rules. This demonstrates consistency. If someone tries to return a book that doesn't match the category or is missing crucial information, like accidentally dropping off a personal book, the librarian will reject it and maintain the consistency of the library's catalog.
Isolation guarantees that transactions happening concurrently do not interfere with each other and produce inconsistent results. Each transaction is executed independently of other transactions until it is committed. MySQL uses REPEATABLE READ as the default isolation level, but also has support for READ UNCOMMITTED, READ COMMITTED, and SERIALIZABLE. To better understand isolation, think about it like a busy supermarket with multiple checkout lines. Each customer joins a specific line and proceeds independently with their own purchases. The cashiers process the transactions in parallel but then make certain that each customer’s items are scanned and billed correctly. Isolation works in the same way, where each transaction is performed without interfering with others.
Durability guarantees that immediately after a transaction is committed, its changes are saved permanently and will survive any possible failures, such as power outages or system crashes. MySQL does this by writing transaction logs and data changes to disk. In the event of a failure, MySQL can easily recover the database by replaying the transaction logs. Think about durability as automatically backing up all the pictures on your phone to the cloud. If anything should happen to your phone, your photos can easily be recovered.
MySQL uses COMMIT and ROLLBACK to support atomicity in a database. If there’s any possibility of failure in any step of the transaction, the transaction would roll back the changes to prevent incomplete writing to the database. MySQL employs a write-ahead logging mechanism to ensure durability. Before modifying the actual database files, MySQL first writes the changes to the transaction logs. This approach guarantees that the transaction logs are written and persisted to disk before committing the changes to the database files.
ACID database compliance is an inherent part of the functionality and design of MySQL with the use of the InnoDB storage engine, making it an ACID-compliant database. MySQL supports more than 10 distinct storage engines, such as MyISAM and InnoDB. InnoDB is the most modern of all MySQL storage engines and fully supports ACID compliance. PlanetScale does not support non-InnoDB storage engines.
Many databases in MySQL use InnoDB. However, if you’re in doubt about which engine your database uses, you can run a command on the CLI or a query through a GUI like Workbench.
To check through the command line:
- Select the database you want to access:
If you don’t know the name of the database, you can run the
SHOW DATABASES command.
- Show the CREATE Table information by running this command:
SHOW CREATE TABLE table_name;
You’ll get a result like the image below that shows all the information of the table, including the engine.
Figure 1 - Create Table information
To check through the graphical interface,
- Run the following command:
SELECT TABLE_NAME, ENGINE FROM information_schema. TABLES WHERE Table_SCHEMA = "database_name";
Figure 2 - Tables and their engine
If your database has a different engine, you can convert it to InnoDB using:
ALTER TABLE table_name ENGINE=InnoDB;
If you’re having difficulty in conversion, you can refer to the MySQL reference manual.
In a database, data integrity is one of the core fundamentals that ensure the accuracy, consistency, and reliability of stored information. Maintaining data integrity is crucial for any database system and MySQL ACID compliance does this by ensuring that the data stored adheres to predefined rules, maintaining referential integrity, and performing data validation.
It is important for developers to be able to rely on the data in a database without concerns about the integrity and accuracy of the data. The properties of ACID compliance practiced together provide a solid foundation for high reliability in MySQL. They enable consistent and accurate data management, prevent data corruption or inconsistencies, ensure transactional integrity, and safeguard against failures. Improved concurrency control In an organization where many users may be accessing the database at the same time, MySQL supports multiple users accessing the database concurrently. The isolation property of MySQL ensures that transactions do not interfere with one another. It provides mechanisms to control the visibility and interaction of concurrent transactions, preventing conflicts and maintaining data consistency even in multi-user environments.
Disasters happen regardless. Fortunately, ACID compliance in MySQL ensures data recovery and fault tolerance. MySQL uses a write-ahead logging mechanism that guarantees that in case of a failure during the write process, MySQL can recover the database by replaying the transaction logs and restoring the committed state of the transactions. Features like replication and MySQL InnoDB Cluster further enhance data recovery and fault tolerance. ACID compliance tradeoffs and considerations Regardless of the advantages of ACID compliance, some drawbacks include the following:
Ensuring atomicity, consistency, isolation, and durability requires extra processing and coordination, which can have an impact on the performance of database transactions. This impact is more pronounced when you are dealing with large volumes of data or operating in an environment where concurrency is highly utilized.
ACID compliance, particularly in terms of strong isolation levels, can hinder scalability in distributed databases or highly concurrent environments. Maintaining strict isolation levels across many nodes or clusters can introduce contention and limit the scalability of the system. Evaluating and adjusting the isolation levels may become necessary to balance isolation and scalability. Alternative strategies, such as relaxed isolation levels and distributed transactions, exist and can be used to balance compliance and isolation. In sharded environments like Vitess that are required for scaling your MySQL database, there are two main issues: cross-shard isolation and atomicity. Vitess helps balance the tradeoffs between atomicity, isolation, and performance. Vitess will attempt to minimize (but not guarantee) the chances of a partial cross-shard update.
Implementing and maintaining ACID compliance in MySQL requires careful attention to relational database schema design, entire transaction management, and enforcing constraints and business rules. Hence the need to understand and follow best practices to ensure consistent and reliable application behavior.
When it comes to ACID compliance in MySQL, using transactions wisely is an important best practice. Avoid unnecessarily large transactions that span multiple unrelated database operations, minimize the duration of transactions to reduce the locking time and improve concurrency, and test whether certain operations within a transaction can be marked as read-only if they don't require write access.
One good way to go about ACID compliance is to optimize MySQL configuration parameters, such as cache sizes, buffer settings, and transaction-related settings, to align with what you are building or the workload that will be required of MySQL. This way, you can significantly improve the performance of MySQL. Regular backup and data restoration It is also best practice to put in place regular backups of your SQL databases for better data durability and to ease recovery in case of system failures or power failures. Regularly test your recovery procedures to ensure they are reliable and can restore the database to a consistent state.
By adhering to ACID principles — Atomicity, Consistency, Isolation, and Durability — MySQL ensures that transactions are processed reliably and consistently. ACID compliance provides several benefits, including data integrity, improved concurrency control, data recovery, and fault tolerance. Developers should focus on adherence to ACID compliance because it sets a strong foundation for developing reliable and robust database systems. Having a strong background in ACID compliance sets database operators apart and future-proofs them by making them better prepared to adapt their systems to changing requirements, emerging technologies, and evolving business needs. Give your engineers the edge in implementing best practices and ensuring consistent and reliable application behavior.