Re: deadlock
From: Lyall Barbour <lyallbarbour_at_sanfranmail.com>
Date: Wed, 27 Mar 2013 15:06:56 -0400
Message-ID: <20130327190657.192620_at_gmx.com>
So, this developer programmed this part of the application taking into account that he wanted to a deadlock to happened? This is a "feature" of his code? Interesting.
Are deadlocks happening frequently? *Are* they the reason for the database slowness? Application locks in general are a big reason why our users *think* our application is slow. They don't know that the sales person didn't save the sales order they are trying to make a work order for and then change the sales order. Our users only see Window's spinning circle. Deadlocks don't get cleaned up immediately like your developer says. They get cleaned up fast, but not instantly. To answer your question, I do not think it's good programming practices to do what they did. And to expand on that answer, i think your developers a bit slow if he thinks any DBA won't make him fix his "feature" immediately. Good luck with that person...
Lyall Barbour
----- Original Message -----
From: Barbara Baker
Sent: 03/27/13 02: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 t hen 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 followi ng 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. -- http://www.freelists.org/webpage/ora cle-l
Date: Wed, 27 Mar 2013 15:06:56 -0400
Message-ID: <20130327190657.192620_at_gmx.com>
So, this developer programmed this part of the application taking into account that he wanted to a deadlock to happened? This is a "feature" of his code? Interesting.
Are deadlocks happening frequently? *Are* they the reason for the database slowness? Application locks in general are a big reason why our users *think* our application is slow. They don't know that the sales person didn't save the sales order they are trying to make a work order for and then change the sales order. Our users only see Window's spinning circle. Deadlocks don't get cleaned up immediately like your developer says. They get cleaned up fast, but not instantly. To answer your question, I do not think it's good programming practices to do what they did. And to expand on that answer, i think your developers a bit slow if he thinks any DBA won't make him fix his "feature" immediately. Good luck with that person...
Lyall Barbour
----- Original Message -----
From: Barbara Baker
Sent: 03/27/13 02: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 t hen 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 followi ng 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. -- http://www.freelists.org/webpage/ora cle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 27 2013 - 20:06:56 CET