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 <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
