Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

-913/-911 Deadlock during UPDATE statement

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: -913/-911 Deadlock during UPDATE statement
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: 7913
Location: Bellevue, IA

PostPosted: Fri Nov 18, 2016 1:16 am    Post subject:
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    Post subject:
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: 7913
Location: Bellevue, IA

PostPosted: Fri Nov 18, 2016 2:13 am    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Nov 18, 2016 2:32 am    Post subject:
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: 331
Location: USA

PostPosted: Fri Nov 18, 2016 2:42 am    Post subject: Reply to: -913/-911 Deadlock during UPDATE statement
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts COBOL DB2 - CALL statement - high CPU... TS70363 DB2 15 Sun Sep 11, 2016 6:07 am
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm
This topic is locked: you cannot edit posts or make replies. Updating value of key using rewrite s... ParminderKumar COBOL Programming 4 Thu Aug 04, 2016 12:46 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us