Neoview SQL Reference Manual (R2.4)

SERIALIZABLE (or REPEATABLE READ) provides the highest level of data consistency. A
statement executing with this access option does not allow dirty reads, nonrepeatable reads, or
phantoms.
SKIP CONFLICT
This option allows transactions to skip rows locked in a conflicting mode by another transaction.
SKIP CONFLICT is not supported at the transaction level. It can only be specified at the table or
statement level.
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 lock mode
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.
Database Integrity and Locking 29