Java multiple threads database access [closed]

Question

What could be the best solution for multithreaded Java application to ensure that all threads access db synchronously? For example, each thread represents separate transaction, and first checks db for value and then depending on answer has to insert or update some fields in database (note between check, insert and commit application is doing other processing). But the problem is that another thread might be doing just the same thing on same table.

More specific example. Thread T1 starts transaction, then checks table ENTITY_TABLE for entry with code '111'. If found updates its date, if not found inserts new entry, then commits transaction. Now imagine thread T2 does exactly same thing. Now there are few problems:

1. T1 and T2 checks db and find nothing and both insert same entry.
2. T1 checks db, find entry with old date, but on commit T2 already has updated entry to more recent date.
3. If we use cache and synchronize access to cache we have a problem: T1 acquires lock checks db and cache if not found add to cache, release lock, commit. T2 does the same, finds entry in cache going to commit. But T1 transaction fails and is roll backed. Now T2 is in bad shape, because it should insert to ENTITY_TABLE but doesn't know that.
4. more?

I'm working on creating simple custom cache with syncronization and solving problem 3. But maybe there is some more simple solution?

Answer 1

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.

Answer 2

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.

1. SELECT with ROWLOCK, or whatever the row lock syntax is for your particular data base.

2. SELECT the row, do your processing, and just before you're ready to update, SELECT the row again with ROWLOCK to see if any other thread made changes. If the two SELECTS return the same values, UPDATE. If not, throw an error or do your processing again.

Answer 3

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.