Locked Transactions with Hibernate in Oracle

Question

I have the following (simplified) Hibernate objects:

@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 am updating these objects in a transaction that looks something 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 called only occasionally, and bar is always updated.

I observe random errors ORA-00060: Deadlock detected when the application starts. The dump seems to suggest that two em.merge(bar) sessions are blocked on em.merge(bar) and em.remove(foo) , but I don't understand why this would be so.

How is this code deadlock? Is there a way to rebuild it to avoid deadlock?

Here is additional information from the track:

 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

If I understood detach correctly, you should do something like this:

 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(); 

Answer 2

There are usually two main causes of deadlocks in Oracle.

• the so-called SX-SSX escalation lock. This is due to the lack of an index on FK (child table). In this case, Oracle must lock the entire child table before checking constraints. See AskTom Artice article
• wrong order of SQL statements

In all cases, the deadlock is caused by an application error. You will need a lock report (.trc file) from the database server. There you will find SQL statements and tables. Since you are using Hibernate, you can hardly predict the order in which the SQL query is executed, sometimes it can help expand the organization’s cache manager to prevent flush() calls too early.

Editorial: OK, so you have a TX (X) lock. This is the row level, and SSX is the table level. Then the dead end object can be either a row in the table or a unique key in the index. The trace file must also contain the previous statement for each session, as well as cursors (the place where the SQL query was executed), cursors must also contain the values ​​of the binding variables.

Try to do:

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

• Are you really using CamelCase for table names?
• What is DDL for "Foo" and "Bar"?
• Does it stall when you drop FK between Foo and Bar?
• When you only em.remove(foo); will also remove the child panel?