To provide a secure and easy to handle method for a multiuser operation TradeDesign provides an own and database independent locking system.
The main target is to keep all data accessible for all user without blocking the access and to make sure that in case changes are to be applied to the database, these changes are consistent with the visible and used data of the transaction and all those changes are executed within a single database transaction which is committed directly after completing the last update and before any further user interaction might be necessary.
Due to the database system independency the application can be used unmodified on different database systems.
This locking provides two different types of locks.
Long-term locks, which shall be raised before a user enters data and which protect the dataset the user works on from modification by other processes, being other interactive user or batch processes. Long term locks are typically raised upon initialization of an application transaction and released after completion of the final database update before leaving the application transaction. As the duration of the lock depends on the input of a user and lasts up and until all necessary changes are entered, the lock is called long-term. Long-term locks are transparent to the underlaying database system.
Short-term locks which protect datasets in the database during the short lifetime of a database transaction. There is no user interaction allowed as long as a short-term lock is active. A database transaction typically starts with reading the data to be updated, which has not already been locked with a long-term lock with a short-term lock, perform the necessary logic and then updates to the database and commit the database transaction. As all updates are executed without waiting for user interaction, the lifetime of such locks is typically below a second and therefore called short-term. In classic SQL the short-term locks are comparable to a select for update.
Below sample code snippet of a generic part used in MTABUT is locking a TRN identified by a passed SPT upon start. The sample is using lock because the TRN must be protected against a second process picking up the same old transaction concurrently. The lock is a long-term lock as the lock must stay up to the end of the business transaction during the necessary user interaction up to the final confirmation when the new TRN is finally written into the database and the old, locked TRN gets deleted.
# read old transaction SdbReadLock( \TRNMOD\TRN, \SYSMOD\SPT\OBJINR ) if ErrorCode = tdOdbcLocked or ErrorCode = tdOdbcHeld then TrnmodCancel( 'Transaction cannot be locked', TrnmodCancelExitWithoutPrompt ) else reraise endif
There are multiple types of database updates which are used in many places within the application, which use the same general behaviour and usage of locking.
In business transactions the contract the transaction is working on is locked with a so-called long-term lock upon entry to avoid two users changing the same contract concurrently. Depending whether the business transaction creates a new contract or a new subcontract or another contract might get affected, one or more contracts are locked with long-term locks up front. The implementation of these locks is done by call to generic rules loading the contract. By that each business transaction might hold the logic to lock the contracts the transaction operates on.
Sample taken from transaction GITRAM
init order 950 TRNMOD.TroLockExistingContract ( GIDGRP )
In case one of the requested contracts cannot be locked, as it is locked by another user or process, the execution of the transaction is denied with a message showing the user and the issuing time of the lock. This behaviour is technically implemented in generic rules in TRNMOD which are called from the application transaction or from a functional module.
In the input phase of the business transaction the data needed to update the contract is collected in memory and might be changed multiple times.
After completing all necessary input and checks the final processing event, the so called save event executes the database transaction which updates all necessary database entries in a single database transaction and after committing this database transaction all locks are removed by a call to DbFree or DbFreeAll. Typically, this is done by the generic ruleset. Only additional raised long-term locks need to be handled by the application developer.
By that there is no database update during the input phase and when a business transaction is not completed and cancelled without saving nothing needs to be rolled back.
In the database transaction executed in the processing event in a first phase all entries to be updated which are not already locked with a long-term lock are read from the database with a short time lock. When all entries are loaded and locked then the second phase starts, where all updates and inserts are executed. When all updates are done, the database transaction is committed, which automatically releases all short-term locks. Additionally, the applied long-term locks, which are raised are released and the business transaction terminates typically by returning to a contract selection showing the currently worked on contract with its current state or by returning to a calling transaction.
The locking in Masterfile maintenance is pretty the same as in business transactions. The entry the transaction is working on is locked with a long-term lock upon entry. Depending on the database structure maintained in that transaction, one or more entries might be locked up front with a long-term lock.
After completing all necessary input and checks the final so called save event executes the database transaction which updates all necessary database entries in a single database transaction and after committing this database transaction all locks are removed.
This is logically equivalent to the handling in business transactions. The difference lies in the entries to be locked up front and during the save event. In business transactions the contract is the main locking entry. In Masterfile maintenance the maintained table holds the main locking entry. Depending on the type of the Masterfile to be maintained additional data elements might be locked. In Masterfile maintenance typically the main entry plus some dependent entries, which are updated in connection, are locked.
In the party maintenance as a sample case, the PTY entry of the party maintained is the main entry. Additional entries locked are the main PTA and the connected ADR.
In some transactions some single-events issue database updates. E.g. the release function has to update the status of a transaction or a button to retrieve the reference number in advance has to retrieve the next running counter value.
In a single event, no user interaction takes place. This implies the logic implemented must apply the same logic but without usage of long-term locks and only using short-term locks (= ReadHold). Beside that the database transaction executed is structured the same. In phase 1 reading and locking with short term locks all entries to be updated, which are not already locked by the surrounding logic, then in phase 2 issue all necessary updates and at the end commit the database transaction and terminate the event.
When handling entries in a batch process there is typically a repetitive logic, which operates on multiple different entries. In import processes, the import file identifies the entry (e.g. currency, contract-reference) and some additional data to be used to update the database entry (e.g. new exchange rate, new message, external references). In other batch processes, some criteria identify the entries to be processed and the operation intended.
Within the processing loop the entry to be processed has to be read with a short-term lock (e.g. DbReadHold or SdbReadHold) before the dependent data is read. Data which needs to get updated, must be read with a short-term lock (e.g. DbReadHold). After having loaded all necessary data, the processing and updates are to be executed. Before switching to the next entry, the database transaction hast to be committed unless updates to multiple entries are to grouped together to build a valid database transaction. In case of groups after completion of a group the database transaction hast to be committed.