Atomicity Consistency Isolation Durability, or "ACID", was coined by Andreas Reuter in 1983. It's a concept in database management systems (DBMS) that identifies a set of standard properties used to guarantee the reliability of a database. ACID properties ensure that all database transactions remain accurate and consistent, and support the recovery from failures that might occur during processing operations. As such, it is implemented by nearly all Relational Databases.
As it turns out, DBMS that offer support for transactions enforce the four ACID properties automatically. In today's blog, we'll learn how transactions do that. In up-coming articles, we'll look at how to use transactions in our stored procedures to guard against data inconsistencies.
Transactions Explained
Before we can employ transactions within our own stored procedures, it might help to understand what a transaction is. Simply put, a transaction is a set of operations performed so all operations are guaranteed to succeed or fail as one unit.
As an example, consider the process of transferring money from a checking account to a savings account. This action is actually made up of two parts:
- Withdraw funds from the checking account.
- Deposit it into the savings account.
Now, imagine what might happen if the power went out after the first step. I think that we can agree that there would be a problem if funds were deducted from the checking account but not added to the savings account! Just as you would not want this to happen with your financial transactions, updating one database table without updating tables that refer to it is just as undesirable. By employing a transaction, both the operations are guaranteed to succeed or fail together. That way, all entities involved remain in a consistent state.
ACID and Transactions
Transactions play a key role in enforcing all four ACID properties: Atomicity, Consistency, Isolation, and Durability. Let's see how they do that.
Atomicity
A database operation is considered atomic if it cannot be further broken down into separate operations. A transaction is also atomic because all of the operations that occur within a transaction either succeed or fail together. Should any single operation fail during a transaction, then everything is considered to have failed and must be undone (i.e. rolled back).
Consistency
One of the main perks of using a transaction is that it should leave the database in a consistent state, whether or not it completes successfully. This ensures that data modified by the transaction complies with all the constraints placed on the columns so that data integrity is maintained.
Isolation
Every transaction is isolated from other transactions. Therefore, a transaction shouldn't affect other transactions running at the same time. Stated another way, data modifications made by one transaction should be isolated from the data modifications made by other transactions. So, while a transaction can see data in the state it was in before another concurrent transaction modified it, as well as after the second transaction has completed, it cannot see any intermediate states.
Durability
Transactions help with Durability in a few ways: data modifications that take place within a successful transaction may be safely considered to be stored in the database regardless of whatever else may occur. As each transaction is completed, a row is entered in the database transaction log. Thus, in the event of a system failure that requires the database to be restored from a backup you can use this transaction log to get the database back to the state it was in after a successful transaction.
Conclusion
Transactions are a fantastic way to enforce the four ACID properties within your database(s). In today's blog, we learned how transactions do that. In the next article, we'll look at how to use transactions in our stored procedures to guard against data inconsistencies.
