Auto-commit commands and Compensating transactions in SQL

As the name suggests, the auto-commit statement is used to avoid the explicit mention of the commit command after each SQL statement. The commit statement itself gets executed by the server after each SQL statement. Each SQL statement is treated as a different transaction. An SQL statement executed in autocommit mode cannot be rolled back.

Auto-commit Commands :

There are four Auto-commit commands that exist in SQL, they are:

1. SET AUTOCOMMIT ON –
   By executing this particular command, the auto-commit status turned to be ON, if it is OFF initially. This command is itself executed by the SQL server initially. After execution, the commit statement itself gets executed after each SQL statement. Here each statement is a separate transaction.
   
2. SET AUTOCOMMIT OFF –
   This instruction is just the reverse of the first one. After executing this, the auto-commit status is changed to OFF. Now, the user needs to explicitly mention the commit statements, wherever required. Here Commit() or rollback() completes a transaction.
   
3. SET AUTOCOMMIT INT_VALUE –
   This instruction is used to limit the number of statements, which itself gets auto-committed by the server. For example, after the execution of the below command, the auto-commit remains ON for every 9 transactions, and then it turned OFF by itself. SET AUTOCOMMIT 9
   
4. SHOW AUTOCOMMIT –
   This statement is used to determine the current status of the auto-commit so that the database users can change it according to the requirements. Hence, this statement has only two possible outcomes, either ON or OFF.

   It completely depends upon the type of the database and the operations that need to be performed in the database that if the status would be kept ON or OFF. Because the changes or the modifications can’t be undone if the auto-commit is ON. The ROLLBACK statement has no effect if the AUTO-COMMIT is ON. Here, the concept of compensating transactions walks in. Yes, you can’t undo the changes but can write a compensating transaction to get back the previous state of the database.  

Let’s take a look at what compensating transactions actually are.

Compensating Transactions in SQL :

A compensating transaction is nothing but a list of database operations that are capable to undo the changes of an incomplete or inconsistent transaction. Compensating transactions are helpful to get back to the previous consistent state of the database. Let’s consider a simple example. A user deleted an important record from the database accidentally. So, the compensating transaction is nothing but the insertion of that record again in the database. Also, the compensating transactions do not ensure the entire restoration of the database initial state, since there is a chance that the other transactions may start executing meanwhile.