2

Developing a Java application that share a single Connection between mutiple threads, the problem of concurrency arise.

If thread A updates record 1 in table T, and simultaneously thread B issues a SELECT on record 1 in table T, how do I ensure thread B reads the updated values of thread A?

java.sql.Connection offers transactions with begin(), commit() and rollback(), but does this process also cover data correctness?

I think I'm missing something.

LppEdd
  • 20,274
  • 11
  • 84
  • 139

2 Answers2

4

Two points:

  1. You shouldn't share a jdbc.Connection between threads, at least for any 'seriously production' code, see here. For demo purposes, I think, sharing a Connection is OK;
  2. If a thread reads from DB after relevant DB transaction is committed, it will see data written by another thread.

For your second question

will thread B timeout until the first transaction has commit() or rollback()

-- B will block till A tx is finished (either by commit or rollback) if:

  1. B tries to update/delete same table row which is being updated by A, and ...
  2. A updates that row under DB-level lock, using SELECT ... FOR UPDATE.

You can get this behavior using two consoles (for example, with PostgreSQL psql), each console stands for a thread:

in A console type following:

BEGIN;
SELECT some_col FROM some_tbl WHERE some_col = some_val FOR UPDATE;

now in B console type:

BEGIN;
UPDATE some_tbl SET some_col = new_val WHERE some_col = some_val;

You should see that UPDATE blocks until in A you do either COMMIT or ROLLBACK.

Above explanation uses separate DB connections, just like Java JDBC connection pool. When you share single connection between Java threads, I think, any interaction with DB will block if connection is used by some other thread.

Victor Sorokin
  • 11,878
  • 2
  • 35
  • 51
  • It is in fact for a small demo application. What I'm missing is: if I begin() a transaction in thread A, and after I begin() another transaction from thread B, will thread B timeout until the first transaction has commit() or rollback() ? Or will thread B throw an SQLException? – LppEdd Sep 14 '17 at 11:43
  • 3. Transactions in Java are implemented with the idea of having one thread using a connection. When transaction is opened underlying implementation calls begin on the connection. On successful return it calls commit, and if there's a runtime exception it will call rollback. – Boris Pavlović Sep 14 '17 at 11:44
  • @BorisPavlović understood the concept. Now another question arise. Will beginning a transaction (calling begin()) from thread A (with Connection A) lock the relevant records until changes are commited or rolled back? Or will thread B (with Connection B) still be able to SELECT those records without waiting for thread A to commit/rollback? – LppEdd Sep 14 '17 at 11:51
  • I'd say the latter, but, it's irrelevant when there's a simple solution to avoid the risk. – Boris Pavlović Sep 14 '17 at 11:54
  • @BorisPavlović Probably I'm overcomplicating things. What would be this simple solution? A Connection per thread? – LppEdd Sep 14 '17 at 11:56
  • Use a connection pool as explained in this answer: https://stackoverflow.com/a/2826295/32090 – Boris Pavlović Sep 14 '17 at 11:57
  • Thanks to both of you. Victor, could you clarify one more thing? Does your second edit about DB level locking relate to Transaction isolation levels? – LppEdd Sep 14 '17 at 12:16
  • @LppEdd `SELECT FOR UPDATE` is "manual mode" db lock, and tx isolations are "auto-mode" db locks, and when use one or other depends on your goals. I used it just as a demo of how Java thread can be blocked via DB-level lock. See https://stackoverflow.com/q/16957638/162634 and https://stackoverflow.com/q/10935850/162634 – Victor Sorokin Sep 14 '17 at 12:33
3

Jdbc is a standard that is broadly adopted but with uneven levels of adherence, it is probably not good to make sweeping statements about what is safe.

I would not expect there is anything to keep statement executions and commits and rollbacks made from multiple threads from getting interleaved. Best case, only one thread can use the connection at a time and the others block, making multithreading useless.

If you don't want to provide a connection to each thread, you could have the threads submit work items to a queue that is consumed by a single worker thread handling all the jdbc work. But it's probably less impact on existing code to introduce a connection pool.

In general if you have concurrent updates and reads then they happen in the order that they happen. Locking and isolation levels provide consistency guarantees for concurrent transactions but if one hasn't started its transaction yet those aren't applicable. You could have a status flag, version number, or time stamp on each row to indicate when an update occurred.

If you have a lot of updates it can be better to collect them in a flat file and execute a bulk copy. It can be much faster than using jdbc. Then with updates out of the way execute selects in jdbc.

Nathan Hughes
  • 94,330
  • 19
  • 181
  • 276
  • 1
    Technically, JDBC requires that connections are thread safe, but that doesn't mean that those threads are isolated from each other. It still means all those threads should cooperate in the same transaction, and coordinate their actions so they don't do work that interferes (eg one thread committing a transaction, while another thread is still doing work, etc). This makes accessing a single connection from multiple threads not very useful at best, and a haven for race-condition infested bugs at worst. – Mark Rotteveel Sep 14 '17 at 15:23