Kbase P3208: An overview of Version 9 SQL92 Table Locks
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/11/2003 |
|
Solution ID: P3208
GOAL:
An overview of Version 9 SQL92 Table Locks
FACT(s) (Environment):
Progress 9.x
FIX:
As part of the SQL-92 database implementation in Version 9, the Progress database now supports table locks. Previous versions only supported row (record) locks.
The Version 9 database supports a hierarchical locking mechanism. It is a two level hierarchy:
- tables
- rows within tables
The row locks are the same as the record locks in previous versions.
The table locks allow SQL-92 operations on the database, and also tablemove and indexmove features.
The hierarchical locking is not the default locking model. The database will operate only with row level locking until the first table lock is requested. At that point, the database will switch into hierarchical locking. When the table locking is no longer necessary, the locking will revert back to row level locking.
Table locks can be requested by these methods:
a) performing a tablemove or indexmove command
b) executing the LOCK TABLE command from a SQL-92 client
c) setting the transaction isolation level to serializable from a SQL-92 client
Note that there is currently no method to request a table lock from the 4GL (or SQL-89).
When the hierarchical locking is active, there are five table lock modes:
- Intent Share (IS)
- Intent Exclusive (IX)
- Shared with Intent Exclusive (SIX)
- Shared (S)
- Exclusive (X)
Exclusive (X) and Shared (S) locks can be requested explicitly (tablemove, indexmove, or SQL-92 client LOCK TABLE statement).
Implicit table locks (IS, IX and SIX) are generated when rows in the table are locked.