SQL timeouts due to automatic revalidation
DB2 V9.7 for Linux/Unix/Windows supports automatic revalidation of objects which is controlled by the database configuration value AUTO_REVAL. If requested by database administrator, this feature for example revalidates a views which was temporary invalid due to a DROP and re-CREATE of a base table or view. But administrators should play attention: Autoamtic revalidation may lead to lock timeouts (SQL0913N) under certain circumstances!
Example: You’ve DROP’ped and re-CREATE’d a table which is part of a view. Without automatic revalidation that view would be not usable and marked as INVALID. Usually you would have to re-CREATE the view. But if you’ve activated automatic revalidation (AUTO_EVAL [IMMEDIATE, , DEFERRED, DEFERRED_FORCE]) DB2 checks whether the invalidated object may be used IMMEDIATE, or when the first process/user try to access the object, so revalidation takes place in mode DEFERRED.
As long as no parallel processing requires an access to that object, auto revalidation works fine. But concurrent processing fails with SQL0913N because exclusive X-locks are hold until the end of the unit-of-work which has initiated the revalidation.
Example:
(1) Unit of work: | (2) Concurrent unit of work: |
DROP and CREATE of a base table, view get invalidated | |
1. SELECT on view | |
AUTO_REVAL starts revalidation | 2. SELECT on view |
data retrieval (S-lock) starts, but view is locked with X-lock | waiting |
still retrieving | waiting |
still retrieving | timeout -> -913, unit of work abends |
finishes work, commits work, view revalidated and active, accessible to concurrent processes. |
Typical DB2 9.7 lock report:
LOCK TIMEOUT REPORT Date: 02/06/2012 Time: 00:46:25 Instance: db2ixp Database: DBPXXXX Database Partition: 0 Lock Information: Lock Name: 0000006900003701736A884043 Lock Type: Internal - Catalog Cache Lock Specifics: Tablespace ID=0, Table ID=105, Row ID=00003701, Additional Info=736A8840 Lock Requestor: System Auth ID: MYUSER Application Handle: [0-21584] Application ID: *LOCAL.db2ixp.120601224529 Application Name: myprog Requesting Agent ID: 29477 Coordinator Agent ID: 29477 Coordinator Partition: 0 Lock timeout Value: 60000 milliseconds Lock mode requested: ..S Application Status: (SQLM_UOWEXEC) Current Operation: (SQLM_PREPARE) Lock Escalation: No Context of Lock Request: Not available. Lock Owner (Representative): System Auth ID: MYUSER Application Handle: [0-21573] Application ID: *LOCAL.db2ixp.120601224414 Application Name: myprog2 Requesting Agent ID: 13687 Coordinator Agent ID: 13687 Coordinator Partition: 0 Lock mode held: ..X List of Active SQL Statements: Entry: #1 Identification: UOW ID (1); Activity ID (1) Package Schema: (NULLID ) Package Name: (SYSSH200NULLID ) Package Version: () Section Entry Number: 4 SQL Type: Dynamic Statement Type: DML, Select (blockable) Effective Isolation: Cursor Stability Statement Unicode Flag: No Statement: SELECT ... FROM MYVIEW1, MYVIEW2 WHERE ... ORDER BY ... List of Inactive SQL Statements from current UOW: Not available
Solution:
In case of concurrent processing you have to control and initiate the revalidation yourself:
- determine invalidates objects, e.g. views:
select * from syscat.invalidobjects where objecttype = 'V';
- initiate revalidation yourself
call sysproc.admin_revalidate_db_objects('VIEW', NULL, NULL);
Comments
Comments are closed.