Progress
Programming
Handbook


Locks and Transactions

To understand the relationship between record locks and transactions, see Figure 13–4, which shows a modified version of p-lock3.p.

p-lock4.p

Figure 13–4: Locks and Transactions

The following hypothetical steps illustrate a common problem with record locks:

  1. At the start of the first iteration of the REPEAT block, Progress starts a transaction.
  2. You supply customer number 1 to the PROMPT–FOR statement.
  3. The FIND statement reads the database record for customer 1 and Progress places a SHARE–LOCK on that record.
  4. The UPDATE statement lets you make changes to the record and Progress places an EXCLUSIVE–LOCK on the record.
  5. At the end of the UPDATE statement, Progress releases the EXCLUSIVE–LOCK.
  6. Another user running the same procedure finds customer 1 and updates information for that customer (this user gets the record with the new data you entered in Step 4).
  7. You answer no to the “Updates OK” prompt and Progress undoes your changes.
  8. The other user answers yes to the “Updates OK” prompt and Progress writes the record back to the database, with the changes that you wanted undone.

Because of this problem and others like it, Progress uses some standards to determine how long to hold a lock. These standards produce behavior that matches what you want to happen in situations like the one just described. Table 13–1 lists the standards Progress uses to determine when to release record locks.

Table 13–1: When Progress Releases Record Locks 
Type of Lock
Acquired
During a
Transaction1
Acquired
Outside a
Transaction2
Acquired Outside,
but Held Going into a Transaction3
SHARE
Held until the transaction end or record release, whichever is later.4
Held until record release.
Held until the transaction end or record release, whichever is later.4
EXCLUSIVE
Held until transaction end. Then converted to SHARE5if record scope is larger than transaction and record is still active in any buffer.
N/A
N/A
  1. A record acquires a lock during a transaction if Progress reads or rereads it after the start, and before the end of a transaction.
  2. A record acquires a lock outside a transaction if Progress reads it when a transaction is not active and releases it before a transaction starts.
  3. A record acquires a lock outside a transaction and holds it going into a transaction if Progress reads the record when a transaction is not active and releases the record when a transaction starts
  4. Progress releases a record from a buffer at the end of the record scope, when Progress executes a RELEASE statement, or when Progress replaces the record in the buffer by a CREATE, FIND, or FOR EACH statement
  5. This is true even if Progress read the record with NO–LOCK prior to the transaction and rereads it with EXCLUSIVE–LOCK during the transaction. By default, Progress converts the lock to SHARE–LOCK, not back to NO–LOCK. To avoid holding the record SHARE–LOCK in this case, execute a RELEASE statement during the transaction. Progress then releases the lock at the end of the transaction. You can then re-read the record with NO–LOCK.

Progress releases locks acquired within a transaction, (or changes them to SHARE–LOCK if it locked the record prior to the transaction) if it backs out the transaction. This does not occur when Progress backs out a subtransaction because Progress does not release the locks a record acquires within a transaction unless the transaction ends or Progress undoes the entire transaction.

How do the rules in Table 13–1 affect the p-lock4.p procedure?

  1. At the start of the first iteration of the REPEAT block, Progress starts a transaction.
  2. You supply customer number 1 to the PROMPT–FOR statement.
  3. The FIND statement reads the database record for customer 1 and Progress places a SHARE–LOCK on that record. The SHARE–LOCK remains until the end of the transaction or until Progress releases the record, whichever occurs later. In this example, transaction end and record release both happen at the end of the REPEAT block.
  4. The UPDATE statement lets you make changes to the record and Progress upgrades the SHARE–LOCK to an EXCLUSIVE–LOCK. This lock remains until the end of the transaction, which is the end of this iteration of the REPEAT block.
  5. Another user running the same procedure tries to find customer 1. However, because the record for customer 1 is EXCLUSIVE–LOCKed, the FIND statement waits until the record is available. The user sees a message that the record is in use.
  6. You answer NO to the “Updates OK” question, Progress undoes the changes you made to the record, reaches the end of the first iteration of the REPEAT block, the transaction ends and releases the EXCLUSIVE–LOCK on the record.
  7. The other user is able to find the record and update it.

Because of the duration of the record locks, Progress processes your transactions consistently in a multi-user environment.


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