|
View previous topic :: View next topic
|
| Author |
Message |
NoSleep319
New User
Joined: 11 Apr 2016
Posts: 6
Location: United States
|
|
|
|
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 |
|
 |
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8700
Location: Dubuque, Iowa, USA
|
|
|
|
| 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 |
|
|
NoSleep319
New User
Joined: 11 Apr 2016
Posts: 6
Location: United States
|
|
|
|
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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8700
Location: Dubuque, Iowa, USA
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| 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 |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
| 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 |
|
|
|
|
