SQL SERVER – ACID (Atomicity, Consistency, Isolation, Durability)

ACID (an acronymn for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved.

Atomicity is an all-or-none proposition.

Consistency guarantees that a transaction never leaves your database in a half-finished state.

Isolation keeps transactions separated from each other until they’re finished.

Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

Above four rules are very important for any developers dealing with databases.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

60 thoughts on “SQL SERVER – ACID (Atomicity, Consistency, Isolation, Durability)

  1. Hey

    Boss ,

    It’s fantastic it is so easy to understand …from last 2 year when the interview time if they asked me ,what as=cid rules i just playing my recorded tape ,after reading your article i grabbed how the ACID RULE is working on server.Especially consistent state..

    Thanks very much sirjee..

    Shiva v p

    • The tempdb database does not follow the ACID property of “Durability”.

      Whenever Microsoft SQL Server is restarted, the tempdb is recreated. Any data that was committed to the tempdb is lost and replaced by a fresh copy from the model database, therefore violating the durability property which states that the effect of a transactoin must be persisted within the database irrespective of the state of the system.

  2. ACID

    *Atomicity states that database modifications must follow an “all or nothing” rule. Each transaction is said to be “atomic.” If one part of the transaction fails, the entire transaction fails. It is critical that the database management system maintain the atomic nature of transactions in spite of any DBMS, operating system or hardware failure.
    * Consistency states that only valid data will be written to the database. If, for some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules. On the other hand, if a transaction successfully executes, it will take the database from one state that is consistent with the rules to another state that is also consistent with the rules.
    * Isolation requires that multiple transactions occurring at the same time not impact each other’s execution. For example, if Joe issues a transaction against a database at the same time that Mary issues a different transaction, both transactions should operate on the database in an isolated manner. The database should either perform Joe’s entire transaction before executing Mary’s or vice-versa. This prevents Joe’s transaction from reading intermediate data produced as a side effect of part of Mary’s transaction that will not eventually be committed to the database. Note that the isolation property does not ensure which transaction will execute first, merely that they will not interfere with each other.
    * Durability ensures that any transaction committed to the database will not be lost. Durability is ensured through the use of database backups and transaction logs that facilitate the restoration of committed transactions in spite of any subsequent software or hardware failures.

  3. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 2 of 31 Journey to SQLAuthority

  4. I marvel at how poor Pinal’s posts usually are, and even more at how many people praise them. Pinal basically hasn’t explained anything, has merely stated definitions without clarifying what they mean. How can this be good?

    For example, for those who claim they had never understood ACID and now they do, please tell me whay you understand from ‘is an all-or-nothing proposition’.

    Or for example, what can you tell me about consistency, is it checked or imposed by the Database?

    I’ve began checking database sites this semester because I need a refreshment before I teach these matters again, but I’ve quickly learned to avoid SQL Authority because it’s always so short, so inflexible so terse that the only way to learn is through the comments of other people. Seriously, there are far better sites to learn about databases and SQL than here. Don’t waste your time.

  5. In fact I did not understand the difference between atomicity and consistency. If atomicity is fullfilled how could consistecy (from your definition) fail?

  6. these are just short n sweet stuff, good only for simple interviews, no enough detail for actual real life understanding

  7. This is a great article but can somebody explain me how these properties are implemented practically in the database. To be more specific, how is atomicity achieved in the database?

  8. The Atomicity is achieved by using the Transactions in your Stored procedures OR SQL blocks.

    Ex:
    BEGIN TRANS

    –PERFORM your actions Updates/deletes/insert/truncate

    IF @@TRANCOUNT>0
    COMMIT TRANS
    ELSE
    ROLLBACK TRANS

  9. pinal dave

    ur explanations are short and clear

    but still am stacked with atomicity and consistence. im understanding as if they are speaking the same concepts.
    they all explain that the transaction must complete otherwise rollback all changes?

    plz can u clarify for me to kill all my dought

    • Atomicity refers to how a transaction is executed, either all of it or none of it, if you are halfway through and it fails, you must restore the data to its previous state, as in ROLLBACK. Consistency refers to how a transaction is designed. It must leave the data in a consistent state. So, if you are transferring money between accounts, you should have one transaction that takes it from one account and puts it in the other NOT two transactions, one to take out the money and the second to put it into the second account.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s