June 20, 2003 at 10:47 am
I have multiple questions in this regard. Let me begin with the simpler and graduate to the more complex.
1. I wrote a small snippet of code to play with transactions (this is my first encounter with them)-
create table #temp
(
name varchar(10)
)
go
begin tran
insert into #temp(name)
values ('test1')
-- save tran firstpoint (commented out)
insert into #temp(name)
values ('test2')
rollback tran --firstpoint (commented out)
commit
go
Even though this rolls back the insert statements, it returns this error-
"Server: Msg 3902, Level 16, State 1, Line 13
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION."
a.) Why does that happen?
b.) The code will work fine (without error)if I use a savepoint (see commented out code)
2. I need to write a nested stored proc (3 levels deep) for generating invoices. The inner most stored proc does the core job of inserting data into tables, the outermost merely loops over a cursor of clients, and the middle one applies certain business logic.
a. Can I use nested transactions across these
stored procs (they are all on one server)?
b. Can I do say "RETURN -1" after my rollback statement in the inner most sproc to signal to the higher level sproc to rollback too? Or should I use @@ERROR?
June 20, 2003 at 12:44 pm
Some observations:
1a) Rollback tran affects all active transactions. The commit is meaningless because the only transaction has just been rolled back.
1b) Code works with the save point because the rollback was specific to the save point.
2a) No problem with transactions over nested procedures.
2b) You can return codes with care.
We use a template for all stored procedures to resolve this. It is something like the following pseudo code
CREATE PROCEDURE procName
@ErrMsg varchar(100) = null output
AS
declare
@procStartedTran
@ErrorCode
@procStartedTran = false, @ErrorCode = 0
IF @@TRANCOUNT = 0
BEGIN
BEGIN TRAN
@procStartedTran = true
END
--To call other procedures
EXEC @ErrorCode = ProcCall
@ErrMsg OUTPUT
if @ErrorCode <> 0 GOTO Cleanup
--If sub-tran needed (we use them rarely)
SAVE TRAN ProcedureName
Do Work
If Failure
ROLLBACK TRAN ProcedureName
--end proc
CleanUp:
if @procStartedTran
begin
if @ErrorCode = 0
commit tran
else
rollback tran
end
return @ErrorCode
We find this resolves all our transaction and nesting problems. Errors can occur 10 levels deep and are properly returned to the calling procedure.
Hope this helps
Guarddata-
June 20, 2003 at 12:58 pm
Thanks Guarddata!
The later section regarding your template seems to be the solution I need to adopt. I guess I'm still a little confused about the first, simpler question though-
In the instances where one needs just a single transaction (no nesting), there will obviously be only one each of the BEGIN, ROLLBACK and COMMIT statements. (The ROLLBACK would most probably be inside some IF condition). Now, are you saying that to avoid getting the error (Error 3902) shown earlier, I always will have to use a save point, even if it is a dummy savepoint right after my BEGIN statement?
June 20, 2003 at 1:11 pm
Put a different way, should the COMMIT statement be always mutually exclusive to the ROLLBACK (like COMMIT in the ELSE condition when ROLLBACK is in the IF) ?
June 23, 2003 at 3:11 am
The answer to your last question is yes. Commit and Rollback are alternative ways of closing a transaction. Once you have rolled back (or committed) a transaction, there is of course no transaction outstanding. So if you issue the 'COMMIT' command, SQL Server will protest that there is nothing to commit.
In the case of the savepoint, the transaction is not closed. So you can still issue simple rollback or commit commands (but not both!) against the transaction.
A note of caution, though - long transactions are likely to cause blocks and possibly deadlocks, which could degrade performance significantly. This is especially true if you have the transaction isolation level set to "SERIALIZABLE" (or include HOLDLOCK hints) - as you probably should for penny-perfect financial data.
It sounds as though you should be reading all the data into work tables first in a transaction, then processing the data, then inserting the rows into your invoice table. (You may not ven need to use a transaction for this insert, depending on whether other processes are likely to be updating the same tables.)
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 23, 2003 at 7:08 am
Thanks stanx68.......in none of the examples of transactions that I've seen is that apparent...... I guess I need to see some better material regarding trans....
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply