Commit and Locking Parameters
The MINCOMMIT parameter controls how many log records will
be written to disk at once. The parameter has values from between 1 and 25 inclusive.
>>db2 get dbm cfg for ARTIST | grep MINCOMMIT
Group commit count (MINCOMMIT) = 1
Default value MINCOMMIT=1: after every commit statement,
log records are written from memory to the disk.
MINCOMMIT =2: a second commit will write the previous and current transactions
to the transaction log entries, if a commit timeout has not already triggered
the commit action. Commit is generated after following statements: ALTER, COMMENT ON, CREATE, DELETE, DROP, GRANT, INSERT, LOCK TABLE, REVOKE,
SET INTEGRITY, SET transition-variable, and UPDATE. On commit, all locks
acquired by the unit of work are released, except necessary locks for open
cursors that are declared WITH HOLD.
registry parameter DB2_RR_TO_RS is used to:
- set a
default isolation level for user tables to RS isolation level
locking the next key for inserted or changed rows
deadlocks when using reverse scan indexes (ALLOW REVERSE SCANS)
should set this parameter when expecting performance improvement.
>> db2set -all | grep DB2_RR_TO_RS
Activating DB2_RR_TO_RS parameter:
>>db2set -g DB2_RR_TO_RS=YES
Other general locking and commit parameters:
>> db2 get db cfg for ARTIST | grep LOCK
Max storage for lock list (4KB) (LOCKLIST) = 50
Percent. of lock lists per application (MAXLOCKS) = 10
Lock timeout (sec) (LOCKTIMEOUT) = -1
Interval for checking deadlock (ms) (DLCHKTIME) = 10000
lock list holds the controlling information for all locked objects for
applications. Storage is allocated for the database lock list
(tables and rows) in sizes of 64 bytes for locked objects and 32 bytes for objects
already locked. If the lock list is too small, lock
escalation can result. When an application reaches the
maximum number of locks allowed and there are no more locks to escalate, it
will then use space in the lock list allocated for other applications. When the
entire lock list is full, an error occurs.
MAXLOCKS - Maxlocks is a threshold in the percentage
of the lock list occupied by only one application, after lock escalation is performed
by the database manager.
LOCKTIMEOUT - Locktimeout is a time in seconds that
the application can wait to obtain a lock on an object.
- (0) - the application request immediately
locks or raises an error -911
- (-1) - the application will wait for a
lock until it gets a lock or is discovered by deadlock detection, which means lock
timeout detection is turned off.
- The time in ms for checking a deadlock. Control over the
deadlock-checking interval saves some CPU time, but could lead to longer
The best way to control and to check status of the locking
resources and their usage is over the performance monitor where we can find
following, similar lock information:
Lock list memory in use (Bytes) = 2952 bytes
Lock Escalations = 0
Exclusive Lock Escalations = 0
Locks Held Currently = 0
Current Applications Waiting on Locks = 0
Lock waits = 0
Time database waited on locks (ms) = 0
Average wait time per lock = 0.00 ms
After observing these numbers we can definitely point to
the potential problem.
data modification action (INSERT, UPDATE, and DELETE statements) without commit
will cause DB2 to hold and maintain locks on the data. All other applications
must wait until locks are released to continue their work. On commit, all
holding locks from applications are released and are enabled free access to
application code will ensure that the application process will hold locks for the shortest time possible.
behavior can be controlled on the database or at the application level. To show
diversity and importance of the commit function here are a few situations that
interact with a commit function:
- on log
files switch, commit is done in the background by the DB2 engine.
- every one second, commit timeout forces writing log records to the disk.
database track transaction situation with database settings "DATABASE IS
CONSISTENT". This setting has a value of NO whenever any update is
performed to a database. When all transactions are committed, the database will
return to a consistent state "YES."
when you issue commit all open cursors for executing application are closed
import, commit is controlled by the COMMITCOUNT parameter for every n records
and closes filled log files.
in JDBC call after insert is managed with AutoCommit flag (managed by the
Temporary tables content being preserved with "on commit preserve rows"
and cleared whenever a session is closed