Topic
  • 1 reply
  • Latest Post - ‏2014-08-18T07:30:58Z by mor
AngocA
AngocA
25 Posts

Pinned topic Autonomous transaction error

‏2014-08-17T13:04:53Z |

Hi,

I am developing a framework that uses Autonomous Transactions, and I found an error when invoking an autonomous procedure. I have tested without autonomous, and the procedure executes normally without throwing any exception.

When it happens, the transaction takes like 30 seconds to go out from the autonomous transaction error, and then continue.

The db2diag.log file has the following:


2014-08-17-14.57.17.610497+120 I5841671E573          LEVEL: Severe
PID     : 2573                 TID : 139715235800832 PROC : db2sysc
INSTANCE: db2inst1             NODE : 000            DB   : LOG4DB2
HOSTNAME: angoca-VirtualBox
EDUID   : 6753                 EDUNAME: db2dlock (LOG4DB2)
FUNCTION: DB2 UDB, base sys utilities, sqleCheckForceSuspendedConn, probe:260
DATA #1 : String, 176 bytes
Autonomous Routine deadlock detected!
Waiting application with appHandle 0-4107 will be rolled back.
Suspended application with appHandle 0-24539 has deadlock state 0x00000008.

2014-08-17-14.57.17.614557+120 I5842245E982          LEVEL: Error
PID     : 2573                 TID : 139715340658432 PROC : db2sysc
INSTANCE: db2inst1             NODE : 000            DB   : LOG4DB2
APPHDL  : 0-4107               APPID: *LOCAL.DB2.140817125648
AUTHID  : ANGOCA               HOSTNAME: angoca-VirtualBox
EDUID   : 10187                EDUNAME: db2agent (LOG4DB2)
FUNCTION: DB2 UDB, relation data serv, sqlrr_rds_common_post, probe:1700
MESSAGE : Severe TERMINATE err at nest lvl 3, in SQL from rtn 
          LOG4DB2_CONF_LOGGERS_DELETE.SETUP:
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -1224   sqlerrml: 0
 sqlerrmc: 
 sqlerrp : SQLRRRCP
 sqlerrd : (1) 0x80100003      (2) 0x00000003      (3) 0x00000000
           (4) 0x00000000      (5) 0xFFFFFC8F      (6) 0x00000000
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)    
           (7)      (8)      (9)      (10)        (11)     
 sqlstate: 55032

2014-08-17-14.57.17.615369+120 I5843228E556          LEVEL: Error
PID     : 2573                 TID : 139715185469184 PROC : db2sysc
INSTANCE: db2inst1             NODE : 000            DB   : LOG4DB2
APPHDL  : 0-24539              APPID: *LOCAL.db2inst1.140815141218
AUTHID  : ANGOCA               HOSTNAME: angoca-VirtualBox
EDUID   : 6787                 EDUNAME: db2agent (LOG4DB2)
FUNCTION: DB2 UDB, routine_infrastructure, sqlerAutonomousInvoker, probe:312
MESSAGE : ZRC=0x801A006D=-2145779603=SQLZ_CA_BUILT
          "SQLCA has already been built"

This is the only transaction I am executing, because I am working on my own computer. The database is not shared with any service, just me. Thus, the message: "Autonomous Routine deadlock detected" is weird, because in order to have a deadlock, there should be at least two transactions, and two resources to reserve.

Also, the sqlcode 1224 is not clear, because the database continue to work without any problem.

I think this is an internal Autonomous Transaction error, but I cannot open a PMR because I am developing the framework for myself (https://github.com/angoca/db2unit)

Do you have any ideas what is happening?

I am using db2 10.1 FP 2 Express-C on Ubuntu.

Updated on 2014-08-17T14:03:27Z at 2014-08-17T14:03:27Z by AngocA
  • mor
    mor
    507 Posts

    Re: Autonomous transaction error

    ‏2014-08-18T07:30:58Z  

    Seems straightforward - your autonomous procedure got a deadlock and was chosen as the victim, so db2 rolled back that transaction.

    You have deadlocked yourself - this is possible if the transaction that calls your autonomous transaction has locks required by the sproc. Be aware that when you use an autonomous sproc it has a separate transaction scope from its caller.

    See the on-line documentation for help with diagnosing deadlocks:

    http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.admin.trb.doc/doc/t0055236.html

    You can also use db2 event monitor for locking,  and db2pd.

    PMRs are available if you are entitled to a support contract, which is not part of the community-edition.