IBM Support

75 ways to demystify DB2: #28: Techtip : How to resolve locking issue caused by indbout transactions in DPF environment

Technical Blog Post


Abstract

75 ways to demystify DB2: #28: Techtip : How to resolve locking issue caused by indbout transactions in DPF environment

Body




	Abstract: 



	In DPF environment, the lock timeout issue could be caused by indoubt transactions. The indoubt transactions cannot be automatically committed or rollback by database manager therefore whenever the application restarts the lock timeout issue occurs.



	 



	Troubleshooting:



	Collect the locking data by:



	db2pd -db <dbname> -alldbp -wlock -app -trans -repeat 5 10



	Note that the above db2pd must have -alldbp option which runs on each logical node on one physical box, if there are multiple physical boxes, then db2pd needs to be run on *EACH* box.



	From db2pd output, the lock holder has no apphandle but only has a transaction id and coordinator edu, appname,authid and appid are not available.



	In below example, we may find a replication process is waiting for row lock held by the indoubt transaction.



	Locks being waited on :



	AppHandl [nod-index] TranHdl  Lockname                                Type Mode Conv Sts CoorEDU AppName  AuthID   AppID              



	             



	0              [000-00000]      2  0010000400000003940800B652 Row  ..X           G            0               n/a           n/a      n/a                



	              



	131681   [002-00609] 127    0010000400000003940800B652 Row  ..X           W           0              replicat user1  N0.db2inst6.1505050440445      



	 



	From db2diag.log, db2dlock edu reports ADM1838W such issue on node 5. 


2015-05-04-13.53.00.865487-240 E414885A584        LEVEL: Warning
PID     : 38077832             TID  : 4371        PROC : db2sysc 5
INSTANCE: user1                NODE : 005         DB   : SAMPLE
EDUID   : 4371                 EDUNAME: db2dlock (SAMPLE) 5
FUNCTION: DB2 UDB, lock manager, sqlpldl, probe:1280
MESSAGE : ADM1838W  An application is waiting for a lock held by an indoubt 
          transaction.  This will cause the application to wait indefinitely.  
          Use the LIST INDOUBT TRANSACTIONS command to investigate and resolve the indoubt transactions   



	 



	How to resolve:



	1. First step is to find on which node it has the indoubt transactions.



	A common mistake is just issuing command LIST INDOUBT TRANSACTIONS in the command line which *ONLY* retrieve the indbout transaction information on *CURRENT* node.  You may see the returning message like below:



	SQL1251W  No data returned for heuristic query.  SQLSTATE=00000



	The correct way is to use db2_all "LIST INDOUBT TRANSACTIONS" to run this command on *ALL* logical nodes.



	Then you may see the below output from particular node:



	 



	 Database Connection Information



	 



	 



	 



	Database server        = DB2/AIX64 9.7.7



	 



	SQL authorization ID   = USER1



	 



	Local database alias   = SAMPLE



	 



	 



	 



	1.   originator: DB2 Enterprise Server Edition



	 



	      appl_id: *N0.db2inst6.1505140745030                                    sequence_no: 0002 status: i



	 



	      timestamp: 05/04/2015 08:39:41 auth_id: CDL02PI



	 



	      log_full: n type: RM



	 



	      xid: 00001D3400000008 0000000000020000 ACE7BAEC



	 



	2. Deal with the indoubt transactions on particular node. 



	First  you need to connect to the particular node which has indoubt transactions.



	

		 

	

		export DB2NODE=<NODE NUMBER. e.g 0,1,2,etc>

	

		 

	

		db2 terminate

	

		 

	

		db2 connect to <dbname>

	

		Use with prompting option in the indoubt transaction command. Then it shows the indoubt transaction info and lets you choose which action needs to be taken:

	

		 

	

		db2 list indoubt transactions with prompting

	

		 

	

		1.   originator: DB2 Enterprise Server Edition

	

		 

	

		      appl_id: *N0.db2inst6.1505140745030                                    sequence_no: 0002 status: i

	

		 

	

		      timestamp: 05/04/2015 08:39:41 auth_id: CDL02PI

	

		 

	

		      log_full: n type: RM

	

		 

	

		      xid: 00001D3400000008 0000000000020000 ACE7BAEC

	

		 

	

		Enter in-doubt transaction command or 'q' to quit.

	

		e.g. 'c 1' heuristically commits transaction 1.

	

		c/r/f/l/q:

	

		You may enter c 1 to commit the transaction number 1

	

		Do you want to heuristically COMMIT this in-doubt transaction? (y/n) y

	

		Enter y to confirm the action.

	

		 

	

		For more information regarding the list indoubt transaction command, Refer to info-center:

	

		 

	

		 

	




	 

          


          
          
                    

          

          [{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
          

                    
                    

  

        
UID

                    
ibm11141084

          



                

     

    

   

  




    

  

  
    
            

          
Page Feedback