Transaction
in SQL server:
Transactions
group a set of tasks into a single execution unit. Each transaction begins with
a specific task and ends when all the tasks in the group successfully complete.
If any of the tasks fails, the transaction fails. Therefore, a transaction has
only two results: success or failure. Incomplete steps result in the failure of
the transaction.
A transaction
has four key properties that are abbreviated as ACID. ACID is an acronym for
"Atomicity", "Consistency", "Isolation" and
“Durability".
Suppose if we
want to execute two statements together, if error occurs in one of the
statement then other will also rolled back. Transaction will be committed if
both the statement got executed without any error. 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.
Steps
we use in transaction:
· Begin Transaction
· Rollback Transaction
· Commit Transaction
Acid
Properties of Transactions:
Each
transaction must have four characteristics to ensure consistency and integrity
of the data:
Atomicity: In a transaction involving two or
more discrete pieces of information, either all of the pieces are committed or
none are.
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:
Banking Transaction. If debit from one account and credit to other account,
both are successful then transaction will be committed otherwise it will be
rolled back.
Consistency:
A transaction
either creates a new and valid state of data, or, if any failure occurs,
returns all data to its state before the transaction was started. 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.
Isolation:
A transaction
in process and not yet committed must remain isolated from any other
transaction. 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.
Durability:
Committed data
is saved by the system such that, even in the event of a failure and system
restart, the data is available in its correct state. 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.
