ArcGIS Server Banner

Dealing with deadlocks in a DB2 database

Dealing with deadlocks in a DB2 database

Release 9.3 E-mail This TopicPrintable VersionGive Us feedback

About dealing with deadlocks

Note:This topic was updated for 9.3.1.

NOTE: Applies to geodatabases created with an ArcGIS Server Enterprise license only

Tuning your database to reduce disk I/O contention will help alleviate deadlocks, but it still may not be an uncommon occurrence that the new_edit_state stored procedure call deadlocks the calling application and blocks all other use of the SDE database.

Imagine a scenario where the stored procedure acquires a large number of row locks on the STATE_LINEAGES table, exceeding the threshold for the maximum number of locks and attempting to escalate to an exclusive table lock. Unfortunately, the calling application's query already holds a shared lock on the STATE_LINEAGES table, thus leading to a deadlock. Large numbers of row locks arise from having a deep state lineage. This, along with having a low setting for lock list size, guarantees that there will be problems. Given how lock escalation is handled, other deadlock scenarios are also possible.

The implication here is that deadlocks may not be an uncommon occurrence at certain sites, depending on one's application and the database configuration. Once again, note that the problem may be aggravated with deep states lineages.

Fortunately, DB2 provides tuning parameters to control the size of the lock list (LOCKLIST), the maximum percentage of locks an application can hold (MAXLOCKS), the amount of time a request will wait for a lock to be acquired (LOCKTIMEOUT), frequency interval for deadlock detection (DLCHKTIME) and deadlock rollback behavior (DB2LOCK_TO_RB).

Briefly, to increase the lock list capacity and lock escalation threshold, modify the LOCKLIST and MAXLOCKS parameters, respectively.

The default value for LOCKLIST and MAXLOCKS in DB2 9 is AUTOMATIC, which enables these parameters for self tuning. This allows DB2's memory tuner to dynamically size the memory resources between different memory consumers. Automatic tuning only occurs if self tuning memory is enabled for the database (SELF_TUNING_MEM=ON).

Additionally, you may be able to improve concurrency through lock avoidance by using DB2's Lock Deferral registry variables DB2_EVALUNCOMMITED, DB2_SKIPDELETED, and DB2_SKIPINSERTED. These registry variables permit scans to unconditionally skip uncommitted deletes and inserts. For more detailed information, refer to http://www.ibm.com/developerworks/db2/library/techarticle/dm-0509schuetz/

By default, a lock time-out will roll back the request transaction. To change this behavior to only roll back the statement making the lock request, modify DB2LOCK_TO_RB with db2set DB2LOCK_TO_RB=STATEMENT. The default behavior should be fine for ArcSDE, though.

See the DB2 documentation or performance tuning guides for detailed information on properly setting these parameters. An overview of using these parameters is given below.

How to alter parameter settings to deal with deadlocks

Viewing lock list settings

To view lock list settings, issue the following command:

db2 get db cfg

Below, is an example of the information returned as a result of issuing this command:

Max storage for lock list (4KB)		(LOCKLIST) = 50
Interval for checking deadlock (ms)	(DLCHKTIME) = 10000
Percent. of lock lists per application	(MAXLOCKS) = 22
Lock time out (sec)			(LOCKTIMEOUT) = -1
Max number of active applications	(MAXAPPLS) = AUTOMATIC

Setting LOCKLIST

For DB2 8:

  1. Estimate the maximum number of active applications (MAXAPPLS, if set).
  2. Estimate the average number of locks per application.
  3. Estimate the lower and upper lock list size:
  4.   (Avg # locks per application * 36 * MAXAPPLS) / 4096
        (Avg # locks per application * 72 * MAXAPPLS) / 4096

    where:

    72 = # bytes of first lock on object

    36 = # bytes of additional locks on object

  5. Set an initial LOCKLIST somewhere between the upper and lower bounds.
  6. For example:

      db2 update db cfg using LOCKLIST 200

For DB2 9, there are new formulas for estimating LOCKLIST size. There are changes for 32-bit and 64-bit platforms. Refer to the LOCKLIST documentation in the DB2 Infocenter for details.

Setting MAXLOCKS

  1. Determine the percentage of lock list any single application can consume before lock escalation occurs. This could be a flat percentage or based on common transaction volumes.
  2. For example, if applications are allowed twice the average number of locks:

     100 * (Avg  # locks per application * 2 * 72 bytes per lock)
      / (LOCKLIST * 4096 bytes)

    Then set MAXLOCKS as shown in the following statement:

     db2 update db cfg using MAXLOCKS 22

  3. Additional tuning of lock list parameters involves the use of the snapshot and event monitors. Look for the following information at the database level:
  4. Use the event monitor for the maximum number of locks held by transaction.

For DB2 9, refer to the MAXLOCKS documentation in the DB2 Infocenter for details at http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0000268.htm.

Diagnosing lock problems

A few useful tools to diagnose lock problems are detailed below.

See Also

  • Recommendations to minimize disk I/O contention