Artur Södler Software-Qualität

Working with a Database

Databases incorporate both storage capacity and management software for almost every complex software. Modern databases are usually referred to as relational, transactional or SQL database management systems.

A database is for instance responsible for updating your bank account balance with every transfer. A bank transfer implies that each and every debit entry on a payer's account matches a corresponding credit entry on a payee's account. It would be fatal if they would not match.

It is imperative that a certain set of changes in the data must form an indivisible logical unit, a transaction. The changes must be done either completely or not at all. Furthermore, we must not allow anybody to see changes halfway done.

If you combine the previous with the directive to build modular software, i.e. to connect software components, the necessity arises to connect entire transactions. If module 1 commits its transaction 1, module 2 must commit its transaction 2 as well. If one fails, the other must also be canceled.

Is your software using nestable transactions?

create procedure AccountEntry (p_acct_no number, p_amount number, p_text varchar) is
begin
insert into acct_entries (when, acct_no, amount, text)
values (sysdate, p_acct_no, p_amount, p_text);

update accounts set balance = balance + p_amount
where acct_no = p_acct_no;

commit;
end AccountEntry;

create procedure AccountTransaction (from_acct_no number, to_acct_no number, amount number, text varchar) is
begin
AccountEntry (from_acct_no, -amount, text);
AccountEntry (to_acct_no, amount, text);

commit; -- huh???
end AccountTransaction;

Please try to arrange a transaction so that both the procedure "AccountEntry" and the procedure "AccountTransaction" effect an atomic transaction. It won't work. Because not even the most modern database supports nestable transactions. Now we are back in the Stone Age of computer science, in 1952, right before subprograms have been invented.

It all comes down to the fact, that the inner procedure "AccountEntry" has to control the transaction if and only if it is called separately. But if "AccountTransaction" already controls the transaction, the subprogram "AccountEntry" has to refrain from transaction control. You need a context, which you either pass to each and every subprogramm as an additional parameter (confusing!), or as global data.

Object-oriented programming suits best for the job:

extern CDbConnection DbConnection;

void AccountEntry (long AccountNo, double Amount, const char *Text) {
   CDbCursor c;
   CTransaction t;
   c.Open (DbConnection);
   c ["acct_no"] = AccountNo;
   c ["amount"] = Amount;
   c ["text"] = Text;
   t.Begin (DbConnection);
   c.Execute (
      "insert into acct_entries (when, acct_no, amount, text) "
      "values (sysdate, :acct_no, :amount, :text)");
   c.Execute (
      "update accounts set balance = balance + :amount "
      "where acct_no = :acct_no");
   t.End();
}

The above example hides an important detail: The invoking of the destructor t.~CTransaction() in case of an exception.

The transaction t has to manage the following:

•		Access to the global transaction nesting count. The object controls access. Erroneous individual access may have a fatal impact.
•		Counting the depth of nesting of transactions. Increment on Begin(), decrement on End(). Only the outermost transacion is operative.
•		Committing or revoking the outermost transaction. This is the very task: commit on t.End(), revoke on t.~CTransaction().
•		Integrity check. If an inner transaction has been canceled, the outer must not be committed. A double transaction start is prohibited as well as End() before Begin().
•		Stat of a watchdog timer. The transaction must be completed within reasonable time.

Some more important points in connection with databases:

•		clear separation of database access and user interface, error messages belong to the user interface
•		automatic repeat on deadlocks, deadlock avoidance strategies
•		data transport between different databases, data transport from a database to a non-transactional system
•		merits and dangers of redundant data
•		SQL or software developer's intelligence — what is best?
•		dynamisc SQL compared to parametrized SQL