Progress
SQL-92
Guide and Reference
LOCK TABLE Statement
Explicitly locks one or more specified tables for shared or exclusive access.
SYNTAX
table_name
The table in the database that you want to lock explicitly. You can specify one table or a comma-separated list of tables.
SHARE MODE
Allows all transactions to read the table(s). Prohibits all other transactions from modifying the table(s). After you acquire an explicit lock on a table in
SHARE
MODE
, anySELECT
statements in your transaction can read rows and do not implicitly acquire individual record locks. AnyINSERT
,UPDATE
, andDELETE
statements do acquire record locks.EXCLUSIVE MODE
Allows the current transaction to read and modify the table(s), and prohibits any other transactions from reading or modifying the table(s). After you acquire an explicit lock on a table in
EXAMPLESEXCLUSIVE
MODE
, you canSELECT
,INSERT
,UPDATE
, andDELETE
rows and your transaction does not implicitly acquire individual record locks for these operations.Unless another transaction holds an EXCLUSIVE lock on the teratab and megatab tables, the SHARE MODE example explicitly locks the tables. The shared lock allows all transactions to read the tables. Only the current transaction can modify the tables:
Unless another transaction holds a lock on the teratab table, the EXCLUSIVE MODE example locks the teratab table for exclusive use by the current transaction. No other transactions can read or modify the teratab table:
Without a table lock, the first SELECT statement in this example could exceed the limits of the record lock table. The LOCK TABLE statement prevents the subsequent SELECT statement from consuming the record lock table:
NOTES
- The LOCK TABLE statement may encounter a locking conflict with another transaction.
- The SHARE MODE option detects a locking conflict if another transaction:
- The EXCLUSIVE MODE option detects a locking conflict if another transaction:
- When there is a locking conflict, the transaction is suspended for up to five seconds until the requested lock can be acquired. If after five seconds the lock is still not available, the database returns an error.
- You can use explicit table locking to improve the performance of a single transaction, at the cost of decreasing the concurrency of the system and potentially blocking other transactions. It is more efficient to lock a table explicitly if you know that the transaction will be updating a substantial part of a table. You gain efficiency by decreasing the overhead of the implicit locking mechanism, and by decreasing any potential wait time for acquiring individual record locks on the table.
- You can use explicit table locking to minimize potential deadlocks in situations where a transaction is modifying a substantial part of a table. Before making a choice between explicit or implicit locking, compare the benefits of table locking with the disadvantages of losing concurrency.
- The database releases explicit and implicit locks only when the transaction ends with a COMMIT or ROLLBACK operation.
- See the SET TRANSACTION ISOLATION LEVEL Statement for information on isolation levels and the inconsistencies allowed by each:
- The isolation level in effect determines the record locking scheme.
- READ UNCOMMITTED ensures that when a record is read, no record locks are acquired.
- READ COMMITTED ensures that when a record is read a share lock is acquired on that record; the duration of the lock varies.
- REPEATABLE READ ensures that when a record is read, a share lock is acquired on that record and held until the end of the current transaction.
- SERIALIZABLE ensures that when a table is accessed the entire table is locked with a lock of appropriate strength; the lock is held until the end of the transaction.
- With READ COMMITTED, the intent is to hold the share lock on a record until the application reads the next record. This behavior is not always achieved.
Under specific conditions, the server might release a share record lock before the record is returned to the client. This can lead to unreliable results when the application intends to update the rows being fetched. To prevent early release of the share lock when READ COMMITTED is in effect the following must be true:
- A SELECT Statement references one table only.
- The statement does not contain an ORDER BY Clause.
- The statement does not contain a GROUP BY CLAUSE.
- A WHERE Clause does not contain any subqueries.
If an application requires a query that does not satisfy the conditions listed and the intent is to update the fetched rows, the application should set the isolation level to REPEATABLE READ.
- To achieve better performance, some client configurations prefetch records from the server. With isolation level READ COMMITTED, it is possible for the server to release a share lock on a record that an application is processing. When prefetching is in effect, a user application should not assume that the current row is locked. If a Progress C Language embedded SQL-92 application uses the array fetch feature, prefetching is enabled.
- If an application employs prefetching and has a requirement for updating the fetched rows, the application should set the transaction isolation level to REPEATABLE READ.
- As illustrated in the third example, some SELECT statements place an entry in the record lock table for every row in a table. For these queries, consider issuing a LOCK TABLE SHARE MODE before the SELECT statement.
AUTHORIZATION
Must have DBA privilege or SELECT privilege on the table.
SQL COMPLIANCE
Progress Extension
ENVIRONMENT
Embedded SQL, interactive SQL, ODBC applications, JDBC applications
RELATED STATEMENTS
COMMIT Statement, ROLLBACK Statement, SET TRANSACTION ISOLATION LEVEL Statement
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |