Re: Deadlock and ORA-0600 ocurred yesterday

Mladen,

Update statement doesn't support "order by" and I almost never seen anyone write sorted update statements like
update (select * from t where b=:b order by rowid) set d=:d So you can easily get deadlock with simple concurrent updates like "update t set d=:d where b=:b" and "update t set d=:d where c=:c"

ps. just about a 5-6 months ago I faced deadlocks in the internal CTX indexes sync functions

On Sun, Mar 25, 2018 at 3:53 AM, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> Trust the query for what? Deadlocks leave trace files. Get someone to send
> you the trace file and the story is over. With the query below, you will
> get the list of all tables modified within 6 PM and 7 PM today. BTW, a good
> practice is to flush the monitoring info to the table, using DBMS_STATS
> call. How will that help you with the deadlock? Another way would be to
> query ASH (active session history) and find all sessions with blocking id
> not null between 6 PM and 7 PM. All you need to locate is a closed graph: A
> is waiting on B, B is waiting on C and C is waiting on A. Every deadlock is
> a closed graph. That will give you SQL statements involved and presumably
> help you to solve the problem. The problem with deadlocks is that they are
> usually application logic errors. It is not possible to have deadlock is
> all the applications lock row in the tables in the same order. If all
> applications first lock table A, then table B and then table C, there will
> be no deadlocks. However, if some applications are locking tables in
> different orders, you may encounter a deadlock.
>
> Regards
>
> On 03/24/2018 05:54 PM, Eriovaldo Andrietta wrote:
>
> Hi,
>
> There were a error ORA-0600 yesterday caused by a deadlock.
>
> I don´t have acesss to the alert of the database server.
>
> Can I trust on this query ?
>
> select table_name,inserts,updates,deletes,truncated,timestamp
> from dba_tab_modifications
> where timestamp > TO_DATE('23/03/2018 18:00:00','DD/MM/YYYY HH24:MI_SS')
> and timestamp < TO_DATE('23/03/2018 19:00:00','DD/MM/YYYY HH24:MI_SS')
>
> With this query I got a list of tables.
>
>
> Is there another way to know what is the table that were root cause of the
> error ?
>
> Great
> Eriovaldo
>
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l