NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
L-46
Lock Release Summary
Lock Release Summary
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
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.