多线程环境中的JDBC事务 [英] JDBC transactions in multi-threaded environment

问题描述

开发在多个线程之间共享单个连接的Java应用程序，会出现并发问题.

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

如果线程A更新表T中的记录1，同时线程B对表T中的记录1发出SELECT，我如何确保线程B读取线程A的更新值?

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提供带有begin()，commit()和rollback()的事务，但是此过程还涵盖数据正确性吗?

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

我想我缺少了一些东西.

I think I'm missing something.

推荐答案

两点:

1. 您不应在线程之间共享jdbc.Connection，至少对于任何严重生产"代码而言，请参见此处.出于演示目的，我认为共享连接是可以的；
2. 如果一个线程在相关的数据库事务提交后从数据库中读取，它将看到另一个线程写入的数据.

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

将使线程B超时，直到第一个事务具有commit()或rollback()

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

-B将一直阻塞，直到A tx完成(通过提交或回滚)，如果:

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

1. B尝试更新/删除A正在更新的同一表行，然后...
2. A使用SELECT ... FOR UPDATE在数据库级别锁定下更新该行.

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.

您可以使用两个控制台(例如，使用PostgreSQL psql)获得此行为，每个控制台代表一个线程:

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

键入以下内容:

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

现在在B控制台中:

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

您应该看到UPDATE处于阻塞状态，直到在A中执行COMMIT或ROLLBACK.

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

以上解释使用了单独的数据库连接，就像Java JDBC连接池一样.我认为，当您在Java线程之间共享单个连接时，如果其他某个线程使用了连接，则与DB的任何交互都会阻塞.

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.

这篇关于多线程环境中的JDBC事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！