SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-22
Using Generic Locks
If the lock length of the ACCOUNT table is defined to be the length of the NAME
column, SQL/MP acquires locks for an application by using a single lock to lock all
rows with the same value for NAME.
If the lock length was not specified when the ACCOUNT table was defined, SQL/MP
uses the default lock length, which is the length of the primary key. The default lock
length of the ACCOUNT table is 28 bytes, the length of the NAME and ACCOUNT_NO
columns. Processing a customer account with the default lock length in effect requires
a separate lock for each account belonging to the same customer (a separate lock on
each row with the same value for NAME).
To define the lock length for generic locking, you must create a table with key-
sequenced organization and a primary key made up of either several columns or an
initial column that contains ASCII data. You can specify the LOCKLENGTH attribute
either in the CREATE TABLE or CREATE INDEX statement, or in an ALTER TABLE or
ALTER INDEX statement for an existing table or index, respectively.
The LOCKLENGTH attribute designates the number of leading bytes of the key that
the system should use to identify the rows to lock. All rows with the same value in
those leading bytes are locked with a single lock anytime one of those rows is
accessed. After you have specified lock length for a table, the length applies to all
applications using the table.
The current LOCKLENGTH is stored in the FILES table in the catalog associated with
the table of interest.
These are the advantages and disadvantages of generic locks:
•
Generic locks improve performance by increasing the granularity of the lock. They
decrease concurrency, though, because a lock controls a larger number of rows.
Figure 14-1. Generic Locking Example
CUSTOMER Table
ACCOUNT Table
NAME
20 Bytes
ADDRESS
NAME
20 Bytes
ACCOUNT_NO
8 Bytes
BALANCE
Concatenated Primary Key
Lock Length 20 Bytes for Generic Locking
•••
•••
Primary Key
VST010.vsd