Transaction in SQL Server

Question

Consider a scenario, when 2 Stored Procedure called inside a Stored procedure like follows. Note: All 3 SP’s are implemented with TRANSACTION.

CREATE PROC [dbo].[SP3]
AS
BEGIN
      BEGIN TRAN
EXEC SP1
EXEC SP2

      COMMIT TRAN
      END

GO

What happens when an error happened in SP2.

a. Commits SP1 alone and raise error for SP2

b. Rollback SP1 and SP2

c. Syntax Error

d. Commits SP1 and SP2 with Error Message

Please give the answer.

Thanks.

Answer 1

I assume you are working on SQL Server, given question tags and T-SQL notation.

Nesting transactions in stored procedures as you are doing can turn into a mess. Basically, when you start a nested transaction, SQL Server does nothing but increment the transaction count and doesn't really start a "new" transaction. Every time you commit a transaction, you decrease the transaction count.

Usually I try to avoid using nested transactions and instead I check for existing transactions to "reuse". Here you can find the stored procedure code template I use to handle nested procedure calls without nesting transactions.

Other than that, you have to handle commits and rollbacks with savepoints to obtain real transaction isolation from the "parent" transaction. If you don't do that, you get the default behavior, which is commit for the innermost transaction and rollback for the whole transaction stack.

Also, take into account that transactions need proper error handling and you have to make sure you get a rollback whenever you encounter an error. TRY/CATCH blocks can handle that for you, as I'm showing in the template I linked. Another thing worth noting is that even SET options can affect the code flow, so make sure you have XACT_ABORT set accordingly.

Answer 2

The answer may depend on the DBMS which you're using. However, in the DBMS I'm most familiar with (Informix), when you start a transaction explicitly as shown, I would expect that if there is an error in SP2, then an exception would be raised in SP2, and since there is no exception handling in SP3 either, the error would propagate back to the caller — and the COMMIT in SP3 would not be executed. The caller would need to know that the transaction was started and decide whether to commit or rollback. If the caller didn't make that decision and simply exited, the transaction would be rolled back (because it was never committed explicitly). As a consequence of this, the actions completed by SP1 would not be automatically rolled back, and neither would the actions completed by SP2 (but the SQL operation that triggered the error would be treated as if it was never executed).

I observe that the lack of exception handling means that the stored procedure SP3 is not very well written. Since it starts a transaction, it should handle errors and ensure that the transaction is rolled back if an error occurs (or committed if that is deemed to be the better course of action). It should never leave a transaction in flight as there was no transaction in flight when it was called.

I believe some other DBMS take the (to my mind) draconian view that once an error has occurred in a transaction, all further operations also fail and the transaction will be rolled back. That seems overly intolerant. If I understand their manuals correctly, it means that if you try to insert a record and find that the record already exists, you can't do an update instead. I hope I misread that book (or that the book was mistaken). But it does emphasize the importance of stipulating the database you're using — different DBMS may have different answers to the same question.

Answer 3

The answer to your question may depend on which database you are running and how the database (server) is configured. But in general, why not just try it out on your server, here is a simple example: (In my case with SQL Server 2008 - out of the box, the result was that I get an error, but SP2 will execute as if nothing happened. Which is what the documentation http://msdn.microsoft.com/en-us/library/ms189336(v=sql.105).aspx says)

CREATE TABLE [dbo].[SimpleTable](
    [ID] [int] NOT NULL
) ON [PRIMARY]

GO

CREATE PROCEDURE SP1 
AS
BEGIN
    BEGIN TRAN

    INSERT INTO SimpleTable
    (ID)
    VALUES
    (1)
    COMMIT TRAN
END
GO

CREATE PROCEDURE SP2
AS
BEGIN
    BEGIN TRAN

    INSERT INTO SimpleTable
    (ID)
    VALUES
    (NULL)
    COMMIT TRAN
END
GO

CREATE PROCEDURE SP3
AS
BEGIN
    BEGIN TRAN

    EXEC SP1
    EXEC SP2

    COMMIT TRAN
END
GO

EXEC SP3

select *
from SimpleTable