Does ``if @@transcount>0 rollback tran'' make sense?
Qing Yin
to have
/* When an error occurred ... */
if @@transcount > 0
rollback tran
/* When no error occurred ...*/
if @@transcount > 0
commit tran
But that is unsafe because it only rolls back transaction by one level.
So if for any reason (e.g., a bug), a previous transaction didn't issue
a `commit' but printed a (misleading) "Hello, transaction committed!"
message to the user, then it will have disastrous effect to all the
subsequent transactions. In other words, the problem caused by a single
bug will have ripple effects on the rest of the session. The reason is
that all subsequent transactions will assume that when it begins, there
is no outstanding transactions enclosing it. If that assumption is faulse
due to a bug, then the entire session will miss one level of rollback/commit
at the end, which effectively discards everything the user has done when
he logs out.
So I thought of changing the above code to
/* When an error occurred ... */
WHILE @@transcount > 0
rollback tran
That is to change the IF statement to a WHILE loop. But on a second
thought, isn't that WHILE loop useless? In many cases, a rollback
statement will stop executing subsequent statements. So the loop will
not actually loop.
So now I'm curious as to what other people are doing? What do people
usually do (in actual applications) to ensure that an accidental outstanding
transaction will not "cast out" subsequent "good" transactions? Something
like common techniques of detecting such errors?
--
Qing Vincent Yin | Repeat
um...@mctrf.mb.ca | delete(next_bug);
| Until 0 = 1;
William T Green
says:
>
>In our application (stored proc, trigger and APT embedded SQL), we used
>to have
>
> /* When an error occurred ... */
> if @@transcount > 0
> rollback tran
>
> /* When no error occurred ...*/
> if @@transcount > 0
> commit tran
>
>But that is unsafe because it only rolls back transaction by one level.
Not true. A "rollback tran" without a savepoint name rolls back to the
outermost transaction, not the innermost.
>So if for any reason (e.g., a bug), a previous transaction didn't issue
>a `commit' but printed a (misleading) "Hello, transaction committed!"
>message to the user, then it will have disastrous effect to all the
>subsequent transactions. In other words, the problem caused by a single
>bug will have ripple effects on the rest of the session. The reason is
>that all subsequent transactions will assume that when it begins, there
>is no outstanding transactions enclosing it. If that assumption is faulse
>due to a bug, then the entire session will miss one level of rollback/commit
>at the end, which effectively discards everything the user has done when
>he logs out.
That's why a warning is generated when you return from a stored procedure and
the transaction count on the way out isn't the same as it was on the way in.
With embedded SQL batches, you can leave a tran pending, but when and if you
finally recognize this, you probably won't know whether you should commit or
rollback, because at the time you probably don't know whether a rollback or
commit was forgotten.
>
>So I thought of changing the above code to
> /* When an error occurred ... */
> WHILE @@transcount > 0
> rollback tran
>
>That is to change the IF statement to a WHILE loop. But on a second
>thought, isn't that WHILE loop useless? In many cases, a rollback
>statement will stop executing subsequent statements. So the loop will
>not actually loop.
>
>So now I'm curious as to what other people are doing? What do people
>usually do (in actual applications) to ensure that an accidental outstanding
>transaction will not "cast out" subsequent "good" transactions? Something
>like common techniques of detecting such errors?
If your user gets to the point of logging out with a transaction pending, you
don't want to simply rollback without doing anything else, and you most
definitely shouldn't commit because you've got a bug and you don't know what
you're committing. Best bet is to check @@trancount before log out. (You
could actually make this check anywhere in the application that you're sure
transaction shouldn't be pending. (ie. before reports are run, before ad-hoc
queries, etc.)) If transactions are pending, rollback, inform the user that
changes that were made may have been lost, and be sure that you're notified.
The fact of the matter is, if transactions are pending at log out, you've
got a bug that has to found.
----------------------------------------------------------------------
Tim Green wt_g...@pnl.gov
Battelle Pacific Northwest Laboratories
** The opinions above are not necessarily those of Battelle PNL **
John Anest
>In our application (stored proc, trigger and APT embedded SQL), we used
>to have
>
> /* When an error occurred ... */
> if @@transcount > 0
> rollback tran
What we do is put our begin tran's and commit's and rollback's all
in top level procs. We found that the whole nested proc thing was so confusing
and Sybase's handling of it so inconsistent, that it just wasn't worth it.
Many of our important procs that are used by other procs begin with
if @@trancount = 0
return -####
This ensures that our programmers as well as end users use these
procs correctly and lets us use them as building blocks in more complicated
transactions.
Hope this helps.
John
David Shanahan
>to have
>
> /* When an error occurred ... */
> if @@transcount > 0
> rollback tran
>
> /* When no error occurred ...*/
> if @@transcount > 0
> commit tran
>
>But that is unsafe because it only rolls back transaction by one level.
This is not true - a rollback tran with no savepoint name or transaction
names given will rollback to the first begin tran. It is different for
"commit tran" with nested begin trans, the commit is not done until the
commit that matches the outermost (first) begin tran is encountered.
See the T-SQL Commands Ref Manual under "rollback transaction" and
"Transactions" for details of how this all works.
>So if for any reason (e.g., a bug), a previous transaction didn't issue
>a `commit' but printed a (misleading) "Hello, transaction committed!"
>message to the user, then it will have disastrous effect to all the
>subsequent transactions. In other words, the problem caused by a single
>bug will have ripple effects on the rest of the session. The reason is
>that all subsequent transactions will assume that when it begins, there
>is no outstanding transactions enclosing it. If that assumption is faulse
>due to a bug, then the entire session will miss one level of rollback/commit
>at the end, which effectively discards everything the user has done when
>he logs out.
>
>So I thought of changing the above code to
> /* When an error occurred ... */
> WHILE @@transcount > 0
> rollback tran
>
>That is to change the IF statement to a WHILE loop. But on a second
>thought, isn't that WHILE loop useless? In many cases, a rollback
>statement will stop executing subsequent statements. So the loop will
>not actually loop.
>
Not required, see above.
>So now I'm curious as to what other people are doing? What do people
>usually do (in actual applications) to ensure that an accidental outstanding
>transaction will not "cast out" subsequent "good" transactions? Something
>like common techniques of detecting such errors?
>--
>
>Qing Vincent Yin | Repeat
>um...@mctrf.mb.ca | delete(next_bug);
> | Until 0 = 1;
Regards
David S.
+----------------------------------------------------+
| God does not play dice with the universe. |
| He does however play pool with the solar system - |
| ref. comet Shoemaker-Levy 9... |
+----------------------------------------------------+
Qing Yin
| In article <310n3p$m...@canopus.cc.umanitoba.ca> um...@mctrf.mb.ca (Qing Yin) writes:
| >In our application (stored proc, trigger and APT embedded SQL), we used
| >to have
| >
| > /* When an error occurred ... */
| > if @@transcount > 0
| > rollback tran
| >
| > /* When no error occurred ...*/
| > if @@transcount > 0
| > commit tran
| >
| >But that is unsafe because it only rolls back transaction by one level.
| This is not true - a rollback tran with no savepoint name or transaction
| names given will rollback to the first begin tran. It is different for
| "commit tran" with nested begin trans, the commit is not done until the
| commit that matches the outermost (first) begin tran is encountered.
| See the T-SQL Commands Ref Manual under "rollback transaction" and
| "Transactions" for details of how this all works.
Thanks for pointing that out. So that solves half of the problem. But
still, the real concern is the accidental outstanding transaction. Although
the problem doesn't exist with rollback, it still exists in commit.
As some other experts commented earlier, at the beginning of some transaction,
we could do
if @@trancount > 0
print "ERR: I shouldn't be nested!"
That's a good practice.
Any other ideas?
Anthony Arrigo
Rollback does NOT rollback 1 level. It rolls back ALL levels to the first
begin tran.
This can be especially interesting if your transaction nesting levels span
several stored procedures.
For example
CREATE PROC A
AS
BEGIN TRAN
/* Do some processing */
EXEC B
COMMIT TRAN
CREATE PROC B
AS
BEGIN TRAN
/* Do processing */
IF @@ERROR
ROLLBACK TRAN
ELSE
COMMIT TRAN
A rollback in B would rollback to the begin tran in A not just the processing
done in B.
I usually have my procs do
RAISERROR nnnnn "Error occurred in proc"
RETURN
The calling proc should do something like
exec proc
if ( @@ERROR != 0 OR @@TRANCOUNT = 0 ) /* We had an error or rollback */
BEGIN
ROLLBACK TRAN
RETURN
END
ELSE
BEGIN
COMMIT TRAN
RETURN
END
---
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
////////////////////////////////////
\\ //
\|/
|
Live long, and prosper.
Mark S Mertel
> to have
>
> /* When an error occurred ... */
> if @@transcount > 0
> rollback tran
>
> /* When no error occurred ...*/
> if @@transcount > 0
> commit tran
>
rollback tran rolls them all back unless you specify a transaction or
savepoint by name. so what you used to have was ok.
--
Mark Mertel Email: m...@pencom.com
Pencom Software Phone: (512) 343-6666
9050 Capital of Texas Hwy. N. FAX: (512) 343-9650
Austin, TX 78759
Mark Hamilton
have stuck to the following convention and have not encountered any
problems.
create proc A_Proc
as
begin
BEGIN TRANSACTION
SAVE TRANSACTION A_Proc_Save
/* The Guts of the Proc */
if @Guts = OK
COMMIT TRANSACTION
else /* A problem has occurred */
begin
ROLLBACK TRANSACTION A_Proc_Save
COMMIT TRANSACTION
end
return
end
Under this scheme each proc has control of its own work and is independent
of any procs from which it may be called. It is up to any caller to
enforce any higher level commits/rollback.
An advantage of this is that the Rollback in A_Proc will not effect any
calling proc and the commits are automatically matched with each begin
tran, therefore only when the last commit is made will all the updates
happen.