-
1. Re: how to choose correct db isolation level for Financial Accounting app ?
martinschweigert Mar 28, 2014 3:21 AM (in response to masummymesingh)Sorry for being rude, but do yourself and your customer(!) a favor and find some professional who codes your "financial app". I'm sure you can so much more learn from her/him.
-
2. Re: how to choose correct db isolation level for Financial Accounting app ?
wdfink Mar 28, 2014 4:44 AM (in response to masummymesingh)To be a bit more detailed
If you have no idea what the different isolation levels are I agree to martinschweigert.
The isolation depends on your requirements (performance/consistence) and you need to have that coordinated with your application.
-
3. Re: how to choose correct db isolation level for Financial Accounting app ?
masummymesingh Mar 28, 2014 6:53 AM (in response to wdfink)Hi wolf-Dieter ,
This app requirements is :
1. banking accounting system
2. high concurrent user
3. need high level data consistency
4. also need regular performance
I want to know that for this situation what is the best practice of DB isolation level ???
==================================
level
Why Occur
problem
Use case
Performance /overhead
Java ee
db
Read Uncommitted
Rollback/failed
3
Search /Select
***
Read committed
Unrepeatable
-Update
multiple reads in same transaction
2
only committed records
**
***
***oracle default
Repeatable Read
Phantom - Insert
1
to read a row exclusively
*
Programmically /Solution is
Optimistic Lock
Read/Write Lock in EJB
Serializable
0
data consistency is an absolute requirement
Very slow /Deadlock
***
-
4. Re: how to choose correct db isolation level for Financial Accounting app ?
wdfink Mar 28, 2014 3:55 PM (in response to masummymesingh)The answer is in that table
As you need Performance, concurrency and consistency I would use RepeatableRead and Optimistic locking (OL).
OL is provided by JPA, you need to define a 'version' column which ensure that you can not upddate if a concurrent change was made.
You should read the JPA tutorial/docs
-
5. Re: how to choose correct db isolation level for Financial Accounting app ?
masummymesingh Mar 29, 2014 2:32 PM (in response to wdfink)Thanks wolf-Dieter for your suggestion .
i want to use Read committed .
in this situation i shall face those three concurrency problem . I want to overcome those problem using following solution
for isolation level -------------Read committed
problem 1 : Lost Update Problem ----------------(old value)-------------JPA Optimistic locking (OL). problem 2 : Unrepeatable ---------------(Single Row)------------ JPA entity read /write lock problem 3 : Phantom -----------(Multi Row)------------------ ???????????? Can you tell me that what is appropriate solution for problem-3 : phantom ???
is it possible any solution (problem 3 ) for when using Read committed ?
-
6. Re: how to choose correct db isolation level for Financial Accounting app ?
wdfink Mar 29, 2014 2:48 PM (in response to masummymesingh)Phantom read might be acceptable in most cases as you can prevent it in you logic with locks