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:
- SHARE locks
The database acquires SHARE locks on rows that a transaction has read. A SHARE lock allows other transactions to read the row but prevents others from modifying the row until this transaction issues a COMMIT or a ROLLBACK, or disconnects from the database.
- EXCLUSIVE locks
The database acquires EXCLUSIVE locks on rows that have been modified by a transaction. EXCLUSIVE locks prevent other transactions from either reading or modifying the rows until this transaction issues either a COMMIT or a ROLLBACK, or disconnects from the database.
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.
EXAMPLEThe following example shows acquiring a lock in the EXCLUSIVE mode for the customer table:
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.
EXAMPLEThe following example shows how to acquire a lock in SHARE mode for the orders table:
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 |