Optimistic and pessimistic reference checks

To optimize concurrency, the LN MSQL database driver supports optimistic reference checking. In the lookup reference mode, when inserts are performed in a child table, the driver checks whether the reference exists in the parent table and locks the referenced record to be sure that another user cannot delete it within the current transaction. This approach is called the pessimistic approach.

The pessimistic approach blocks an insert of another user referencing the same parent row, thereby affecting the concurrency. To avoid this problem there is another approach where the row in the parent table is not locked. This approach is called the optimistic approach. As the record is not locked, another user can still perform an insert operation, which improves the concurrency. Enabling of this option is configurable via the dbsinit resource variable. Pessimistic reference checking is not available for MSQL driver at present.