Neoview SQL Reference Manual (R2.4 SP2)

Database Integrity and Locking
To protect the integrity of the database, Neoview SQL provides locks on data. For example,
Neoview SQL locks a row when an executing process 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, 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. You can use the LOCK TABLE statement to lock a table. 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 subset of rows, or a single row.
You can control locks for the entire table with the LOCK TABLE statement. Otherwise, Neoview
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.
Neoview SQL can automatically increase the granularity of locks for a particular volume per
transaction, depending on processing requirements. This increase in granularity is referred to
as lock escalation.
Neoview SQL can automatically increase the granularity of locks for a particular table partition,
depending on processing requirements. This increase in granularity is referred to as lock escalation.
The number of locks prior to escalation is about 8,000, provided there is no conflicting lock. If
there is a conflicting lock, escalation will occur anywhere between 8,000 and 100,000, at about
8,000 lock increments. If the lock conflict prevents lock escalation when the transaction hits the
volume lock limit, or the volume lock table space is exhausted, locking errors will result.
Lock Mode
Lock mode controls access to locked data. You can specify lock mode only for rows that are read.
SHARE lock mode allows multiple users to lock and read the same data. EXCLUSIVE lockmode
limits access to locked data to the lock holder and to other users who specify READ
UNCOMMITTED (but not READ COMMITTED or SERIALIZABLE) access. Lock modes are the
same when you choose READ COMMITTED or SERIALIZABLE access.
Lock mode is sometimes determined by Neoview SQL. SQL ensures that an exclusive lock is in
effect for write operations and usually acquires a shared lock for operations that access data
without modifying it. You choose lock mode in these instances:
• On the LOCK TABLE statement, you can choose EXCLUSIVE or SHARE.
• On the SELECT statement, you can specify IN EXCLUSIVE MODE or IN SHARE MODE.
Database Integrity and Locking 27