LN MSQL database driver locking strategies

In most cases, the driver generates queries that contain optimizer lock hints to direct MSQL to choose a particular lock level when executing a query. In other cases, the driver does not supply these hints, but uses the default locking determined by the isolation level being used in the connection.

When a record is locked for update or delete by the LN MSQL database driver, the row must be selected with a lock before it is actually updated or deleted. The row is locked when the SELECT WITH LOCK statement is executed to make sure that another user does not change the row. When the SELECT WITH LOCK statement locks the row; it acquires a shared or exclusive lock depending on the isolation level and any lock hints used. If the process tries to acquire an exclusive lock on a row that is already locked by another process, the process waits until the locked resources are released or the lock timeout period expires. If a time-out occurs, the client either retries the same operation or rolls back the transaction.

The database driver uses a delayed locking strategy to improve concurrency. This means that before an update is executed, the driver checks each column to determine whether the related columns have been changed. If the columns are not changed, the update is not executed. This reduces both the workload on the RDBMS and the network traffic between the database driver and the RDBMS and improves concurrency by reducing locking in the server.

By default, the driver uses the ”read uncommitted isolation level” to acquire shared locks, and uses the exclusive lock for update and delete actions.

The driver typically uses an uncommitted read for normal read operations. Exclusive locks are required so that any locks are retained until the transaction is committed or aborted, even after the cursor is closed.

Several aspects of the LN MSQL database driver locking behavior can be configured. The following default characteristics of the LN MSQL database driver can be modified: the lock time-out interval, the number of high-level lock retries, and the fill factor for indexes. Each of these locking behaviors is described in the following sections.