TCL IN SQL SERVER

Transaction control language

Related to transaction management sql server provides a set of commands which are collectively called as tcl and those commands are as follows

Begin

Commit

Explicit transaction

A transaction is a unit of work, mainly consists of DML operational 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

Implicit transaction

Explicit transaction

Auto commit

In auto commit mode, the transactions are started with “DML operations

After executing DML command the transactions end automatically with commit

In auto commit mode, user can’t control transaction

Implicit transaction

In implicit transaction mode, a transaction starts with DML operation

A transaction end with Commit/Rollback

Are the transactions that will be automatically created when the first sql statement is executed without Begin Transaction command.

By default implicit transaction are set to off . To make them On use the set statement as follows

set implicit_transactionson/off

Explicit Transaction Mode

Each transaction is explicitly started with the Begin Transaction statement and explicitly ended with a commit or rollback statement

 By default sql server uses Auto commit Transactions i.e. after executing each statement it will automatically commit it.

Note:

If we want to use the Explicit Transactions before executing the statements we need to start with a “Begin Transaction” statement and then decide whether it has to be commit or Rollback , until the transactions ends the record get locked.

Begin Transaction

This command is used to create a transaction and once you create a transaction and then make any changes to the table then, the changes will be accessible only for the current user and the tables for which changes are made will be luck for the current user and they will not be accessible to other users until the transaction ends.

After creating a transaction and making changes to the table, if any interruption occurs like the system restarts because  of any power failure or any other reason  before the transaction is committed then the transaction will be automatically rollback.

Commit

This command is used to make all the changes in the current transaction permanent and end the transaction.

Rollback Transaction

This command is used to cancel, all the changes in the current transaction and end the transaction.

Examples

Begin Transaction

Delete from EMP where empno=1014

Commit

Rollback

Begin transaction

Delete from EMP where empno=1014

Rollback

Roll of log File in Transaction Management

When we create a transaction and then make changes to tables then every change you make will be recorded in log file.

When you create a transaction with Begin Transaction command then a unique Id will be assigned to the transaction and an entry will be returned to log file indicating the transaction is started along with transaction id.

From here onwards every change you make to a table will write two entries to the log file, before image and after image of the table along with transaction_id

               

When you commit the transaction, then first it will write one entry to the log file indicating the transaction is committed along with transaction id and then uses after image of every change in the transaction to commit the transaction.

During commit changes in the transaction are executed from top to bottom.

When you roll back the transaction , then first it will write one entry to the log file indicating the transaction is roll back along with transaction id  and then uses before image of every change in the transaction to roll back the transaction. During roll back changes in the transaction are executed from bottom to top.

Save points

Save points are used to divide the transactions into two parts and allow the user to commit one part , while the other part is RollBack.

To Create a save point, use save transaction command that has the following syntax

Save transaction <savepointname>

save transactiont1

When you roll back the transaction by specifying save point name then all the changes in the transaction prior to the save point will be committed and next to it will be roll back

Note:

With in a single transaction you can create multiple save points.But you can RollBack the transaction to only one save point, when you roll back a transaction to a particular save point, then all remaining save points in the transaction will be ignored and all the changes in the transaction prior to that save point will be comitted and all the changes Next to that save point will be RollBack.

Implicit Transaction

Setting  Implicit transaction to On and create a transaction explicity will have the followingg problem

If you create  a transactions explicity when implicity transaction is set to ON. Then when you execute the first sql statement after creating a transaction explicity , then an implicity transaction will be created and every change you make to tables will be recorded twice in the log file, one for the explicit transaction, and  one for the implicit transaction. When you commit the transaction then the implicit transaction wil be comitted, and the explicit transactions which is not committed are not roll back will be roll back  next time the sql server starts , that is the same changes that are committed by implicit transactions will be roll back. Now hence whenever you are creating a transaction explicity make sure implicit transaction is set to off.