Progress
Embedded SQL-92
Guide and Reference


Locking and Transactions

This section discusses general points about programs and row locking. For more information on table locking and the LOCK TABLE statement, see Chapter 3, “SQL-92 Statements,” in the Progress SQL-92 Guide and Reference .

During execution of an ESQL program, transactions implicitly lock rows in a database. This is done so that the data in the database remains consistent. The implicit locks are released when you commit the transaction or disconnect from the database.

Typically, a storage system supports two types of locks:

In applications where a large number of rows will be accessed for either reading or modifying, ESQL provides an explicit locking construct for locking all the rows of a table. The LOCK TABLE statement explicitly locks a table in either SHARE or EXCLUSIVE mode.

EXAMPLE

The following example shows acquiring a lock in the EXCLUSIVE mode for the customer table:

EXEC SQL
     LOCK TABLE customer IN EXCLUSIVE MODE ; 

This statement prevents other transactions from either reading or modifying the table customer until the transaction issues a COMMIT or ROLLBACK, or disconnects from the database.

EXAMPLE

The following example shows how to acquire a lock in SHARE mode for the orders table:

EXEC SQL
     LOCK TABLE orders IN SHARE MODE ; 

This statement prevents other transactions from modifying the orders table until the transaction issues either a COMMIT or a ROLLBACK. You can use explicit locking to improve the performance of a single transaction at the cost of decreasing the concurrency of the system, and potentially blocking other transactions. The increased performance comes from reducing the overhead imposed by the implicit locking mechanism, along with eliminating any potential waits for acquiring row level locks for the table.


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