Transaction in SQL Server

In .NET environment we can define transaction boundary by Transaction object.

1. If you are using SqlClient (namespace System.Data.SqlClient) Managed Provider you can SqlTransaction object.
2. If you are using Oledb (namespace System.Data.Oledb) Managed Provider you can OledbTransaction object.
3. If you are using Odbc (namespace Microsoft.Data.Odbc) Managed Provider you can OdbcTransaction object

A transaction must follows this properties:-

In a perfect transaction world, a transaction must contain a series of properties known as ACID. These properties are:

A transaction is an atomic unit of work or collection of separate operations. So, a transaction succeeds and is committed to the database only when all the separate operations succeed. On the other hand, if any single operations fail during the transaction, everything will be considered as failed and must be rolled back if it is already taken place. Thus, Atomicity helps to avoid data inconsistencies in database by eliminating the chance of processing a part of operations only.

A transaction must leave the database into a consistent state whether or not it is completed successfully. The data modified by the transaction must comply with all the constraints in order to maintain integrity.

Every transaction has a well defined boundary. One transaction will never affect another transaction running at the same time. Data modifications made by one transaction must be isolated from the data modification made by all other transactions. A transaction sees data in the state as it was before the second transaction modification takes place or in the state as the second transaction completed, but under any circumstance a transaction cannot be in any intermediate state.

If a transaction succeeds, the updates are stored in permanent media even if the database crashes immediately after the application performs a commit operation. Transaction logs are maintained so that the database can be restored to its original position before failure takes place.

Leave Comment