DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Commit and rollback transaction for nested execution of stored procedures

  1. #1
    Alp Guner Guest

    Commit and rollback transaction for nested execution of stored procedures

    Hello,

    I have 15 stored procedures to be used for data conversion purposes. In
    addition to their main conversion update statements, each SP has an update
    query to update a transaction log table with the information such as # of
    records read, written etc.

    These 15 stored procedures are called from another stored procedure in their
    predefined order. This calling stored procedure (SP_MASTER) also has Begin
    Transaction, Rollback Transaction and Commit Transaction to make sure even
    if one of the procedures does not go thru, it will rollback everything from
    the start. I initially thought transactions from calling and called SPs
    would work independently. So, calling SP would only roll-back uncommitted
    conversion data updates, not already committed transactions (updates to
    transaction log) in the called SP. However, this part does not work the way
    I planned. When there is an error in the conversion, the calling SP
    rolls-back everything, including the Transaction Log updates. Is there a way
    of preventing the transaction log from being rolled back?

    Thanks,

    Alp

    ---------------Calling SP-----------------
    CREATE PROCEDURE [SP_MASTER] AS

    DECLARE @IntReturn INT
    SET @IntReturn = 0

    BEGIN
    BEGIN TRANSACTION CONVERSION

    EXECUTE @IntReturn = import_A
    IF @IntReturn <> 0
    BEGIN
    ROLLBACK TRANSACTION CONVERSION
    RETURN
    END

    EXECUTE @IntReturn = import_B
    IF @IntReturn <> 0
    BEGIN
    ROLLBACK TRANSACTION CONVERSION
    RETURN
    END

    COMMIT TRANSACTION CONVERSION

    END

    -------------------------------------------


    ----------------Called SP------------------
    CREATE PROCEDURE [import_A] AS

    ....
    BEGIN TRANSACTION TRANSACTIONLOG
    INSERT INTO IMPORT_TRANSACTIONLOG
    (PROCEDURENAME, SOURCETABLE, TARGETTABLE, RECORDSREAD,
    RECORDSWRITTEN....
    ....
    COMMIT TRANSACTION TRANSACTIONLOG
    ....
    ----------------------------------------------





  2. #2
    James T. Stanley Guest

    Re: Commit and rollback transaction for nested execution of stored procedures

    That's the funny thing about SQL Server transactions... only the outermost
    one really counts when it comes to commit/rollback. It would be nice if
    rather than nested transactions you could have two separately controlled
    named transactions on a single connection at one time (but you can't).

    James


    "Alp Guner" <aguner@KSMINC.COM> wrote in message
    news:3a9c3ee9$1@news.devx.com...
    > Hello,
    >
    > I have 15 stored procedures to be used for data conversion purposes. In
    > addition to their main conversion update statements, each SP has an update
    > query to update a transaction log table with the information such as # of
    > records read, written etc.
    >
    > These 15 stored procedures are called from another stored procedure in

    their
    > predefined order. This calling stored procedure (SP_MASTER) also has

    Begin
    > Transaction, Rollback Transaction and Commit Transaction to make sure even
    > if one of the procedures does not go thru, it will rollback everything

    from
    > the start. I initially thought transactions from calling and called SPs
    > would work independently. So, calling SP would only roll-back uncommitted
    > conversion data updates, not already committed transactions (updates to
    > transaction log) in the called SP. However, this part does not work the

    way
    > I planned. When there is an error in the conversion, the calling SP
    > rolls-back everything, including the Transaction Log updates. Is there a

    way
    > of preventing the transaction log from being rolled back?
    >
    > Thanks,
    >
    > Alp
    >
    > ---------------Calling SP-----------------
    > CREATE PROCEDURE [SP_MASTER] AS
    >
    > DECLARE @IntReturn INT
    > SET @IntReturn = 0
    >
    > BEGIN
    > BEGIN TRANSACTION CONVERSION
    >
    > EXECUTE @IntReturn = import_A
    > IF @IntReturn <> 0
    > BEGIN
    > ROLLBACK TRANSACTION CONVERSION
    > RETURN
    > END
    >
    > EXECUTE @IntReturn = import_B
    > IF @IntReturn <> 0
    > BEGIN
    > ROLLBACK TRANSACTION CONVERSION
    > RETURN
    > END
    >
    > COMMIT TRANSACTION CONVERSION
    >
    > END
    >
    > -------------------------------------------
    >
    >
    > ----------------Called SP------------------
    > CREATE PROCEDURE [import_A] AS
    >
    > ....
    > BEGIN TRANSACTION TRANSACTIONLOG
    > INSERT INTO IMPORT_TRANSACTIONLOG
    > (PROCEDURENAME, SOURCETABLE, TARGETTABLE, RECORDSREAD,
    > RECORDSWRITTEN....
    > ....
    > COMMIT TRANSACTION TRANSACTIONLOG
    > ...
    > ----------------------------------------------
    >
    >
    >
    >




  3. #3
    James T. Stanley Guest

    Re: Commit and rollback transaction for nested execution of stored procedures

    I did get one other idea. I don't think that the table data type is subject
    to transactions. You could create one or more table variables that you use
    as a staging area for your "transaction log" entries. After you call
    commit/abort, write some more code outside of the transaction scope to take
    those values in the table variable and insert them into the actual tables.

    James

    "James T. Stanley" <jstanley@powerwayinc.com> wrote in message
    news:3a9d1d19$1@news.devx.com...
    > That's the funny thing about SQL Server transactions... only the outermost
    > one really counts when it comes to commit/rollback. It would be nice if
    > rather than nested transactions you could have two separately controlled
    > named transactions on a single connection at one time (but you can't).
    >
    > James
    >
    >
    > "Alp Guner" <aguner@KSMINC.COM> wrote in message
    > news:3a9c3ee9$1@news.devx.com...
    > > Hello,
    > >
    > > I have 15 stored procedures to be used for data conversion purposes. In
    > > addition to their main conversion update statements, each SP has an

    update
    > > query to update a transaction log table with the information such as #

    of
    > > records read, written etc.
    > >
    > > These 15 stored procedures are called from another stored procedure in

    > their
    > > predefined order. This calling stored procedure (SP_MASTER) also has

    > Begin
    > > Transaction, Rollback Transaction and Commit Transaction to make sure

    even
    > > if one of the procedures does not go thru, it will rollback everything

    > from
    > > the start. I initially thought transactions from calling and called SPs
    > > would work independently. So, calling SP would only roll-back

    uncommitted
    > > conversion data updates, not already committed transactions (updates to
    > > transaction log) in the called SP. However, this part does not work the

    > way
    > > I planned. When there is an error in the conversion, the calling SP
    > > rolls-back everything, including the Transaction Log updates. Is there a

    > way
    > > of preventing the transaction log from being rolled back?
    > >
    > > Thanks,
    > >
    > > Alp
    > >
    > > ---------------Calling SP-----------------
    > > CREATE PROCEDURE [SP_MASTER] AS
    > >
    > > DECLARE @IntReturn INT
    > > SET @IntReturn = 0
    > >
    > > BEGIN
    > > BEGIN TRANSACTION CONVERSION
    > >
    > > EXECUTE @IntReturn = import_A
    > > IF @IntReturn <> 0
    > > BEGIN
    > > ROLLBACK TRANSACTION CONVERSION
    > > RETURN
    > > END
    > >
    > > EXECUTE @IntReturn = import_B
    > > IF @IntReturn <> 0
    > > BEGIN
    > > ROLLBACK TRANSACTION CONVERSION
    > > RETURN
    > > END
    > >
    > > COMMIT TRANSACTION CONVERSION
    > >
    > > END
    > >
    > > -------------------------------------------
    > >
    > >
    > > ----------------Called SP------------------
    > > CREATE PROCEDURE [import_A] AS
    > >
    > > ....
    > > BEGIN TRANSACTION TRANSACTIONLOG
    > > INSERT INTO IMPORT_TRANSACTIONLOG
    > > (PROCEDURENAME, SOURCETABLE, TARGETTABLE, RECORDSREAD,
    > > RECORDSWRITTEN....
    > > ....
    > > COMMIT TRANSACTION TRANSACTIONLOG
    > > ...
    > > ----------------------------------------------
    > >
    > >
    > >
    > >

    >
    >




  4. #4
    Guest

    Re: Commit and rollback transaction for nested execution of stored procedures

    When you think about it though, what youre really saying is that you have a
    transaction that is not REALLY a transaction, but rather a series of
    seperate transactions.

    You can't allow some transactions to commit and others not to within an
    outer transaction.

    Sounds like you might be able to reorder and reposition some of your BEGIN
    TRANS and COMMIT/ROLLBACKS and get the behaviour your after...

    Its easy to put too much into a transaction. If it really doesn't matter to
    the transaction, it probably shouldn't be in one.




    "Alp Guner" <aguner@KSMINC.COM> wrote in message
    news:3a9c3ee9$1@news.devx.com...
    > Hello,
    >
    > I have 15 stored procedures to be used for data conversion purposes. In
    > addition to their main conversion update statements, each SP has an update
    > query to update a transaction log table with the information such as # of
    > records read, written etc.
    >
    > These 15 stored procedures are called from another stored procedure in

    their
    > predefined order. This calling stored procedure (SP_MASTER) also has

    Begin
    > Transaction, Rollback Transaction and Commit Transaction to make sure even
    > if one of the procedures does not go thru, it will rollback everything

    from
    > the start. I initially thought transactions from calling and called SPs
    > would work independently. So, calling SP would only roll-back uncommitted
    > conversion data updates, not already committed transactions (updates to
    > transaction log) in the called SP. However, this part does not work the

    way
    > I planned. When there is an error in the conversion, the calling SP
    > rolls-back everything, including the Transaction Log updates. Is there a

    way
    > of preventing the transaction log from being rolled back?
    >
    > Thanks,
    >
    > Alp
    >
    > ---------------Calling SP-----------------
    > CREATE PROCEDURE [SP_MASTER] AS
    >
    > DECLARE @IntReturn INT
    > SET @IntReturn = 0
    >
    > BEGIN
    > BEGIN TRANSACTION CONVERSION
    >
    > EXECUTE @IntReturn = import_A
    > IF @IntReturn <> 0
    > BEGIN
    > ROLLBACK TRANSACTION CONVERSION
    > RETURN
    > END
    >
    > EXECUTE @IntReturn = import_B
    > IF @IntReturn <> 0
    > BEGIN
    > ROLLBACK TRANSACTION CONVERSION
    > RETURN
    > END
    >
    > COMMIT TRANSACTION CONVERSION
    >
    > END
    >
    > -------------------------------------------
    >
    >
    > ----------------Called SP------------------
    > CREATE PROCEDURE [import_A] AS
    >
    > ....
    > BEGIN TRANSACTION TRANSACTIONLOG
    > INSERT INTO IMPORT_TRANSACTIONLOG
    > (PROCEDURENAME, SOURCETABLE, TARGETTABLE, RECORDSREAD,
    > RECORDSWRITTEN....
    > ....
    > COMMIT TRANSACTION TRANSACTIONLOG
    > ...
    > ----------------------------------------------
    >
    >
    >
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center