Skip to content

Record Locking

Since CLI is a record locking environment, the find logic also influences how locking works.

Save (Update) and Delete

The save logic distinguishes between creating new records (insert) and changing existing records (update). The update and delete logic uses “where rowid = current rowid” logic.

Locking Records

In SQL environments, the lock granularity is usually one record. The CLI drivers are designed to assume record locking is supported by the back end. Locking is part of the find logic (you can only lock a record if you find it). Not all back ends support the same lock functionality. If it is not possible to lock a record when finding, it will eventually get locked when it is saved or deleted.

The lock logic is only used in a transaction. All the descriptions below assume a transaction has been initiated. The type of lock functionality depends on the type of cursor supported by the back end.

  • The DataFlex SQL Server Driver locks records “as they are found” inside a transaction, no matter what find operation is done.

  • The DataFlex DB2 Driver locks a record when a find equal (on any index) is performed inside a transaction. No lock is issued on other find operations. If an unlocked record is updated, it is locked at the moment of the update (save).

  • The DataFlex ODBC Driver gets information from the back end on what lock functionality is supported for the database cursor types that are supported. It chooses the most appropriate strategy for the environment. There are three possible ways locking during find operations works when using ODBC:

  • No locks are set during find operations. If a record is updated, it is locked at the moment of the update (save).

  • Only find equal operations set a lock. If an unlocked record is updated, it is locked at the moment of the update (save).

  • All find operations set a lock.

We tested a number of back ends on the supported lock strategy. The result can be found in the table below:

Back End Lock Strategy
MySQL 2
Oracle 3
PostgreSQL 2
Access 3

Deadlocks/Lock Timeouts

The CLI based Connectivity Kits translate a deadlock or lock timeout error to the DataFlex lock timeout error DFERR_LOCK_TIMEOUT (4106). This error triggers a built-in retry mechanism when explicit transactions are used.

If the DataFlex runtime detects the DFERR_LOCK_TIMEOUT error, it retries the transaction by jumping to the Lock, Reread, or Begin_Transaction command that started the transaction. The retry is attempted a number of times, which can be set by using the Set_Transaction_Retry command. It can be queried by using the Get_Transaction_Retry command.

A retry consists of a two-step process: first, the function Verify_Retry is called; if the return value is 0 (zero), the transaction is retried by jumping to the Lock/Reread/Begin_Transaction command; if the return value is non-zero, the transaction is aborted and logic will jump to the Unlock/End_Transaction command. The function is called in the object that “contains” the transaction (i.e., the object that issued the Lock/Reread/Begin_Transaction).

If a program relies on changing program variables in a transaction, it can be adjusted to use the automatic retry mechanism. Create a Verify_Retry function (Function Verify_Retry Returns Integer) that resets the program variables. The translation can be switched off by setting the Use_DF_LockError keyword in the driver configuration file. When switched off, error CLIERR_DEADLOCK_OR_TIMEOUT (12303) is generated.

The DataFlex ODBC Driver allows the user to set up a translation list of deadlock and lock timeout errors or SQL States in the database level configuration file. An error number list can be created using the Native_LockError keyword; a SQL State list can be created using the Lock_State keyword.