 |
» |
|
|
|
|
|
5.23 How do table types affect locking?
Key points
- The first parameter in the
CREATE TABLE statement specifies
the implicit locking structure. You may use the ALTER TABLE
statement to permanently change the structure associated with a table.
- The four implicit locking structures and their normal locking behaviors are:
- PRIVATE (default)
- Locks at the table level (X) for reads.
- Locks at the table level (X) for writes.
- Allows one transaction at a time to read from or write to the table.
PRIVATE tables reduce overhead and shared memory needs for ALLBASE/SQL (since
row or page locks are never acquired), and are less likely to cause a deadlock
condition because each table is always accessed exclusively by one user.
However, they severely reduce concurrency.
- PUBLICREAD
- Locks at the table level (S) for reads.
- Locks at the table level (X) for writes.
- Allows only one transaction at a time to write to the table. When a transaction is
writing to the table, no other transaction can access it. When no transaction
is writing to the table, multiple transactions may read from it at the same
time. PUBLICREAD tables also reduce overhead and shared memory needs for
ALLBASE/SQL (since row or page locks are never acquired), but they provide
better concurrency than PRIVATE tables. Tables that are rarely updated should
generally be PUBLICREAD.
- PUBLIC
- Locks at the page level (S) for reads. Intention locks are generated at the
table level.
- Locks at the page level (X) for writes. Intention locks are generated at the
table level.
- Allows multiple read transactions and write transactions to execute at
the same time on the table. Page level locking is used, which uses more
shared memory than PRIVATE or PUBLICREAD, but less shared memory than
PUBLICROW. Large tables for which maximum read and write concurrency is
desired should generally be PUBLIC.
- PUBLICROW
- Locks at the row level (S) for reads. Intention locks are generated at the
table level and at the page level.
- Locks at the row level (X) for writes. Intention locks are generated at the
table level and at the page level.
- Allows multiple read transactions and write transactions to execute at
the same time on the table. Row level locking is used. Small PUBLICROW
tables are less likely to have deadlocks then small PUBLIC tables. Small
tables for which maximum read and write concurrency is desired should
generally be PUBLICROW.
Page last updated on November 29, 1995
|
|
|
Printable version
