Partially executed Transaction

Question

It seems like my transaction is partially saved. Are there any circumstances where a transaction is partially saved or am i missing something with in my stored procedure here is what i used

Create PROCEDURE [dbo].[AES_Transaction] (@p1 VARCHAR(MAX))
AS 
    BEGIN
       begin try
        BEGIN TRANSACTION
        exec(@p1)
        COMMIT TRANSACTION
       end try
       begin catch
    ROLLBACK TRANSACTION
    raiserror('Transaction failed, try again',16,1)
       end catch

    END

@p1 is my insert & update statements in a string format divided by semicolon

Answer 1

0

The transactions should be committed or rolledback in full. That is the basics of transactions, but there could be some situations when the error is not trapped by the catch statement and thrown to the caller, depending on the error (and SQL server version?).

If the caller (the application or another stored procedure) doesn't trap the error and make a commit then you could get this kind of situation.

Are you using two different statements in EXEC? Are you sure that the second statement is executed at all? Have you tried to validate the two different statements independent of each other? Use a print to display the code (@p1) and see if it is correct.

I use dynamic SQL with caution for a couple of reasons:

The threat from SQL injection
Performance, sql server (prior to sql server 2008) doesn't cache exection plans for "ad hoc" SQL
Maintainability, it is hard for a DBA to tune and maintain a database where the majority of the code is outside the database.
Error investigations

answered Feb 17 '10 at 04:18 AM

Håkan Winther
15k ● 29 ● 35 ● 46

Thank you for your fast response. I understand your concerns on using dynamic SQl and we are moving away from it but i still wanted to know what is wrong @p1 is passed 10 insert statements of which the first 7 committed and the last 3 did not. This is running on production and thousands of transactions have been executed of which 2 of them have this partial commit so far and if we delete and retry it works fine so it is not a bad sql statement causing it. i am guessing it may be a dead lock or a time out but it should still have been rolled back.

Feb 17 '10 at 04:03 PM Amanuel

It sounds like it could be a deadlock but tjat would have been caught in the catch block

Feb 17 '10 at 07:07 PM Håkan Winther

(comments are locked)

Partially executed Transaction

Follow this question

Related Questions