Home How to handle row lock contention at application level
Reply: 1

How to handle row lock contention at application level

Nikhil Surve
Nikhil Surve Published in 2018-02-11 13:01:22Z

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

osama yaccoub
osama yaccoub Reply to 2018-02-11 19:49:26Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.296895 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO