ORA-00060: Deadlock Trace file query found but can we get its parameter values

As this ORA-00060 is quite famous and I too have analyzed it in past and have solved a few of them in past. In my case I have ensured there is no common update from multiple session, still the error was occurring, and for such solutions I have used to increase the initrans and frequent commit in the concurrent transactions have helped me to solve it. Now in another program again I saw the ORA-00060. Then I analyze the .trc file. There it is showing the merge query running from two session which is causing deadlock. This query has bind parameters. I wanted to know the values of this bind parameter. Because if by any bug if this binding parameter is same from the two session then it will be obvious that the two sessions are updating the same records, and eventually causing the deadlock. Where I am struggling is that I am not able to see any way to get the value of these bind parameter in the .trc file. The call stack trace in the .trc file does have some blocks but very difficult to figure out where and what is the value of this parameter when the deadlock graph was created. Does anyone know whether it is possible to get the bind parameter values from .trc file for the queries causing the deadlock? I have started putting the log in the code so that next time when it occurs I will get that from normal logs.

Deadlock graph: ------------Blocker(s)----------- ------------Waiter(s)------------

Resource Name process session holds waits serial process session holds waits serial TX-0012000B-0004A991-00000000-00000000 193 203 X 7643 195 583 S 11914 TX-000F000E-000871FC-00000000-00000000 195 583 X 11914 193 203 S 7643

The query (table/column names I have changed) is:

----- Information for waiting sessions -----
Session 203:
sid: 203 ser: 7643 audsid: 38682755 user: 151/SCHMEA1
...
pid: 193 O/S info: user: grid, term: UNKNOWN, ospid: 11010512
image: oracle@hostname
client details:
...
application name: JDBC Thin Client, hash value=2546894660
current SQL:
MERGE INTO TABLE_A TA USING ZPD_TABL2 ZPD ON (TA.COY=ZPD.COY AND TA.COL2=ZPD.COL2 AND TA.TRXNO=ZPD.TRXNO AND (TA.COL3 = ' ' OR TA.COL3 IS NULL OR TA.COL3 = 'I') AND ZPD.THREADNO=:B1 ) WHEN MATCHED THEN UPDATE SET TA.COL3 = 'Y', TA.USRPRF=:B3 , TA.JOBNM=:B2 , TA.DATIME=LOCALTIMESTAMP

Session 583:
sid: 583 ser: 11914 audsid: 38682758 user: 151/VM1DTA
...
pid: 195 O/S info: user: grid, term: UNKNOWN, ospid: 58064926
image: oracle@hostname
client details:
..
application name: JDBC Thin Client, hash value=2546894660
current SQL:
MERGE INTO TABLE_A TA USING ZPD_TABL2 ZPD ON (TA.COY=ZPD.COY AND TA.COL2=ZPD.COL2 AND TA.TRXNO=ZPD.TRXNO AND (TA.COL3 = ' ' OR TA.COL3 IS NULL OR TA.COL3 = 'I') AND ZPD.THREADNO=:B1 ) WHEN MATCHED THEN UPDATE SET TA.COL3 = 'Y', TA.USRPRF=:B3 , TA.JOBNM=:B2 , TA.DATIME=LOCALTIMESTAMP

----- End of information for waiting sessions -----

So if I get the value of THREADNO =:B1 (this bind parameter) value from .trc file then I can confirm whether these two merge from two sessions are updating the same records or not.

Solution 1:

One possible suspect constalation in your use case would be if the threadno is not uniquely mapped to the join keys used in the MERGE.

You may quickly check it with the query below - it should not return any row. If yes, you have a postential problem described later.

select COY, COL2, TRXNO, min(THREADNO), max(THREADNO)
from zpd_tabl2 zpd 
group by COY, COL2, TRXNO
having count(distinct THREADNO) > 1;

Bind Variables Used in Dealock Statements

Well, the deadlock trace file contains some information

Peeked Binds
============
  Bind variable information
    position=1
    datatype(code)=2
    datatype(string)=NUMBER
    precision=0
    scale=0
    max length=22
    value=1

But this will not help you as it is the peeked value used while the statement was parsed.

Rows waited on

More usefull information is the rowid of the rows caused the problem

Rows waited on:
  Session 138: obj - rowid = 00012563 - AAASVjAARAAAACbAAB
  (dictionary objn - 75107, file - 17, block - 155, slot - 1)
  Session 12: obj - rowid = 00012563 - AAASVjAARAAAACbAAA
  (dictionary objn - 75107, file - 17, block - 155, slot - 0)

The object_id (here 75107) should point to your TABLE_A and you can check the threadnos that try to modify the problematic rows with the following query.

select a.rowid, a.COY, a.COL2, a.TRXNO , zpd.threadno
from table_a a 
join zpd_tabl2 zpd
ON ( a.coy = zpd.coy
AND a.col2 = zpd.col2
AND a.trxno = zpd.trxno)
where a.rowid in ( 'AAASVjAARAAAACbAAB','AAASVjAARAAAACbAAA');

If you see at least four rows, that you suffer the threadnoproblem stated in the beginning.

Reproducible Example

Note, I can't claim that this is exact your situation, but this is the simplest way how to get a deadlock you observed.

create table table_a as
select rownum coy, rownum col2, rownum trxno, ' ' col3, localtimestamp datime 
from dual
connect by level <= 2;

create table zpd_tabl2 as
select rownum coy, rownum col2, rownum trxno, 1 threadno
from dual
union all
select  rownum coy,  rownum col2,  rownum trxno, 2 threadno
from dual
union all
select 1+rownum coy, 1+rownum col2, 1+rownum trxno, 3 threadno
from dual
union all
select  1+rownum coy,  1+rownum col2,  1+rownum trxno, 4 threadno
from dual
;

• in Session 1 run the MERGE with pareter 1

.

MERGE  INTO table_a ta
USING zpd_tabl2 zpd ON ( ta.coy = zpd.coy
                         AND ta.col2 = zpd.col2
                         AND ta.trxno = zpd.trxno
                         AND ( ta.col3 = ' '
                               OR ta.col3 IS NULL
                               OR ta.col3 = 'I' )
                         AND zpd.threadno = :b1 )
WHEN MATCHED THEN UPDATE
SET ta.col3 = 'Y',
    ta.datime = localtimestamp;

 1 row merged 

• in Session 2 run the MERGE with pareter 4

1 row merged

• in Session 1 run the MERGE with pareter 3

waiting

• in Session 2 run the MERGE with pareter 1

waiting

• in Session 1

ORA-00060: deadlock detected while waiting for resource