1. Problem description
PL/SQL package receive -727 SQL error when "prepAnonBlock" EDU
triggers a lock conflict and deadlock with the original EDU.
For example,
(1) There is two PL/SQL packages A and B.
(2) Package B refers to package A in the definition.
(3) Package A was re-compiled recently whereas package B is not.
(4) An application with auto-commit disabled calls package A and
then package B.
(5) After finish calling package A, the application encounters
the lock conflict and deadlock with "prepAnonBlock" EDU when
calling package B.
(6) Eventually the application terminates with SQL0727N error.
2. Diagnostic information
(A) db2diag.log has the following message pattern.
EDUID : XXX EDUNAME: db2agent (DBNAME)
FUNCTION: DB2 UDB, SW- common services, sqlnn_regen_function,
probe:1
DATA #1 : String, 0 bytes
Object not dumped: Address: 0x0000000000000000 Size: 0 Reason:
Address is NULL
DATA #2 : String, 0 bytes
Object not dumped: Address: 0x0000000000000000 Size: 0 Reason:
Address is NULL
...
EDUID : 3343 EDUNAME: db2dlock (DBNAME)
FUNCTION: DB2 UDB, base sys utilities,
sqleCheckForceSuspendedConn, probe:280
DATA #1 : String, 170 bytes
Anonymous Block deadlock detected!
Waiting application with appHandle 0-XXX will be rolled back.
Suspended application with appHandle 0-XXX has deadlock state
0x00000010.
...
EDUID : XXX EDUNAME: db2agent (DBNAME)
FUNCTION: DB2 UDB, catalog services, sqlrlCatalogScan::update,
probe:60
RETCODE : ZRC=0x80040003=-2147221501=SQLD_INTRP "USER INTERRUPT
DETECTED"
DIA8003C The interrupt has been received.
...
EDUID : XXX EDUNAME: db2agent (DBNAME)
FUNCTION: DB2 UDB, access plan manager,
sqlraProcessPrepAnonBlock, probe:314
MESSAGE : ZRC=0x80100003=-2146435069=SQLP_LINT "Interrupt from
application"
DIA8003C The interrupt has been received.
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCAL sqlcabc: 136 sqlcode: -727 sqlerrml: 13
(B) Lock information
- Lock waiter is the "prepAnonBlock" EDU requesting 'X' lock.
Lock holder is the original application and in the
"Anonymous Block Wait" status. |
