Understanding ACID Properties

From WikiContent

(Difference between revisions)
Jump to: navigation, search
Line 1: Line 1:
-
When dealing with databases and transaction, you will frequently hear the word "ACID". 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.
+
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? 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 applied 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.
+
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.
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.

Revision as of 21:24, 5 January 2010

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