Try OpenEdge Now
skip to main content
SQL Development
OpenEdge SQL and Advanced Business Language Interoperability : ABL and OpenEdge SQL interaction in an OpenEdge application : Working with locking behavior and isolation levels
 

Working with locking behavior and isolation levels

Consistency and concurrency are maintained with record locks. Because SQL relies on table and record locks to carry out the intent of transaction isolation levels, table locks were implemented in the database engine. Both ABL and SQL clients encounter table locks while executing transactions.
A locking conflict occurs when two transactions request the same resource at the same time. The SQL client will wait for a resource for a specified time before giving up, at which point an error would be generated and the operation would need to be retried.
The default wait time is five seconds, but can be modified to a duration that meets your application's needs. The SQL lock wait time-out value can be set by using the startup parameter -SQL_LOCKWAIT_TIMEOUT along with the proserve command. The specified time-out value must be a minimum of five seconds.
For ABL clients, there is a lock wait time-out parameter (-lkwtmo) that specifies how long a client should wait for a resource. The current default value is 30 minutes. If a SQL client has a lock on a table for which the ABL client also requested a lock, the SQL client times out and gives up waiting long before the ABL client.
A SELECT statement can fail if some records of the selected tables are locked by other transactions. The SELECT transaction is not able to continue until the record locks are released by other transactions. The READPAST lock hint causes a transaction to skip rows locked by other transactions. The skipped rows do not appear in the result set, and a warning is returned to the client.
The following conditions apply to the READPAST locking hint:
*Applies only to the SELECT statement
*Applies only to transactions operating at READ COMMITTED isolation
*Reads only past row-level locks
*Only specified in the main SELECT statement but not in the subquery SELECT statement in the search_condition of the WHERE clause
As an option to the READPAST locking hint, you may use the WITH NOLOCK option as part of the table reference of the SELECT statement. While the READPAST hint will skip locked records, the WITH NOLOCK option allows a dirty read to be performed. For more information on record locks, the READPAST locking hint, and the WITH NOLOCK option, see Data Control Language and Transaction Behavior