Transaction in SP

1 Jul 2005 1:17 PM

Ed

Hi,
Is it necessary to put Begin Tran right before another calling SP?
e.g.

Begin Tran
Exec SPName
    If @@Error <>0
      Rollback Tran
    Else
      Commit Tran

or

I should do all the transactions inside SPName?

Thanks
Ed

1 Jul 2005 1:29 PM

Cowboy (Gregory A. Beamer) - MVP

It really depends on what you want included in your transaction. YOu can nest
transactions, if that is necessary, but realize that rolling back gets either
a) harder/impossible or b) more time consuming or c) both when you commit
many smaller transactions inside of a transaction that fails.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

Show quote

"Ed" wrote:

> Hi,
>   Is it necessary to put Begin Tran right before another calling SP?
> e.g.
>
> Begin Tran
>   Exec SPName
>     If @@Error <>0
>       Rollback Tran
>     Else
>       Commit Tran
>
> or
>
> I should do all the transactions inside SPName?
>
> Thanks
> Ed

1 Jul 2005 1:44 PM

Ed

I just find out that if there are anything wrong insdie the called SP, the
@@Error on the calling SP is always 0
unless
expects parameter was not supplied for the calling SP

Ed

Show quote

"Cowboy (Gregory A. Beamer) - MVP" wrote:

> It really depends on what you want included in your transaction. YOu can nest
> transactions, if that is necessary, but realize that rolling back gets either
> a) harder/impossible or b) more time consuming or c) both when you commit
> many smaller transactions inside of a transaction that fails.
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> ***************************
> Think Outside the Box!
> ***************************
>
>
> "Ed" wrote:
>
> > Hi,
> >   Is it necessary to put Begin Tran right before another calling SP?
> > e.g.
> >
> > Begin Tran
> >   Exec SPName
> >     If @@Error <>0
> >       Rollback Tran
> >     Else
> >       Commit Tran
> >
> > or
> >
> > I should do all the transactions inside SPName?
> >
> > Thanks
> > Ed

1 Jul 2005 2:35 PM

David Browne

Show quote

"Ed" <E*@discussions.microsoft.com> wrote in message
news:AB324AD4-9311-400A-B7A7-747CB42EF441@microsoft.com...
> Hi,
> Is it necessary to put Begin Tran right before another calling SP?
> e.g.
>
> Begin Tran
> Exec SPName
>    If @@Error <>0
>      Rollback Tran
>    Else
>      Commit Tran
>

Stored procedures don't reliably set @@error. There are ofted statements in
the procedure after the one on which the error occured, in which case
@@error will have been cleared. In case of an error the stored procedure
should return a non-zero return code.

begin tran
declare @rc int
exec @rc = SPNAme
if @rc <> 0
rollback tran
else
commit tran

But I would recommend that you don't put any transaction handling in your
stored procedures. It's really tricky to get right. It's generally easier
to control transaction scope at the client level.

Look at it this way: Who knows when to commit? Only the client does.

David

3 Jul 2005 3:36 PM

Brian Selzer

To ensure maximum concurrency and throughput, you want to keep your
transactions as small as possible. With this in mind, if your procedure does
calculations prior to issuing an insert/update/delete statement or uses one
or more cursors, you should wait until the calculations have been computed
or the updates have been cached in a table variable before you apply locks.
Of course, this raises the possibility that another transaction may change
the data while the calculations are being computed, so you have to check for
that possibility in the statement(s) that apply the locks.

I always add error handling to my stored procedures, and if any error occurs
return that value in the return code of the stored procedure, for example:

CREATE PROCEDURE procName (paramList) AS
BEGIN
    DECLARE @_ERROR INT, @_ROWCOUNT INT, @_TRANCOUNT INT
    DECLARE @TRANNAME CHAR(20) SET @TRANNAME = 'procNameTran'
    SET @_TRANCOUNT = @@TRANCOUNT

SET NOCOUNT ON
-- perform some lengthy calculations

-- now that the calculations are done, wrap the updates in a transaction
IF @_TRANCOUNT > 0
    SAVE TRANSACTION @TRANNAME    -- if a transaction is already in place,
create a savepoint
ELSE
    BEGIN TRANSACTION @TRANNAME

--
SET NOCOUNT OFF
UPDATE ...
SELECT @_ERROR = @@ERROR, @_ROWCOUNT = @@ROWCOUNT
IF @_ERROR != 0 GOTO ERROR -- fail if a sql server error occurred
IF @_ROWCOUNT > 0    -- not a problem if no rows updated, just skip the
update to the second table
BEGIN
    UPDATE ...
    SELECT @_ERROR = @@ERROR, @_ROWCOUNT = @@ROWCOUNT
    IF @_ERROR != 0 OR @_ROWCOUNT != 0 GOTO ERROR -- problem if no rows or
a sql server error
END

-- most of my procedures end with this code
COMMIT TRANSACTION @TRANNAME
RETURN 0

ERROR:
    IF @@TRANCOUNT > @_TRANCOUNT ROLLBACK TRANSACTION @TRANNAME
    IF @_ERROR != 0 RETURN @_ERROR
    RETURN -1 -- a failure occurred that did not generate an SQL Server
error, maybe another tran changed a row
END

Note: I never use dynamic cursors. I populate a cursor using WITH(NOLOCK),
avoid table accesses within the body of the fetch loop, cache the results of
a cursor in a table variable and issue the updates using a set-based
operation. This minimizes the overhead of using a cursor. To verify that
no other transaction changed a row that I've used to perform my
calculations, I read out the maximum ROWVERSION of each result set used to
perform the calculations while or immediately before I perform the
calculations, and then re-read and compare them within the transaction
immediately before issuing the updates This places a shared lock on the
tables used to perform the calculations, ensuring integrity, and provided
none of the rows have changed, the maximum ROWVERSION for each result set
will be the same, and that indicates that the calculations are correct and
the update should proceed.

Show quote

"Ed" <E*@discussions.microsoft.com> wrote in message
news:AB324AD4-9311-400A-B7A7-747CB42EF441@microsoft.com...
> Hi,
>   Is it necessary to put Begin Tran right before another calling SP?
> e.g.
>
> Begin Tran
>   Exec SPName
>     If @@Error <>0
>       Rollback Tran
>     Else
>       Commit Tran
>
> or
>
> I should do all the transactions inside SPName?
>
> Thanks
> Ed

3 Jul 2005 5:20 PM

Brian Selzer

OOPS, I forgot a statement. To avoid confusion:

-- most of my procedures end with this code
IF @@TRANCOUNT > @_TRANCOUNT
    COMMIT TRANSACTION @TRANNAME -- only commit if a new transaction was
created
RETURN 0

ERROR:
     IF @@TRANCOUNT > @_TRANCOUNT
         ROLLBACK TRANSACTION @TRANNAME -- only rollback if a new
transaction was created
     IF @_ERROR != 0 RETURN @_ERROR
     RETURN -1 -- a failure occurred that did not generate an SQL Server
error, maybe another tran changed a row
END

Using transaction save points is a useful tool, since the most likely
scenario in a collision is that another transaction modified a row used to
calculate the results, so recovery from a collision is as simple as calling
the procedure again using the same parameters. You should know that if a
transaction is only partially rolled back, the locks obtained by the
transaction are held until the entire transaction commits. This means that
other transactions must wait until your transaction has completed before
they are able to perform their updates. This ensures that if a collision
occurs, your transaction will be next in line; however, the downside is that
the locks obtained are held during recalculation, thereby adding to the cost
of a collision. Of course, collisions are usually rare, so it doesn't
affect overall performance significantly.

It may appear that the additional read will negatively impact performance,
but if your server has enough memory, it is most likely that the information
read during the calculation phase will still be in memory when the locks
need to be applied, so the impact on overall performance is negligible.
That performance impact is usually far outweighed by the improved
concurrency that results from defering locks until you absolutely need them.

It's important that the SELECTS that follow BEGIN TRANSACTION that obtain
the locks be issued in the same order in every procedure (that includes
triggers and udf's too). This minimizes the possibility of deadlocks.

One other note: it is important to populate the UI using at least a READ
COMMITTED transaction isolation level. If a dirty read occurs, incorrect
information could be presented to a user, and worse yet, the user may waste
time changing it, and then see an error message when he tries to save his
changes. An error message due to a legitimate collision (someone else
changed something while he was working on it) is usually OK, because if you
track changes, you can indicate which user changed it in the error message.
Other than that, the procedure above applies to both presentation tier and
middle-tier updates.

Show quote

"Brian Selzer" <br***@selzer-software.com> wrote in message
news:#074MU#fFHA.2484@TK2MSFTNGP15.phx.gbl...
> To ensure maximum concurrency and throughput, you want to keep your
> transactions as small as possible. With this in mind, if your procedure
does
> calculations prior to issuing an insert/update/delete statement or uses
one
> or more cursors, you should wait until the calculations have been computed
> or the updates have been cached in a table variable before you apply
locks.
> Of course, this raises the possibility that another transaction may change
> the data while the calculations are being computed, so you have to check
for
> that possibility in the statement(s) that apply the locks.
>
> I always add error handling to my stored procedures, and if any error
occurs
> return that value in the return code of the stored procedure, for example:
>
> CREATE PROCEDURE procName (paramList) AS
> BEGIN
>     DECLARE @_ERROR INT, @_ROWCOUNT INT, @_TRANCOUNT INT
>     DECLARE @TRANNAME CHAR(20) SET @TRANNAME = 'procNameTran'
>     SET @_TRANCOUNT = @@TRANCOUNT
>
> SET NOCOUNT ON
> -- perform some lengthy calculations
>
> -- now that the calculations are done, wrap the updates in a transaction
> IF @_TRANCOUNT > 0
>     SAVE TRANSACTION @TRANNAME    -- if a transaction is already in place,
> create a savepoint
> ELSE
>     BEGIN TRANSACTION @TRANNAME
> --
> SET NOCOUNT OFF
> UPDATE ...
> SELECT @_ERROR = @@ERROR, @_ROWCOUNT = @@ROWCOUNT
> IF @_ERROR != 0 GOTO ERROR -- fail if a sql server error occurred
> IF @_ROWCOUNT > 0    -- not a problem if no rows updated, just skip the
> update to the second table
> BEGIN
>     UPDATE ...
>     SELECT @_ERROR = @@ERROR, @_ROWCOUNT = @@ROWCOUNT
>     IF @_ERROR != 0 OR @_ROWCOUNT != 0 GOTO ERROR -- problem if no rows
or
> a sql server error
> END
>
> -- most of my procedures end with this code
> COMMIT TRANSACTION @TRANNAME
> RETURN 0
>
> ERROR:
>     IF @@TRANCOUNT > @_TRANCOUNT ROLLBACK TRANSACTION @TRANNAME
>     IF @_ERROR != 0 RETURN @_ERROR
>     RETURN -1 -- a failure occurred that did not generate an SQL Server
> error, maybe another tran changed a row
> END
>
>
> Note: I never use dynamic cursors. I populate a cursor using
WITH(NOLOCK),
> avoid table accesses within the body of the fetch loop, cache the results
of
> a cursor in a table variable and issue the updates using a set-based
> operation. This minimizes the overhead of using a cursor. To verify that
> no other transaction changed a row that I've used to perform my
> calculations, I read out the maximum ROWVERSION of each result set used to
> perform the calculations while or immediately before I perform the
> calculations, and then re-read and compare them within the transaction
> immediately before issuing the updates This places a shared lock on the
> tables used to perform the calculations, ensuring integrity, and provided
> none of the rows have changed, the maximum ROWVERSION for each result set
> will be the same, and that indicates that the calculations are correct and
> the update should proceed.
>
>
>
> "Ed" <E*@discussions.microsoft.com> wrote in message
> news:AB324AD4-9311-400A-B7A7-747CB42EF441@microsoft.com...
> > Hi,
> >   Is it necessary to put Begin Tran right before another calling SP?
> > e.g.
> >
> > Begin Tran
> >   Exec SPName
> >     If @@Error <>0
> >       Rollback Tran
> >     Else
> >       Commit Tran
> >
> > or
> >
> > I should do all the transactions inside SPName?
> >
> > Thanks
> > Ed
>
>

3 Jul 2005 6:39 PM

Brian Selzer

OOPS, I screwed up again. I should have looked at one of my procedures
before posting. When I'm writing a new sp I usually copy an old one and
modify it. Sorry! This should be correct:

CREATE PROCEDURE procName (paramList) AS
BEGIN
    DECLARE @_ERROR INT, @_ROWCOUNT INT, @_TRANCOUNT INT
    SET @_TRANCOUNT = @@TRANCOUNT
    DECLARE @TRANNAME CHAR(20)   -- this starts out NULL, indicating that
the transaction hasn't started

SET NOCOUNT ON
-- perform some lengthy calculations

-- now that the calculations are done, wrap the updates in a transaction
SET @TRANNAME = 'procNameTran' -- we're starting the update phase
IF @_TRANCOUNT > 0
    SAVE TRANSACTION @TRANNAME    -- if a transaction is already in place,
create a savepoint
ELSE
    BEGIN TRANSACTION @TRANNAME

/*
add SELECT ... WITH(REPEATABLEREAD) statements here obtain shared locks for
rows used to calculate
   updates and to verify that none of the rows changed since they were read
out during the calculation
   phase and to ensure that they won't change until the transaction
completes,
   and SELECT ... WITH(UPDLOCK) statements to obtain update locks for rows
that are to be updated.

make sure the locks are obtained in the same order in every procedure.
*/

SET NOCOUNT OFF
UPDATE ...
SELECT @_ERROR = @@ERROR, @_ROWCOUNT = @@ROWCOUNT
IF @_ERROR != 0 GOTO ERROR -- fail if a sql server error occurred
IF @_ROWCOUNT > 0    -- not a problem if no rows updated, just skip the
update to the second table
BEGIN
    UPDATE ...
    SELECT @_ERROR = @@ERROR, @_ROWCOUNT = @@ROWCOUNT
    IF @_ERROR != 0 OR @_ROWCOUNT != 0 GOTO ERROR -- problem if no rows or
a sql server error
END

-- most of my procedures end with this code
IF @@TRANCOUNT > @_TRANCOUNT
    COMMIT TRANSACTION @TRANNAME -- only commit if a new transation was
initiated
RETURN 0

ERROR:
    IF @TRANNAME IS NOT NULL AND @@TRANCOUNT > 0
        ROLLBACK TRANSACTION @TRANNAME -- undo any changes made by this
procedure
    IF @_ERROR != 0 RETURN @_ERROR
    RETURN -1 -- a failure occurred that did not generate an SQL Server
error, maybe another tran changed a row
END

Show quote

"Brian Selzer" <br***@selzer-software.com> wrote in message
news:#074MU#fFHA.2484@TK2MSFTNGP15.phx.gbl...
> To ensure maximum concurrency and throughput, you want to keep your
> transactions as small as possible. With this in mind, if your procedure
does
> calculations prior to issuing an insert/update/delete statement or uses
one
> or more cursors, you should wait until the calculations have been computed
> or the updates have been cached in a table variable before you apply
locks.
> Of course, this raises the possibility that another transaction may change
> the data while the calculations are being computed, so you have to check
for
> that possibility in the statement(s) that apply the locks.
>
> I always add error handling to my stored procedures, and if any error
occurs
> return that value in the return code of the stored procedure, for example:
>
> CREATE PROCEDURE procName (paramList) AS
> BEGIN
>     DECLARE @_ERROR INT, @_ROWCOUNT INT, @_TRANCOUNT INT
>     DECLARE @TRANNAME CHAR(20) SET @TRANNAME = 'procNameTran'
>     SET @_TRANCOUNT = @@TRANCOUNT
>
> SET NOCOUNT ON
> -- perform some lengthy calculations
>
> -- now that the calculations are done, wrap the updates in a transaction
> IF @_TRANCOUNT > 0
>     SAVE TRANSACTION @TRANNAME    -- if a transaction is already in place,
> create a savepoint
> ELSE
>     BEGIN TRANSACTION @TRANNAME
> --
> SET NOCOUNT OFF
> UPDATE ...
> SELECT @_ERROR = @@ERROR, @_ROWCOUNT = @@ROWCOUNT
> IF @_ERROR != 0 GOTO ERROR -- fail if a sql server error occurred
> IF @_ROWCOUNT > 0    -- not a problem if no rows updated, just skip the
> update to the second table
> BEGIN
>     UPDATE ...
>     SELECT @_ERROR = @@ERROR, @_ROWCOUNT = @@ROWCOUNT
>     IF @_ERROR != 0 OR @_ROWCOUNT != 0 GOTO ERROR -- problem if no rows
or
> a sql server error
> END
>
> -- most of my procedures end with this code
> COMMIT TRANSACTION @TRANNAME
> RETURN 0
>
> ERROR:
>     IF @@TRANCOUNT > @_TRANCOUNT ROLLBACK TRANSACTION @TRANNAME
>     IF @_ERROR != 0 RETURN @_ERROR
>     RETURN -1 -- a failure occurred that did not generate an SQL Server
> error, maybe another tran changed a row
> END
>
>
> Note: I never use dynamic cursors. I populate a cursor using
WITH(NOLOCK),
> avoid table accesses within the body of the fetch loop, cache the results
of
> a cursor in a table variable and issue the updates using a set-based
> operation. This minimizes the overhead of using a cursor. To verify that
> no other transaction changed a row that I've used to perform my
> calculations, I read out the maximum ROWVERSION of each result set used to
> perform the calculations while or immediately before I perform the
> calculations, and then re-read and compare them within the transaction
> immediately before issuing the updates This places a shared lock on the
> tables used to perform the calculations, ensuring integrity, and provided
> none of the rows have changed, the maximum ROWVERSION for each result set
> will be the same, and that indicates that the calculations are correct and
> the update should proceed.
>
>
>
> "Ed" <E*@discussions.microsoft.com> wrote in message
> news:AB324AD4-9311-400A-B7A7-747CB42EF441@microsoft.com...
> > Hi,
> >   Is it necessary to put Begin Tran right before another calling SP?
> > e.g.
> >
> > Begin Tran
> >   Exec SPName
> >     If @@Error <>0
> >       Rollback Tran
> >     Else
> >       Commit Tran
> >
> > or
> >
> > I should do all the transactions inside SPName?
> >
> > Thanks
> > Ed
>
>