More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
What's a good example of a transaction? Sybase
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.
(Continued on next question...)
