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
Description: A 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."
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..