I've just created a forced deadlock by opening two sql*plus sessions and doing updates out of sequence:

session 1: update names set lastname='Smith' where id=1;

session 2: update salary set taxes=500 where id=1;

session 1: update salary set amount=50000 where id=1;
waiting...

session 2: update names set firstname='Joey' where id=1;
waiting...

It's been over 10 minutes. How long does it take for Oracle to clear this deadlock?

That scenario does not seems to be a dead lock to me.
Both sessions are accessing id=1. The second session 
will wait as long as session 1 commits;

Example:
========

SQL> select * from t2;

      COL1       COL2
---------- ----------
         1
                    2
SQL> select rowid, k.* from t2 k;          

ROWID                    COL1       COL2
------------------ ---------- ----------
AAABNtAANAAAFXVAAA          1
AAABNtAANAAAFXVAAB                     2

SESSION1:

SQL> begin
  2  update t2 set col2 = 1 where rowid = 'AAABNtAANAAAFXVAAA';
  3  dbms_lock.sleep(5);
  4  update t2 set col1 = 2 where rowid = 'AAABNtAANAAAFXVAAB';
  5  end;
  6  /
begin
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

SESSION2:

SQL> begin
  2  update t2 set col2 = 3 where rowid = 'AAABNtAANAAAFXVAAB';
  3  dbms_lock.sleep(5);
  4  update t2 set col2 = 4 where rowid = 'AAABNtAANAAAFXVAAA';
  5  end;
  6  /

PL/SQL procedure successfully completed.


SQL> select * from t2;

      COL1       COL2
---------- ----------
         1          4
                    3

 

except mislead you a bit. I should have said two Oracle sessions, not two SQL*Plus sessions, because I was using TOAD and SQL*Plus for my test. Session 1 was TOAD, and for some reason the "ORA-00060: deadlock detected while waiting for resource" popup message was obscured by Windows and I never saw it, so I thought the deadlock never got cleared.

But it doesn't matter. If you'll notice in my example, I am querying two different tables (names and salary), so this deadlock scenario is valid. And you are right, the deadlock was cleared automatically, although it wasn't instantaneous, it took about 15 seconds for the error message to come back.

I just did a test using Oracle 8.1.7 and after a few second's in session B, Oracle returned this message:
ORA-00060: deadlock detected while waiting for resource

However, Session B was still active and free to either commit, rollback, or do something else. Here is my example:

create table test (
pk number(38) primary key,
my_data varchar2(40)
)
/

begin
for x in 1..20 loop
insert into test (x,'Hello');
end loop;
end;
/

session A:
select * from test where pk = 5 for update;

session B:
select * from test where pk = 6 for update;

session A:
update test set my_data = 'test1' where pk = 5;

session B:
update test set my_data = 'testing' where pk = 6;

session A:
select * from test where pk = 6 for update;
[waits]

session B:
select * from test where pk = 5 for update;


After a few seconds, I get Oracle error message above, but session B is still active and able to issue other commands.
Session A waits until session B commits or rollsback data.

So, did Oracle change their logic to only kill the offending statement (and not the entire session/transaction) allowing the session to clean itself up?

Thanks.

Hi Tom,
I am a bit confused about the terms "DEADLOCK" and "BLOCK" used in most of the REVIEWS.
Could you please explain that to me?

Many Thanks.

Regards,
Sajid Anwar
London

I opened two sqlplus sessions. Db version 9.2
session a.
SQL>  update test set my_data  = upper(my_data) where pk = 1 ;

1 row updated.
session b
SQL>  update test set my_data  = lower(my_data) where pk = 1

Now session B is blocked as it is waiting for the  session A to commit or rollback.
Q) How long will it remained Blocked. ?Does it come out of it byitself. 

Thanks for the response Tom.

The reason I asked, was that in your second response above, you said that the session would have been killed and not the statement.

Are there scenarios where a session is killed in a deadlock situation and not just the statement?

Finally, what is the best way to handle the following situation?

I have a table called account_balance defined as:

acc_bal_id primary key
account_id fk to account table
fiscal_year_id fk to fiscal_year table
balance1 number
balance2 number
...
balance12 number

There may be a few hundered accounts in a given fiscal year. Users call a routine which updates the account balance for a given fiscal period (balance 1 - 12).

Since different users can be updating the accounts at the same time, I want to avoid deadlocks where users are updating the accounts in different orders from each other.

How would I test the performance of selecting all accounts for a give fiscal year for update, when I am only updating one or two accounts? I may have 200 - 300 accounts per fiscal year. Is this the right approach or would you suggest something else?

Thanks.

tom, I have inherited a database system, about 200 tables,
80 packages and proces about 70 triggers,

based on the code written, I think there can be a dead lock.
couple of times it happened also. so before rewriting it, or massing refactoring it I want to audit the code to find out what are the situations where this can occur ? any ideas ?
it's lot of code and i wnat to find all dead lock situations

It will talk some time to understand the existing code and then add appropriate error handling

The ones that have occured in the past, I have the trace files and fixed it. but I can not wait till another happens. I want to detect in code before that happens in run time. any way to proceed ? Any way I am going to go thro' all code but it will take some time . any easy , not easier , but smater way ?

Create or Replace Procedure PUMP_DATA_IN_25 (p_date DATE)
AS
begin
declare
p_insert CHAR(1) default 'N';
p_match_term_id Number;
p_string VARCHAR2(100);
begin
for x in (Select Match_term_text,Request_Count from myexternal_table) LOOP
begin
Select Match_term_id into p_Match_term_id from Match_term
Where Match_term_text = x.Match_term_text;
EXCEPTION
WHEN NO_DATA_FOUND THEN
begin
Select get_sequence_id(NULL) into p_Match_term_id from dual;
p_insert := 'Y';
end;
end;
If p_insert = 'Y' then
INSERT into Match_term Values (p_Match_term_id,x.Match_term_text);
p_insert := 'N';
end if;
Insert into Match_terms_daily Values (p_Match_term_id,p_date,x.Request_Count,100,100,3);
for y in 0..2 LOOP
Insert into match_terms_position_daily Values(p_Match_term_id,p_date,y,100,10,90,30000,300000,60,60,60,60,60);
end loop;
end loop;
commit;
end;
end;
/

Create or Replace Function GET_SEQUENCE_ID (p_Value IN VARCHAR2)
RETURN NUMBER AS
return_val NUMBER;
BEGIN
SELECT Match_term_id INTO return_val FROM MATCH_TERM WHERE MATCH_TERM_TEXT = p_Value;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Select my_sequence.nextval into return_val from dual;
RETURN return_val;
END get_sequence_id;
/

I know the reason for DEADLOCKS as the UNINDEXED Foreign Keys but out here I am not updating any of the rows for the child table to be locked for any reasons. these are simple inserts happening all over the place.

Also can you please tell me how to build a Dynamic SQL and get it executed with in an implicit cursor.

AS an example we have p_date as date passed from procedure and the Ist two characters should build the external_table.
eg.
Exec PUMP_DATA_IN ('10-SEP-2005') should have the implicit cursor work like

For x in (Select * from MYExternal_table_10) and so on...
which I tried but to no success.

Isn't execute immediate 'string' works like a implicit cursor.

Thanks

Hi Tom,

*** 2005-10-10 11:42:28.655
*** ACTION NAME:() 2005-10-10 11:42:28.642
*** MODULE NAME:(SQL*Plus) 2005-10-10 11:42:28.642
*** SERVICE NAME:(SYS$USERS) 2005-10-10 11:42:28.642
*** SESSION ID:(1057.27841) 2005-10-10 11:42:28.642
DEADLOCK DETECTED
Current SQL statement for this session:
INSERT INTO MATCH_TERM VALUES (:B2 ,:B1 )
----- PL/SQL Call Stack -----
object line object
handle number name
0x9e49a5e0 37 procedure PARIS_USER.PUMP_DATA_IN_10
0x9f1f5a10 1 anonymous block
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:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0004001b-00000b1b 19 1057 X 50 1060 S
TX-00050026-00000324 50 1060 X 19 1057 S
session 1057: DID 0001-0013-00002933 session 1060: DID 0001-0032-00000069
session 1060: DID 0001-0032-00000069 session 1057: DID 0001-0013-00002933
Rows waited on:
Session 1060: obj - rowid = 00016C83 - AAAWyDAAIAADlrLAAA
(dictionary objn - 93315, file - 8, block - 940747, slot - 0)
Session 1057: obj - rowid = 00016C83 - AAAWyDAAIAACJnIAAA
(dictionary objn - 93315, file - 8, block - 563656, slot - 0)
Information on the OTHER waiting sessions:
Session 1060:
pid=50 serial=19143 audsid=89755 user: 138/PARIS_USER
O/S info: user: oracle, term: pts/1, ospid: 32084, machine: search3.internal
program: sqlplus@search3.internal (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
Current SQL Statement:
INSERT INTO MATCH_TERM VALUES (:B2 ,:B1 )
End of information on OTHER waiting sessions.
===================================================

is the trace of the file. Can you please let me know why the deadlock is coming at INSERT INTO MATCH_TERM - the parent table of both the other tables. Both the Child tables have INDEXED foreign keys.Some of the DDL statements for these tables are:

CREATE TABLE Match_term(
Match_term_id NUMBER(10, 0) NOT NULL,
Match_term_text VARCHAR2(255) NOT NULL,
PRIMARY KEY (Match_term_id)
);

CREATE TABLE Match_terms_daily(
Match_term_id NUMBER(10, 0) NOT NULL,
Aggr_Date DATE NOT NULL,
Request_Count NUMBER(10, 0),
Sum_Impr_Requested NUMBER(10, 0),
Sum_impr_Requested_Sqr NUMBER(10, 0),
Max_impr_requested NUMBER(10, 0),
PRIMARY KEY (Match_term_id, Aggr_Date)
);

CREATE TABLE Match_terms_position_daily(
Match_term_id NUMBER(10, 0) NOT NULL,
Aggr_Date DATE NOT NULL,
Position_Num NUMBER(10, 0) NOT NULL,
Impr_Count NUMBER(10, 0) NOT NULL,
Click_count NUMBER(10, 0) NOT NULL,
Max_bid NUMBER(20, 0),
Sum_bid NUMBER(20, 0),
Sum_bid_Sqr NUMBER(20, 0),
Max_CPC_Charged NUMBER(20, 0),
Sum_CPC_Charged NUMBER(20, 0),
Sum_CPC_Charged_Sqr NUMBER(20, 0),
Sum_Impr_Score NUMBER(20, 0),
Sum_Impr_Score_Sqr NUMBER(20, 0),
PRIMARY KEY (Match_term_id, Aggr_Date, Position_Num)
);

ALTER TABLE Match_terms_daily ADD CONSTRAINT FK_MATCH_TERMS_D_MT_ID
FOREIGN KEY (Match_term_id)
REFERENCES Match_term(Match_term_id);

ALTER TABLE Match_terms_position_daily ADD CONSTRAINT FK_MATCH_TERMS_POS_D_MT_ID
FOREIGN KEY (Match_term_id)
REFERENCES Match_term(Match_term_id);

Please let me know why Oracle is throwing Deadlock errors. Confused at this behaviour!

Hi Tom,

I understand your point, but what can be done in order to avoid deadlocks while execution of procedures concurrently from many sessions.

Regards

interesting and understandable information:

block = you wait, you are blocked. you are trying to modify some data that some
other session is modifying.

deadlock = you locked "row x". they locked "row y". You need to lock "row y",
you get blocked by them. They now need to lock "row x". They get blocked by
you. You are now deadlocked - waiting on eachother. We terminate one of the
statements (else you and they would just sit there forever waiting for
eachother). That session decides whether it should a) commit, b) rollback, c)
goto lunch, d) do something else

Hi Tom,

I read this thread and I still have a question.

I think if I design my transaction (code) in such a way that it ALWAYS follows the same update sequence on tables, there will be fewer situations where a deadlock can occur. Is this true?

If yes, how significant this is? Sometimes, in order to enforce this rule, you may have to sacrifice some functionality. Is it worth compromising functionality and re-designing the transactions or just live with some deadlock occurences?

I hope my question is clear enough.

Thanks as always!

-Bipin.

Thanks, Tom for confirming my understanding. I think we will live with potential deadlock occurences rather than reducing the functionality.

-Bipin

Is there any possiblity to see deadlocks with in a single seeion??
Could you give sopme example?

it takes two TRANSACTIONS to deadlock and since a single session may have more than one transaction - sure we can do this:

ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2> insert into t values ( 1 );

1 row created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> update t set x = x+1;

1 row updated.

ops$tkyte%ORA10GR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          update t set x = x+1;
  5          commit;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
 

Is having unindexed foreign keys wrong design. Because, we do have lot ofunindexed foreign keys and deadlocks occured. Should they be simply indexed? Are there any real reasons why someone should have them unindexed?

create table temp_table as
select object_id, object_id as object_id_1 from all_objects

session 1:
----------

update temp_table set object_id = 0

session 2:
----------

update temp_table set object_id_1 = 0

session 2 is locked untill i commit or rollback session 1

Iam updating two different column and the two column are not related.

Is it possible to avoide the lock.


Regards,

Karthick.

you mean i cant update untill the transaction ends in the first session.

select for update nowait just lets me know that the table is been blocked by giving the following error..

ORA-00054: resource busy and acquire with NOWAIT specified

am i getting it right?

Regards,

Karthick

Tom:

1. Do you know why I am getting this error. I did find two columns on the neg_cust_table unindexed and i did index them but i still get it. However, when i create a temp table with no constraints things seem to work.

2. also, can you to point to anything inefficient in the code.

I want to process orders at end of month using a web link. If some customers are flagged as negative i want to save them to a table (Autonomous transaction) and my main transaction will roll back.

ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "PROCESS_ORDERS", line 259
ORA-06512: at line 1


ORDERED_ITEMS
------------
ORDER_DATE PK
CUST_ID PK
CATEGORY
PART_NO PK
ORD_QTY


CUST_NEG_TABLE
---------------
order_Date PK
cust_id PK
period_code PK
create_Date

procedure P1
......

begin

..some code

For x in (select order_date,cust_id,category,sum(ord_qty) total_qty from
(
select a.order_date,a.cust_id,decode(a.category,'CD',a.category,b.category),
ord_Qty from ordered_items a, parts b
where a.part_no=b.part_no
and order_date = v_order_Date
)
group by order_Date,cust_id,category
having sum(ord_Qty) > 0
order by order_date,cust_id,category
)
LOOP
v_month_credit := 0;

v_month_credit := Calc_credit(x.cust_id,x.category,v_month,v_year);

IF (x.total_qty > v_month_credit)
v_year_credit := null;

SELECT year_credit into v_year_credit from CREDITS....
SELECT total_used into v_total_used from ordered_items....

IF (v_year_credit < v_total_used) THEN
--insert into table to flag those customers
PROCESS_NEG_CUST(z.cust_id,z.order_Date);
l_neg_customer := true;
END IF;
END IF;
END LOOP;

IF (l_neg_customer) THEN
RAISE EXC_NEG_CUST;
END IF;

COMMIT;
dbms_output.put_line('transaction worked');

EXCEPTION

WHEN EXC_NEG_CUST
ROLLBACK;
dbms_ouput('transaction failed');





procedure process_neg_Cust
(p_order_date IN VARCHAR2 default null,
p_cust_id IN VARCHAR2 default null 0

pragma autonomous_transaction;

begin

update cust_neg_table
set created_date = sysdate
where order_Date = p_order_Date, cust_id=p_cust_id,period_code='A';

If (sql%rowcount = 0) THEN
insert into cust_neg_Table(order_date,cust_id,period_Code,created_date)
values (p_order_date,p_cust_id,'A',sysdate);
end if;

update parent_cust_neg_Table
set status_code = 'X' where order_date = p_order_Date;

commit;

end;

SQL>exec PROCESS_ORDERS

ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "PROCESS_ORDERS", line 259
ORA-06512: at line 1


Line 259 is  

PROCESS_NEG_CUST(z.cust_id,z.order_Date);


I think it is the loop thing. Is there a way to collect all the records first and then call the autonomous transaction. would you use a pl/sql table.

It seems main transaction waits for the autonomous transaction to complete.
When it hits the second record it deadlocks. I added an exception handler to
the autonomous transaction. I also think that the IF statement between lines may have caused it. Not sure why.

My loop generates 4 records per customer/category. I only want to add one record to
the cust_neg_table for the order_date and customer and I only want to update one master record in "parent_cust_neg_table".
How do you modify this to do this? It is kind of redundant now.


SQL> exec process_orders;
****  PASS 1 ****
****  PASS 1  ****
Error -60: ORA-00060: deadlock detected while waiting for resource
Error -60: ORA-00060: deadlock detected while waiting for resource
BEGIN process_orders; END;

*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "PROCESS_ORDERS", line 272
ORA-06512: at line 1


procedure P1
......

begin

..some code

For x in (select order_date,cust_id,category,sum(ord_qty) total_qty from
        (
        select a.order_date,a.cust_id,decode(a.category,'CD',a.category,b.category),
        ord_Qty from ordered_items a, parts b
        where a.part_no=b.part_no 
        and order_date = v_order_Date 
        )
        group by order_Date,cust_id,category
        having sum(ord_Qty) > 0
        order by order_date,cust_id,category
        )
LOOP
  v_month_credit := 0;
  
  v_month_credit := Calc_credit(x.cust_id,x.category,v_month,v_year);
  
  IF (x.total_qty > v_month_credit)
  
  ------------------------------------------------------------------------------------------------
         v_year_quantity := null;
  --If customer has no credit record, or a credit of "0" do not process
 
           SELECT year_credit INTO v_year_credit FROM (
               SELECT * FROM credits
                WHERE cust_id = x.cust_id AND media_type_code = x.category AND status_code = 'A'
                AND effective_date <= v_effective_date)
                ORDER by seq_no DESC )
            WHERE rownum = 1;
  
  --Find the Total Previous Used Credit AND Current Month selections
   
            IF (upper(x.categroy) in ('CD') ) THEN
  
             SELECT NVL(SUM(Ord_Qty),0)
               INTO V_TOTAL_USED
               FROM ordered_items
               WHERE cust_id = x.cust_id
               AND category = x.category
               AND order_Date <= x.order_date
               AND to_char(order_Date,'YYYY') = to_char(x.order_date,'YYYY');
  
            elsif (upper(x.category) in ('DVD') ) then
  
            SELECT NVL(SUM(Ord_Qty),0)
              INTO V_TOTAL_USED
              FROM ordered_items  a, parts b
              WHERE a.part_no = b.part_no
              AND a.cust_id = x.cust_id
              AND a.order_date <= x.order_Date
              AND to_char(order_date,'YYYY') = to_char(x.order_date,'YYYY')
              AND b.category = x.category;
  
           END IF;
  -------------------------------------------------------------------------------
    
  
     IF (v_year_credit < v_total_used) THEN
     --insert into table to flag those customers
       PROCESS_NEG_CUST(z.cust_id,z.order_Date);
       l_neg_customer := true;
     END IF;
  END IF;
END LOOP;

   IF (l_neg_customer) THEN
      RAISE EXC_NEG_CUST;
   END IF;
  
  COMMIT;
  dbms_output.put_line('transaction worked');
  
EXCEPTION

  WHEN EXC_NEG_CUST
   ROLLBACK;
   dbms_ouput('transaction failed');



procedure process_neg_Cust
 (p_order_date  IN VARCHAR2  default null,
  p_cust_id     IN VARCHAR2  default null 0
  
  pragma  autonomous_transaction;
  
  begin
  
  update cust_neg_table
    set  created_date = sysdate
    where order_Date = p_order_Date, cust_id=p_cust_id,period_code='A';
  
  If (sql%rowcount = 0) THEN
  insert into cust_neg_Table(order_date,cust_id,period_Code,created_date)
     values (p_order_date,p_cust_id,'A',sysdate);
  end if; 
  
  update parent_cust_neg_Table
     set status_code = 'X' where order_date = p_order_Date;
  
  commit;
  
   EXCEPTION
    
    WHEN OTHERS THEN
         dbms_output.put_line(substr('Error '||to_char(SQLCODE)||': '||SQLERRM, 1, 255));
       RAISE;
  
  end;

Tom:

I found in the "some code" block I had this statement to clean up the child table without commiting. I think this may cause a conflict because the autonomous transaction si trying to update/insert into it

delete from cust_neg_table where order_date=v_order_date' and period_code='A'

1. Do you agree?

2. How would you still do the call to the autonmous to insert for one customer/order only (not by category in loop).

3. do you see problem with other stuff.

Tom:

You got me worried by your comment. What do you mean exactly by this.

<pretty much all of it - but none of it >

SQL> exec process_orders;
****  PASS 1 ****
****  PASS 1  ****
Error -60: ORA-00060: deadlock detected while waiting for resource
Error -60: ORA-00060: deadlock detected while waiting for resource
BEGIN process_orders; END;

*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "PROCESS_ORDERS", line 272
ORA-06512: at line 1

Tom:

Excellent comments. I am going to make it modular and clean it up. This is first cut draft actually.

The deadlock error is gone after taking out the delete statement.

The only thing I would like to confirm with you is how would you call the autonomous transaction.

The requirement is to insert one record into neg_cust_table the orders that are flagged negative and update the parent table which has one record for that order.

The issue is that my processing loop sums up the records by order_Date,customer,category and I only need to insert one record if SUM(ORD_QTY) > MONTH_CREDIT.

Was not the code listed above clear enough to show you what the intention was.

Hi Tom,

So you know, the link you mention in your first response of this thread ( http://asktom.oracle.com/~tkyte/unindex/index.html ) is a dead link.

Hi Tom,

I have 2 things mention and get it cleared.

--Could you please explain how its a deadlock error, when there is a unindexed foreign_key in the table (in detail.. u jst said slows down performance and locks)?

-- Also please explain why only a restart will resolve deadlock?

Thanx,

The guy answering these questions is a smug wise ass.

The "smug wiseass" answering these questions is a professional who is here to help other professionals. All he asks is that people communicate their problem in a manner that is CLEAR, CONCISE, and EASILY REPRODUCIBLE.
So, let's pretend you were sending out a cover letter. Would it look like this:
Thnk U 4 the opprtunty to intrvw @ ur co.

Or like this:

Thank you for the opportunity to interview with your company.

Have we become so dependent on texting that we can no longer communicate any other way? Seriously, this is BUSINESS, not a game, not chatting with your buds. When you have a problem you need HELP with, you need to be CLEAR about the problem and what you've done to resolve it so far.

It isn't hard. I've been following this site for almost a decade, and I've never had a problem Tom couldn't assist with. Of course, I'm a professional who understands the importance of communication. Maybe that's the difference.

Your comment is uncalled for. Not sure if you are a regular reader of this forum, but I have been reading Tom's forum since 2003 and it has benefitted me a lot. It has made me a much better DBA.

Not sure how Tom treats it, but to me personally, if someone addresses me as "u", it is derogatory. I feel that these one letter acronyms do not have any place in professional communication.

As a regular reader of this forum, I can say without any hesitation that your comment has offended not only Tom, but many of the readers also.

I hope you will make amends by apologizing.

Tom, on deadlock detection, agree it normally happens in 3 seconds or so. I have a problem at a customer site now where I can see from the trace files it is taking several minutes up to an hour. Clearly I have a problem to sort about why I am getting them but don't understand how it can take a long time to release. AIX & Ora 11.1.0.7.0.

SR 3-3425997691 is the SR we have logged with support looking into this. If we do a very simple two table test case deadlock detection takes up to a minute. In production we are seeing up to an hour at times. The time is measured from the timestamp at the top and bottom of the trace file. If we try the same thing on their test system, exactly same versions of everything as production it does as expected, about 3 seonds. Its a standalone database with dataguard.

Deadlock test scenario

a) First I created two tables geert1 and geert2 and inserted 1 row in each table with the following same values FIELD1 = 'VALUE1', field2 = 1

CREATE TABLE GEERT1
(
FIELD1 VARCHAR2(30),
FIELD2 NUMBER
);

CREATE TABLE GEERT2
(
FIELD1 VARCHAR2(30),
FIELD2 NUMBER
);
b) I then started 2 seperate sqlplus sessions and entered the following update statements:

Session1 : update geert1 set field1 = 'VALUE2', field2 = 2 where field1 = 'VALUE1';
Session 2: update geert2 set field1 = 'VALUE2', field2 = 2 where field1 = 'VALUE1';
Session1 : update geert2 set field1 = 'VALUE2', field2 = 2 where field1 = 'VALUE1'; 
Session2 : update geert1 set field1 = 'VALUE2', field2 = 2 where field1 = 'VALUE1';


Deadlock test with different tracing 

session 1

dcsdba@plloraprd ksh[663]: sql

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Apr 27 14:06:00 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

SQL> set timing on
SQL> update geert1 set field1 = 'VALUE2', field2 = 2 where field1 = 'VALUE1';

1 row updated.

Elapsed: 00:00:00.01
SQL> update geert2 set field1 = 'VALUE2', field2 = 2 where field1 = 'VALUE1';
update geert2 set field1 = 'VALUE2', field2 = 2 where field1 = 'VALUE1'
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource


Elapsed: 00:01:13.41
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.01

session 2

dcsdba@plloraprd ksh[662]: sql

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Apr 27 14:05:47 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

SQL> set timing on
SQL> update geert2 set field1 = 'VALUE2', field2 = 2 where field1 = 'VALUE1';

1 row updated.

Elapsed: 00:00:00.00
SQL> update geert1 set field1 = 'VALUE2', field2 = 2 where field1 = 'VALUE1';

1 row updated.

Elapsed: 00:01:14.10
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00

b) alter system set events '10027 trace name context forever,level 1'

session 1


dcsdba@plloraprd ksh[674]: sql

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Apr 27 14:20:43 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

SQL> set timing on
SQL> update geert1 set field1 = 'VALUE2', field2 = 2 where field1 = 'VALUE1';

1 row updated.

Elapsed: 00:00:00.00
SQL> update geert2 set field1 = 'VALUE2', field2 = 2 where field1 = 'VALUE1';
update geert2 set field1 = 'VALUE2', field2 = 2 where field1 = 'VALUE1'
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource


Elapsed: 00:00:06.56
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00

session 2

dcsdba@plloraprd ksh[674]: sql

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Apr 27 14:20:45 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

SQL> set timing on
SQL> update geert2 set field1 = 'VALUE2', field2 = 2 where field1 = 'VALUE1';

1 row updated.

Elapsed: 00:00:00.00
SQL> update geert1 set field1 = 'VALUE2', field2 = 2 where field1 = 'VALUE1';

1 row updated.

Elapsed: 00:00:04.59
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00

So we observe that the delay appears to be in the amount of time it takes to write the deadlock trace file. Without level 1 enabled for 10027 we get a 250k trace file, with level 1 we get 5k. Oracle support confirmed that the latches are held until the trace file is written. We have just found out the customer has mounted the ORACLE_HOME and trace directories as RW,CIO. On their QA system they have not and we don't see the problem. My assumption would be that with CIO that does not do inode locking we are having problems writing to the file system. Actually I would be worried about possible corruption. They are going to change the QA system to be the same so we can see if we can reproduce the problem.

I did some research into what CIO on AIX really does. Basically apart from bypassing the file system cache as per DIO it also relaxes inode write exclusive locking while a write request is in progress. Thus allows both read and write requests to occur concurrently. With normal file cache or DIO a write request will take an exclusive write lock on the file inode and block any other reads or writes to the file while the write is in progress. So with oracle home / trace area mounted as CIO I would expect the trace I/O to be slower, maybe 15 ms per write to a lun as no cache is used but don't understand why a 250k trace file takes over 1 minute as in the example above. It should only be about 64 writes of 4k thus would expect it to take < 11 seconds. Of course I don't know what write I/O size the shadow process is writing at, if it was using small write requests I guess it could be very slow. Oracle support have closed the call on the basis that its the customers I/O that is the problem.

Of course we have asked the customer to change the mount options ASAP.

Hello,

we are using FOR UPDATE statement on a TABLE in Oracle, which is causing deadlock as we do the updates very frequently with FOR UPDATE statement.

Is there anyway to avoid this situation here. Please suggest.

Hello,

Is locking via SELECT FOR UPDATE atomic operation?
I mean, when different sessions runs at the same time following:

Session 1: SELECT * FROM table_a WHERE x = 10 FOR UPDATE;
Session 2: SELECT * FROM table_a WHERE y = 20 FOR UPDATE;
and table_a has multiple rows with x = 10 and y = 20

or this
Session 1: SELECT * FROM table_a WHERE x IN (1,2,3,4) FOR UPDATE;
Session 2: SELECT * FROM table_a WHERE x IN (4,5,8,1) FOR UPDATE;

Is there a danger of deadlock or it is "deadlock safe" (one of session will wait till all needed rows are available)?

thanks

Hello, thanks for your answer.

Maybe 'atomic' was not correct english word to use, but I was referring only to locking part not full execution of SELECT FOR UPDATE.
(I know that each operation in Oracle is atomic so that it either fully complete or fully fail)

Probably better verbalization of my question would be:

Do I need to implement statement

SELECT * FROM table_a WHERE x IN (4,5,8,1) FOR UPDATE;

manually as

BEGIN
<sort array of x-values>
LOOP y:=<sorted x values>
SELECT FROM table_a WHERE x = y FOR UPDATE; -- manualy lock rows one by one from sorted list
END LOOP;
<process locked rows>
END;

if I want to be sure it will not cause deadlock?

From your answer I concluded I do not need to. Although generally speaking locking in SELECT FOR UPDATE for multiple rows is not 'atomic locking' operation (means that theoretically it can create deadlock), in my example it is safe to write it as "one statement using IN" as long as x has index created, because Oracle will do sort and row lock instead of me.

thanks again

wow ... respect for your professionalism and calmness to answer all the question

is it possible to restore the original link? or repost the content?

http://asktom.oracle.com/~tkyte/unindex/index.html

i'm having some deadlock issues trying to query oracle data from sas, although i dont think its a sas problem. i suspect unindexed foreign keys, or unindexed somethings, and i'd like to be able to take a look at your old solution.

the following link is not working, it says access denied, what's wrong here?

http://tkyte.blogspot.com.au/2009/10/httpasktomoraclecomtkyteunindex.html

Hello,

It happens for me to get deadlock error when 2 UPDATE statement are using same table but different data set.

Is it possible? (I don't have am 100% reproducibility rule.
From time to time, it just occurs and gives me headaches.

There are some scripts which uses same table but each of them are using different data sets.

As far as I know, this error occurs when 2 session are using same data set but it is not my case.

Can yo u tell me it this can happen with different data sets, please?

Thank you,
Ionut P.

Q: How exactly are you sure this happens when the updates affect different rows?
A: The 1st update has a particular condition, hardcoded, while the 2nd one is using a package which does not comply with the condition of the 1st statement.

Q: Can you build a test case to demonstrate this?
A: I am not sure I can do that by I will try to do that.