Q2.6: What's a good example of a transaction?
This answer is geared for Online Transaction Processing
(OTLP) applications.
To gain maximum throughput all your transactions should be in stored
procedures - see Q8.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:
<![CDATA[
/* 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:
<![CDATA[
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.