Understand ACID compliance’s role in database management systems
ACID is a term to describe the set of properties that guarantee that database transactions are processed reliably.
It stands for atomicity, consistency, isolation and durability.
Hmmm…what does atomicity stands for?
Atomicity means that either all transactions happen or nothing happens. We also refer it as “all or nothing”. This ensures the database maintains in a consistent state in case of an event of failure.
Well…what is a good example of atomicity?
Let’s say you transfer $1000 to your friend’s bank account. If the transfer is happened successfully, you will get a message saying that the transfer is complete. You will have $1000 less and your friend will receive $1000.
To ensure atomicity, the database management system will ensure that the transaction either completes successfully or not at all. The transfer transaction will include two operations: debit $100 from Account A and credit $100 to Account B.
If any part of this transaction fails, the entire transaction will be rolled back. The database state will be reverted as if nothing has happened. This way, the transfer of funds between the two accounts will either be completed entirely or not at all, which ensures the atomicity property.
Well… what would be good example of failure events and how does atomicity come into play?
Imagine the database that stores transaction data and money data went down during the transaction. If it goes down, the transaction going from person A to person B would be cancelled and nothing would have happened. The database can be down because of a hardware error or some unknown issues that cause the database to crash.
How about consistency? What does consistency mean then?
It means that transactions must take the database from one valid state to another. The database must satisfy a set of constraints or rules, known as the database schema.
hmmm…what does that mean?
Let’s say in the database we have a table called todo which has an integer id and a description as a string schema. If we try to insert data into the database with a todo list item that has a date as a description, it will fail because the data is not consistent with the constraints of the table.
Let’s take a look at another example: let’s say we have two tables, one called user and another one called todo. Let’s say this todo table also requires a foreign key field called userId. If we try to insert into todo table with an invalid/non-existent userId, the database will reject the insertion and generate an error saying the insertion is not valid.
Now let’s talk about isolation.
Isolation is the concept that all the database transactions must be isolated and not interfere with each other.
For example, let’s say we have a bank account A and there are two transactions to withdraw money from the bank account at the same time. With isolation, the database puts a lock on all other transactions and allow one transaction to happen at a time. Once the first transaction is done, the database lifts the lock and allow other transactions to happen, one at a time. That way it will ensure the database remains consistency and reliable.
Hmmm….what about durability?
Durability means once a transaction is commited, the changes must persist in the events of database failure. A database failure can mean hardware failure or the database server completely going down.
Let’s say we deposit $2000 into bank account A. When the deposit is happening, let’s say there is a global outage and all of a sudden all the database servers goes down. With durability in place, the transaction will still happen after the outage is over.
How does that work?
What happens is before the actual transaction is commited into the database server, the actions for the transaction is kept in a log or script in local hard drive. So when the database server goes down and come back up again, it reads any script that might be run and runs the actions to commit the transaction to the database.
