SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
L-49
Lock Duration
Lock Granularity
Lock granularity controls the number of rows affected by a single lock. The level of
granularity can be a table, a partition, a subset of rows, or a single row.
The LOCKLENGTH file attribute for a table or index controls the granularity of locks for
the table or index. You can control locks for an entire table and its associated indexes
with LOCK TABLE and CONTROL TABLE; otherwise, SQL determines the granularity
by considering the access option you specify, the table size and definition, and the
estimated percentage of rows the query will access.
SQL can automatically increase the granularity of locks for a particular transaction,
depending on processing requirements. This is called lock escalation. For example, if a
process holds many row locks in different partitions of a partitioned table, SQL might
escalate the row locks to a table lock. If a process holds many row locks on the same
Table L-1. Lock Release Summary
Audited Tables/Views Nonaudited Tables/Views
Operation STABLE REPEATABLE STABLE REPEATABLE
SELECT INTO RA KA RA KA
FETCH cursor
updateable
1
RS,KE
2
KA RA KA
FETCH cursor
not updateable
RS
3
KA RA KA
Set UPDATE or
DELETE
RS,KE KA RA KA
INSERT KA KA RA KA
CLOSE cursor RS,KE KA RA KA
FREE RESOURCES RS,KE KA RA RA
FREE RESOURCES
AUDIT ONLY
RS,KE KA N.A. N.A.
UNLOCK TABLE N.A. N.A. RA RA
TMF transaction
abort/commit
RA RA RA RA
COMMIT or
ROLLBACK WORK
AUDITONLY
RA RA N.A. N.A.
A Any type of lock R Release locks
E Exclusive locks S Shared locks
K Keep locks
Example: RS,KE is release shared locks; keep exclusive.
1
Locks for UPDATE and DELETE through a cursor are processed through FETCH operations.
2
The lock is kept if the record is updated.
3
The lock is kept if the record is deleted.