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