RE: deadlock
- From: "Patterson, Joel" <jpatterson@xxxxxxxxxx>
- To: "barb.baker@xxxxxxxxx" <barb.baker@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 27 Mar 2013 15:41:00 -0400
I believe there was a little side note about the idea of oracle cleaning up the
deadlocks in the 'scratchpad' by Jonathan Lewis -- ie not guaranteed in all
situations or something to that affect. I am in the midst of trying to change
my email address on that, and logging out as well. If I find it, I'll post the
link.
Joel Patterson
Database Administrator
904 928-2790
--
Joel Patterson
Sr. Database Administrator | Enterprise Integration
Phone: 904-928-2790 | Fax: 904-733-4916
http://www.entint.com/
http://www.entint.com/
http://www.facebook.com/pages/Enterprise-Integration/212351215444231
http://twitter.com/#!/entint http://www.linkedin.com/company/18276?trk=tyah
http://www.youtube.com/user/ValueofIT
This message (and any associated files) is intended only for the use
of the addressee and may contain information that is confidential,
subject to copyright or constitutes a trade secret. If you are not the
intended recipient, you are hereby notified that any dissemination,
copying or distribution of this message, or files associated with this
message, is strictly prohibited. If you have received this message in
error, please notify us immediately by replying to the message and
deleting it from your computer. Messages sent to and from us may be
monitored. Any views or opinions presented are solely those of the
author and do not necessarily represent those of the company. [v.1.1]
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:
