For long transactions, a program can hold many locks over a long period of time, which will probably hinder other users. For this reason, transactions should be as short as possible. On the other hand, locks must be requested after each COMMIT WORK or ROLLBACK WORK, which can result in increased database activity for very short transactions. The most effective transaction length should therefore be determined taking into consideration recovery duration, lock duration, and locking overhead.
If modifications to one or more tables are to be applied within one transaction, the following transaction programming is recommended:
1) Reading phase with user dialog. All values which are to be entered into the database are collected in host variables. For this purpose, read accesses to the database and interactive entries are necessary. Reading is done without read locks in order not to hinder other users and to avoid deadlocks. No write accesses to the database are used. More details are included in the Reference manual for 'SELECT .. WITH LOCK OPTIMISTIC'.
2) Locking phase. By means of the LOCK statement, write locks are requested for all rows or tables to be modified, and also read locks are requested for all rows or tables to be kept statically. This ensures that all rows or tables are accessible before they are write accessed. Rows read without read lock in phase 1 must be read again in order to ensure that these rows have not been modified in the meantime. If modifications have been made, phase 1 has to be repeated after releasing the lock. When optimistic locks (SELECT ... WITH LOCK OPTIMISTIC) are used, it is not necessary to read the rows again, because modifications to the read object made by other users result in a corresponding message for the UPDATE or DELETE statement as long as the corresponding row was not locked explicitly.
3) Writing phase. In this phase, the contents of the host variables are entered into the rows or tables previously locked. User dialog is not allowed in the phases 2 and 3.
Deviations from this 'textbook' schema, e.g., user dialog with set locks, can lead to decreased concurrency or deadlocks.
A COMMIT WORK is recommendable after each CREATE or DROP statement, because these statements set write locks on the ADABAS system information. These locks are released by COMMIT WORK.
If it is not essential to have a consistent database state during a long query (e.g., during statistical queries), isolation level 0 can be specified as the lock set mode. In this case, write-locked tables can also be read without read locks, i.e., 'readers' will not be blocked by 'writers'.
When table contents are modified without previously requesting a write lock, this lock will be set implicitly.