Understanding ACID Properties

From WikiContent

(Difference between revisions)
Jump to: navigation, search
Current revision (21:44, 5 January 2010) (edit) (undo)
 
(2 intermediate revisions not shown.)
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. However, this property is only one of the ways to achieve database consistency and integrity. ''Consistency'' means that database referential integrity rules will be applied within the scope of the transaction. For example, consistency ensures that you cannot insert a customer's order without first inserting the customer record it is referencing. ''Isolation'' means that no one else can see (or use) the data you are adding or changing until the transaction is complete. Database isolation is inversely proportional 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 contained absolutely no transaction logic. 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 databases were 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 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.
+
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.
By [[Mark Richards]]
By [[Mark Richards]]

Current revision

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. However, this property is only one of the ways to achieve database consistency and integrity. Consistency means that database referential integrity rules will be applied within the scope of the transaction. For example, consistency ensures that you cannot insert a customer's order without first inserting the customer record it is referencing. Isolation means that no one else can see (or use) the data you are adding or changing until the transaction is complete. Database isolation is inversely proportional 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 contained absolutely no transaction logic. 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 databases were 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 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.

By Mark Richards

This work is licensed under a Creative Commons Attribution 3

Return to 97_Things_SQL

Personal tools