x

Partially executed Transaction

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

more ▼

asked Feb 16, 2010 at 10:07 PM in Default

Amanuel gravatar image

Amanuel
21 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Feb 17, 2010 at 04:18 AM

Håkan Winther gravatar image

Håkan Winther
15.7k 35 37 48

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, 2010 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, 2010 at 07:07 PM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x60

asked: Feb 16, 2010 at 10:07 PM

Seen: 1328 times

Last Updated: Feb 17, 2010 at 04:03 AM