Re: Re:RE: Deadlock
I think I'll resist the temptation to review the entire trace file. However, since this is a v9 deadlock dump, I think you should find that you have a complete processstate dump after the initial deadlock graph. Somewhere near the end of the dump you should find the CURSOR section, which should list all the current cursors for the session. Read through these, they may give you a clue about the SQL that has pushed the TM lock from a 3 to a 5 on the problem table. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 27 December 2002 00:15 Yes it is 9013. This is not an array based update. As per the trace file same statement is being executed by both sessions. I can directly send you the trace file if there is a need. There are triggers on the tables, I'll look into parent table activity. But there are indexes on all foreign keys except one which corresponds to a static master table containing PO TYPES. That table is not being updated. How can I dig deeper into this issue. Thanks Shaleen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
Re: Re:RE: Deadlock
This looks like a v9 trace file, which means there may be new issues involved that I haven't come across yet. For example, 9.2 introduces a mode 2 TM lock on pk/fk activity for some reason that I haven't worked out, so this may be a side-effect. However, (assuming no big changes from v8) this is TM lock in mode 5 (SSX) colliding with a mode 3 (SX), so it is most likely a pk/fk issue - despite your comment to the contrary. If it were a purely data problem I would expect to see a mode 6 TX lock, if it were any of the internal structure issues I would expect to see a mode 4 TX lock. The 'Rows waited on:' line could be down to v9 recording the block address of the most recent buffer busy wait, write wait, etc. which is a very recent enhancement - but since the values are not cleared when the wait ends, this can cause confusion. Is this an array-based update ? And is the SQL from this session (the one that dumped the graph) the same as the SQL that has been dumped for the other session ? Most critically - do you have any triggers on the child table that may be doing parent table activity that you've overlooked ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 24 December 2002 23:49 Jonathan, What do you make out of following deadlock graph. This is happenning when 2 instance of same batch process are running. We are absolutely certain that these batch processes are not working on same set of records (although records can be in same block). object f9d5 is wcu_po_line table. I am unable to understand why the update statements are requesting SSX lock on the table. This is not a case of primary/forign key issue with a missing index in child table where primary key is change in master table because master table is not being updated. Thanks Shaleen Deadlock graph: -Blocker(s) -Waiter(s )-- --- Resource Name process session holds waits process session holds waits TM-f9d5- 390 503SX SSX 290 597 SX SSX TM-f9d5- 290 597SX SSX 390 503 SX SSX session 503: DID 0001-0186-0002 session 597: DID 0001-0122-0002 session 597: DID 0001-0122-0002 session 503: DID 0001-0186-0002 Rows waited on: Session 597: obj - rowid = 98A5 - AAADFAAAGCsAAA (dictionary objn - 39077, file - 197, block - 24748, slot - 0) Session 503: no row SQL statements executed by the waiting sessions: Session 597: UPDATE wcu_po_line SET po_no = :b21, po_line = :b20, item_price = :b19, po_qty = :b18, invoice_shipped_qty = 0, --invoice_shipped_qty distributor_item_no = :b17, current_status = :b16, created_dtm = SYSDATE, status_change_dtm = SYSDATE, --created_dtm return_id = NULL, --return_id_in, return_line_no = NULL, --return_line_no_in, min_qty = :b15, wrap_code = :b14, invoice_id = :b13, gift_wrap_UPC = :b12, gift_wrap_price = :b11, wrap_to_label = :b10, wrap_from_label = :b9, item_cost = nvl(:b7,:b6), xml_po_line = :b8, wmc_item_cost = nvl(:b7,:b6), distributor_id = :b5, po_type = :b4 WHERE po_no = :b3 AND co_order_no = :b2 AND co_line_no = :b1 === - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 20, 2002 3:33 PM -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
Re: Re:RE: Deadlock
Yes it is 9013. This is not an array based update. As per the trace file same statement is being executed by both sessions. I can directly send you the trace file if there is a need. There are triggers on the tables, I'll look into parent table activity. But there are indexes on all foreign keys except one which corresponds to a static master table containing PO TYPES. That table is not being updated. How can I dig deeper into this issue. Thanks Shaleen - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 26, 2002 1:58 AM This looks like a v9 trace file, which means there may be new issues involved that I haven't come across yet. For example, 9.2 introduces a mode 2 TM lock on pk/fk activity for some reason that I haven't worked out, so this may be a side-effect. However, (assuming no big changes from v8) this is TM lock in mode 5 (SSX) colliding with a mode 3 (SX), so it is most likely a pk/fk issue - despite your comment to the contrary. If it were a purely data problem I would expect to see a mode 6 TX lock, if it were any of the internal structure issues I would expect to see a mode 4 TX lock. The 'Rows waited on:' line could be down to v9 recording the block address of the most recent buffer busy wait, write wait, etc. which is a very recent enhancement - but since the values are not cleared when the wait ends, this can cause confusion. Is this an array-based update ? And is the SQL from this session (the one that dumped the graph) the same as the SQL that has been dumped for the other session ? Most critically - do you have any triggers on the child table that may be doing parent table activity that you've overlooked ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 24 December 2002 23:49 Jonathan, What do you make out of following deadlock graph. This is happenning when 2 instance of same batch process are running. We are absolutely certain that these batch processes are not working on same set of records (although records can be in same block). object f9d5 is wcu_po_line table. I am unable to understand why the update statements are requesting SSX lock on the table. This is not a case of primary/forign key issue with a missing index in child table where primary key is change in master table because master table is not being updated. Thanks Shaleen Deadlock graph: -Blocker(s) -Waiter(s )-- --- Resource Name process session holds waits process session holds waits TM-f9d5- 390 503SX SSX 290 597 SX SSX TM-f9d5- 290 597SX SSX 390 503 SX SSX session 503: DID 0001-0186-0002 session 597: DID 0001-0122-0002 session 597: DID 0001-0122-0002 session 503: DID 0001-0186-0002 Rows waited on: Session 597: obj - rowid = 98A5 - AAADFAAAGCsAAA (dictionary objn - 39077, file - 197, block - 24748, slot - 0) Session 503: no row SQL statements executed by the waiting sessions: Session 597: UPDATE wcu_po_line SET po_no = :b21, po_line = :b20, item_price = :b19, po_qty = :b18, invoice_shipped_qty = 0, --invoice_shipped_qty distributor_item_no = :b17, current_status = :b16, created_dtm = SYSDATE, status_change_dtm = SYSDATE, --created_dtm return_id = NULL, --return_id_in, return_line_no = NULL, --return_line_no_in, min_qty = :b15, wrap_code = :b14, invoice_id = :b13, gift_wrap_UPC = :b12, gift_wrap_price = :b11, wrap_to_label = :b10, wrap_from_label = :b9, item_cost = nvl(:b7,:b6), xml_po_line = :b8, wmc_item_cost = nvl(:b7,:b6), distributor_id = :b5, po_type = :b4 WHERE po_no = :b3 AND co_order_no = :b2 AND co_line_no = :b1 === - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 20, 2002 3:33 PM -- Please see the official ORACLE-L FAQ: http://www.orafaq.net --
Re: Re:RE: Deadlock
Jonathan, What do you make out of following deadlock graph. This is happenning when 2 instance of same batch process are running. We are absolutely certain that these batch processes are not working on same set of records (although records can be in same block). object f9d5 is wcu_po_line table. I am unable to understand why the update statements are requesting SSX lock on the table. This is not a case of primary/forign key issue with a missing index in child table where primary key is change in master table because master table is not being updated. Thanks Shaleen Deadlock graph: -Blocker(s) -Waiter(s)-- --- Resource Name process session holds waits process session holds waits TM-f9d5- 390 503SX SSX 290 597SX SSX TM-f9d5- 290 597SX SSX 390 503SX SSX session 503: DID 0001-0186-0002 session 597: DID 0001-0122-0002 session 597: DID 0001-0122-0002 session 503: DID 0001-0186-0002 Rows waited on: Session 597: obj - rowid = 98A5 - AAADFAAAGCsAAA (dictionary objn - 39077, file - 197, block - 24748, slot - 0) Session 503: no row SQL statements executed by the waiting sessions: Session 597: UPDATE wcu_po_line SET po_no = :b21, po_line = :b20, item_price = :b19, po_qty = :b18, invoice_shipped_qty = 0, --invoice_shipped_qty distributor_item_no = :b17, current_status = :b16, created_dtm = SYSDATE, status_change_dtm = SYSDATE, --created_dtm return_id = NULL, --return_id_in, return_line_no = NULL, --return_line_no_in, min_qty = :b15, wrap_code = :b14, invoice_id = :b13, gift_wrap_UPC = :b12, gift_wrap_price = :b11, wrap_to_label = :b10, wrap_from_label = :b9, item_cost = nvl(:b7,:b6), xml_po_line = :b8, wmc_item_cost = nvl(:b7,:b6), distributor_id = :b5, po_type = :b4 WHERE po_no = :b3 AND co_order_no = :b2 AND co_line_no = :b1 === - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 20, 2002 3:33 PM There is a deadlock here - but I confused the issue by making complete garbage of the last phrase. Instead of: both X and Y might end up waiting for A. I should have said both Y and Z might end up waiting for X (which is when you won't get the deadlock) The critical point comes in the previous paragraph though: With a little luck, Y will be waiting for Z and Z will be waiting for Y (i.e. DEADLOCK) For Oracle 9, I have only introduced the X session to take out one ITL slot from each of the two blocks because Oracle 9 forces a minimum value of 2 entries per ITL. This really is a deadlock - which will show a deadlock graph with holders in mode 6 and waiters in mode 4. (X and S if I've got the letters right - personally I prefer numbers). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 20 December 2002 22:45 Jonathon, This produces ITL waits for sessions Y and Z; but this is not deadlock. The deadlock occurs due to a situation where the Session 1 waits for something to finish in Session 2, which in turn waits for Session 1 AND, this is important, Oracle detects it and kills one of them, rolling back the changes, making a deadlock detected error. Is this not the true error message that occured in the original thread? In your example, sessions Y and Z will wait indefinitely until X commits or rolls back. This is not going to be detected by Oracle nor killed by it. So you wouldn't see a message DEADLOCK DETECTED in alert log. Therefore setting INITRANS higher is not going to help at all. Rather the application logic should be checked to remove a real locking conflict. Am I correct, or am I missing something here? Arup Nanda -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: Re:RE: Deadlock
It is possible for ITL starvation to result in deadlocks, and changing INITRANS (and rebuilding the problem objects) would help - but no-one can give you an appropriate answer without seeing the deadlock graph that usually comes as The following information It would also help if you told use whether this was an ORA-04020 deadlock (dictionary internal) or ORA-00060 (data related). I think the text is the one that comes with ORA-00060, but the two texts are pretty similar. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
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 [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
RE: Re:RE: Deadlock
Jonathan, What do you mean by ITL starvation? And how would it result in a deadlock? Dan Fink -Original Message- Sent: Friday, December 20, 2002 1:44 AM To: Multiple recipients of list ORACLE-L It is possible for ITL starvation to result in deadlocks, and changing INITRANS (and rebuilding the problem objects) would help - but no-one can give you an appropriate answer without seeing the deadlock graph that usually comes as The following information It would also help if you told use whether this was an ORA-04020 deadlock (dictionary internal) or ORA-00060 (data related). I think the text is the one that comes with ORA-00060, but the two texts are pretty similar. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
Re: Re:RE: Deadlock
Set maxtrans to 2 on a table. Insert three rows into the same block and commit. Use three sessions to update one row each. The third transaction has to wait for one of the other two transactions to commit, as there are insufficient ITL (interested transaction list) entries for three concurrent transactions on the same block. Now repeat the experiment with: rows A1, B1, C1 in block 1 rows A2, B2, C2 in block 2. Session X updates row A1 and A2, Session Y updates row B1 Session Z updates row C2 Session Y tries to update row B2 and waits because the ITL is full Session Z tries to update row C1 and waits because the ITL is full With a little luck, Y will be waiting for Z and Z will be waiting for Y (i.e. DEADLOCK) but you may have to fiddle with a more complex example, as both X and Y might end up waiting for A. It's easier to do this in 8.1 because MAXTRANS can be set to 1, so you need only use two sessions and two rows per block. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 20 December 2002 16:56 Jonathan, What do you mean by ITL starvation? And how would it result in a deadlock? Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
RE: Re:RE: Deadlock
Jonathan, Thanks for the explanation. I've always addressed deadlocks as a row-level issue, but now I see how it can also be a block-level issue. What a way to start the holidays, with new knowledge (and some tests to run!). Dan Fink -Original Message- Sent: Friday, December 20, 2002 10:10 AM To: Multiple recipients of list ORACLE-L Set maxtrans to 2 on a table. Insert three rows into the same block and commit. Use three sessions to update one row each. The third transaction has to wait for one of the other two transactions to commit, as there are insufficient ITL (interested transaction list) entries for three concurrent transactions on the same block. Now repeat the experiment with: rows A1, B1, C1 in block 1 rows A2, B2, C2 in block 2. Session X updates row A1 and A2, Session Y updates row B1 Session Z updates row C2 Session Y tries to update row B2 and waits because the ITL is full Session Z tries to update row C1 and waits because the ITL is full With a little luck, Y will be waiting for Z and Z will be waiting for Y (i.e. DEADLOCK) but you may have to fiddle with a more complex example, as both X and Y might end up waiting for A. It's easier to do this in 8.1 because MAXTRANS can be set to 1, so you need only use two sessions and two rows per block. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 20 December 2002 16:56 Jonathan, What do you mean by ITL starvation? And how would it result in a deadlock? Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
Re: Re:RE: Deadlock
Jonathon, This produces ITL waits for sessions Y and Z; but this is not deadlock. The deadlock occurs due to a situation where the Session 1 waits for something to finish in Session 2, which in turn waits for Session 1 AND, this is important, Oracle detects it and kills one of them, rolling back the changes, making a deadlock detected error. Is this not the true error message that occured in the original thread? In your example, sessions Y and Z will wait indefinitely until X commits or rolls back. This is not going to be detected by Oracle nor killed by it. So you wouldn't see a message DEADLOCK DETECTED in alert log. Therefore setting INITRANS higher is not going to help at all. Rather the application logic should be checked to remove a real locking conflict. Am I correct, or am I missing something here? Arup Nanda Original Post 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 /Original Post - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 20, 2002 12:10 PM Set maxtrans to 2 on a table. Insert three rows into the same block and commit. Use three sessions to update one row each. The third transaction has to wait for one of the other two transactions to commit, as there are insufficient ITL (interested transaction list) entries for three concurrent transactions on the same block. Now repeat the experiment with: rows A1, B1, C1 in block 1 rows A2, B2, C2 in block 2. Session X updates row A1 and A2, Session Y updates row B1 Session Z updates row C2 Session Y tries to update row B2 and waits because the ITL is full Session Z tries to update row C1 and waits because the ITL is full With a little luck, Y will be waiting for Z and Z will be waiting for Y (i.e. DEADLOCK) but you may have to fiddle with a more complex example, as both X and Y might end up waiting for A. It's easier to do this in 8.1 because MAXTRANS can be set to 1, so you need only use two sessions and two rows per block. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 20 December 2002 16:56 Jonathan, What do you mean by ITL starvation? And how would it result in a deadlock? Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Arup Nanda 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).
Re: Re:RE: Deadlock
There is a deadlock here - but I confused the issue by making complete garbage of the last phrase. Instead of: both X and Y might end up waiting for A. I should have said both Y and Z might end up waiting for X (which is when you won't get the deadlock) The critical point comes in the previous paragraph though: With a little luck, Y will be waiting for Z and Z will be waiting for Y (i.e. DEADLOCK) For Oracle 9, I have only introduced the X session to take out one ITL slot from each of the two blocks because Oracle 9 forces a minimum value of 2 entries per ITL. This really is a deadlock - which will show a deadlock graph with holders in mode 6 and waiters in mode 4. (X and S if I've got the letters right - personally I prefer numbers). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 20 December 2002 22:45 Jonathon, This produces ITL waits for sessions Y and Z; but this is not deadlock. The deadlock occurs due to a situation where the Session 1 waits for something to finish in Session 2, which in turn waits for Session 1 AND, this is important, Oracle detects it and kills one of them, rolling back the changes, making a deadlock detected error. Is this not the true error message that occured in the original thread? In your example, sessions Y and Z will wait indefinitely until X commits or rolls back. This is not going to be detected by Oracle nor killed by it. So you wouldn't see a message DEADLOCK DETECTED in alert log. Therefore setting INITRANS higher is not going to help at all. Rather the application logic should be checked to remove a real locking conflict. Am I correct, or am I missing something here? Arup Nanda -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
