SQL Server and ACID property of Database

Aryan SuryaWansi picture Aryan SuryaWansi · Jul 9, 2011 · Viewed 24.9k times · Source

I am newbie to database and SQL Server.

So when i have search things about database on internet i have found that The Database said to be good if it obey or follow the ACID (Atomicity, Consistency, Isolation, Durability) property.

I wonder that the Microsoft SQL Server (Any Version Current or previous one) follow the ACID property internally or if we are using MS SQL Server in our application then we have to write coding such way that our Application follow the ACID property.

In Short: Maintain the ACID property is task (or liability) of Database Or its task of Application Programmer.

Thanks..

Answer

naveen picture naveen · Jul 9, 2011

IMHO, it is a two fold maintainance. Both DB Admins (writing stored procedures ) and programmers should enforce ACID Properties. SQL Server maintains its own ACID properties internally and we don't have to worry about that.

ACID Properties are enforced in SQL Server.

Read this: Acid Properties of SQL 2005

But that doesn't mean that that the Database would handle everything for you.

According to Pinal Dave (blog.sqlauthority.com)

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.

That goes for developers dealing with databases.

But application developers should also write business logic on which ACID properties are being enforced.

An example on Practical use of ACID properties would help you more I guess