Troubleshooting
Problem
Deadlocks might occur with following conditions:
- auto_reval database configuration parameter is set to DEFERRED.
- Multiple SQLs are issued within the same transaction (no commit or rollback in between) and two or more SQLs depend on the same object (same table for our case here).
- On other connection, simultaneously, alter depended object (for example, alter table command).
- If 2 and 3 happen during the same time under condition in 1, Db2 might fail into deadlock situation.
Deadlock in this case can be either of the following types.
- Plan lock vs Plan lock
- Plan lock vs Global Package lock
- Plan lock vs Plan lock
- Plan lock vs Global Package lock
Cause
When DB2 attempts processing SQL, it needs to acquire that SQL's plan lock. And if Db2 found that the execution plan for that SQL was invalidated and auto_reval is set to DEFERRED, Db2 needs to acquire both plan lock and global plan lock of that SQL, then recompile that SQL, creating a new execution plan.
Whereas when "alter table" attempts invalidating the SQL's execution plan, "alter table" will need to acquire both plan lock and global package lock of all the SQLs depending on the table.
Problem is, the order of SQLs being recompiled and order of SQL's execution plan being invalidated might not be the same order. Hence deadlock can occur.
Resolving The Problem
Following actions might be considered as work-around, up to business' requirement for each individual system.
- set auto_reval database configuration parameter to DISABLED.
- alter table (or depended object) only during maintenance window, or make sure no dependent SQLs are issued during the time of altering.
- set auto_reval database configuration parameter to DISABLED.
- alter table (or depended object) only during maintenance window, or make sure no dependent SQLs are issued during the time of altering.
Document Location
Worldwide
[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PmMAAU","label":"Performance-\u003ELocks"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
06 March 2023
UID
ibm16959557