Neoview SQL Reference Manual (R2.5)

• “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.
Lock Holder
Only the lock holder can release a lock. A transaction releases the locks it holds at the end of the
transaction in either of these cases:
• Locks on data read using SERIALIZABLE access
• Locks on rows updated
Transaction Management
A transaction (a set of database changes that must be completed as a group) is the basic recoverable
unit in case of a failure or transaction interruption. Transactions are controlled through client
tools that interact with the database using ODBC or JDBC. The typical order of events is:
Transaction Management 27