Commiting only Specific Changes made inside a TRANSACTION which may ROLLBACK

Question

This is a significant edit from the original question, making it more concise and covering the points raised by existing answers...

Is it possible to have mulitple changes made to multiple tables, inside a single transaction, and rollback only some of the changes?

In the TSQL below, I would NOT want any of the changes made by "myLogSP" to ever be rolled back. But all changes made by the various myBusinessSPs should rollback if necessary.

BEGIN TRANSACTION  

    EXEC myLogSP

    EXEC @err = myBusinessSPa
    IF (@err <> 0) BEGIN ROLLBACK TRANSACTION RETURN -1 END

    EXEC myLogSP

    EXEC @err = myBusinessSPb
    IF (@err <> 0) BEGIN ROLLBACK TRANSACTION RETURN -1 END

    EXEC myLogSP

    EXEC @err = myBusinessSPc
    IF (@err <> 0) BEGIN ROLLBACK TRANSACTION RETURN -1 END

    EXEC myLogSP

COMMIT TRANSACTION
RETURN 0

The order is important, the myLogSPs must happen between and after the myBusinessSPs (the myLogSPs pick up on the changes made by the myBusinessSPs)

It is also important that all the myBusinessSPs happen inside one transaction to maintain database integrity, and allow all their changes to rollback if necessary.

It's as if I want the myLogSPs to behave as if they're not part of the transaction. It is just an inconvenient fact that they happen to be inside one (by virtue of needing to be called between the myBusinessSPs.)

EDIT:

Final answer is "no", the only option is to redesign the code. Either to using table variables for the logging (as variables don't get rolled back) or redesign the business logic to Not require Transactions...

vladr · Accepted Answer · 2009-03-07T16:07:26.830

5

Use SAVEPOINTs, e.g.

BEGIN TRANSACTION  

    EXEC myLogSP

    SAVE TRANSACTION savepointA
    EXEC @err = myBusinessSPa
    IF (@err <> 0) BEGIN
        ROLLBACK TRANSACTION savepointA
        COMMIT
        RETURN -1
    END

    EXEC myLogSP

    SAVE TRANSACTION savepointB
    EXEC @err = myBusinessSPb
    IF (@err <> 0) BEGIN
        ROLLBACK TRANSACTION savepointB
        COMMIT
        RETURN -1
    END

    EXEC myLogSP

    SAVE TRANSACTION savepointC
    EXEC @err = myBusinessSPc
    IF (@err <> 0) BEGIN
        ROLLBACK TRANSACTION savepointC
        COMMIT
        RETURN -1
    END

    EXEC myLogSP

COMMIT TRANSACTION

EDIT

Based on the information provided so far (and my understanding of it) it appears that you will have to re-engineer you logging SPs, either to use variables, or to use files, or to allow them to run 'after the fact' as follows:

BEGIN TRANSACTION  

    SAVE TRANSACTION savepointA
    EXEC @err = myBusinessSPa
    IF (@err <> 0) BEGIN
        ROLLBACK TRANSACTION savepointA
        EXEC myLogSPA -- the call to myBusinessSPa was attempted/failed
        COMMIT
        RETURN -1
    END

    SAVE TRANSACTION savepointB
    EXEC @err = myBusinessSPb
    IF (@err <> 0) BEGIN
        ROLLBACK TRANSACTION savepointB
        EXEC myLogSPA -- the call to myBusinessSPa originally succeeded
        EXEC myLogSPB -- the call to myBusinessSPb was attempted/failed
        COMMIT
        RETURN -1
    END

    SAVE TRANSACTION savepointC
    EXEC @err = myBusinessSPc
    IF (@err <> 0) BEGIN
        ROLLBACK TRANSACTION savepointC
        EXEC myLogSPA -- the call to myBusinessSPa originally succeeded
        EXEC myLogSPB -- the call to myBusinessSPb originally succeeded
        EXEC myLogSPC -- the call to myBusinessSPc was attempted/failed
        COMMIT
        RETURN -1
    END

    EXEC myLogSPA -- the call to myBusinessSPa succeeded
    EXEC myLogSPB -- the call to myBusinessSPb succeeded
    EXEC myLogSPC -- the call to myBusinessSPc succeeded

COMMIT TRANSACTION

edited Mar 07 '09 at 16:07

answered Mar 06 '09 at 14:00

vladr

65,483
18
129
130

I think you should remove the else statement, otherwise the first insert would never be committed in case of a rollback. – Jimmy Stenke Mar 06 '09 at 14:08
In my experience ROLLBACK TRANSACTION will rollback all open transactions, not just the current level of the nested transactions... – MatBailie Mar 06 '09 at 14:10
No, not those cases where you uses named transactions or save points, then it will roll back to that transaction or save point that you've named. – Jimmy Stenke Mar 06 '09 at 14:13
I have made an edit which explains better what I need. Thanks for the info on named transaction and save points, I'm sure I'll be using that soon. But I don't think this solves it for me... – MatBailie Mar 06 '09 at 14:20
that question is getting stupidly long, should I delete the question and post a new one with more (better) detail? leave it as is? or edit the question down to what I would otherwise post ina new question? (I don't know the etiquette *grin*) – MatBailie Mar 06 '09 at 14:53
That was my first thought when I saw SAVE TRANSACTION for the first time (thanks again, I didn't know about it). However (sorry) If I rollback after SavePointC I want the effects of myBusinessSPa and myBusinessSPb to also rollback. Without ANY of the myLog effects rolling back *cringe* – MatBailie Mar 06 '09 at 15:27
+1 for entertaining my inept drivels and being of most help so far :) – MatBailie Mar 06 '09 at 15:36
@Dems, how exactly do myLogSP and myBusinessSPXXX interact? Do you have control over what myLogSP is doing (i.e. get it to write its data into an array instead of going directly to table, and you flush the entire array to table at the very end after rollback savepoint but before commit? – vladr Mar 06 '09 at 18:49
...essentially HLGEM's avenue of investigation. – vladr Mar 06 '09 at 18:51
This is a generic question and is deliberately generalised. myBusinesSP can have any effect (insert, update, delete) on multiple tables. The myLogSPs update different tables based on the effects of the myBusinessSPs. Because this logging can be complex it is encapsulated into SPs modifying tables – MatBailie Mar 07 '09 at 14:48
Unfortunately your only way out appears to be reengineering the logging SPs and/or the logging mechanism (how the logging methods obtain their data.) You cannot break up your transaction as selectively as you wish, and inner transactions are no help either – vladr Mar 07 '09 at 16:03
(I quote, "Committing inner transactions is ignored by the SQL Server Database Engine") – vladr Mar 07 '09 at 16:05
This is what I thought, but hoped there was something out there I did not know. |A simple re-engineering won't be possible, the logging needs to know the state between the business SPs, information lost by the end of the transaction. Thanks for looking at it though... – MatBailie Mar 07 '09 at 18:33
The only re-engineering I can see is to engineer the business SPs to not require transactions. Difficult and not completely clean, but possible and less untidy that the alternatives... – MatBailie Mar 07 '09 at 18:36
It should be noted that rolling back a nested transaction is bad news. If the inner rollback statement is executed WITHOUT a transaction name, it will rollback ALL open transactions and any rollbacks or commits for the outer transaction will fail because there are no open transactions. If the inner rollback is executed with the name of the inner transaction, an exception is thrown. This happens whether its all in the same statement or from nested procedure calls. I know this because, not believing it to be true, I wrote sql to test it and that is exactly what happened. – Richard B Nov 04 '10 at 20:27

HLGEM · Answer 2 · 2009-03-06T18:18:13.310

2

We have had luck with putting the log entries into table variables and then inserting to the real tables after the commit or rollback.

OK if you aren't on SQL Server 2008, then try this method. It's messy and a workaround but it should work. The #temp table and the table variable would have to be set up with the structure of what is returned by the sp.

create table #templog (fie1d1 int, field2 varchar(10))

declare @templog table (fie1d1 int, field2 varchar(10))

BEGIN TRANSACTION      
insert into #templog
Exec my_proc

insert into @templog (fie1d1, field2)
select t.* from #templog t 
left join @templog t2 on t.fie1d1 = t2.fie1d1 where t2.fie1d1 is null

insert into templog
values (1, 'test')

rollback tran
select * from #templog
select * from templog
select * from @templog

edited Mar 06 '09 at 18:18

answered Mar 06 '09 at 14:46

HLGEM

94,695
15
113
186

Heh... I saw that a new answer came in just as I was pasting my answer in and I thought, "I'll bet someone else just pointed out table variables". +1 :) – Tom H Mar 06 '09 at 14:48
This is the only option I can see right now too. But it has implications. The inserts in to the logging tables are done by a SP, which maintains timestamps and other metadata. Copying that code into multiple places in multiple SPs is not 'ideal' :( – MatBailie Mar 06 '09 at 14:56
YOu can insert into a table variable from an sp in newer version of SQL Server. SQL Server 2000 you have to use temp tables instead. I haven't tried that, so I don't know if they stay in scope when the rollback occurs, but you could two step if need be, sc to temp table temp table to table varaible. – HLGEM Mar 06 '09 at 16:10
Asfar as I am aware, SQL 2008 allows table variables to be passed to SPs, and table variables are out of scope as far as TRANSACTIONS go. But I do not believe there is a way for a SQL 2005 SP to update a table variable from declare outside of the SPs scope... – MatBailie Mar 06 '09 at 17:30
unfortunately, due to creation of primary keys and foreign key relationships this is not quite as straight forward. I agree it is technically do-able, but it is very messy and this impacts on the clients ability to maintain it. Thus the desire to commit specific changes. – MatBailie Mar 07 '09 at 14:51
@HLGEM: Could you explain why you say this is only OK if you aren't on SQL Server 2008? What has changed form SQL Server 2005 to SQL Server 2008? – snth Nov 26 '10 at 14:52
@snth, I think in 2008 you can shorten this, not that it won't work. – HLGEM Nov 30 '10 at 14:20

score 2 · Answer 3 · answered Mar 06 '09 at 14:46

You need to basically jump outside of the current context. There are a couple of ways to do that. One (which I have never tried) is to call the CLR to do the insert.

Perhaps a better way though is using the fact that table variables are not affected by transaction. For example:

CREATE TABLE dbo.Test_Transactions
(
     my_string VARCHAR(20) NOT NULL
)
GO

DECLARE
     @tbl TABLE (my_string VARCHAR(20) NOT NULL)

BEGIN TRANSACTION

INSERT INTO dbo.Test_Transactions (my_string) VALUES ('test point one')

INSERT INTO @tbl (my_string) VALUES ('test point two')

INSERT INTO dbo.Test_Transactions (my_string) VALUES ('test point three')

ROLLBACK TRANSACTION

INSERT INTO dbo.Test_Transactions (my_string) select my_string from @tbl

SELECT * FROM dbo.Test_Transactions
SELECT * FROM @tbl
GO

This is the only option I can see right now too. But it has implications. The inserts in to the logging tables are done by a SP, which maintains timestamps and other metadata. Copying that code into multiple places in multiple SPs is not 'ideal' :( — MatBailie, Mar 06 '09 at 14:56

rick schott · Answer 4 · 2009-03-06T18:45:39.733

1

Use SAVEPOINTS and TRANSACTION ISOLATION LEVELS.

edited Mar 06 '09 at 18:45

answered Mar 06 '09 at 14:20

rick schott

21,012
5
52
81

in my case I don't think save points help. Can you elaborate on how I would use isolation levels to solve this? – MatBailie Mar 06 '09 at 14:23
"Also, is there a way to lock only the rows being inserted into the tables, rather than the whole table? (The tables have identity columns which is preventing this)" The Isolation levels can help with reading the data during the transaction: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; – rick schott Mar 06 '09 at 18:52
Also, I had the wrong link on my original comment for TRANSACTION ISOLATION LEVELS. – rick schott Mar 06 '09 at 18:54

score 1 · Answer 5 · answered Mar 06 '09 at 15:17

1

Perhaps you could put the inserts/updates to the business tables in their own atomic transaction t1 and wrap each of these transactions in another transaction t2 that executes the log table update and t1 (the business table updates) without any rollbacks. For example:

BEGIN TRANSACTION t2
     <insert to log>
     <execute stored procedure p1>
END TRANSACTION t2

CREATE PROCEDURE p1
AS
     BEGIN TRANSACTION t1
         <insert to business tables>
         <rollback t1 on error>
     END TRANSACTION t1

I believe that when you rollback t1 in the stored procedure this will leave the calling transaction t2 unaffected.

answered Mar 06 '09 at 15:17

Daniel

1,231
3
15
20

I can't move the log insert out side of the transaction. Some log inserts happen between/after different business logic changes, and that order can not be changed... – MatBailie Mar 06 '09 at 15:21
Hmmm... well couldn't you make each business table change atomic so that it only performs one insert/update and then wrap that in another transaction as I suggested above? – Daniel Mar 06 '09 at 15:25
Each businessSP depends upon the previous one, and all must happen before the transaction can close. In short, the order can not be changed, and none of them can be taken out of the transaction :( – MatBailie Mar 06 '09 at 15:43

score 0 · Answer 6 · answered Mar 06 '09 at 13:59

0

Wouldn't the easy way be to move the log insertion outside the transaction?

I don't really have an answer for you for the table lock, I think you already have the answer, there will have to be a table lock because the identity column may roll back.

answered Mar 06 '09 at 13:59

Moose

5,354
3
33
46

score 0 · Answer 7 · answered Mar 06 '09 at 14:00

0

move the BEGIN TRANSACTION statement to after the first insert.

answered Mar 06 '09 at 14:00

CodeMonkey1313

15,717
17
76
109

Commiting only Specific Changes made inside a TRANSACTION which may ROLLBACK

7 Answers7