Every day few transactions error return code is -911

Dev's · New User Joined: 12 Mar 2007 Posts: 14 Location: Hyderabad

Hi,

As part of my analysis In production. While processing all transactions if any transaction is not processing properly then program update corresponding return code in error processing table.

My analysis is, Every day few transactions error return code is -911.
These records will processed successful next day without any error. Why it is happenned in database updating.

Let's share your ideas and help me.

Dev's · New User Joined: 12 Mar 2007 Posts: 14 Location: Hyderabad

Hi,

-911 issue while updating tables for few records, next day these records are processing fine...???

As part of my analysis In production. While processing all transactions if any transaction is not processing properly then programs (many) update corresponding return code in error processing table.

My analysis is, Every day few transactions error return code is -911.
These records will processed successful next day without any error. Why it is happenned in database updating.

Let's share your ideas and help me.

chtrisa · New User Joined: 19 Dec 2006 Posts: 12 Location: chennai

hi Dev

In general a -911 means that a deadlock or timeout has occurred while the statement was attempting to obtain a lock on the object that is necessary for an insert to occur. That means that some other process has a lock on the object you need, and DB2 waiting for the lock to be released (wait time determined by a DB2 pram that can be set) and finally gave up waiting and terminated your transaction.

It is possible that another process has a row lock on the table or index
that your need, or the other process has a table lock (either taken
explicitly or escalated by DB2 from a row lock). Try to find out what other
locks are being held on that object.

bansal · New User Joined: 03 Jan 2007 Posts: 27 Location: Hyderabad

HI,

Why are you posting your post twice. It is confusing.

-911 enconters when their is a deadlock for the particular stuff which your job wants at that point of time or timeout occurs which has been specified for your job to access that particular stuff.

Next day that job runs successfully bcoz that deadlock is not their and job can access the required stuff.

dick scherrer · Posted: Mon Jun 11, 2007 8:09 pm

Hello,

If you examine your application code you will find that:
1. there are programs that need to update both tableA and tableB.
2. some of these programs lock items in tableA before locking items in table B.
3. other of these programs lock items in tableB before locking items in tableA.
4 when these programs need to lock the same items at the same time, the -911 occurs.

Re-running "corrects" the problem because of timing - by the time you re-run, the problem-causing locks are gone. To permanently eliminate these -911's, you would need to restructure the code to always lock tables in the same order. Dependng on your system, this may or may not be feasible now - it is something that should be considered when the database/transactions are being designed, not when it is already in production and there are problems.

khamarutheen · Active Member Joined: 23 Aug 2005 Posts: 677 Location: NJ

Hi,

I faced the same problem in my shop long back ... It's a deadlock or timeout ...In my case i talked with DBA peoples and asked them to list out the users who use the table and i found there were 5 users who get's some updation with the same table where my job works.

So restructuring with locks now was not possible as my high off says .. so i just mailed to that 5 users about the problem, since upon completion of my job there are 50 more jobs needs to be executed .. So everday i use to mail the users to stop accessing the table and upon completion of the job i use to send a mail again to them to start using it...

I dont know how far this works in your shop ... But it would be better if you implement some locking tech as dick says or you can talk with DBA and do the next level ...

TG Murphy · Posted: Tue Jun 12, 2007 7:39 pm

Check the DB2 logs. They will tell you who suffered the deadlock, who caused it, and which tables caused it.

Santoshdorge · New User Joined: 27 Jun 2006 Posts: 48 Location: Pune

Hi
I would like to sugget the use of WITH UR clause in queries used on the particular tables.