Java multiple threads database access [closed]
This should be dealt with primarily within the DB, by configuring the desired transaction isolation level. Then on top of this, you need to select your locking strategy (optimistic or pessimistic).
Without transaction isolation, you will have a hard time trying to ensure transaction integrity solely in the Java domain. Especially taking into consideration that even if the DB is currently accessed only from your Java app, this may change in the future.
Now as to which isolation level to choose, from your description it might seem that you need the highest isolation level, serializable. However, in practice this tends to be a real performance hog due to extensive locking. So you may want to reevaluate your requirements to find the best balance of isolation and performance for your specific situation.
If you want to SQL SELECT a row from a database, and then later UPDATE the same row, you have 2 choices as a Java developer.
SELECT with ROWLOCK, or whatever therow lock syntax is for yourparticular data base.
SELECT the row, do your processing,and just before you're ready toupdate, SELECT the row again with ROWLOCK to seeif any other thread made changes. If the two SELECTS return the samevalues, UPDATE. If not, throw anerror or do your processing again.
The problem you are facing is transaction isolation.
Seems like you need to have each thread lock the row concerned in the where clause, which requires serializable isolation.