Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Feb 8 '13 at 8:39

This question came from our site for professional and enthusiast programmers.

    
Which DBMS are you using? (Given the [dbo].[SP3] notation, it's likely to be MS SQL Server, but it's as well to be specific.) Are autonomous transactions a possibility? –  Jonathan Leffler Feb 8 '13 at 6:37
1  
Autonomous transactions are not possible in SQL Server, unless you use a loopback linked servers with enlist=false or a CLR procedure with the same trick. Not something I would recommend anyway. –  spaghettidba Feb 8 '13 at 9:16

3 Answers 3

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.

share|improve this answer
2  
Similar to mine: stackoverflow.com/a/2074139/27535 –  gbn Feb 8 '13 at 11:05

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.

share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.