Transaction Management

Content

• What is transaction?
• Beginning of transaction
• Committing or Rollback 
• TRY CATCH TRANSACTION
• TRANSACTION in .NET

What is transaction?

A transaction is a single unit of work.

Transactional control commands are only used with the DML commands INSERT, UPDATE and DELETE only.

They can not be used while creating tables or dropping them because these operations are automatically committed in the database.

Properties of Transactions

Transactions have the following four standard properties, usually referred to by the acronym ACID:

• Atomicity: ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.
• Consistency: ensures that the database properly changes states upon a successfully committed transaction.
• Isolation: enables transactions to operate independently of and transparent to each other.
• Durability: ensures that the result or effect of a committed transaction persists in case of a system failure.

Transaction Control

There are following commands used to control transactions:

• COMMIT: to save the changes.
• ROLLBACK: to rollback the changes.
• SAVEPOINT: creates points within groups of transactions in which to ROLLBACK

The COMMIT Command

The COMMIT command is the transactional command used to save changes invoked by a transaction to the database.

The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.

Syntax

BEGIN TRAN 

SQL Statement

COMMIT TRAN

Example:

BEGIN TRAN

     INSERT INTO Score (Id, StudentId, SubjectId, Score) VALUES(11,1,2,80)

COMMIT TRAN

The ROLLBACK Command

The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database.

The ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.

Syntax

BEGIN TRAN

sql statement

ROLLBACK TRAN

The SAVEPOINT Command

A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.

This command serves only in the creation of a SAVEPOINT among transactional statements. The ROLLBACK command is used to undo a group of transactions.

Syntax

SAVE TRAN SAVEPOINT_NAME;

SQL Statment

ROLLBACK TRAN SAVEPOINT_NAME;

TRY CATCH TRANSACTION

Implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C# and Microsoft Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.

Syntax

BEGIN TRY      

{ sql_statement | statement_block } 

END TRY 

BEGIN CATCH     

 [ { sql_statement | statement_block } ] 

END CATCH [ ; ]

.NET TRANSACTION

Using System.Data.SqlClient.SqlTransaction

Declare Database Connection

Declare Transaction object

Beginning of transaction

TRY CATCH Transaction