Deadlocked transactions with Hibernate on Oracle

Question

I have the following (simplified) Hibernate entities:

@Entity(name = "Foo")
public class Foo {

    @Id
    @GeneratedValue
    public int id;

    @OneToOne
    public Bar bar;
}

and,

@Entity(name = "Bar")
public class Bar {

    @Id
    @GeneratedValue
    public int id;

    @Column
    public String field;

    @Version
    public int version;

}

I update these entities in a transaction that looks roughly like this:

Bar bar = findBar(em);
Foo foo = findFoo(em);

bar.field = "updated value";

if (<condition>) {
    em.remove(foo);
}

em.detach(bar);
em.merge(bar);

Note that em.remove(foo) is only called sometimes, while bar is always updated.

I'm noticing occasional ORA-00060: Deadlock detected errors when running the application. The dump seems to suggest that the two deadlocked sessions are locked on em.merge(bar) and em.remove(foo), but I don't understand why that would be the case.

How can this code deadlock? Is there a way to restructure it avoid deadlocking?

Here's some extra information from the trace:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00040005-000010dd        73    6557     X             81    6498           X
TX-00010018-000010bd        81    6498     X             73    6557           X

session 6557: DID 0001-0049-000002F5    session 6498: DID 0001-0051-0000030E 
session 6498: DID 0001-0051-0000030E    session 6557: DID 0001-0049-000002F5 

Rows waited on:
  Session 6557: obj - rowid = 00004797 - AAAEeXAB4AAADH0BBP
  (dictionary objn - 18331, file - 120, block - 12788, slot - 15)
  Session 6498: obj - rowid = 00007191 - AAAHGRAB4AAAACBBBo
  (dictionary objn - 29041, file - 120, block - 129, slot - 40)

----- Information for the OTHER waiting sessions -----
Session 6498:
program: JDBC Thin Client
    application name: JDBC Thin Client, hash value=2546894660
  current SQL:

delete from Foo where id=:1 

----- Current SQL Statement for this session (sql_id=sfasdgasdgaf) -----
update Bar set field=:1, version=:2 where id=:3 and version=:4 

Answer 1

Generally there are two main reasons for deadlock occurrence in Oracle

• so called SX to SSX lock escalation. This one is caused by missing index on FK(child table). In such a case Oracle has to lock the whole child table before validating the constrains. See AskTom Artice
• wrong order of SQL statements order

In all cases deadlock is caused by application error. You will need deadlock report(.trc file) from database server. There will you find SQL statement and tables involved. Since you use Hibernate you can hardly predict the order of SQL statement execution, sometimes it might help to extend Entity manager cache, to prevent too early calls to flush().

EDITED: OK so you have TX(X) locks. These are row level, while SSX are table level. Then the deadlocked object might be either a row in a table or a unique key in an index. The trace file should also contain previous statement for each session and also cursors(place of SQL statement execution), cursors should contain also values of bind variables.

Try to execute:

select * from Foo where rowid = 'AAAHGRAB4AAAACBBBo';
select * from Bar where rowid = 'AAAEeXAB4AAADH0BBP';

• Do you really use CamelCase for table names?
• What is DDL for "Foo" and "Bar"?
• Does deadlock occur when you drop FK between Foo and Bar?
• When you only call em.remove(foo); will also child Bar be removed?

Answer 2

If I understood correctly the detach you should do something like that:

Foo foo = findFoo(em);
Bar bar = findBar(em);

if (<condition>) {
    em.remove(foo);
    em.detach(bar); //If it is really necessary
    em.flush();
}

bar = findBar(em); //It will reattach the entity on the persistence context
bar.field = "updated value";

em.merge(bar); 
em.commit();