Search Mallstuffs

Flag Counter
Spirituality, Knowledge and Entertainment




Latest Articles


Move to top
Nested Transaction, SavePoint and error handling in sql server
Posted By Sarin on Mar 19, 2013     RSS Feeds     Latest Hinduism news
5334 Views

In my earlier article, we saw how to us transactions in SQL server. In this article, we will see the use of nested transactions in SQL server. Nested transaction will be useless without error handling and savepoints. So, we would also look at error handling and savepoint in this article.
  
Why Nested Transactions?
Nested transactions are one of the important features of SQL. As the name suggests, nested transaction means a transaction within transaction. Nested transactions let you split your large database operations into smaller chunk of operations. Operation is issued with BEGIN TRAN command. You can determine the number of active transactions using @@TRANCOUNT automatic variable. 0 means you are at first level. 1 means you are at first nested transactions, 2 mean second nested transactions and so on. Data is not committed till the first outermost transaction is successful. So, even after committing the inner transaction, if the outer transaction fails, then the data committed by the inner transaction will also be rolled back. Rollback operation, on the other hand, works at all level. Irrespective of whether you use rollback at the first level or the 10th level, all the operations will be rolled back.
Count of @@TRANCOUNT automatic variable increases by 1 for every transaction. Commit operation decreases @@TRANCOUNT by one. ROLLBACK reduces the count directly to 0.

Above figure explains how the commit and rollback statement works. Commit only commit the local transactions while rollback undo changes done by all the transactions. Thus nested transactions implicates that data is not committed until the last commit statement is executed. Even if you use ten commit statements in nested transactions, they are of no significance till the last commit is successful.
Savepoint in SQL server
Savepoint is one more important feature of SQL. Using save point, you can rollback portions of nested transactions. Savepoint is like a marker you can return to incase you want to revert the changes done after that point. If is like rolling the transactions up to the point where the data is consistent. Savepoint are declared using via the SAVE TRAN statement. @@TRANCOUNT automatic variable stays unaffected by the use of SAVE TRAN statement.  
        SAVE TRAN SP2  

Error Handling in SQL server
Error handling is also very important in Sql server. Whenever an error occurs in a database operation, best thing is to rollback the entire transactions else if the error is not severe, you can return the control back to the transaction. @@ERROR automatic variable is used to track and implement error handling in SQL. This variable stores the error id of the last error statement executed by the client. This variable stores 0 if no error is encountered. You should check the value of @@ERROR after triggering any statement which you think might result in error. Check the value of @@ERROR immediately after your DML statement. If it is greater than zero than it means some error occurred. @@ERROR value is updated after every successful command. So you need to check the value of @@ERROR immediately after running your DML command.  
Following piece of code shows how to use error @@ERROR variable
  IF (@@ERROR <> 0) BEGIN
        PRINT Some error occurred...'
        ROLLBACK TRAN
        RETURN 1
    END
COMMIT TRAN

Raise your own custom Error
You can also raise your own custom error message using RAISERROR statement. This statement has three parameters.  
First is your custom error message with optional format designators.
Second parameter defines the severity of error which ranges from 0 to 25. User can set severity level from 0 to 18. 0 to 10 means a warning whereas 11 to 18 mean errors. Level 19 to 25 are used by only members of sysadmin role. Level 19 to 25 mandates the use of LOG option.  Severity levels from 20 to 25 are extremely fatal for database. Fatal error terminates the client connections and logs the error in error log and application log.
Third parameter can be any value between 1 through 127. This parameter is used to pass additional user defined code to be carried by the exception. Normally, it is not required and quite often is set to 1.
RAISERROR ('Errors found, please fix these errors and retry', 1, 2) WITH SETERROR
RETURN

Demo:
In below chunk of code, I begin a transaction by updating a user last name. I check for error. If error then raises custom error else create a savepoint. Then I begin a nested transaction which updates another user last name. Again, I check for error, if error raise it else create second savepoint. Then I rollback to first save point.Now, if I check the state of my userinfo table then I notice that result of my first update statement is persisted while the second update has been rolled back. Next On rolling back the entire transaction, my first update statement is also rolled back.

BEGIN TRAN main
    print 'start of Transaction main:'+cast(@@TRANCOUNT as varchar(5))
     -- No of Transactions: 1
    update UserInfo set LastName='Singh' where UserId=1
    IF @@ERROR <> 0
     BEGIN
      -- Rollback the transaction
      ROLLBACK
      -- Raise an error and return
      RAISERROR ('Error in updating records of UserInfo.', 16, 1)
      RETURN
     END
    SAVE TRAN SP1  -- Create savepoint
    print 'After first savepoint:'+cast(@@TRANCOUNT as varchar(5))
   -- No of Transactions: 1
  
    BEGIN TRAN first
        print 'start of nested first transaction:'+cast(@@TRANCOUNT as varchar(5))
        -- No of Transactions: 2
       update UserInfo set LastName='Mandal' where UserId=3
        IF @@ERROR <> 0
        BEGIN
         -- Rollback the transaction
         ROLLBACK
         -- Raise an error and return
         RAISERROR ('Error in deleting records of UserInfo.', 14, 1)
         RETURN
        END
        SAVE TRAN SP2  -- Create second savepoint
        print 'After second savepoint:'+cast(@@TRANCOUNT as varchar(5))
        -- No of Transactions: 2
    ROLLBACK TRAN SP1
      print 'After ROLLBACK to first savepoint'++cast(@@TRANCOUNT as varchar(5))
     -- No of Transactions: 2
    SELECT * FROM UserInfo
IF (@@TRANCOUNT > 0) BEGIN
    ROLLBACK TRAN
    print 'After ROLLBACK whole transaction'+cast(@@TRANCOUNT as varchar(5))
     SELECT * FROM UserInfo
    -- No of Transactions: 0 (rollback command rolls back all transactions)
END
  
Output:
As you see above, my first table shows the persisted state of first update statement while in the second table, that persisted change has been rolled back.
  


Conclusion:
In this article, we saw the use of nested transactions, error handling, savepoint and raising custom errors in SQL server.


  

Note: Images used on this website are either a production of Bhaktivedanta Book Trust(http://www.krishna.com), Iskcon Foundation or were found in google search under "Free to use and share". If any of the images presented here violates copyright issues or infringes anyone copyright or are not under "Fair use", then please bring it to our notice. Read Disclaimer for more.

Share this to your friends. One of your friend is waiting for your share.

Related Articles
Nested Transaction, SavePoint and error handling in sql server

Post Comment




Software developer by profession,
Poet by hobby,
Blogger by Passion
Birth Place: Munger, Bihar
Residence: Mumbai, Maharashtra
Prof exp: 8 years

Read More about me

Read my daily what'sapp quotes
Most Viewed Articles

Latest Spiritual Videos


Latest Funny Videos

Latest News

Latest Social Post
Latest WhatsApp Quotes