Deadlocks in a DB2 database

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 geodatabase.

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 your application and the database configuration. Once again, the problem may be aggravated with deep states lineages.

Fortunately, IBM 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 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.

By default, a lock time-out will roll back the request transaction. The default behavior should be sufficient for geodatabases in DB2.

See the DB2 documentation in the IBM Knowledge Center for detailed information on properly setting these parameters.

The following section provides some tips for diagnosing deadlocks.

Diagnose lock problems

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

• Find db2 application IDs for ArcGIS processes.

  SELECT  appl_id 
  FROM  TABLE(SNAPSHOT_APPL_INFO('SDE',-1)) 
  AS SNAPSHOT_APPL_INFO 
  WHERE appl_name LIKE 'gsrvr%'
  
  SELECT  appl_id,appl_name 
  FROM  TABLE(SNAPSHOT_APPL_INFO('SDE',-1))
  

• Use snapshots for lock and application information; for example:

  db2 get snapshot for locks on sde > all_locks.txt
  
  db2 get snapshot for locks for application applid
  '*LOCAL.DB2.00AB42215335' > app_locks.txt
  
  db2 get snapshot for application applid '*LOCAL.DB2.00AB42215335' > app_info.txt
  

  A quick search on the snapshot output for items of interest yields the following:

  Application status = Lock-wait
   Locks held by application = 1254
   Number of SQL requests since last commit = 12
   Open local cursors = 1
   Most recent operation = Execute
  
   Object type	= Table
   Tablespace name	= USERSPACE1
   Table schema	= SDE
   Table name	= STATE_LINEAGES
   Mode = X
   Status	 = Converting
   Current mode = IX
   Lock escalation	= YES

• As noted above, deep lineages may be an issue for acquiring a large number of row locks. The following SQL statements can provide a quick check of lineage depths and of max lineage depth:

  SELECT COUNT (*) 
  FROM state_lineages 
  GROUP BY lineage_name
  
  SELECT MAX(a.depth) 
  FROM (SELECT COUNT (*) FROM state_lineages GROUP BY lineage_name) a(depth)