Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, TM.

Asked: November 30, 2020 - 7:34 am UTC

Last updated: December 04, 2020 - 3:38 am UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

Hi,
we have 2 processes: the FIRST is our in our domain, the SECOND is outer process and we can't control it.
We are too late for FIRST process to maintain and change the logic. So the problem is when these 2 processes colide together - in that case we get deadlock and wait until deadlock is resolved or when one process give up. But while we are waiting for resolving a deadlock, we wait too much and all system (that one where is FIRST process) stucks.
Can we somehow correct or change some parameter in order to "shorten" deadlock resolving? We want that deadlock in resolved after 180 seconds.
What can we do ?

Kind regards,
Darko

and Connor said...

Deadlocks in the same database are typically detected within a few seconds.

Deadlocks across database links are controlled by the distributed_lock_timeout parameter because we can't see both sides of the transaction.

So are you sure this is a *deadlock* or just waiting for a lock?

We need some detail here. Also take a look at this video to see if you're impacted by this mechanism as well

<youtube> https://www.youtube.com/watch?v=wHcoOfA8fRM <youtube>

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Deadlock in deed

Darko, December 02, 2020 - 9:41 pm UTC

Hi, we have really suffer from deadlocks, here are some lines.
Can I send you whole trace file, just to have a look and maybe some idea?
---------------
Trace file D:\ORACLE122\diag\rdbms\oracle12\oracle12\trace\oracle12_ora_7896.trc
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
Build label: RDBMS_12.2.0.1.0_WINDOWS.X64_170210.4
Windows NT Version V6.2
ORACLE_HOME = D:\oracle122\product\12.2.0\dbhome_1
Node name : ORACLE
CPU : 4 - type 8664, 4 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:5380M/16381M, Ph+PgF:7583
Instance name: oracle12
Redo thread mounted by this instance: 1
Oracle process number: 63
Windows thread id: 7896, image: ORACLE.EXE (SHAD)


*** 2020-11-09T08:12:45.547066+01:00
*** SESSION ID:(426.59931) 2020-11-09T08:12:45.547066+01:00
*** CLIENT ID:() 2020-11-09T08:12:45.547066+01:00
*** SERVICE NAME:(oracle12.etol.si) 2020-11-09T08:12:45.547066+01:00
*** MODULE NAME:(visual.exe) 2020-11-09T08:12:45.547066+01:00
*** ACTION NAME:() 2020-11-09T08:12:45.547066+01:00
*** CLIENT DRIVER:(ODBCCLNT : 12.2.0.1) 2020-11-09T08:12:45.547066+01:00

2020-11-09 08:12:45.530*:ksq.c@12954:ksqdld_hdr_dump():
DEADLOCK DETECTED ( ORA-00060 )
See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
------------Blocker(s)----------- ------------Waiter(s)------------
Resource Name process session holds waits serial process session holds waits serial
TX-00070001-0006A692-00000000-00000000 63 426 X 59931 57 151 X 6954
TX-0008001C-0006327B-00000000-00000000 57 151 X 6954 63 426 X 59931


*** 2020-11-09T08:12:45.547066+01:00
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0)
----- Error Stack Dump -----
----- Current SQL Statement for this session (sql_id=1jh51q7a8an97) -----
UPDATE AIDAOrderRDB SET createdTimestamp= TO_DATE( '2020-11-09 08:12:42', 'YYYY-MM-DD HH24:MI:SS') WHERE idData = '07201255775'

----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+89 CALL??? ksedst1() 000000000
Connor McDonald
December 04, 2020 - 3:38 am UTC

Unfortunately we're not allowed to accept trace files via email, because its a violation of customer data policies. You'd need to open a call with Support.

But deadlocks dont take 180seconds to detect.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database