Running transactions

In order to store changes made in a transaction without violating database consistency, database entries referenced in the transaction might be locked (update lock) in order to prevent them to be updated by other applications. Thus, the essential rule when running a transaction is:

R2.1.1: All database entries updated while running a transaction have to be locked (update lock) within the transaction. This also includes database entries updated because of side effects (physical or conceptual correlations).

This sounds simple, but creates several problems. One problem is a deadlock situation, which may happen as soon as there are more than one database entry involved in a transaction. In order to avoid deadlock situations, conditional locks might be used in order to lock instances in a transaction, i.e. database entries that cannot be locked, will not wait, until locking succeeds, but fail, when locking is not possible , or fail after a number of limited tries.

R2.1.2: In order to lock instances within a transaction, conditional (or time limited) locks have be used, which cause the transaction to fail, when locking does not succeed.

Conditional locking is the typical way of avoiding dead lock situations in transactions. This works fine in many cases for locking instance entries, since locking conflicts for instance entries are rather rare. It causes, however, problems when locking index entries, since index entries are frequently object of modification and need to be locked in a transaction. Thus, inserting or removing instances into or form collections or tables may cause the transaction to fail, when several processes try to update an index asynchronously. In order to avoid this sort of problems, so-called key locks are supported by several systems. While running the transaction, indexes are not changed, but new key values created are stored in a key lock list, which is known to other processes and which prevents other applications from using a key value which has been created within another transaction. In this case, indexes are updated while storing the transaction, which creates very short locking intervals for indexes. On the other hand, key locks are a bit less efficient, since locked keys have to be maintained.

R2.1.3: In order to avoid transaction errors because of failed index locks, key locks might be used for maintaining indexes within a transaction.

There are several DBMS supporting key locks. Since key locking is not always the optimal solution, the application should decide from case to case (collection to collection), whether using key lock features or not, supposed the DBMS supports such an option. In general, we can say that, key locks should be used, when

    DCF(p) > 5 * DCF(i)

When the database correlation factor is high, while the database instance correlation factor is low, the risk of index conflicts in transactions becomes high. More detailed criteria are described in the last chapter.

This the typical transaction strategies provided by database vendors with the DBMS, but those are not sufficient, when there are conceptual correlations to other instances or sets (indexes).

Example: Supposed, the employees must not earn more money than their boss. When changing the salary of employee E to 5000, whose boss B gets 5100, this does not violate conceptual consistency. E will be lock in the transaction, but not B. Now, while the transaction is running, the boss of B decides to reduce the salary of B to 4900. This is also correct, since the database state still display the old salary of 4500 for E. Storing both transactions results in a conceptual inconsistency, since now B gets less that E.

When the database model contains information about the conceptual correlation between employee and boss, the DBMS may lock instance B while running the transaction. Otherwise, the application has to lock B before starting the update operation. Since most DBMS do not model conceptual dependencies in a way, that those can be processed by a transaction, usually the application has to care about locking.

R2.1.4: In order to guarantee conceptual consistency, all instances included in consistency checks have to be locked. Conditional locks are suggested in order to avoid dead lock situations.

Another strategy for avoiding conceptual inconsistency is serializing transactions referring to conceptual correlations. Since the DBMS cannot know at the beginning of a transaction, whether conceptual correlations play a role within the transaction or not, this can be safely handled by the application, only. In general, this is not a good solution, since it increases the total locking time,