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

ACID (an acronym 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 these 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.

SQL SERVER - ACID (Atomicity, Consistency, Isolation, Durability) acid

This is one of the most popular interview question and answer. Additionally, I strongly believe that every software developer as well as database administrator should know the answer to this question. When you design any system or database, make sure you select the database which follows these properties as that will help you better develop applications for your business.

Andreas Reuter and Theo Härder created the acronym ACID in the year 1983 however Jim Gray had already defined these properties in the late 1970s. SQL Server, Oracle, MySQL, PostgreSQL are some of the databases which follows ACID properties by default.

Reference: Pinal Dave (https://blog.sqlauthority.com)

ACID, Database, DBA, Software Development
Previous Post
SQL SERVER – FIX : Error : 3702 Cannot drop database because it is currently in use.
Next Post
SQLAuthority News – Microsoft SQL Server 2008 Community Technology Preview (November 2007) VHD

Related Posts

71 Comments. Leave new

  • Doesn’t SQL Server have a default isolation level (Read Committed) that is not ACID compliant (braking the I – Isolation concept)? Don’t you have to change the isolation level by turning on the setting at for database: SET ALLOW_SNAPSHOT_ISOLATION ON or is it the Snapshot Isolation level that you use?

    Reply
  • Darren Woodford
    April 30, 2019 8:01 pm

    You have stated “Consistency guarantees that a transaction never leaves your database in a half-finished state” whereas it is my understanding that Consistency guarantees that “data written to the database must be valid according to all defined rules, including constraints, cascades & triggers”. See https://en.wikipedia.org/wiki/Consistency_(database_systems). Your definition is more a restating of Atomicity.

    Reply

Leave a Reply