What is transaction ?

                               A transaction is one or more actions that are defined as a single unit of work. In the Relational Database Management System (RDBMS) world they also comply with ACID properties:

Atomic(ity) - The principle that each transaction is 'all-or-nothing', i.e. it either succeeds or it fails, regardless of external factors such as power loss or corruption. On failure or success, the database is left in either the state in which it was in prior to the transaction or a new valid state. The transaction becomes an indivisible unit.


Consistency - The principle that the database executes transactions in a consistent manner, obeying all rules (constraints). For example, consider the following table:



CREATE TABLE dbo.MyTestTable (
 ColA SMALLINT,
 CONSTRAINT uq_ColA UNIQUE )



Now consider the following valid transactions that will leave the database in a consistent state:
• INSERT INTO dbo.MyTestTable VALUES (1)
• INSERT INTO dbo.MyTestTable VALUES (9)
But the following statements, if executed and allowed to modify data, will leave the database in an inconsistent state, since they violate some constraint (or allowed datatype) of the defined table. Hence an error is returned:
• INSERT INTO dbo.MyTestTable VALUES ('Hello')
• INSERT INTO dbo.MyTestTable VALUES (3),(3),(3)


Isolation - This property means that each transaction is executed in isolation from others, and that concurrent transactions do not affect the transaction. This property level is variable, and as this article will discuss, SQL Server has five levels of transaction isolation depending on the requirements of the database.


Durability - This property means that the data written to the database is durable, i.e. it is guaranteed to be in storage and will not arbitrarily be lost, changed or overwritten unless specifically requested. More formally, it means that once a transaction is committed, no event can 'un-commit' the transaction - it is written and cannot be changed retrospectively unless by another transaction.