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.
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)
71 Comments. Leave new
very good explanation
I have confusion in durability. Plz explain it.
Durability means making the transactions permanent (Never losses in any kind of interruptions)
Thanks @Sravan
Can anyone please tell me the difference between Automicity and consistency.Are the same ?
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.
Simply better
Its very clear and short examples given for ACID properties
Thanks a lot for simple explanation.
thanks for very nice and simple explanation….
thanks, really very helpful
Thanks…
can u pls explain with Example.
It’s Simple and valuable one…cute explanation… thanks
nice but still explain with example
nice….
Good effort but it needs more explanation with example.
well answered…
Please explain ,how to achieve the ACID in sql server.
Thanks SJMohideen for the apt description on ACID property.
Perfect explanation :) cheers
“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. ”
Hi, I’ve been a DBA for more than a decade and if I had seen this in a tech test, I’d challenge the interviewer to explain why this question had been asked of a Production DBA. If I didn’t like his answer, I’d walk out. This actually happened for another question, after the interviewer confessed to me they needed the DBA to also write and implement code daily. One way to weed out cheap, nasty companies. Now I agree it is nice if a DBA knows what ‘atomicity’ is, but this is NOT necessary for him. A Developer yes, of course.
Very valid points JB!
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?