Managing Multi-user Applications

To coordinate concurrent access to records in multi-user applications, ESQL statements use a record locking scheme based on the Progress record locking phrases. You do not insert Progress record locking phrases directly into ESQL statements. Record locking phrases are derived from the corresponding SQL syntax elements.

Table 2–9 shows the Progress record locks and explains their effects.

Table 2–9: Progress Record Locking Phrases  
Lock Type
Effect
NO-LOCK
Rows are not locked. A row is read even if another application holds an EXCLUSIVE-LOCK on it. You cannot update rows read with NO-LOCK.
SHARE-LOCK
Rows are read in anticipation of a possible update. A row is not read if another application holds an EXCLUSIVE-LOCK on it.
EXCLUSIVE-LOCK
Rows are locked until the end of the transaction. Another application cannot read rows using SHARE-LOCK or EXCLUSIVE-LOCK.

Table 2–10 lists the types of SQL statements that obtain locks and the locking options associated with them.

Table 2–10: SQL Statements and Associated Record Locks  
Embedded SQL Statement
Associated Record Lock
Non-cursor SELECT statements
(SELECT INTO)
NO-LOCK only. You can see any uncommitted changes to the database.
OPEN cursor statements1
SHARE-LOCK by default. Records retrieved through a cursor use SHARE-LOCK while being read and are converted to EXCLUSIVE-LOCK if updated or deleted.
To read records with the NO-LOCK option, specify FOR READ ONLY in the associated DECLARE CURSOR statement or use the -NL startup parameter to set the default locking mode to NO-LOCK.2
A cursor declared FOR UPDATE will SHARE-LOCK the records retrieved. The records are updated to EXCLUSIVE-LOCK when they are fetched.
Searched UPDATE statements
(UPDATE WHERE condition)
Positioned UPDATE statements
(UPDATE WHERE CURRENT)
Searched DELETE statements
(DELETE WHERE condition)
Positioned DELETE statements
(DELETE WHERE CURRENT)
EXCLUSIVE-LOCK only.
  1. Cursor SELECT statements do not obtain locks until the cursor is opened.
  2. In dynamic ESQL, specify the lock criteria in the SQL request string following the cursor specification. For more information, see the "Executing Dynamic Cursor Specifications" section in this chapter.


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