Understanding ACID Properties
When dealing with databases and transactions, you will frequently hear the word "ACID". ACID is an acronym that stands for "Atomicity, Consistency, Isolation, and Durability". These words describe how the database (and application) should behave when applying changes within the scope of a transaction. These properties directly impact the level of consistency and integrity of your database. Atomicity means that all database update operations will be performed in a single unit of work; either all updates will all be applied, or no updates will be applied. This is one of the ways you achieve database consistency. 'Consistency means that database referential integrity rules will be applied during the course of the transaction. For example, this property ensures that you cannot insert a customer's order without first inserting the customer record. Isolation means that no one else can see (or use) the data you are adding or changing until the transaction is complete. The database isolation level is inversely proportionate to database concurrency; the higher the isolation level, the lower the consistency level. Durability means that once you commit a transaction, you are guaranteed that the database updates you applied will survive a system crash, regardless of the technology or methods used by the database vendor to manage updates.
So why is it so important to understand ACID properties? Several years ago I was at a client site working on a large financial application. Curiously enough, the application had no transaction logic - none at all. I was told the reason the application didn't contain transaction logic was that "transactions were slow, and besides, our systems rarely fail, so there is no need for rollback logic in our application". As skeptical as I was, they were in fact correct about the error rate - their application was one of the most robust I had seen. However, I pointed out that their database was most likely suffering from a lack of data integrity and robustness. How could this be? One of the things they were missing was the fact that while updates were being applied to the database, action was possibly being taken on those updates before the unit of work was completed (the "Isolation" part of ACID). After a quick demonstration of the possible issue, they agreed that they should probably have transaction support in their application.
There is more to a database transaction than simply coordinating multiple updates into a single unit of work and managing transaction rollbacks. Remember - all four properties must be considered to ensure a high degree of database consistency and integrity. By understanding ACID properties you can make better decisions about when and where to use transactions.
This work is licensed under a Creative Commons Attribution 3
Return to 97_Things_SQL