I've put the appropriate information @ the end because I know what the problem is...I just don't know how to fix it.

As you can see, I have a web service that takes a "ServerType" and inserts it to the database. If it already exists, it removes it and re-inserts it.

Regular transaction "inserts" take about 15-20 seconds. I'm guessing simple inserts don't lock the database so that part is fine. If an entry already exists, though, it has to delete/insert the ServerType so the DB gets locked and other servers can't update the database until that 15 second transaction is complete. If you have 40+ systems trying to update at the same time, the deadlock exceptions occur.

Anyone know of a better way to do this? I'm all for figuring out how to lower the insertion time to a few seconds (though 800+ SQL inserts probably require all 15-20 seconds), removing the db lock on deletes, etc.

Hibernate version:
3.2.4

Mapping documents:

Code between sessionFactory.openSession() and session.close():


Full stack trace of any exception that occurs:
14:38:28,586 WARN JDBCExceptionReporter:77 - SQL Error: 60, SQLState: 61000
14:38:28,589 ERROR JDBCExceptionReporter:78 - ORA-00060: deadlock detected while waiting for resource
14:38:28,590 WARN JDBCExceptionReporter:77 - SQL Error: 60, SQLState: 61000
14:38:28,592 ERROR JDBCExceptionReporter:78 - ORA-00060: deadlock detected while waiting for resource
14:38:28,593 ERROR AbstractFlushingEventListener:301 - Could not synchronize database state with session
org.hibernate.exception.LockAcquisitionException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:87)

Name and version of the database you are using:
Oracle 10g

It looks like I have to figure out a way to not lock the DB because I "cleared" the 2 @CollectionOfElements in the ServerType and the inserts took 4-5 seconds. This is still too much for 40+ update calls because I'm still getting the deadlock issues.

Hmm, I replaced



with



And there is no locking.

The only difference between the two is that the former does a drop on the "server" table and a drop on all of the child tables; the latter does an update on the "server" table and a drop on the child tables.

I wonder if the delete on the "server" table was the locking culprit. Any guesses?