Is there any way to undo the roll back associated with -911?

Lijo · New User Joined: 24 Aug 2008 Posts: 41 Location: Cochin

Hi,

Scenario is as follows:
program A calls program B and then program C. Program B inserts records into table X (but no explicit commit in program B). After that program A calls Program C, which updates the record inserted by program B. Program C will find the record during its first try for update as its uses 'with UR'. During its first try program C encounters a -911 due to timeout. program C has a Retry logic, which handles -911 and tries for 3 more times. During its 2nd try, since first -911 had rolled back the insert made by program B, program C couldn't find any records for update and abends with no row found.

Program A -> Program B ( inserts into table X) -> program C (update table X where it gets -911 and because of retry logic it tries again (after sometime) and gets no row found as -911 had rolled back current unit of work)

Is there any way to undo the implicit rollback associated with -911 by changing isolation levels or something???

ashimer · Posted: Tue Apr 21, 2009 2:41 pm

Instead of undoing the rollback why not try and fix the occurence of deadlock itself ? is program c going for a select for existence check and then updating ? check how locksize is defined on the table ...

Lijo · New User Joined: 24 Aug 2008 Posts: 41 Location: Cochin

-911 can be due to deadlock or timeout. Issue described above was due to timeout. Program C is not doing an existence check because, it called only when the record is present.

ashimer · Posted: Tue Apr 21, 2009 3:35 pm

timeout happened as C was waiting for a lock on the table or row .. . check the locksize defined on your table ... else increase the maxlocks parameter or timout parm ... contact ur dba ..

dbzTHEdinosauer · Posted: Tue Apr 21, 2009 3:39 pm

Lijo · New User Joined: 24 Aug 2008 Posts: 41 Location: Cochin

All are batch programs. Jobs are dataset triggered and it is possible that multiple jobs can execute in parallel, which will need the same resource (like table X).
In the above mentioned scenario, timeout was due to the lock on table X by another program. Why I said it is just timeout not deadlock, because in our 2nd try of program C, it was able to get that resource and go ahead with the 'update' operation.

As Ashimer said, I've already posted this to DBA, but was searching if I could get something out of here which could be of their help.

Thanks for the responses guys...

dbzTHEdinosauer · Posted: Tue Apr 21, 2009 4:30 pm

Lijo · New User Joined: 24 Aug 2008 Posts: 41 Location: Cochin

I've not contradicted anything. In 2nd statement I said, it was able to get the resource and hence it is going ahead with update operation. While trying to update it returns no rows found error. I didn't say that update happened successfully in my 2nd statement...