a question about DEAD LOCK WHEN OPEN CURSOR

haohao · Posted: Thu Jul 10, 2008 11:48 am

HELLO EVERYBODY!
I have a question about OPEN CURSOR. THE Scenario is:

I have an application uses a DB2 table,just name it TB1. there are two online transactions running concurrently named TRA1 AND TRA2.
TRA1 inserts into TB1 and TRA2 read TB1 and update its records. TRA2 USES CURSOR TO get records from TB1(TRA2'S program is BIND with CS isolation level). Sometimes when TRA2 OPEN CURSOR,there is a sql code -913 returned, I am puzzled how can a deadlock occurd in this circumstance.
Can a insert operation also apply X-lock on a table?
Thanks in advance!!

dbzTHEdinosauer · Posted: Thu Jul 10, 2008 12:32 pm

in batch you would not have this problem, because you could commit - thus freeing locks due to insert,update,fetch cursor. Yeah, the fetch cursor with read only, still will issue a lock (a very small one), but one that nevertheless will lock out x-locks from other tasks. In your case, you are locking in both tasks - insert and update.

but, your problem is that you are batch processing inserts and updates in an online environment which sorta precludes the use of intermediate commits.

why is it suggested not to commit in an online (cics) program? Because cics should not be used to perform batch operations.

what you could do is do a start task for each insert or update (make the cursor read only). thus each task would insert (or update) and then terminate, thus making a commit, thus releasing the locks.

I imagine your read only cursor would probably still issue enough locks to impede your other tasks. A way out of that would be drain the cursor into a TDQueue (recovery....) then process the que, starting tasks to perform the updates.

haohao · Posted: Thu Jul 10, 2008 12:58 pm

further information about DECLARE CURSOR:

EXEC SQL
DECLARE D_SELJR01_PK CURSOR WITH HOLD FOR
SELECT
...... some columns
FROM TB1
WHERE
........SOME criterias
END-EXEC

haohao · Posted: Thu Jul 10, 2008 1:20 pm

hi,dbzTHEdinosauer

in my case,the two transactions both are cics online task.
TRA2 fetches and updates TB1'S records one by one and commits one by one.
What I am wondering is the -913 is occured at OPEN CURSOR(with CS isolation level),at that time the tables records are not be FETCHed, could a X-LOCK be issued on the table or index at that time?
the whole process is a little complex, I wish I made me clear about it.

dbzTHEdinosauer · Posted: Thu Jul 10, 2008 3:23 pm

well, you could try WITH UR.

I would investigate the reason code that came with -913.
(you are of course issuing a CALL to DSNTIAR when you have a negative sql code).

The reason code explanation in addition to some analysis concerning what was actually going on in the machine at the time will be necessary for you to solve this problem.

ashimer · Posted: Thu Jul 10, 2008 4:20 pm

haohao · Posted: Fri Jul 11, 2008 11:29 am

HI DICK:
I issued DSNTIAR LIKE THAT (the code is copied from DB2 Developer's Guide):

dick scherrer · Posted: Fri Jul 11, 2008 7:52 pm

Hello,

When posting code, it is best to use the "Code" tag to make your post more readable as well as preserve the alignment (your post has been "Code"ed).

Looking at your code, it appears to be incorrect. Review where you have periods. . . .

dbzTHEdinosauer · Posted: Fri Jul 11, 2008 9:41 pm

what does your data layout look like?

error-message should be an s9(4) comp field containing the total length of the following area.

call using sqlca, db2-message-w40, DB2-LRECL-W40.

haohao · Posted: Fri Jul 11, 2008 9:48 pm

the data layout look like:

dbzTHEdinosauer · Posted: Sat Jul 12, 2008 2:16 pm

dbzTHEdinosauer · Posted: Sat Jul 12, 2008 2:21 pm

and change this:

05 ERROR-MSG-LENGTH PIC S9(9) COMP VALUE +1200

to this:

05 ERROR-MSG-LENGTH PIC S9(4) COMP VALUE +1200