Durability in Database

I have talked about about the three properties (Atomicity, Consistency and Isolation) in my previous posts. In this post, we'll cover the last but very important property i.e. Durability. Well, It's important because it saves the world from situation like THIS. Now let's see why and how.

 As said in earlier post Durability ensures that any transaction committed on the database is not lost in case of events like error, power crash, etc. Probably, the concepts is relatively easy to understand compared to it's importance.

Think of a hypothetical situation where a bank's database table stores the numerous transactions related to the accounts of its users. What happens now is a power crash, system failure, or anything like these one would prefer not to happen. And result: Data loss i.e. loss (partial/full) of the records storing the transaction details of all the accounts of bank. So it should result into one of two situations for user (first and second) depending on the his/her credit/debit situation.

Now, think: such database issue, happening in other organizations specially those related to defense, tax, etc. So, it can concluded that this issue has be avoided. In other words the history of data operations/transaction needs to be saved from loss due to crashes, failures, etc. The database should be durable.

To maintain the durability of database, data restoration is done by using database backups and transaction logs. e.g. In case of Oracle, there are redo logs.

Isolation in Database

This post is to discuss the Isolation in database. It took me a little difficulty to understand this concept as I couldn't find any suitable example over internet. It was explained as a theoretical concept.

So, We'll try to discuss it using an example. But before that let's have a look on a statement on Isolation in my earlier post, "Any two or more transactions on a database are isolated from each other. i.e. they don’t interfere with each other."

Now let's take an example of table named EMP in a database which supports isolation. And we have two users: user X and user Y who frequently access/use this table.

The table contains data as shown in picture above.
In a situation, where user X logins into database and change the Age of Michelle from 19 to 23 as shown in picture below. And this change in the EMP table is uncommitted yet.

Now, consider three scenarios.
scenario(I): While the session of user X is still running with uncommitted data change, user Y logins into same database as a separate user.
scenario(II): User X commits the change made by him on table EMP. After that User Y logins into database.
scenario(III): While the session of user X is still running with uncommitted data change, user Y logins into same database as a separate user. After that user X commits the change.

Here is a task for you to guess how the data of table EMP would appear to user Y for each of three scenarios.

Here are the answers.
scenario(I): The data of table EMP would appear unchanged as in first picture to user Y.
scenario(II) and (III): The data of table EMP would appear changed as in the second picture.

Now, hopefully you would have figured out the reason why. Still, here is the explanation.

Only committed data is visible across the sessions. Since the data change was uncommitted for session(I), the last committed data of table EMP (as in first picture) would appear to user Y.

Now after going through this example, we conclude that any database changes/operations/transaction are isolated from each other across different sessions untill and unless those are committed. This is the property of isolation in the database.

Hope you liked my post. Here is more on this topic on Wikipedia

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.