Re: Re:RE: Deadlock

Hello Dick

If both processes first update table a and then table b there will be no deadlock problem.
The first process will lock the row for update in table a and goes on to update table b.
The second one will attempt to lock the row in table a and will wait for the first to finish.
This can cause a delay but not a deadlock.

I can see another potential problem:
Process a selects item 1 and update stock on hand to 0. Process b reads item 1 and sees that stock on hand is 1 as process a did not finished the update in table b yet.
In this case process b might decide that it does not need to update the stock on hand.
Afterwards process a commit and you got stock on hand = 0 despite the fact that you have it in the warehouse.

You must check that process b do select for update or does the update anyway without checking the stock on hand field.

Yechiel Adar
Mehish
----- Original Message -----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Friday, December 20, 2002 12:34 AM

> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Yechiel Adar
  INET: adar76_at_inter.net.il

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).