CODEWITHSUNDEEP
sqlsql-server
user1557886
user1557886

Reputation: 257

Multiple transactions in a single stored procedure

I need to delete data from 2 tables in SQL Server 2008.

I have two tables A and B. I need to put each in a separate transaction.

This is the code I am using.

BEGIN TRANSACTION;
BEGIN TRY
    DELETE      from A
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;


BEGIN TRANSACTION;
BEGIN TRY
    DELETE     from B
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

Is there any better way to implement multiple transactions and error handling in SQL Server?

I need to put separate transactions for each table.

I am getting an error when one of the transactions is failed.

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. the records are not deleting from other transaction

Upvotes: 2

Views: 28597

Answers (3)

Randy in Marin
Randy in Marin

Reputation: 1143

Every query is in an implicit transaction, so an explicit transaction for a single query is redundant. A DELETE will either delete all the records or none of them, not some of them and then fail.

If the two delete queries need to succeed or fail together, then the explicit transaction is required.

If these are huge tables, then deleting a few records at a time can be more efficient without an explicit transaction. (Done this many times.) However, if it has to be a single transaction, then putting the separate smaller deletes in a larger transaction will not prevent transaction log bloat. I don't know if it will be faster in this case. In the old days, I remember using a single transaction could require hours rather than minutes to complete the process. In one case, it would never finish...we gave up after several days.

Upvotes: 1

user1626780
user1626780

Reputation: 1

May be you can do this way:

BEGIN TRANSACTION one;
BEGIN TRY
    DELETE      from A
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION one;

END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION one;


BEGIN TRANSACTION two;
BEGIN TRY
    DELETE     from B
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION two;
END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION two;

Upvotes: 0

AnandPhadke
AnandPhadke

Reputation: 13496

try this:

BEGIN TRANSACTION;
BEGIN TRY
    DELETE from A
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH


BEGIN TRANSACTION;
BEGIN TRY
    DELETE     from B
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
END CATCH

Upvotes: 8

Related Questions