CODEWITHSUNDEEP
javaoracle-databasejpaoracle11gdatabase-deadlocks
John John
John John

Reputation: 4575

JPA Deadlock when delete from a table in several parallel threads

I have several Threads, with different Transactions and EntityManagers, that must refresh events of an object. To refresh these events, first I delete the old ones to after persist the new. With one thread it works good, but with several it occurs dead lock when deleting the event.

All Threads are deleting different objects, and sometimes, in different tables. So why this competition for resources occurring? I'm using the primary key to JPA block the right object. I looked if there is other code that are also using the resource, but I didn't found it. Is JPA locking the whole table instead of the row?

Exception in thread "Thread-4" javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.0.v20150309-bf26070): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

Error Code: 60
Call: DELETE FROM event WHERE ((id = ?) AND (version = ?))
    bind => [426687, 1]
Query: DeleteObjectQuery(Event[id=426687,tipo=BDE,status=1,data=java.util.GregorianCalendar[time=1431489600000,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="America/New_York",offset=-18000000,dstSavings=3600000,useDaylight=true,transitions=235,lastRule=java.util.SimpleTimeZone[id=America/New_York,offset=-18000000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7200000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,endDayOfWeek=1,endTim

Here is the OOracle Trace File.

*** 2015-07-05 15:21:02.351
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

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
TM-00007874-00000000        44      29    SX             51     185    SX   SSX
TX-00020021-00000a5f        51     185     X             44      29           X

session 29: DID 0001-002C-0000000D      session 185: DID 0001-0033-00000004
session 185: DID 0001-0033-00000004     session 29: DID 0001-002C-0000000D

Rows waited on:
  Session 29: obj - rowid = 00007874 - AAAHh0AABAAAO2fAAX
  (dictionary objn - 30836, file - 1, block - 60831, slot - 23)
  Session 185: no row

----- Information for the OTHER waiting sessions -----
Session 185:
  sid: 185 ser: 3883 audsid: 422763 user: 55/LUPAZUL_DEV
    flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-
  pid: 51 O/S info: user: oracle, term: UNKNOWN, ospid: 9977
    image: oracle@sydney-oracle11gexpress
  client details:
    O/S info: user: Pickler, term: unknown, ospid: 1234
    machine: MacBook program: JDBC Thin Client
    application name: JDBC Thin Client, hash value=2546894660
  current SQL:
  DELETE FROM correios_event WHERE ((id = :1 ) AND (version = :2 ))

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=fcrp8hfyatd79) -----
DELETE FROM correios_destiny WHERE ((id = :1 ) AND (version = :2 ))

Upvotes: 3

Views: 3995

Answers (1)

John John
John John

Reputation: 4575

I solved the bug.

I had two JPA entities with bidirectional @OneToOne relationship. When I called the EntityManager.remove() I was not passing the owner of the relationship. This made Oracle throw DeadLockException.

This way EclipseLink created two statements on the same entity to delete. It seems a bug to me, JPA implementation should handle this.

Upvotes: 3

Related Questions