RE: deadlock
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <barb.baker@xxxxxxxxx>, "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 27 Mar 2013 16:41:01 -0400
1) Oracle will indeed detect deadlocks. It does a remarkably good job of
that.
2) When Oracle detects a deadlock it takes UP TO 3 SECONDS from the stalling
event until it starts the rollback of the step of the transaction holding
the most junior lock request on the most junior wait involved in the
deadlock. Notably this is done by time of lock request that might be quite
fine grained, not the size of the overall transaction step (which could
possibly be enormous.)
3) As Jonathan Lewis documents nicely in a blog post from Feb. 22, that is
all it does. The sense in which Oracle "resolves" the deadlock is only the
sense in which it then allows the previously entangled sessions to continue.
The onus is on the application to handle properly the continuation (or
complete rollback) of the transaction that had one step rolled back.
4) If the UP TO 3 SECONDS bit alone does not give you pause (pun intended),
the notes about potential costs of the transaction step rollback of
something poorly designed should. (I'm not aware of a way to change the 3
SECONDS part, possibly there will be in a future release)
5)Putting even a little bit of effort into designing transaction systems so
they AVOID deadlocks pays huge dividends. At the very least the schema
should have a defined order of updating tables in transactions (and rows
within tables whenever that is practical). Because of the possibility of
overlapping row sets within tables and different rows in different tables
within the transactions done in the same order this is not bulletproof, but
it tends to dramatically reduce the incidence of trivial deadlocks.
6) Stranding a partially complete transaction across an interactive response
is one of the most common ways to generate a lot of deadlocks.
7) Complicated transaction sets may require sentinel objects to introduce
ordered locking and an important class of sentinel objects is business rules
(thank you Toons K [who may be writing about this even as I type...])
8) Summary: Oracle does what it can to allow processing to proceed in the
face of application generated deadlocks, but the applications and/or
assertions must be well behaved to avoid potentially huge wastes of
resources.
9) If your application developer cannot explain the deadlock to you fairly
simply, then said developer is an odds on favorite to not understand the
deadlock. Explaining it to you, step by step, as the multiple threads
involved do things as time proceeds is very likely the best next step in
your pursuit of getting this resolved AND in said developer understanding
the problem as well.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Barbara Baker
Sent: Wednesday, March 27, 2013 2:43 PM
To: ORACLE-L
Subject: deadlock
10.2.0.1.1 database on windows. Response is slow. Application seems to be
a piece of crap.
I set trace on for his session, and he re-enacted the "slowness"
Details of the deadlock are below. After I traced the session and explained
to the developer that his code created a deadlock when it should not have,
here's what I got back from him:
Barb,****
** **
I do not want to explain how deadlock works/happens but that is not the
cause for slow response. If you get a deadlock situation between two
sessions, Oracle automatically terminates one of the sessions immediately
and allows other session to continue. Deadlock does not slow down the
database response but it does terminates one of the sessions involved in the
deadlock.
He further went on to explain that the deadlock would be frequent because
the database is slow. He's trying to talk me into exporting the entire
database and then re-importing it.
Show of hands: who thinks it is good development to lock a table on a
select and then allow Oracle to terminate one of the sessions??
(Extra points to Oracle for putting in the caveat about the deadlock
directly into the trace file!!)
*** SESSION ID:(284.4872) 2013-02-25 09:55:29.715
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
SELECT * FROM LOC WHERE LOC = :B1 FOR UPDATE
----- PL/SQL Call Stack -----
object line object
handle number name
46FD56A8 330 package body MTC.S2P_LOC
46FD56A8 346 package body MTC.S2P_LOC
46FD56A8 296 package body MTC.S2P_LOC
4BA81DC0 513 package body MTC.S2P_ROUTE
4682AC10 840 package body MTC.S2P_TASKTRN_CTL
46C6CBD8 193 MTC.S2T_TASKTRN_IAS
4BBDF384 64 package body MTC.S2P_TASKTRN
4BBDF384 1786 package body MTC.S2P_TASKTRN
4B9903AC 115 package body MTC.S2P_RF_TASK
4B9903AC 2198 package body MTC.S2P_RF_TASK
4B976B1C 1 anonymous block
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 process session holds waits
TX-00040012-00041544 30 *284* X 15 *319*
X
TX-0003001c-00041519 15 *319* X 30 *284*
X
session 284: DID 0001-001E-000E178A session 319: DID 0001-000F-037146D9
session 319: DID 0001-000F-037146D9 session 284: DID 0001-001E-000E178A
Rows waited on:
Session 319: obj - rowid = 0000F34E - AAAPNOAAFAAAAInAAA
(dictionary objn - 62286, file - 5, block - 551, slot - 0)
Session 284: obj - rowid = 0000F34E - AAAPNOAAFAAAAInAAJ
(dictionary objn - 62286, file - 5, block - 551, slot - 9)
Information on the OTHER waiting sessions:
Session 319:
pid=15 serial=64939 audsid=0 user: 57/MTC
O/S info: user: SYSTEM, term: HKWMSN01, ospid: 2476, machine: HKWMSN01
program: ORACLE.EXE (J000)
Current SQL information unavailable
End of information on OTHER waiting sessions.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
