Database Transactions in SQL – SQL Server Tutorial

DATABASE TRANSACTIONS :-

A transaction is a unit of work,mainly consist of DML operations which must be made permanent as a whole and must be under as a whole.

SQL server runs in 3 types of transaction modes :-

• Auto commit mode [default]
• Implicit transaction mode.
• Explicit transaction mode.

(I) Auto commit mode :- {default}

• In auto commit mode,the transaction are starts with “DML operations”.
• After executing dml command the transaction ends automatically with “commit”.
• In auto commit mode,user can’t control transaction.

(II) Implicit transaction mode :–

• In implicit transaction mode a transaction starts with DML operation & A transaction ends with “commit/rollback”.

 Example  :–

Insert → transaction starts

Update

Commit→ transaction ends

• If transaction ends with “commit” then the operators performed in transaction(or)recorded in the database.

Example :-

Insert → transaction starts

Update

Rollback → transaction ends

• If transaction ends with rollback the changes mode to the database one
• To run SQL Server in implicit transaction mode executing the following command :-

Set implicit_transaction on

(III) Explicit transaction mode :-

• To run sql server in implicit transaction mode executing the following command.

Set implicit_transaction on

• If u want to run on auto commit mode.

Set implicit_transaction off

• In this trasanction starts with begin tansaction statement and ends with commit/rollback transaction.

Example   :-

(1)  Begin transaction

Insert

Update

Commit transaction

(2) begin transaction

Insert

Update

Rollback transaction

• Auto commit             implicit                                  explicit 
• Update                              Update                                      begin transaction
•                                             Commit/rollback                  update
•                                                                                                   Commit transaction/rollback Transaction

 Example  :-  Insert  transaction starts

Update

Commit  transaction ends

Rollback  transaction ends → Invalid [useless]

Note:-  If commit is ends,after rollback is useless similarly if rollback is ends,after commit is useless.

SAVE TRANSACTION  :-

• The save transaction is used to break a long transaction into small parts.
• Using this we can cancel part of the transaction:

Example  :-

                  Insert

                  Update

                   Save transaction st1

                   Update  

                   Delete

                   Rollback transaction st1

Advantage   :

“Only part of the transaction can be cancelled”