IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

-913/-911 Deadlock during UPDATE statement


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
NoSleep319

New User


Joined: 11 Apr 2016
Posts: 6
Location: United States

PostPosted: Fri Nov 18, 2016 12:37 am
Reply with quote

Hello mainframe guru's! I'm looking for some deadlock insight.

I have a COBOL module that does a SELECT on a table looking for records for that system and that have 'PENDING' in the status column, generates some output if found, updates the status column to 'PROCESSED', then moves onto the next record. This job runs on 27 different partitions/systems and is scheduled every 15 minutes. The table resides on one system and is connected to remotely by the other systems. I'm somewhat new to understanding deadlocks, but I do understand that one program/system is trying to perform an UPDATE while, at the exact same time, another program/system is trying to perform a SELECT. I think I may need to reconstruct my SELECT statement, because there is a column specific to the system, and the SELECT statement queries it's own system records only.

This is my SELECT cursor:
Code:

SELECT UPPER(RACF_ID)                 
     , UPPER(ACTN_CD)                 
     , UPPER(STTS_CD)                 
     , UPPER(ROLE_NM)                 
     , RACF_PRCS_INFO_ID             
  FROM RACF_PRCS_INFO                 
 WHERE RACF_ENV_CD = :WS-RACF-ENV-CD 
   AND STTS_CD = :C-STTS-CD-PEND     
 ORDER BY RACF_ID ASC, RQST_TSTMP DESC


This is my Update statement:
Code:

UPDATE RACF_PRCS_INFO                           
   SET STTS_CD     = 'PROCESSED',               
       CMPLT_TSTMP = CURRENT TIMESTAMP           
 WHERE RACF_PRCS_INFO_ID  = :WS-RACF-PRCS-INFO-ID


Is there a way to query it while it's being updated and not caring that it's updated?
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Fri Nov 18, 2016 1:16 am
Reply with quote

Quote:
I do understand that one program/system is trying to perform an UPDATE while, at the exact same time, another program/system is trying to perform a SELECT.
Not precisely -- a deadlock occurs when two applications (programs) are each locking data that is needed by the other. Unless your SELECT is locking data, then it CANNOT be involved in a deadlock. It is far more likely that multiple updates are deadlocking.

Furthermore, the messages manual for -911 and -913 codes mentions deadlock OR timeout -- why have you ruled out timeout? Have you worked with your site support group to have the DBA look into what is going on?
Back to top
View user's profile Send private message
NoSleep319

New User


Joined: 11 Apr 2016
Posts: 6
Location: United States

PostPosted: Fri Nov 18, 2016 1:41 am
Reply with quote

You're right, it could be a timeout as well...I have reached out to my DBA, but I have yet to hear back from him. I thought I would reach out here while I was waiting.

So help me understand the process. When a program locks data, it's locking that record/row and not the entire table, correct? If system A is only querying ENV_CD = A and system B is only querying ENV_CD = B, how does cause deadlocking or a timeout?
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Fri Nov 18, 2016 2:13 am
Reply with quote

DB2 has record locks, page locks, table space locks, table locks, partition locks. So the lock may -- or may not -- be for the row.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Fri Nov 18, 2016 2:32 am
Reply with quote

Quote:
Is there a way to query it while it's being updated and not caring that it's updated?
WITH UR in the Cursor should solve the problem.
If using UR, your program can be reading data that is in the process of being changed. It provides a high level of concurrency.

Another approach would be dump all the valid rows from the cursor to the GTT and in the update have a inner join with this GTT on key column(RACF_PRCS_INFO_ID ), So in one update query you will have all the work done instead of a traditional looping logic.

Please move this to DB2 section of the forum.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Fri Nov 18, 2016 2:42 am
Reply with quote

Quote:
SQLERRD(3) also contains the reason-code which indicates whether a deadlock or timeout occurred. The most common reason codes are:

•00C90088 - deadlock
•00C9008E - timeout

Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts JOIN STATEMENT PERFORMANCE. DFSORT/ICETOOL 12
No new posts Read a flat file and update DB2 table JCL & VSAM 2
No new posts how to update an ISR appl var from an... TSO/ISPF 8
No new posts Relate COBOL statements to EGL statement All Other Mainframe Topics 0
No new posts process statement for SUPREC, CMPCOLM... TSO/ISPF 4
Search our Forums:

Back to Top