0

I have 2 applications (Spring - Hibernate with Boot) using same oracle database (11g). Both apps hit a specific table consistently and there are huge number of hits on this table. we can see row lock contention exceptions in the DB logs and applications have to be restarted each time we get these or when it creates a deadlock like situation.

we are using JPA entitymanager for these applications. need help for this issue

Nikhil Surve
  • 3
  • 1
  • 2

1 Answers1

1

According to this link : http://www.dba-oracle.com/t_enq_tx_row_lock_contention.htm

This error occurs because a transaction is waiting for another transaction to commit or roll back ... This behavior is correct from the database POV and if you think of Data consistency ..... But if availability / fulfillment is a concern for you... You might need to make some work around including :

1 make separate tables for each of the application then update the main table with data offline (but u will sacrifice data consistency)

2 make a separate thread to log and retry unsuccessful transactions

3 bear the availability issue (latency) if consistency is a big concern

Also there are some general tips to consider :

1 make the transaction minimal ... Think about every process included in the transaction. If it's mandatory or can be removed outside

2 tune transaction demarcation ... U might find transaction open for long with no reason but bad coding

3 don't make read operations inside transactions

4 avoid extended persistence context (stateless) whenever possible

5 u might choose to use non jta transactional data source for reporting and reading queries

6 check the lock types you are using and try to avoid -according to your case- any thing but OPTIMISTIC

But finally you agree with me we shouldn't blame the database from blocking two transactions from modifying the same row.

osama yaccoub
  • 1,884
  • 2
  • 17
  • 47
  • Thanks for your answer..!! i have created a separate service which consumes rest calls from both these application and this service inserts / updates the table. also added PESSIMISTIC_WRITE lock to avoid deadlocks. please let me know your views on the same. – Nikhil Surve Feb 12 '18 at 04:31
  • if I understand right, then instead of letting the 2 applications write concurrently to the same database, you created another WS that consumes from both and write to the database , thus there's no more synchronization issue ... If this is the scenario, I think no need for lock then (unless for some other reason that I don't know)...and according to my knowledge, optimistic lock doesn't avoid deadlock , on the contrary, you are increasing the surface area of the protected region making it more prone to deadlock specially if you are using extended lock mode (lock the entities and their related) – osama yaccoub Feb 18 '18 at 13:52
  • Still we are getting same issue. I think transactions are open for long time. There are timeout logs which indicate that. Single transaction contains inserts and updates on different tables. Optimistic locking does not prevent deadlocks. Please advise any solutions that can help further. – Nikhil Surve Feb 24 '18 at 14:53
  • cab you please confirm the case description I mentioned in the previous comment – osama yaccoub Feb 25 '18 at 13:13
  • sorry I meant : `pessimistic` lock doesn't avoid deadlock – osama yaccoub Feb 25 '18 at 13:14