In this post we will discuss about the ACID properties and how these properties are followed by InnoDB storage engine of MySQL. I’m writing this post as I’m a MySQL DBA and I’ve been asked this question in multiple interviews. So this post will help others who are going through the same path as I’ve been.
Lets quickly discuss the ACID properties:
Atomicity:
It means all or nothing, in RDBMS we will understand this with the example of transaction. If a transactions is being processed either it should complete or shouldn’t occur at all. There won’t be anything like a partial transaction.
Consistency:
It means the correctness of a transaction, if a transaction make any changes to a data then those changes should be preserved. The database before and after the transaction should be consistent. Let’s take a simple example a transaction is executing and due to some hardware failure your database shuts down then your data should stay in consistent state, that is the duty of the RDBMS (MySQL).
Isolation:
Isolation means one transaction should not affect any other transaction, so its the duty of RDBMS to have a locking mechanism such that each and every transaction is isolated.
Durability:
It means the data that is committed by a transaction should get written to disc and it should be preserved even in case of any system failure.
Now let’s understand how MySQL(InnoDB) makes sure ACID properties are followed.
Atomicity in MySQL:
You should have the basic idea of InnoDB architecture.
As discussed earlier a transaction should either get completed (commit) or shouldn’t occur at all(rollback). COMMIT/ROLLBACK ensures atomicity in MySQL. By default in MySQL all your transactions are autocommited (variable autocommit=1). Once the transaction gets committed the changes are written from memory to disk.
For rollback MySQL writes older values to UNDO LOGS so in case you want to rollback MySQL has the older values as well.
Consistency in MySQL:
Here I’ll introduce you to MySQL DOUBLEWRITE BUFFER, whenever the data is flushed from memory to Disk, firstly it is written to DOUBLEWRITE BUFFER, don’t get confused with the name buffer it is actually on disk not in memory, then it is written to the respective .ibd file of the table. The purpose of DOUBLEWRITE BUFFER is to have a copy of all the changes on disk in case of a system failure, so in case of CRASH RECOVERY all the available changes in DOUBLEWRITE BUFFER can be recovered such that our data is consistent again. You might have questions like writing data twice will be a overhead on disk, it is not, we will discuss this in another post.
Isolation in MySQL:
It basically means the locking mechanism, the default level of ISOLATION in MySQL is REPEATABLE-READ. So once a transaction starts a copy of old values is saved, as we discussed in atomicity, all the tables/rows modified by that transactions are locked, suppose a SELECT query wants to see that data then that copy of old values is returned to the SELECT. MySQL also supports other 3 ISOLATION levels READ UNCOMMITED, READ COMMITED and SERIALIZABLE.
You can see the locks on any transaction by executing ‘SHOW ENGINE INNODB STATUS\G’ in the output under section TRANSACTIONS you can see what locks are acquired by which transaction.
Durability in MySQL:
InnoDB achieves durability by many settings:
Using BINARY LOGS, every transaction is first written to BINARY LOGS when a COMMIT is executed, so in case of system failure if some data is lost(even from DOUBLE WRITE buffer) then we can use the latest backup and then we can apply the BINARY LOGS till the time of failure so that the missing data can be recovered.
How often the data is flushed to disk is decided by the variable ‘ innodb_flush_log_at_trx_commit‘ which defaults to 1, which means flush data to disk on every commit. That also helps achieve durability.
DOUBLEWRITE BUFFER also accounts for durability.
I’ve tried to keep the post simpler by not introducing more in-depth concepts of data flushing or REDO logs so it would be easier for beginners to understand the ACID properties in MySQL
Comment below in case you want to add any further points or correct me in case I wrote anything incorrect about the ACID properties. Thank You.
Source: https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html