Understanding ACID Properties

From WikiContent

Revision as of 21:24, 5 January 2010 by Wmr513 (Talk | contribs)
Jump to: navigation, search

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.

Most people typically consider only the atomicity property when talking about transactions. But now you know that is only part of the transaction story. Remember ACID; embrace it, use it, think about it. But be very careful - it can become quite addicting.

By Mark Richards

This work is licensed under a Creative Commons Attribution 3

Return to 97_Things_SQL

Personal tools