SQL Server IO | SansSQL

Saturday, January 5, 2013

SQL Server IO

I had the opportunity to look back into the basics of SQL Server IO. To start off... first was to understand "What is Transaction" and then "Transaction properties".

What is Transaction?
There can be many ways of defining the same... however, the most simple could be as follows:
A transaction is a logical unit of work in which, all the steps must be performed or none.

Once we understand "what is transaction"... next is to understand "the properties" exhibited by transaction.
There are FOUR important properties of a transaction:
1. Atomicity
2. Consistency
3. Isolation and then
4. Durability

ATOMICITY - Any... for that matter any database modifications must follow an “all or nothing” rule... which means: If one part of the transaction fails, the complete transaction fails. Each transaction is said to be “ATOMIC”. It is critical that SQL Server maintains the atomic nature of transactions... not only SQL Server any DBMS, operating system or hardware failure.

CONSISTENCY - States that only valid data will be written to the database. In simple words if the database was consistent before the execution of the transaction then it should remain consistent after the complete execution of that transaction.

For some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules.

On the other hand, if a transaction successfully executes, it will take the database from one state that is consistent with the rules to another state that is also consistent with the rules.

ISOLATION - First, let me put it in straight words: The transaction should not be interfered by any other transaction executing concurrently.

Now that means - multiple transactions occurring at the same time not impact each other’s execution. For example, if User A issues a transaction against a database at the same time that User B issues a different transaction, both transactions should operate on the database in an isolated manner. The database should either perform User A's entire transaction before executing User B's or vice-versa.

This helps in preventing User A's transaction from reading intermediate data due to User B's transaction that will not eventually be committed to the database.

We  always get into confusion w.r.t the way we describe isolation. We should alyways remember that isolation property does not ensure which transaction will execute first, it is just that transactions will not interfere with each other.

DURABILITY - means any changes made by the transaction should be permanently committed in the database. This ensures that any transaction committed to the database will not be lost in spite of any subsequent software or hardware failures.

To ensure Durability is at its best we make use of database backups and transaction logs that facilitate the restoration of committed transactions.

I hope the above helps us to understand ACID properties in a simple way! 

No comments:

Post a Comment