IMAGE/SQL: Issues and answers concerning SQL tables

Software

Papers & Training

Java

HP Partners

News & Events

Register for more info

5.24 Explain the lock table statement

NOTE: the shading on this picture may not be accurate. Please click on the picture to obtain a larger image, which is accurate.

Key points

• When data is accessed in a table, ALLBASE/SQL generates appropriate locks by using the implicit locking structure that has been established for the table. The implicit locking structure is originally established using the CREATE TABLE statement, but it can be changed by using the ALTER TABLE statement.

• You can use the LOCK TABLE statement to override implicit locking in some situations.

• To use the LOCK TABLE statement, you must have OWNER or SELECT authority for the table, or DBA authority.

• All locks acquired by the LOCK TABLE statement are released when the transaction terminates.

• The LOCK TABLE statement explicitly locks tables in three modes:

  • SHARE (S)

  • SHARE UPDATE (SIX)

  • EXCLUSIVE (X)

• The following are good uses of the LOCK TABLE statement on a PUBLICROW or PUBLIC table:

  • If a query will read most or many rows in the table, it may be useful to obtain an S lock at the table level to minimize deadlocks and shared memory needs. When the table is locked in SHARE mode, other users can not modify it for the duration of the transaction. Also, row and page locks are not acquired, which reduces the overhead and shared memory needs of ALLBASE/SQL.

  • If a query will write to most or many rows in the table, it may be useful to obtain an X or an SIX lock at the table level to minimize deadlocks and shared memory needs.

    • When the table is locked in EXCLUSIVE mode, other users can not access it for the duration of the transaction. Row and page locks are not acquired.

    • When the table is locked in SHARE UPDATE mode, other users can not modify it for the duration of the transaction. Row and page locks are not acquired for reads, but they are acquired for the rows or pages to which the transaction writes. An SIX lock provides greater concurrency than an X lock, but more shared memory will be used by the transaction. Compared to a query in which an X lock has been obtained, an SIX query can take longer to execute because it may need to wait to modify data that has been read (and locked with S locks) by other transactions.

Page last updated on November 29, 1995