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


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

more ▼

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

avatar image

21 1 1 3

(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

avatar image

Håkan Winther
16.6k 37 46 58

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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Feb 16, 2010 at 10:07 PM

Seen: 1564 times

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

Copyright 2018 Redgate Software. Privacy Policy