Miladin Modrakovic’s Blog: Oraclue

April 23, 2009

Detecting Deadlock Source Part 2

Filed under: Troubleshooting — oraclue @ 7:26 pm

This is very simple  deadlock case.Instad of event 10046 ( that I have used in previous blog ) to identify initial TX locks this time
I  have used flashback version query and flashback transaction query .

So here is demo:

First craete table and populate some rows:

create table t(a int primary key);

insert into t select rownum from dba_tables where rownum <5;
commit;

SQL> @ct

Table created.
4 rows created.
Commit complete.

SQL> select * from t;

A
———-
1
2
3
4

Now cause deadlock by running these updates in 2 different session is following order:

Session 1:

update t set a=5 where a=1;
update t set a=6 where a=2;

Session 2:

update t set a=7 where a=3;
update t set a=8 where a=4;

Session 1:

update t set a=9 where a=3;

Session 2:

update t set a=10 where a=1;

Last update will cause deadlock and my session 1 will die:

update t set a=9 where a=3
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

Now when I select from t and see what it looks like:

SQL> select * from t;

A
———-
5
6
7
8

Diagnostic part:

I will first run flashback version query to get transaction ID ( XID ) for my transactions:

SQL> ALTER SESSION SET nls_timestamp_format = ‘DD-MON-YYYY HH24:MI:SS.FF3′;

SQL> l
1  SELECT     VERSIONS_XID
2      ,      VERSIONS_STARTTIME
3      ,      VERSIONS_ENDTIME
4      ,      VERSIONS_STARTSCN
5      ,      VERSIONS_ENDSCN
6      ,      VERSIONS_OPERATION
7      ,      a
8      FROM   miladin.t VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
9     ORDER  BY
10*            VERSIONS_STARTTIME
SQL> /

VERSIONS_XID     VERSIONS_STARTTIME             VERSIONS_ENDTIME               VERSIONS_STARTSCN VERSIONS_ENDSCN V          A
—————- —————————— —————————— —————– ————— – ———-
0A0015008B3D0000 23-APR-2009 14:08:54.000                                               30260800                 U          5
0A0015008B3D0000 23-APR-2009 14:08:54.000                                               30260800                 U          6
05000A00162C0000 23-APR-2009 14:09:00.000                                               30260807                 U          7
05000A00162C0000 23-APR-2009 14:09:00.000                                               30260807                 U          8
23-APR-2009 14:09:00.000                                30260807            3
23-APR-2009 14:09:00.000                                30260807            4
23-APR-2009 14:08:54.000                                30260800            1
23-APR-2009 14:08:54.000                                30260800            2

8 rows selected. (more…)

April 20, 2009

Detecting Deadlock Source Part 1

Filed under: Internals — oraclue @ 7:51 pm

In the case of deadlock oracle will raise ORA-60 error.Trace file is writen by the Global Enqueue Service Daemon (LMD) background process.

Trace file will record sql involved but NOT the SQL  that initialy took the locks.It will record last entries.

So how to resolve issue and get all data involved with deadlocks?

Before I get to that point let me create simple deadlock case:

create table test(a int primary key);

insert into test select rownum from dba_tables where rownum <9;
commit;
Session 1:

update test set a=9 where a=1;
update test set a=10 where a=2;

session 2:

update test set a=11 where a=3;
update test set a=12 where a=4;

session 1:

update test set a=25 where a=8;
update test set a=13 where a=3;
session 2:

update test set a=22 where a=6;
update test set a=14 where a=1;

To troubleshoot deadlock I have create after logon trigger on database.
Although I can place all these commands together ( new option with events ) just for easy read I have used execute immediate for every command.
I have used new events syntax in 11g so  event 10046 is sql_trace, ORA-60 is deadlock.I have posted it on previous blogs.

For HANGANALYZE level 4 is used.This level dumps blockers.

Bellow is hanganalyze level description:

10     Dump all processes (IGN state)
5      Level 4 + Dump all processes involved in wait chains (NLEAF state)
4      Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)
3      Level 2 + Dump only processes thought to be in a hang (IN_HANG state)
1-2    Only HANGANALYZE output, no process dump at all

and  trigger:

CREATE OR REPLACE TRIGGER SYS.TRACE_MILADIN_DEADLOCKS
after logon on database
begin
if user like ‘MILADIN’ then
execute immediate ‘alter session set timed_statistics=true’;
execute immediate ‘alter session set max_dump_file_size=unlimited’;
execute immediate ‘alter session set tracefile_identifier=”miladin_deadlock”’;
execute immediate ‘alter session set events ‘’sql_trace  wait=true, bind=true,plan_stat=all_executions,level=12”’;
execute immediate ‘alter session set events ”deadlock trace name hanganalyze_global level 4,forever”’;
execute immediate ‘alter session set events ”deadlock trace name systemstate level 266,lifetime 1”’;
execute immediate ‘alter session set events ”deadlock trace name processstate level 10,forever”’;
end if;
end;
/

(more…)

Blog at WordPress.com.