I am facing a issue with Transaction management during multi-threading on db layer.
Application is built using spring for integration and hibernate for persistance (3.X). Transactions are handled by Container. Requests are served by MDB(2.1).
Object relations are as follows
Configurations is one object and different products can be created based on this configurations
Configurations --> one to many relation with Products
Application works fine with single thread, during multi threading, the persistance layer fails with deadlock issue even for two concurrent messages.
From logs could find that if one thread is working updating persistance layer and if another thread too starts updating another set of record (both messages are unique), dead lock occurs. Ideally lock on a table during update is row level, but seems like it is locking entire table (may be even due to parent child relations).
If the other thread starts after previous thread completes it works.
org.springframework.dao.CannotAcquireLockException: could not update: [com.xx.Products#10180873]; SQL [update Products set Prod_ID=?... where PROD_TID=?]; nested exception is org.hibernate.exception.LockAcquisitionException: could not update: [com.xx.Products#10180873]
Caused by: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource
Its an Oracle error. You can get deadlock for things other than row locks : http://www.oratechinfo.co.uk/deadlocks.html. Check you indexes, cascading deletes, triggers etc. In short, you need to check at database level.