Protecting DB2 against Non Concurrent Application Code - Page 2

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.

DB2 registry parameter DB2_RR_TO_RS is used to:

• set a default isolation level for user tables to RS isolation level
• reduce locking the next key for inserted or changed rows
• reduce deadlocks when using reverse scan indexes (ALLOW REVERSE SCANS)
  You should set this parameter when expecting performance improvement.

	>> db2set -all | grep DB2_RR_TO_RS
	 [i] DB2_RR_TO_RS=NO

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

LOCKLIST- The 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.

DLCHKTIME - The time in ms for checking a deadlock. Control over the deadlock-checking interval saves some CPU time, but could lead to longer deadlocks.

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.

Every 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 data. Well-written application code will ensure that the application process will hold locks for the shortest time possible.

Commit 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
• during import, commit is controlled by the COMMITCOUNT parameter for every n records and closes filled log files.
• commit in JDBC call after insert is managed with AutoCommit flag (managed by the Connection.setAutoCommit method)
• Temporary tables content being preserved with "on commit preserve rows" and cleared whenever a session is closed