So I guess there is no definitive way to determine the sequence of events that led up to the deadlock?

Creating an index on A and B has helped, but I don't KNOW that the user has re-enacted the sequence of events that created the deadlock in the first place.

Oh, I forgot to mention that transactions involving updating/deleting the primary key of X do not manipulate A or B directly.

I looked up A and B in dba_objects. That's how I was able to create indexes on them and tell you their only relationship is that both have X as a parent. Learning this information only made the puzzle harder because the two tables have nothing to do with one another. One is a history of customer activities the other a list of purchase orders (made by the company). Furthermore the site experiencing the problem does not have the purchase order module for the app.

Given this scenario, I'm having a hard time figuring out what has to happen to cause the deadlock.

>The transactions that update/delete the parent table DO >manipulate A and B -- they LOCK them!

So if I have two sessions going and both try to update the pk of X this could cause a deadlock? Doesn't Oracle lock A and B in the same order every time? If so, wouldn't this just cause a block?

It's a number(9) and the PK. The only other column in X is a blob.

If there is a deadlock, a log file is created. However, it is not easy to read this.

Is there anyway to write an error log from the application for the following situations :

1. The current session waited for a record lock that is being held by another session and during timeout, is it possible to log which user, what sql statement locked the record?

2. The current session got into a deadlock and is it possible to clearly know what SQL statements made this deadlock?

Note that in 9i, the other statement(s) involved in the
deadlock ARE written to the trace file.

-Mark

Tom,

Lets say there are two applications A B. A and B will be doing DML s on a set of tables, they doin't know the order. they will be performing randomly. But lets say the operations should be efficient. i.e both the users expect to see results with no blocks, deadlocks etc .. ex: both want to update information related to a record (same pk for both). Assume it is a competition for people with quick and fast fingers and hands. Now, what should application A and B use?

select for update tablename?
select for update
select for update nowait?

Please clarify. Whats the difference between the following?

select for update tablename?
select for update
select for update nowait?

what happens if nothing is used (just select statements).

Tom,

1) You gave the following sql to query:

The transactions that update/delete the parent table DO manipulate A and B
directly -- they LOCK them!


You can get a little more info by taking:

TM-000010da-00000000 11 12 SSX 33 31 SSX
TM-00000e72-00000000 33 31 SX 11 12 S

and then

select object_name, object_type from all_objects
where object_id in ( to_number( '10da', 'xxxx' ), to_number( '0e72', 'xxxx' ) );

One session was waiting on TM-10da, the other on TM-0e72. That'll tell you the two tables involved here.

--------------------------------

My trc file has this:


---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-001f0058-00019e07 18 75 X 35 55 X
TX-001e0035-00018407 35 55 X 18 75 X
session 75: DID 0001-0012-00000002 session 55: DID 0001-0023-00000002
session 55: DID 0001-0023-00000002 session 75: DID 0001-0012-00000002
Rows waited on:
Session 55: obj - rowid = 0000AF44 - AAAK9EAAJAACYOxAAM
Session 75: obj - rowid = 0000AF21 - AAAK8hAAJAABqyWAAJ


Could you please give the sql for this (Note: the TX format is different in my case).



2) There are LOTS of sql statements in trc file. The trc file definitely occured due to a deadlock.

a) Should we consider just this?

DEADLOCK DETECTED
Current SQL statement for this session:

sql statement here ....

b) Is this the statement that would've rolled back?

c) Which session does that belong to?

d) Could the other sqls in trc file be ignored? Otherwise, is there a way to analyze trc file (there is lots of other data in that)?


3) Normally what happens when a deadlock occurs, suppose that one application is gui, other is process based (java, c etc). I think if a gui user sees a deadlock, he would know what happened, if a java application faces it, what would it do? Should it repeat? Would it skip? Is there a chance for lost action (it may have to do an update or insert a record and because of deadlock it might ignore it?) What happens if it is in a loop (a loop of inserts or updates and the deadlock occurs ).

Please clarify

Hi Tom,

I am having following deadlock details

update ptdt31 set a1050 = 'x' where a0090 = :b1 and a1010 = :r1
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-0002002d-000dcbcd 13 8 X 8 7 X
TX-00020020-000dcaf6 8 7 X 13 8 X
session 8: DID 0001-000D-00000002 session 7: DID 0001-0008-00000002
session 7: DID 0001-0008-00000002 session 8: DID 0001-000D-00000002
Rows waited on:
Session 7: obj - rowid = 00066ACB - AABmrLAAGAAAGdeAAC
Session 8: obj - rowid = 00066ACC - AABmrMAAFAAAHNyAAD
===================================================

But If I am trying to do following as explained by you in this thread
( trying to get object no from hexadecimal to decimal )

21:52:24 atlas@ORACLEDB1> select to_number('0002002D', 'xxxxxxxx') from dual;

TO_NUMBER('0002002D','XXXXXXXX')
--------------------------------
131117


23:13:14 atlas@ORACLEDB1> select dbms_rowid.rowid_object('AABmrLAAGAAAGdeAAC') from dual

DBMS_ROWID.ROWID_OBJECT('AABMRLAAGAAAGDEAAC')
---------------------------------------------
420555

21:47:09 atlas> select object_id, object_name from user_objects where object_id=420555;

OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
420555
PTDC31

My Questions are

1. Why I am getting different Object id when converting to number ? I am getting correct object id from dbms_rowid.

2. In deadlock I know which two sessions involved in a deadlock but I come to know this error after some hours only. But at this time most of sessions already been logged of. In this situation how can I get more session details later on ?

3. Suppose there are 2 sessions involved in deadlock, then oracle is killing which session and on what basis ?

4. I am having thousands of Pro-Cobol programs running having various SQL's. ( Written by external vendor, and I am having only executables of these programs ) Can I know which are Pro-Cobol programs were involved in a deadlock so I can take care of deadlock ?

5. From where I can get more details about another session that locked a row ?( In my case I had tried to update two tables for deadlock ptdt31, ptdc31 . I had given following two commands in two SQL session windows
update ptdt31 set a1050 = 'x' where a0090 = :b1 and a1010 = :r1
update ptdc31 set a1050 = 'x' where a0090 = :b1 and a1010 = :r1 and a6000 = 025;

but I can see only one update in deadlock ). If I can more information about second sql statement in my case update ptdc31 ... then it will be easy to find out sequence of activities.

thanks for explaining each of these questions in detail to clear my understanding about deadlock.

regards & thanks
pjp

If the application doesn't notice the deadlock error and continue to do its work. Suppose two java apps are inserting 100 rows each ... ids 100-200. Lets say deadlock resulted at id 150. When one java application rollbacks it rolls back until last commit. If it ignores the error, continues to insert what happens? I see the sessions are deadlocked, but from the application point, is it possible that application b misses adding somerows i.e 150 to 160, skip them and later commit. Then application a inserted 100 rows and application b 90

Tom,

Thanks, but the application is noticing deadlocks and logging them, however I am not sure how it is behaving when it encountered deadlock. The sessions deadlock, what happens when the application skip those records and insert rest. From the example described above, is it possible that it skip ids from 150 to 160? Then role does a session deadlock play here? Please clear the confusion

Tom,
In Parag's question above, please see:
23:13:14 atlas@ORACLEDB1> select dbms_rowid.rowid_object('AABmrLAAGAAAGdeAAC')
from dual

DBMS_ROWID.ROWID_OBJECT('AABMRLAAGAAAGDEAAC')
---------------------------------------------
420555

21:47:09 atlas> select object_id, object_name from user_objects where
object_id=420555;

The dbms_rowid.rowid_object returns the data object number. The query will fail to return the correct object if the object_id and data_object_id are different. Should we not write as:

21:47:09 atlas> select object_id, object_name from user_objects where data_object_id=420555;


I did the following when faced with deadlock issue:
<start>
Rows waited on:
Session 37: obj - rowid = 00001E69 - AAAB8wAAKAAAhMEAAg
(dictionary objn - 7785, file - 10, block - 135940, slot - 32)
Session 61: obj - rowid = 00001E5E - AAAB8nAAGAAALFYABC
(dictionary objn - 7774, file - 6, block - 45400, slot - 66)
Information on the OTHER waiting sessions:

</end>
select * from user_objects where object_id= to_number('00001E69','xxxxxxxx');

select * from user_objects where object_id= to_number('00001E5E','xxxxxxxx');


select * from <Table1> where rowid=chartorowid('AAAB8wAAKAAAhMEAAg');

select * from <Table2> where rowid=chartorowid('AAAB8nAAGAAALFYABC');

This gives me the table name and rows involved in the deadlock. This combined with the queries which both sessions were executing at the time deadlock occurred tells me what was going on. Please let me know if this approach is correct.
Thanks

Tom,

What does it mean when we have a 'TX' lock where 'Rows waited on' = 'no row'?

.............................

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00150015-000c37e7 15 54 X 20 67 S
TX-0019002c-000a171a 20 67 X 27 71 S
TX-00130015-000c37d8 27 71 X 15 54 S
session 54: DID 0001-000F-0000540F session 67: DID 0001-0014-00005795
session 67: DID 0001-0014-00005795 session 71: DID 0001-001B-000053F9
session 71: DID 0001-001B-000053F9 session 54: DID 0001-000F-0000540F
Rows waited on:
Session 67: no row
Session 71: no row
Session 54: no row

.............................

Thank you,

Robert.

Tom,
We are constantly getting deadlocks in our database. There are two questions:
(1.) The alert log is sometimes mentioning about a trace file in udump, but I do not see the existence of the trace file:
For example this is what the alert file indicates:
Tue Jan 4 09:10:32 2005
ORA-000060: Deadlock detected. More info in file /s00/orms/app/oracle/admin/omsprd/udump/ora_8590_omsprd.trc.
However, the file itself is not there:
pfuse(omsprd)>/s00/orms/app/oracle/admin/omsprd/udump$ ll ora_8590*.trc
ora_8590*.trc not found
Why is this abnormality? Although at times I am getting the right trace files.
(2.) I created the log, seq and fgac_violation
trigger on this. However, the log file is empty. Why is it not capturing the sqlerror ora-60?
I have tried to simulate the artificial deadlock and it is captured during my test, but why not by default? I have created the trigger under System schema.
My database version is 8.1.7.4.0
Regards,

Tom,
In one of our batch jobs, we get ora-0060 error once in a while. The trace file clearly shows two sessions blocking each other and waiting for each other causing the deadlock. The batch job is run as a single user session. This is an .exe file which connects to the database and runs a database stored procedure. All the processing happens within the stored procedure code.

I have explored all possibilities with the application team and they firmly confirmed that nothing else is running when they get the ora-0060. Guess my stupid question is that is it possible to get ora-0060 with a single user session? All examples that I have seen for deadlocks show at least two user sessions required to create a deadlock.

Thanks...

<quote>
… is it possible to get ora-0060 with a single user session?
</quote>

Deadlocks are between transactions ... not between sessions. Typically the transactions will belong to different sessions ... but think one session with an autonomous transaction ... that could deadlock with the _main_ transaction in the session.

So ... do you actually see different session ids?
And if not, do you use autonomous transactions in your batch?


flip@FLOP> create table a ( id number );

Table created.

flip@FLOP> insert into a values (1);

1 row created.

flip@FLOP> commit;

Commit complete.

flip@FLOP> update /* main-tx */ a set id=id+1;

1 row updated.

flip@FLOP> declare
2 pragma autonomous_transaction;
3 begin
4 update /* auto-tx */ a set id=id+1;
5 commit;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

And the trace file:

*** SESSION ID:(17.11579) 2005-06-24 14:13:01.000
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE /* auto-tx */ a set id=id+1
----- PL/SQL Call Stack -----
object line object
handle number name
66A7603C 4 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-00030026-000090b7 12 17 X 12 17 X
session 17: DID 0001-000C-00000002 session 17: DID 0001-000C-00000002

Hallo,
under circumstances is the deadlock in our application possible.
It's TX- lock (row lock).

Session 1 :
select akt_stand_nr from tbl_SID where sid= 4055000000021
for update wait 3
-- 1 row selected

Session 2 :
select akt_stand_nr from tbl_sid where sid = 4055000000022
for update wait 3
-- 1 Rows selected

Session 1 :
select akt_stand_nr from tbl_sid where sid = 4055000000022
for update wait 3
-- Session waits 3 seconds on commit in session 2.

-- During these 3 seconds.
Session 2 :
select akt_stand_nr from tbl_SID where sid = 4055000000021
for update wait 3
-- Deadlock

The deadlock is always on the same Select in both sessions.
What is your opinion , is it correct to handle "SELECT FOR UPDATE wait 3"
with exception:
DECLARE

EXC_DEADLOCK EXCEPTION;
PRAGMA EXCEPTION_INIT (EXC_DEADLOCK, -00060);
...
BEGIN
BEGIN
select akt_stand_nr into v_dummy from TBL_SID where sid = v_sid
for update wait 3;
EXCEPTION
WHEN EXC_DEADLOCK
THEN
dbms_output.put_line('Deadlock! ');
SELECT akt_stand_nr into v_dummy from tbl_sid where sid = v_sid;
END;
................
END;

I saw, that deadlock comes in our sample in session 1, and not in session 2.
Is it always so ? Makes it influence on our solution ?

Thanks in advance

Hi Tom,

i was thinking that the 'for update clause' should never be
involved in a deadlock situation , because we told Oracle our intention to lock the rows and therefore any other session which selects the same row(s) will immediately get -54 ,
or am i missing something here .

Thanks in advance.

oracle 9.2.0.6
Our web application uses only one single database user..then different sessions open for the same user....
Is it fare to say..that to avoid deadlocks..every update statement should not be just an update statement..but it should be "select for update no wait"..and then followed by a commit?

Thanks

oracle ver 9206
whether I do select for update or not...I still get ora-00600.
so what is the advantage of using "Select for update"..
What is our goal here? Do we want to avoid these deadlocks
if at all possible?
I am sorry..just really lost on this issue..
trying to make a decision whether to change our existing web app code..Here is my script with select for update:

--session 1

9.2.0.6.0(unix) testuser@dev> create table t1 (col1 integer, col2 integer);

Table created.

9.2.0.6.0(unix) testuser@dev> insert into t1 values (1,2);

1 row created.

9.2.0.6.0(unix) testuser@dev> commit;

Commit complete.

9.2.0.6.0(unix) testuser@dev> insert into t1 values (5,4);

1 row created.

9.2.0.6.0(unix) testuser@dev> commit;

Commit complete.

9.2.0.6.0(unix) testuser@dev>

9.2.0.6.0(unix) testuser@dev> select * from t1
2 where col1 = 1;

COL1 COL2
---------- ----------
1 2

9.2.0.6.0(unix) testuser@dev> update t1
2 set col2 = 3
3 where col1 = 1;

1 row updated.



--session 2
9.2.0.6.0(unix) testuser@dev> select * from t1
2 where col1 = 5 for update;

COL1 COL2
---------- ----------
5 4

9.2.0.6.0(unix) testuser@dev> update t1
2 set col2 = 6
3 where col1 = 5;

1 row updated.

9.2.0.6.0(unix) testuser@dev> update t1
2 set col2 = 6
3 where col1 = 1;

--session 1

9.2.0.6.0(unix) testuser@dev> update t1
2 set col2 = 3
3 where col1 = 5;


--session 2 (gives ora-00600)

update t1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource


9.2.0.6.0(unix) testuser@dev> rollback;

Rollback complete.

9.2.0.6.0(unix) testuser@dev>

--session 1 (update completes)

9.2.0.6.0(unix) testuser@dev> update t1
2 set col2 = 3
3 where col1 = 5;

1 row updated.

9.2.0.6.0(unix) testuser@dev> rollback;

Rollback complete.

Tom,

I just read the "locking and concurrency" chapter in your book..funny..there is whole section on deadlocks...why they occur and what to do..
and when I ran your query to find un-indexed Foriegn key columns...the issue jumped right out...got lot of indexing to do...

Thanks..a bunch...

Oracle Version 9.2.0.6
In your book (in locking and concurrency chapter's deadlocks section)...you
say that when the child table does not have an index on the foreign key..the whole table gets locked even if there is a a single row deleted in the parent table..
I am not seeing this behavior in the test case..may be that particular key..but not the entire child table:

9.2.0.6.0(unix) testuser@dev> create table p (x int primary key);

Table created.

9.2.0.6.0(unix) testuser@dev> create table c (y references p);

Table created.

9.2.0.6.0(unix) testuser@dev> insert into p values (1);

1 row created.

9.2.0.6.0(unix) testuser@dev> insert into p values (2);

1 row created.

9.2.0.6.0(unix) testuser@dev> insert into c values (1);

1 row created.

9.2.0.6.0(unix) testuser@dev> commit;

Commit complete.

9.2.0.6.0(unix) testuser@dev> delete from p
2 where x = 2;

1 row deleted.


--session 2


9.2.0.6.0(unix) testuser@dev> delete from c
2 where y = 1;

1 row deleted.

/***************
Why does this succeed?
*/

9.2.0.6.0(unix) testuser@dev> rollback;

Rollback complete.

9.2.0.6.0(unix) testuser@dev> update c
2 set y = 1;

1 row updated.

/***************
Why does this succeed?
*/

9.2.0.6.0(unix) testuser@dev> update c
2 set y = 2;

/***************
this gets blocked..(as expected) since parent key 2 is being deleted..in
the other session...
and succeeds only after a roll back in the session 1
*/

Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
....

Current SQL statement for this session:
INSERT INTO Customer_orders (OD_ID, CUSTOMER_ID, order_type, OD_AMOUNT, is_adsl, is_van, OD_DATE, OD_PAID, AMOUNT_IF_WIN) VALUES (:B7 , :B6 ,
:B5 , :B4 , :B3 , :B2 , SYSDATE, 0, :B1 )
----- PL/SQL Call Stack -----
object line object
handle number name
601518820 431 package body appuser.SPKGB23
5fd41aa30 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-00860003-000fddb1 522 1481 X 735 1143 X
TX-00660002-001c4be5 735 1143 X 522 1481 S
session 1481: DID 0001-020A-00000005 session 1143: DID 0001-02DF-00037DBD
session 1143: DID 0001-02DF-00037DBD session 1481: DID 0001-020A-00000005
Rows waited on:
Session 1143: obj - rowid = 000053B1 - AAAQhQAGVAAATE2AAr
(dictionary objn - 21425, file - 405, block - 78134, slot - 43)

<........ this is pointing to one row in Table acc_balance>

Session 1481: obj - rowid = 00010D2F - AAASZ/AI9AAHEnJAAA
(dictionary objn - 68911, file - 573, block - 1853897, slot - 0)

<....... this is pointing to an index on table Customer_orders:
CREATE INDEX co_idx1 ON Customer_orders (CUSTOMER_ID, is_adsl , OD_ID, OD_AMOUNT)
>

Information on the OTHER waiting sessions:
Session 1143:
pid=735 serial=47647 audsid=198732495 user: 10/appuser
O/S info: user: apache, term: , ospid: 24564, machine: apsb01
program:
client info: dddxyz

Current SQL Statement:
UPDATE acc_balance SET acc_balance = acc_balance + T.OD_PAID where customer_id IN (select customer_id from tmp_pending)
End of information on OTHER waiting sessions.

No, index was created long time ago.

Can you think of other reason why the index was involved in a deadlock ?

Just got a deadlock situation involving row in a table T1, and an entry in an index
of another table SP_ORDERS.

<...................................>
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00860001-000fdda1 523 795 X 735 1143 X
TX-00660002-001c4be5 735 1143 X 523 795 S
session 795: DID 0001-020B-00000005 session 1143: DID 0001-02DF-00037DBD
session 1143: DID 0001-02DF-00037DBD session 795: DID 0001-020B-00000005
Rows waited on:
Session 1143: obj - rowid = 000053B1 - AAAQhQADNAAAPVTAA/
(dictionary objn - 21425, file - 205, block - 62803, slot - 63)
Session 795: obj - rowid = 00010D2F - AAASZ/AI+AAHdb4AAA
(dictionary objn - 68911, file - 574, block - 1955576, slot - 0)
Information on the OTHER waiting sessions:
Session 1143:
pid=735 serial=47647 audsid=198732495 user: 10/appuser
O/S info: user: apache, term: , ospid: 24564, machine: antplsb01
program: /www/content/securemgmt/tes
client info: spprocessing
application name: sporder plsql, hash value=121354523
<.........................................>


The following query proved that the index is involved in the deadlock:

select object_name from user_objects
2 where data_object_id in (select
dbms_rowid.rowid_object('AAASZ/AI+AAHdb4AAA') from dual)
3 /

OBJECT_NAME
---------------------------------------------------------------------------------
-----------------------------------------------
SP_ORDERS_IDX001


select customer_id from sp_orders where
rowid=chartorowid('AAASZ/AI+AAHdb4AAA');
select customer_id from SP_ORDERS where rowid=chartorowid('AAASZ/AI+AAHdb4AAA')
*
ERROR at line 1:
ORA-01410: invalid ROWID


It seems that the rowid of the index released from the deadlock trace file is
not the same as the corresponding table's rowid. INDEX entry has its own rowid,
correct?

IF so, How do I navigate from INdex entry's rowid to the corresponding data's
rowid?

Suppose there is a deadlock due to TX lock - 1 session doing a delete and another session trying to update the same row, from the deadlock trace file, how can one identify which transaction(session) went through and which one(session) got Ora-60 ?

thanks
Anto

TX-0005000e-006b00b6 47 45 X 17 138 X
TX-00030027-006db000 17 138 X 47 45 X

for the above deadlock(copied from deadlock trace file)

Is there any way we can identify(from the full deadlock trace file), which session got Ora 60 and which one went through ?

thanks
Anto

I think I got it - we can identify that using the unix ospid(which is again part of trace file name) whose SQL will be generally the first listed in the trace file and which will be the one which rolled back(or do whatever is mentioned in the exception handler).

Am I right in my assumption here ?

For the below portion of trace file :

The session which detected the deadlock is the one used by ospid 4519 and which will be the one which got Ora 60 and got rolled back (or do whatever is mentioned in the exception handler) ?

trace file name : instance_name_ora_4519.trc

Redo thread mounted by this instance: 1
Oracle process number: 47
Unix process pid: 4519, image: oracle@sstm2155por (TNS V1-V3)

*** 2006-01-05 19:27:38.512
*** SESSION ID:(45.2392) 2006-01-05 19:27:38.510
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE table_name set
(
-- project_id,
-- trade_id,
-- busent_id,
-- product_id,
-- customer_id,
-- month_id,
-- reporting_code,
-- trade_date,
last_updated,
revenue_forecast_USD,
...


thanks
Anto

Dear Tom.

I have package function which updates the same table for 2 ID's:

create table t1 ( x1 int primary, y1 int);

function do_update(Prm_x1_1, Prm_x1_2, Prm_y1)
IS
BEGIN
UPDATE t1
SET y1 = Prm_y1
WHERE x1 = Prm_x1_1;

UPDATE t1
SET y1 = Prm_y1
WHERE x1 = Prm_x1_2;

END;

So, clent application starts transaction, calls this funtion and then commits. If exception rolls back;

As you see there is distinct values for x1 field.

Sample data:

x1 y1
1 234
2 968
3 454
4 96864
5 87454
6 9549
7 2154
8 5845

Now
user1 calls function with Prm_x1_1 = 1 and Prm_x1_2 = 2.
user2 calls function with Prm_x1_1 = 2 and Prm_x1_2 = 1.

I thnik there can be deadlock if opearation sequence as below:

1. (user 1)update with Prm_x1_1 = 1 -> Row with x1=1 locked
2. (user 2)update with Prm_x1_1 = 2 -> Row with x1=2 locked
3. (user 1)tries to update with Prm_x1_1 = 2 -> but waits...
4. (user 2)tries to update with Prm_x1_1 = 1-> but waits and deadlock.

So, how can avoid from this deadlock scenario. I have to execute that function.

It looks like bank sistem:
One user gets money from account1(x1=1) to account2(x1=2), another user gets money from account2 to account1.

Thanks in advance.

Tom, thanks for your time.
May be I missed something.
AS banking system below:
One user gets money from account1(x1=1) to account2(x1=2), another user gets money from account2 to account1.

When User1 execute funtion, it must update row where account_id1 resides, then update row with account_id2.

So, User2 execute funtion, it must update row where account_id2 resides, then update row with account_id1.

It is simply may cause deadlock. Have you any solution to avoid from it in such statement?

Thansk in advance

Dear Tom.

1. As I understand, you mean before update of account_id,
I have to check account_id1 < account_id2 (lets say I select assending order) then 1st update account_id1, then account_id2.
2. If I am right, then user2 may wait for user1 if account_id1 is same for both users. Do you think this normal?

Dear Tom,

I just sent you a question about a dead lock in autonomous transaction. This is the trace file I got

*** 2006-09-22 12:09:59.396
*** SESSION ID:(238.77) 2006-09-22 12:09:59.362
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE SPS_RECORD_COCLI SET ERROR_CODE=:b1 WHERE IDE = :b2
----- PL/SQL Call Stack -----
object line object
handle number name
d51b4a24 673 package body SPS.PCK_SPS_COCLI_REPLAY
d51b4a24 161 package body SPS.PCK_SPS_COCLI_REPLAY
d6c1c7a4 12 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-000b005b-003cb8e1 222 238 X 222 238 S
session 238: DID 0001-00DE-00000002 session 238: DID 0001-00DE-00000002
Rows waited on:
Session 238: no row
===================================================
PROCESS STATE
-------------
Process global information:
process: e7a97e8c, call: da1212ac, xact: e826f2ac, curses: e7b6a230, usrses: e7b6a230
----------------------------------------
SO: e7a97e8c, type: 1, owner: 0, pt: 0, flag: INIT/-/-/0x00
(process) Oracle pid=222, calls cur/top: da1212ac/da12102c, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 35
last post received-location: kcbzww
last process to post me: e7a987e0 2 0
last post sent: 3749892204 0 13
last post sent-location: ksasnd
last process posted by me: e7a6d8b4 1 2
(latch info) wait_event=0 bits=10

Could you please help me to understand it

Thanks in advance

Tom,
Using 10.2.0.3, I have recreated a deadlock using the example in chapter 3 of your Expert one-on-one as a guide, where there is no index on the foreign key.

SessionA:
  LAB-SQL>create table parent (x int primary key);
  Table created.
  LAB-SQL>create table child (y references parent);
  Table created.
  LAB-SQL>create table sometable (z int);
  Table created.
  LAB-SQL>insert into sometable values(1);
  1 row created.
  LAB-SQL>insert into sometable values(2);
  1 row created.
  LAB-SQL>insert into parent values(1);
  1 row created.
  LAB-SQL>insert into parent values(2);
  1 row created.
  LAB-SQL>insert into child values(2);
  1 row created.
  LAB-SQL>commit;
  Commit complete.
  LAB-SQL>update parent set x=3 where x=1;
  1 row updated.

SessionB:
  LAB-SQL>update sometable set z=2 where z=1;
  1 row updated.
  LAB-SQL>update child set y=1 where y=2;
  <blocked>

SessionA:
  LAB-SQL>update sometable set z=3 where z=1;
  <blocked>

SessionB:
  update child set y=1 where y=2
         *
  ERROR at line 1:
  ORA-00060: deadlock detected while waiting for resource

I ran your script for finding the unindexed foreign key and it does show table:CHILD column:Y.

My question is: How can the initial block (of SessionB) be avoided by creation of an index on the child table's column Y?

I did create an index after the 'create table child' statement above when repeating this (create index child_y_ind on child (y) online;), but the block still occurred (and obviously the deadlock afterwards).  And your script no longer showed the child table as being unindexed.

I think I'm just misunderstanding the correct syntax of the index creation.

Thanks for your time.

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-000a0007-0009580a 49 2688 X 41 2909 S
UL-401a2744-00000000 41 2909 X 49 2688 X
session 2688: DID 0001-0031-0000007C session 2909: DID 0001-0029-00000016
session 2909: DID 0001-0029-00000016 session 2688: DID 0001-0031-0000007C
Rows waited on:
Session 2909: no row
Session 2688: no row
Information on the OTHER waiting sessions:
Session 2909:
pid=41 serial=2135 audsid=56938917 user: 44/APPS
O/S info: user: ojahmeap, term: , ospid: 12523, machine: japap20w.ydc.fujixerox.co.jp
program:
client info: 108
application name: XXMPOKDS001F, hash value=3256021263
action name: FRM:FXY15E1:(FX_A)受入担当閠, hash value=3146057035
Current SQL Statement:

SELECT DELIVERY_ID,NAME,PLANNED_FLAG,STATUS_CODE,INITIAL_PICKUP_DATE,INITIAL_PICKUP_LOCATION_ID,ULTIMATE_DROPOFF_LOCATION_ID,ULTIMATE_DROPOFF_DATE,CUSTOMER_ID,INTMED_SHIP_TO_LOCATION_ID,POOLED_SHIP_TO_LOCATION_ID,FREIGHT_TERMS_CODE,FOB_CODE,FOB_LOCATION_ID,WAYBILL,ACCEPTANCE_FLAG,ACCEPTED_BY,ACCEPTED_DATE,ACKNOWLEDGED_BY,CONFIRMED_BY,ASN_DATE_SENT,ASN_STATUS_CODE,ASN_SEQ_NUMBER,REASON_OF_TRANSPORT,DESCRIPTION,GROSS_WEIGHT,NET_WEIGHT,WEIGHT_UOM_CODE,VOLUME,VOLUME_UOM_CODE,ADDITIONAL_SHIPMENT_INFO,PORT_OF_DISCHARGE,BOOKING_NUMBER,COD_AMOUNT,COD_CURRENCY_CODE,SERVICE_CONTRACT,COD_REMIT_TO,COD_CHARGE_PAID_BY,PROBLEM_CONTACT_REFERENCE,BILL_FREIGHT_TO,CARRIED_BY,PORT_OF_LOADING,ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,TP_ATTRIBUTE_CATEGORY,TP_ATTRIBUTE1,TP_ATTRIBUTE2,TP_ATTRIBUTE3,TP_ATTRIBUTE4,TP_ATTRIBUTE5,TP_ATTRIBUTE6,TP_ATTRIBUTE7,TP_ATTRIBUTE8,TP_ATTRIBUTE9,TP_ATTRIBUTE10,TP_ATTRIBUTE11,TP_ATTRIBUTE12,TP_ATTRIBUTE13,TP_ATTRIBUTE14,TP_ATTRIBUTE15,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,REQUEST_ID,GLOBAL_ATTRIBUTE_CATEGORY,GLOBAL_ATTRIBUTE1,GLOBAL_ATTRIBUTE2,GLOBAL_ATTRIBUTE3,GLOBAL_ATTRIBUTE4,GLOBAL_ATTRIBUTE5,GLOBAL_ATTRIBUTE6,GLOBAL_ATTRIBUTE7,GLOBAL_ATTRIBUTE8,GLOBAL_ATTRIBUTE9,GLOBAL_ATTRIBUTE10,GLOBAL_ATTRIBUTE11,GLOBAL_ATTRIBUTE12,GLOBAL_ATTRIBUTE13,GLOBAL_ATTRIBUTE14,GLOBAL_ATTRIBUTE15,GLOBAL_ATTRIBUTE16,GLOBAL_ATTRIBUTE17,GLOBAL_ATTRIBUTE18,GLOBAL_ATTRIBUTE19,GLOBAL_ATTRIBUTE20,CONFIRM_DATE,SHIP_METHOD_CODE,DOCK_CODE,DELIVERY_TYPE,CARRIER_ID,CURRENCY_CODE,ORGANIZATION_ID,LOADING_SEQUENCE,LOADING_ORDER_FLAG,NUMBER_OF_LPN,BATCH_ID,SOURCE_HEADER_ID,HASH_VALUE,FTZ_NUMBER,ROUTED_EXPORT_TXN,ENTRY_NUMBER,ROUTING_INSTRUCTIONS,IN_BOND_CODE,SHIPPING_MARKS,SERVICE_LEVEL,MODE_OF_TRANSPORT,ASSIGNED_TO_FTE_TRIPS,AUTO_SC_EXCLUDE_FLAG,AUTO_AP_EXCLUDE_FLAG,AP_BATCH_ID,TP_DELIVERY_NUMBER,TP_PLAN_NAME,VENDOR_ID,EARLIEST_PICKUP_DATE,LATEST_PICKUP_DATE,EARLIEST_DROPOFF_DATE,LATEST_DROPOFF_DATE,IGNORE_FOR_PLANNING,PARTY_ID,ROUTING_RESPONSE_ID,RCV_SHIPMENT_HEADER_ID,ASN_SHIPMENT_HEADER_ID,SHIPMENT_DIRECTION,SHIPPING_CONTROL,WV_FROZEN_FLAG,HASH_STRING,ITINERARY_COMPLETE,DELIVERED_DATE FROM WSH_NEW_DELIVERIES WHERE DELIVERY_ID = :b1
End of information on OTHER waiting sessions.

Could you please any way to identify the problem?
My difficulty is:
1. No rows information on both sessions
2. The current SQL is a SELECT statement not an insert/update/delete.
3. No call stack informations to help me locate the pl/sql code.

Thanks,
William

Now I know:
1. The session raised ora-60 is 2688 which is updating something not commited and wait for updating a user lock.
2. The other session is 2909 which updating a user lock not commited and wait for a share locking on the table which session 2688 is updating.

How to find the table session 2688 is updating?
Why the current SQL of session 2909 is an update statement not a statement like 'lock table xxx in share mode'?

Thanks,
William

Hi Tom,

I want to get tablespace information from dba_data_file, dba_free_space and dba_segment tables for my dot net screen. I want to keep that queries in Oracle package and call it from Dotnet. But some DBAs dont like that. Can you please advice me whether the query can be in Dot net or in Oracle package.