Re:RE: Deadlock

From: <dgoulet_at_vicr.com>
Date: Thu, 19 Dec 2002 14:34:44 -0800
Message-ID: <F001.0051F20C.20021219143444@fatcity.com>

Please allow me to provide a case in point on the subject that we discovered and fixed some time ago.

    We have 2 PeopleSoft SQR's that are used for material movement into and out of the stock room. Both run on a scheduled basis and it is NOT odd to see both running at the same time. Now for every item in the stock room there is an entry in two different tables, one is a master list of all items (TABLE A) and if they have stock in the stock room + a couple of other control type columns. The other table says where the item is and how much is in that location (table B). Not bad at this point.

    Now, SQRA starts up to allocate material out of the stock room to the assembly floor. It starts by selecting all items that it needs to process and attempts to set stock on hand flag to zero on table A for each item it has. It then looks in the storage location (table B) and updates the quantity on hand field to decrement it by the amount to be sent to the floor.

    SQRB does similar things setting stock on hand in Table A to 1 and incrementing the on hand quantity in Table B, but in the reverse order.

    Can you see a potential deadlock brewing??

Dick Goulet

____________________Reply Separator____________________
Author: "Fink; Dan" <Dan.Fink_at_mdx.com>
Date:       12/19/2002 2:04 PM

Seema,
        Changing INITRANS may help IF you see waits for data block headers.

INITRANS/MAXTRANS deal with the number of transactions that can lock a block at a given time.

        Deadlocks are caused when TransactionA has locked RowA and TxB has locked RowB. Then TxA needs to lock RowB (but can't because TxB has locked it) and TxB needs to lock RowA (but can't because TxA has locked it). The locks won't be released until the transaction completes, but they cannot complete successfully since they cannot acquire the needed lock. So you have a round robin affair. The transaction discovering the deadlock will be rolled back.

        Check the application code. Therein lies the problem.

Dan Fink

-----Original Message-----

Sent: Thursday, December 19, 2002 12:55 PM To: Multiple recipients of list ORACLE-L

Hi
I have been noticing some times following error with one table during update.

DEADLOCK DETECTED
Current SQL statement for this session:
"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:"

Is chaning of INITTRANS would help ?
Thx
-Seema

---

STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Seema Singh
  INET: oracledbam_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Fink, Dan
  INET: Dan.Fink_at_mdx.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author:
  INET: dgoulet_at_vicr.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Dec 19 2002 - 16:34:44 CST