Atomicity in Database

It's time to discuss the Atomicity of a database in little more detail. But before we begin, here is a disclaimer.

Disclaimer: this post has no relation whatsoever to this , this and this

Now, I mentioned in my previous post that the transaction on an atomic database follows "all or none" rule. So, here it means that a database transaction is either successful or failed.

There is no such thing as partial successful/failed transaction. If any part of transaction fails, the whole transaction fails. (See this to learn about a database transaction)

e.g. A there is database transaction consisting of three steps occurring serially (to move record from one table to other table).

1. Copy a row from one table to a other table
   
2. Delete the same from first table
   
3. Commit the changes on database.

So, to be an Atomic database it should be programmed in such a way that if any of these step fail during the transaction, the whole transaction must fail and all the previous successful steps of the same failed transaction must be undone.

In above example, if step 2 fails, the step 3 should not occur and step 1 should be undone (rolled-back). This transaction will be successful only if all the steps are successful.

In the nutshell, in an atomic database, either all statements or steps in a database transaction are successful or none.

Here is the Wikipedia link on the same topic.

The ACIDic nature of a Database

Now, this concept is not specific to relational database, but it must be known as it's a basic principle applied practically on all kinds of databases. So, here we go. Let's discuss some ACIDic properties of a database.

Well just to have a clear start, we are not talking about stuff which somehow relates to "this".

The four letters of the word ACID denote the set of four properties.

[You can click on them to see more on each properties in my other posts.]

• Atomicity It means that a database is said to be atomic if each transaction on the database follows  "all or none" rule.
• Consistency It means that a database is consistent (in a valid state) before and after each transaction with respect to the rules and constraints applied on the database.
• Isolation Any two or more transactions on a database are isolated from each other. i.e. they don't interfere with each other.
• Durability This property ensures that any transaction committed on the database is not lost in case of events like error, power crash, etc.