Release 10.1A: OpenEdge Data Management:
SQL Development

> ## 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 4GL 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 you application’s needs.

> For 4GL clients, there is a lock wait timeout 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 4GL client also requested a lock, the SQL client will time-out and give up waiting long before the 4GL client. SQL can be set by using the environment variable `PRO_LOCKWAIT_TIMEOUT`.

> 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.

> - Will read only past row-level locks.

> - Can only be specified in the main `SELECT` statement but not in the subquery `SELECT` statement in the `search_condition` of the `WHERE` clause.

> For detailed information on record locks and the `READPAST` locking hint, see Chapter 8, " Data Control Language and Transaction Behavior."

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095