Best Practices - SQL Transactions

I have a web app (ASP) that does all updates, inserts by calling
transaction-supported COM+ components (with the transaction started in the
ASP page, i.e. transaction=required) that use ADO to call stored procedures
(that usually involve single tables). If there is any error (missing SP,
parameter value of wrong type, etc.) with the database insert/update, MTS
automatically rolls back everything that was done in the database (and maybe
SQL Server does that with any error in an SP anyway'). As such, when I
write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
So, I have many SPs (that do not return any indication of success, or not)
like
INSERT INTO Table
(ColumnA, ColumnB)
VALUES
(ValueA, ValueB)
WHERE
Some condition
As a matter of best practice, should SQL programmers always enclose INSERTs,
UPDATEs within transactional statements in a production database? Should one
always check for errors with INSERTS or UPDATES? Or, with errors like I
describe (but not with business logic), does SQL Server automatically
rollback everything in an SP? Or, should one just save those statements for
when the SQL script and logic itself takes care of rolling back a database
when a series of updates or inserts are made?
Thanks for any thoughts."Don Miller" <nospam@.nospam.com> wrote in message
news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
>I have a web app (ASP) that does all updates, inserts by calling
> transaction-supported COM+ components (with the transaction started in the
> ASP page, i.e. transaction=required) that use ADO to call stored
> procedures
> (that usually involve single tables). If there is any error (missing SP,
> parameter value of wrong type, etc.) with the database insert/update, MTS
> automatically rolls back everything that was done in the database (and
> maybe
> SQL Server does that with any error in an SP anyway'). As such, when I
> write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
> checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
> So, I have many SPs (that do not return any indication of success, or not)
> like
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
> INSERTs,
> UPDATEs within transactional statements in a production database? Should
> one
> always check for errors with INSERTS or UPDATES?
No,

>Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP?
No, but the client will get an error message and rollback.
Usually, stored procedures should not contain transactional logic. Let the
client take care of it.
David|||Don Miller wrote:
> As a matter of best practice, should SQL programmers always enclose INSERT
s,
> UPDATEs within transactional statements in a production database? Should o
ne
> always check for errors with INSERTS or UPDATES? Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP? Or, should one just save those statements fo
r
> when the SQL script and logic itself takes care of rolling back a database
> when a series of updates or inserts are made?
My thoughts are:
1) Transactions are only required if there's more than one DML
statement (or SELECT statement that needs to maintain a lock).
2) Error checking should be done after any statement that can fail.
This includes all DML and DDL statements. Pretty much everything except
SELECTs (though I guess they could technically fail as well...).
Kris|||David wrote:
> Usually, stored procedures should not contain transactional logic. > Let the clie
nt take care of it.
Are you sure? Isn't it better to ensure your stored procedures are
transactionally correct regardless of where they are executed from?
Kris|||To my knowledge, an error is not sufficient for MTS to roll back: if you do
not call ObjectContext.SetAbort explicitely, MTS will think that the whole
operation has been successfull and will commit it; even if there have been
one or multiple errors.
In the same way, if there is an error inside a SP, SQL-Server will not
rollback the transaction automatically for you: you must check for any error
(@.@.error) and call the rollback operation yourself.
Even if the SP is already enrolled in a transaction, you still need to check
for a transaction error (@.@.error) if there is such a possibility inside the
SP and this, even if you have not included a BEGIN TRAN inside it.
To be clear, transactions and errors are not same: the fact that there have
been an error doesn't mean that the transaction will be or need to be
aborted.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Don Miller" <nospam@.nospam.com> wrote in message
news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
>I have a web app (ASP) that does all updates, inserts by calling
> transaction-supported COM+ components (with the transaction started in the
> ASP page, i.e. transaction=required) that use ADO to call stored
> procedures
> (that usually involve single tables). If there is any error (missing SP,
> parameter value of wrong type, etc.) with the database insert/update, MTS
> automatically rolls back everything that was done in the database (and
> maybe
> SQL Server does that with any error in an SP anyway'). As such, when I
> write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
> checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
> So, I have many SPs (that do not return any indication of success, or not)
> like
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
> INSERTs,
> UPDATEs within transactional statements in a production database? Should
> one
> always check for errors with INSERTS or UPDATES? Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP? Or, should one just save those statements
> for
> when the SQL script and logic itself takes care of rolling back a database
> when a series of updates or inserts are made?
> Thanks for any thoughts.
>|||Don Miller (nospam@.nospam.com) writes:
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
> INSERTs, UPDATEs within transactional statements in a production
> database? Should one always check for errors with INSERTS or UPDATES?
> Or, with errors like I describe (but not with business logic), does SQL
> Server automatically rollback everything in an SP? Or, should one just
> save those statements for when the SQL script and logic itself takes
> care of rolling back a database when a series of updates or inserts are
> made?
If it's a single statement, there is no reason to have BEGIN/COMMIT
TRANSACTION around it, since the statement is a transaction in itself.
However, if you procedure performs several INSERT/UPDATE/DELETE statements
there should be a transaction around it. The procedure should not rely on
that the caller has set up a transaction. Sometimes you have a procedure
that you know is only performing part of a game. In this case, it is a
good habit to have this in the beginning:
IF @.@.trancount = 0
BEGIN
RAISERROR ('This procedure must be called with an active transaction',
16, 1)
RETURN 1
END
In SQL 2005, error checking in stored procedures can be handled with
TRY-CATCH. In SQL 2000, you need to check @.@.error, and if you have
started a transaction, you should rollback, since you know that you
were not able to fulfil your contract.
I have two articles on error handling in SQL Server on my web site.
http://www.sommarskog.se/error-handling-II.html gives more suggestions
on implementing error handling.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> In the same way, if there is an error inside a SP, SQL-Server will not
> rollback the transaction automatically for you: you must check for any
> error (@.@.error) and call the rollback operation yourself.
The exception is when SET XACT_ABORT ON is active on the connection or proc.
SQL Server will then rollback the transaction and abort the batch when
runtime errors are encountered. However, compile errors are not affected
with XACT_ABORT ON so @.@.ERROR still needs to be checked when you want to
safeguard against all errors.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uUuZ5PNbGHA.1536@.TK2MSFTNGP02.phx.gbl...
> To my knowledge, an error is not sufficient for MTS to roll back: if you
> do not call ObjectContext.SetAbort explicitely, MTS will think that the
> whole operation has been successfull and will commit it; even if there
> have been one or multiple errors.
> In the same way, if there is an error inside a SP, SQL-Server will not
> rollback the transaction automatically for you: you must check for any
> error (@.@.error) and call the rollback operation yourself.
> Even if the SP is already enrolled in a transaction, you still need to
> check for a transaction error (@.@.error) if there is such a possibility
> inside the SP and this, even if you have not included a BEGIN TRAN inside
> it.
>
> To be clear, transactions and errors are not same: the fact that there
> have been an error doesn't mean that the transaction will be or need to be
> aborted.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
>|||<kriskirk@.hotmail.com> wrote in message
news:1146449658.432935.118620@.j73g2000cwa.googlegroups.com...
> David wrote:
> Are you sure? Isn't it better to ensure your stored procedures are
> transactionally correct regardless of where they are executed from?
>
Ideally yes. Stored procedures should be atomic, consistent, and isolated.
They should typically not be durable because that makes assumptions about
where the procedure fits inside user transactions. But it requires quite a
bit of transaction handling code to make that happen.
Here's an example. A stored procedure should almost never issue a ROLLBACK
except to a savepoint. If it does then it can't be called in the scope of
an existing transaction. That might be OK for administrative stuff that you
know will be run from Management Studio, but for regular database
transactions.
create procedure foo
as
begin
begin transaction foo
begin try
'do work here
commit transaction
end try
begin catch
rollback transaction foo
commit transaction
exec usp_reraise_error
end catch
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e4G3seSbGHA.4116@.TK2MSFTNGP05.phx.gbl...
> <kriskirk@.hotmail.com> wrote in message
> news:1146449658.432935.118620@.j73g2000cwa.googlegroups.com...
>
> Ideally yes. Stored procedures should be atomic, consistent, and
> isolated. They should typically not be durable because that makes
> assumptions about where the procedure fits inside user transactions. But
> it requires quite a bit of transaction handling code to make that happen.
> Here's an example. A stored procedure should almost never issue a
> ROLLBACK except to a savepoint. If it does then it can't be called in the
> scope of an existing transaction. That might be OK for administrative
> stuff that you know will be run from Management Studio, but for regular
> database transactions.
>
Oops, here's a correction after morinng coffee.
create procedure foo
as
begin transaction
save transaction proc_scope
begin try
--DO WORK HERE
commit transaction
end try
begin catch
rollback transaction proc_scope
commit transaction
declare @.errormessage nvarchar(4000),
@.errorseverity int
select
@.errormessage = error_message(),
@.errorseverity = error_severity()
raiserror(@.errormessage, @.errorseverity, 1)
end catch
In SQL 2005 you can just cut and paste all this junk around your procedure,
and you don't have to pollute the implementation with a bunch of error
handling noise.
So there is a right way to do transaction handling in a stored proceudre,
and it isn't all that hard, but transacaction handling is basically the
responsibility of the client code.
David|||Thanks to all who have responded, although I'm still not quite sure whether
I should (or need to) revisit about 100 SPs I have (that are called from a
client transaction through COM+ - and yes, the client does the
ObjectContext.SetAbort duties). It does work today with rollbacks as
necessary and expected but I felt lazy by relying on ASP to start the
transaction and have the MTS blackbox take care of the details especially
when dealing with SQL Server. But I guess that's a feature ;)
And thanks to Erland Sommarskog for the very thoughtful piece about error
handling.
"Don Miller" <nospam@.nospam.com> wrote in message
news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
> I have a web app (ASP) that does all updates, inserts by calling
> transaction-supported COM+ components (with the transaction started in the
> ASP page, i.e. transaction=required) that use ADO to call stored
procedures
> (that usually involve single tables). If there is any error (missing SP,
> parameter value of wrong type, etc.) with the database insert/update, MTS
> automatically rolls back everything that was done in the database (and
maybe
> SQL Server does that with any error in an SP anyway'). As such, when I
> write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
> checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
> So, I have many SPs (that do not return any indication of success, or not)
> like
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
INSERTs,
> UPDATEs within transactional statements in a production database? Should
one
> always check for errors with INSERTS or UPDATES? Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP? Or, should one just save those statements
for
> when the SQL script and logic itself takes care of rolling back a database
> when a series of updates or inserts are made?
> Thanks for any thoughts.
>