This answer is geared for Online Transaction Processing (OTLP) applications.

To gain maximum throughput all your transactions should be in stored procedures - see Q1.5.8. The transactions within each stored procedure should be short and simple. All validation should be done outside of the transaction and only the modification to the database should be done within the transaction. Also, don't forget to name the transaction for sp_whodo - see Q9.2.

The following is an example of a good transaction:

/* perform validation */
select ...
if ... /* error */
/* give error message */
else /* proceed */
begin
begin transaction acct_addition
update ...
insert ...
commit transaction acct_addition
end

The following is an example of a bad transaction:

begin transaction poor_us
update X ...
select ...
if ... /* error */
/* give error message */
else /* proceed */
begin
update ...
insert ...
end
commit transaction poor_us

This is bad because:

* the first update on table X is held throughout the transaction. The idea with OLTP is to get in and out fast.
* If an error message is presented to the end user and we await their response, we'll maintain the lock on table X until the user presses return. If the user is out in the can we can wait for hours.

Hello,
I red your comment.Well nice information sharing,i must say.I am really pleased to see such article sharing at the community.Many people are just start the activity as craze,but the information that you have shared is must known to them.Please try to share more articles like this one,that is really superb.Thank you for sharing such nice post..