Locking in TradeDesign is used to block database entries from update by other (processes or user) for the time needed to complete all updates. The target is to be able to update the data of multiple entries in one go and to make sure that during the handling of a transaction nobody else can change the data the user is working on. The target of this locking is to provide a robust operation of a multiuser system with many users and a high transaction throughput.

To allow the application to provide an exclusive access to selected database entries TradeDesign provides the possibility to lock one or multiple database entries. A locked database entry cannot be locked or changed by another user or process as long as the entry is locked.

TradeDesign provides two different types of locks. To be able to lock complex application datasets like contracts from concurrent change of data for the duration of an interactive transaction a so-called long-term lock is provided. For the locks necessary to update data within a technical database transaction so-called short-term locks are provided.

To enable TradeDesign to identify a database entry or, to be more precise, a lockable unit uniquely for each table a so-called lock key must be setup as part of the table definition module. Using the table name and the content of the fields building the lock key definition of the table allows TradeDesign to maintain a central lock table keeping all current active locks. This table is used by TradeDesign to check for existing locks when setting new locks. In case no lock key is defined in a table definition module, TradeDesign will lock the whole table when locks are requested.

Databases allow to group multiple database updates into a database transaction. A database transaction starts with reading and locking (with short-term locks) all not yet locked entries to be updated. Then all changes are applied to the database, independent whether insert, update, or delete of data. When all needed changes which are needed for a consistent update are completed, the database transaction is committed and by that gets final. To support this functionality TradeDesign provides commands to set a short-term lock and to commit the current database transaction.

To support user driven interactive updates to the database TradeDesign provides so called long-term locks which can be set by the application when starting to allow the change of data of a dataset. It is the idea that the application does not allow to change the data when the main entry cannot be read with lock. This guarantees that only one user (or process) can concurrently change one dataset. In case multiple datasets need to get changed multiple locks need to be raised. To support this functionality TradeDesign provides commands to set a long-term lock and to release a long-term lock.

To ensure robust implementation TradeDesign forbids to have any user interaction when a database transaction is not yet committed. Any try to do so will result in runtime errors.

It is important to understand that the provided framework (SYSMOD, MDxBUT, MTABUT etc.) will generically issue most of the locking functions. The application programmer typically has no need to code anything on locking in business transactions or static data.

Below a time-sequence of two user trying to update the same entry in table ETA (entity address), which is connected to table ETAUIL (language specific entity address fields). The table SLG used in the below sample is the generic used system log used to keep a log of all database maintenance updates.

In the below sample just the two bold marked parts accessing ETAUIL need to be coded as part of the application, as they are not supported by the general framework.

To illustrate below the content of the TradeDesign internal lock table of user A at the moment when the database transaction is complete just before the lock of ETA shall get released.

Locked Records:

Details about the TradeDesign commands and the implementation within TradeDesign can be found in the TradeDesign documentation on page “Record Locking”.

Further general information on record locking can be found in wikipedia at https://en.wikipedia.org/wiki/Record_locking and on database transaction at https://en.wikipedia.org/wiki/Database_transaction.