Database ACID properties
ACID stands for Atomicity, Consistency, Isolation, and Durability. Database ACID properties are a set of database properties(rules) which transactions must obey to maintain the integrity and consistency of a database. In this article, we’ll take a look at each ACID property.
But first, what is a transaction? A transaction is a single logical unit of work that accesses and possibly modifies the contents of a database. Transactions access data using read and write operations – GeeksForGeeks
With the atomicity rule, all operations within a transaction must be successful in order for any changes to be committed to the database. If any failure occurs during a transaction then the entire transaction fails. Let’s look at an example.
Imagine we have two bank accounts, account A and account B. Account A has $500, while account B has $100. We’ll like to transfer $50 from account A to B. The transaction would like the following.
Let’s imagine that a failure occurs right after we write the deductions to account A. This would leave account A with $450 but, account B was not updated. So we just magically lost $50. This would corrupt your data. The property of atomicity prevents this – once there’s a failure anywhere in the transaction then the entire transaction fails – and nothing gets committed to the database.
Database consistency must be maintained after each transaction. This means that all changes should be accurate, and leave the database in a valid state which also upholds data integrity. Let’s look at the following example.
Imagine we have the following two tables(customers, and orders).
Both tables are linked via the foreign key constraint based on the customer_id on the. This will ensure that upon inserting a new record in the orders table, the customer_id used is valid (is an id of an existing customer record in the customers table).
If a transaction inserts a new record in the orders table using a customer_id (e.g 90) that does not exist then the database would be in an inconsistent state as two. Thankfully our foreign key constraint would prevent this, and the entire transaction would be aborted(including any other changes that may have occurred earlier in the transaction) and consistency would be maintained.
A transaction being executed should not be affected by any other transactions executing simultaneously, therefore isolated. The result of concurrent transactions should be the same as it would be if the transactions were executed sequentially. Isolation also determines how/if changes in an uncommitted transaction are visible to other transactions or whether different transactions can access the same data at the same time. – this is done via isolation levels.
Durability ensures that changes committed during a transaction persist permanently(stored in non-volatile memory), even if there happens to be a system failure or a power outage. Let’s take a look at an example.
Let’s say you’re transferring funds from account A to account B, and right after completing the transaction the bank has a sudden power outage, the durability property would ensure that the changes made to both accounts are still stored.
The ACID properties ensure that we have a database with correct data at any given time. In this article, we looked at each of the ACID properties – their impact and importance.
This is my first time writing about a database topic, hope you enjoyed it and found it useful. Thank you for reading. Until next time, think, learn, create, repeat!