SQL/MX 2.x Reference Manual (H06.04+)

Introduction

HP NonStop SQL/MX Reference Manual—540440-003

1-10

Database Integrity and Locking

To protect the integrity of the database, NonStop SQL/MX provides locks on data. For

example, NonStop SQL/MX locks a row when an executing process (either MXCI or a

host program) accesses a row to modify it. The lock ensures that no other process

simultaneously modifies the same row.

Default locking normally protects data but reduces concurrency. If your application has

problems with lock contention, you might want to use options that control the

characteristics of locks.

Locks have these characteristics:

•

Lock Duration (short or long)

•

Lock Granularity (table lock, partition lock, subset of rows, or single row)

•

Lock Mode (exclusive, shared, no lock)

•

Lock Holder (transaction or process)

Lock Duration

Lock duration controls how long a lock is held. You can specify lock duration for only

the read portion of a statement. All write locks are held until the end of the transaction

(for audited tables) or until the program releases the locks (for nonaudited tables).

You can use the LOCK TABLE statement to lock a table. How long the lock is held

depends on whether the locked table is audited or nonaudited.

Lock duration is also affected by whether you choose the SERIALIZABLE access

option for DML statements. This access option causes the maximum 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.

You can control locks for the entire table with LOCK TABLE. Otherwise, NonStop

SQL/MX 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.

NonStop SQL/MX can automatically increase the granularity of locks for a particular

transaction, depending on processing requirements. This increase in granularity is

referred to as lock escalation. If a process holds many row locks on the same partition

of a partitioned table, NonStop SQL/MX might escalate the row locks to a partition lock.

For a nonpartitioned table, a partition lock is a table lock.

Lock escalation is affected by the value of MaxLocksPerTCB. If an application acquires

locks on more than 10% of this value, or a default of 500, since MaxLocksPerTCB's

default is 5000 for the volume, DP2 attempts to escalate to a table lock, even though

these rows may be locked only on a single partition of the table. If there are other

transactions running concurrently with locks on the table, DP2 may not be able to

escalate to a table lock, but it will keep trying.