SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

Introduction

HP NonStop SQL/MX Reference Manual—523725-004

1-9

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 Mode

Lock mode controls access to locked data. You can specify lock mode only for rows

that are read.