Pages

Tuesday, March 19, 2013

SQL 2005 - Understanding Auto Commit, Implicit, Explicit Transaction

1. Introduction


In SQL Server we call a complete set of action as "Transaction". The complete set of action together forms a meaningful change to the database. Say for Example, you are transferring money from one bank account to another one. This involves the below specified actions:

  1. Create a withdraw entry in the First back account
  2. Reduce the Balance in your First Bank account
  3. Create a Deposit entry in the second bank entry
  4. Increment the Balance in the Second bank account.

Now consider replacing first bank account as Savings Account and second bank account as Current Account within the same bank (For easy understanding). Now we say the bank website will consider all these four actions together as Money Transfer Transaction. Even a single action fails the entire transaction get cancelled to avoid existence of unmatched records for the account holder.

In this article, we will explore how do we use transactions in SQL server.

In SQL Server, we can set three different types of transactions. These transactions are:

  1. Auto Commit Transactions
  2. Implicit Transactions
  3. Explicit Transactions

2. Auto Commit Transactions


In "Auto Commit Transaction" mode, SQL Server immediately commits the change after executing the statement. That’s why we call this as Auto commit transaction. Auto commit transaction is the default transaction mode in SQL Server.

Have a look at the video 1.


In the video you can observe that we executed three update statements by highlighting each update statement one by one. The second update statement is made wrong intentionally. After executing all three statements, the select query result shows first and third update persisted to the database. Here, after executing each statement, the change is committed to the database automatically. That is why we call this as Auto Commit Transaction, which is the default transaction mode in SQL server 2005.

Now, have a look at the video 2.


The script is same. We have same three update statements with a same spelling mistake in the second update statement. However, this time we update all three update statements at once, by selecting all three and hitting the execute button. Here, all these three statements are executed on a single go and hence we call these three statements as batch. Since the second update statement in the batch is failed, none of the row update takes place in the database. In this case, a Roll-back is performed because of the batch failure (2nd update).

The "Go" statement actually forms the batch of SQL statement. We can write the script for video 1 and video 2 (The way the update statements are executed) as shown below:

--Example 1
--Batch 1
Update Authors Set Au_LName = 'Black' where Au_Id = '172-32-1176';
Go
--Batch 2
Update Authors Ser Au_LName = 'Voyer' where Au_Id = '213-46-8915';
Go
--Batch 3
Update Authors Set Au_LName = 'Peterson' where Au_Id = '238-95-7766';
Go
--Batch 4
Select Au_Id, Au_Fname + ',' + Au_Lname as Name,
Phone, City from Authors where Au_id in ('172-32-1176',
        '213-46-8915','238-95-7766');
Go
--Batch 5
Update Authors Set Au_LName = 'White' where Au_Id = '172-32-1176';
Update Authors Set Au_LName = 'Green' where Au_Id = '213-46-8915';
Update Authors Set Au_LName = 'Carson' where Au_Id = '238-95-7766';
Go
--Example 2
--Treat All three as a Single Batch.
--Batch 1
Update Authors Set Au_LName = 'Black' where Au_Id = '172-32-1176';
Update Authors Ser Au_LName = 'Voyer' where Au_Id = '213-46-8915';
Update Authors Set Au_LName = 'Peterson' where Au_Id = '238-95-7766';
Go
--Batch 2
Select Au_Id, Au_Fname + ',' + Au_Lname as Name,
Phone, City from Authors where Au_id in ('172-32-1176',
        '213-46-8915','238-95-7766');
Go
--Batch 3
Update Authors Set Au_LName = 'White' where Au_Id = '172-32-1176';
Update Authors Set Au_LName = 'Green' where Au_Id = '213-46-8915';
Update Authors Set Au_LName = 'Carson' where Au_Id = '238-95-7766';
Go

From the above statements, I can say that SQL server performs a Commit or Rollback after the Go statement is encountered. Hence, in the Auto commit transaction we don’t have to control the commit and rollback as it is taken care by the SQL Server. 

Also note that the roll-back will not be applied when SQL Server encounters a Runtime error. Imagine that in example 2, instead of the misspelled keyword Set we violate a constraint (Say Primary key constraint) in the second statement. In this case, SQL server commits first and third statement.

3. Implicit Transactions

In "Implicit Transaction" Mode, we can control the "Rollback" and the "Commit" operation. A new transaction automatically begins after the commit/Rollback. We can turn ON and Turn OFF this transaction mode based on the need.
In the above picture, two commit and one rollback statement are executed after turning on the Implicit Transaction. As already told after a commit or rollback a new transaction gets started and that is how you see three transactions in the above depiction. Once you turn OFF the implicit transaction, the transaction will be set back to the default Auto Commit transaction.

Use the below SQL statements to Turn-ON or Turn-OFF the implicit transaction:

Set Implicit_Transactions On;
Set Implicit_Transactions Off;

Now Have a look at the below given example:

--Example 3
Set Implicit_Transactions On;

--Transaction 1
Update Authors Set Au_LName = 'Black' where Au_Id = '172-32-1176';
commit;

--Transaction 2
Update Authors Set Au_LName = 'Voyer' where Au_Id = '213-46-8915';
Rollback;

--Transaction 3
Update Authors Set Au_LName = 'Peterson' where Au_Id = '238-95-7766';
commit;

--Select the Authors, to see the effect of Update statement
Select Au_Id, Au_Fname + ',' + Au_Lname as Name,
Phone, City from Authors where Au_id in ('172-32-1176',
        '213-46-8915','238-95-7766');

--Revert back to Original
Update Authors Set Au_LName = 'White' where Au_Id = '172-32-1176';
Update Authors Set Au_LName = 'Green' where Au_Id = '213-46-8915';
Update Authors Set Au_LName = 'Carson' where Au_Id = '238-95-7766';
Go

Set Implicit_Transactions Off;

In this example, three update statements are executed followed by either a Rollback or a commit. Note that, here we have the Freedom of specifying where we want to do a commit or a roll back which we cannot do in the default Auto Commit Transaction mode. Also be aware that even though we can specify where we want to end the transaction, it is not possible to control the starting of the transaction. Because, the transaction starts immediately after it ends.

After the first Update statement, we asked for a commit, so the update of author’s last name as Black is committed to the database and new transaction is started after that. The second Transaction comes to an End when the rollback statement is executed. The update of author’s last name (as Voyer) kept in the memory is discarded after the rollback and one more new Transaction is started. Note that at the end of the script we turned off the implicit transaction. Once the implicit transaction is turned off, the mode changes to default Auto Commit transaction mode.

OK. What is the Final result of executing this script? The first and last update statement committed to the database and the second update is discarded.

4. Explicit Transactions


In "Explicit Transaction", we control the starting and ending of the transaction. Look at the below Pic:
As we control the starting and ending of the transaction, this transaction type is widely used while making the compiled unit of code Say for Ex. Stored Procedures. Have a look at the below specified code:


Here we started two transactions and note that the transaction will end when there is a commit or rollback. In this example, the first transaction will get succeed and the “Data Inserted message” will appear in the message area while executing the script. The second Transaction fails as Not Null column Contract is skipped from the insert (Note skipping a column means we like to leave the column as null) statement.


As this is a constraint violation and this happens at runtime the "@@Error" in our script returns a Non-Zero Error number. When the statement execution succeeds without any problem, we do get zero in the "@@Error environment variable". The remaining portion of the script is understandable. If you need complete example, you can take that from below:

--Example 04
Declare @ErrNo int
Begin Transaction;
Insert into Authors(Au_id, au_lname, au_fname, Phone, contract)
values ('112-33-1811', 'Jane', 'Marey', '409 210-2331', 1);
Set @ErrNo = @@Error;
if @ErrNo != 0
        Begin
               Print 'Error Occurred. Transaction Cancelled';
               RollBack;
        End
else
        Begin
               Print 'Data Inserted.';
               Commit;
        End
Begin Transaction;
Insert into Authors(Au_id, au_lname, au_fname, Phone)
values ('112-33-1234', 'Billy', 'Jones', '409 234-2232');
Set @ErrNo = @@Error;
if @ErrNo != 0
        Begin
               Print 'Error Occurred. Transaction Cancelled';
               RollBack;
        End
else
        Begin
               Print 'Data Inserted.';
               Commit;
        End
-- Revert back the Change
Delete from Authors where Au_Id = '112-33-1811';

4 comments:

  1. What if the code is closing connection without committing or rollback in both implicit and explicit transactions with auto-commit set to on and off ?

    .. Sanjay

    ReplyDelete
  2. Great Article and thanks

    ReplyDelete

Leave your comment(s) here.