Data source record locking

In a DataServer application, MS SQL Server handles all of its own locking issues. ABL locking rules are modified when you access information from a MS SQL Server data source. As a result, the OpenEdge phrases NO-LOCK and SHARE-LOCK have isolation-level dependencies. The EXCLUSIVE-LOCK behaves the same in MS SQL Server as in an OpenEdge database.

The following table provides data source specific comparisons.

Table 1. OpenEdge database and data source locking
OpenEdge Data source
NO-LOCK Supports the NO-LOCK option in a manner consistent with the OpenEdge database when transaction isolation level is set to read uncommitted.
SHARE-LOCK Supports shared locks at the table, page, and record level. However, the scope and duration of the OpenEdge database vs. MS SQL Server shared locks can differ depending on how data source cursors behave at a transaction boundary and how isolation levels are set. The repeatable read isolation level emulates the OpenEdge database SHARE-LOCK behavior most closely. For more information, see your MS SQL Server documentation.
EXCLUSIVE-LOCK Supports the EXCLUSIVE-LOCK option in a manner consistent with the OpenEdge database using any available isolation level. However, the MS SQL Server optimizer might produce locks at either the table, page, or the record level.

The DataDirect drivers provide four transaction isolation levels in the following order from least to most restrictive: read uncommitted, read committed, repeatable read, and serializable. In a multi-user configuration, you can isolate users from each other in your data source by setting the isolation level. In your OpenEdge schema holder, use the -Dsrv TXN_ISOLATION,n connection parameter (where n = 1, 2, 4, or 8) to set the isolation level in ODBC. See Microsoft documentation and the MS SQL Server documentation for more information.

Note: MS SQL Server might use page-level or table-level locking rather than record-level locking, if its optimizer determines this is the best choice. This can affect data access when two or more users attempt to read or update different records that are on the same page. See your MS SQL Server documentation for details.

The following table shows the possible -Dsrv TXN_ISOLATION,n values with the respective meaning.

Table 2. TXN_ISOLATION values in the -Dsrv parameter
Value Meaning
1 Read uncommitted (default)
2 Read committed
4 Repeatable read
8 Serializable