Please disable your adblock and script blockers to view this page
 

What is transaction in SQL Server Database and ACID (atomicity, consistency, isolation and durability) properties?

Introduction: In this article i am going to explain what are transactions in sql server database and the ACID (atomicity, consistency, isolation and durability) properties?  In previous related articles i explained Example to explain Transaction in Sql server using Asp.net and What is cursor? Advantages and disadvantages,when to use and example to explain and Remove duplicate records from Sql Server table and Split large string separated by comma in Sql Server and 20 main differences between Stored procedures and Functions  and Create Sql server database script and Create database from that script

DescriptionA transaction is a "sequence of operations performed as a single logical unit of work" or we can say it is a unit of work that is performed against the database. A transaction has four key properties that are abbreviated as ACID. ACID is an acronym for "Atomicity", "Consistency", "Isolation", "Durability".

Practically, we club two or more SQL queries into a group and execute all of them together as a part of a transaction.

Transactions provide an "all-or-nothing" concept that means each work-unit performed in a database must be either completed or have no effect. So if a transaction is successful, all of the data modifications made during the transaction are committed using COMMIT statement and become a permanent part of the database. If a transaction encounters any errors then the changes need to be aborted and all of the data modifications are erased. This process of reversing the changes is called ROLLBACK in SQL Server terminology.

We can group two or more Transact-SQL statements into a single transaction using the following statements:
  • Begin Transaction
  • Rollback Transaction
  • Commit Transaction

Properties of Transactions:
Transactions have the following four properties that are abbreviated as ACID.

Atomicity: This property ensures that all operations is treated as a single work unit and are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their previous state. So either it is all performed or none of it.
For example in banking application that transfers funds from one account to another, this property ensures that, if a debit is made successfully from one account then the corresponding credit is made to the other account.

Consistency: Transactions provide an "all-or-nothing" concept that means each work-unit performed in a database must be either completed or have no effect thus will never leave the database in inconsistent or half-finished state.
For example, in banking application that transfers funds from one account to another, this property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction.

Isolation: This property enables transactions to operate independently of each other and also transparent to each other.  It keeps transactions separated from each other until they’re finished. Transaction sees the database in a consistent state. This transaction operates on a consistent view of the data. If two transactions try to update the same table, one will execute first and then the other will execute.
For example, in banking application that transfers funds from one account to another this property ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither.

Durability: This property ensures that the result or effect of a committed transaction persists in case of a system failure. So it means that the results of the transaction are permanently stored in the system.
For example, in banking application that transfers funds from one account to another this property ensures that the changes made to each account will be saved permanently and will not reversed.

Now over to you:
" I hope you have got what is transaction in Sql server and what is ACID properties. If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily.. ConversionConversion EmoticonEmoticon