Understanding ACID Properties
It is important to realize that there is more to transactions than simply coordinating all database updates in a single logical unit of work. When dealing with databases and transaction, you will frequently hear the word "ACID". No wonder no one gets any work done! In our industry ACID is an acronym that stands for "Atomicity, Consistency, Isolation, and Durability". These four words describe the basic properties of a transaction. However, what they really refer to is 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. However, atomicity is only part of the story. Consistency means that database referential integrity rules will be applied during the course of the transaction, meaning that you cannot insert a detail row without first inserting the header row. Isolation means that no one else can see (or use) the data you are adding or changing until the transaction is complete. This property is just as important as atomicity - you can seriously affect the overall level of database consistency and integrity without transaction isolation. Durability, the last property, 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? Let me answer that question by describing an experience I had several years ago at a client site I was working at. I was told, with confidence, that the reason the application I was working on 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, with few errors. However, I was confident, I told them, that their database was most likely suffering from a lack of data integrity and robustness. Why? Think ACID. Remember the "I" part of ACID (Isolation)? One of the things they were missing was the fact that while updates were being made to the database, action was possibly being taken on those updates before the unit of work was completed. After a quick demonstration of the possible issue, they agreed that they should probably have transaction support in their application.
We typically only consider the atomicity property when talking about transactions. But now we know that is only part of the story. Remember ACID; embrace it, use it, think about it. But be very careful - it can become quite addicting.
This work is licensed under a Creative Commons Attribution 3
Return to 97_Things_SQL