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 the row lock syntax is for your particular data base.
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.
/**
* Subclass this class and implement the persistInTransaction method to perform
* an update to the database.
*/
public abstract class DBOperationInTransaction {
protected Logger logger = Logger.getLogger(DBOperationInTransaction.class.getName());
public DBOperationInTransaction(ConnectionFactory connectionFactory) {
DBConnection con = null;
try {
con = connectionFactory.getConnection();
if(con == null) {
logger.log(Level.SEVERE, "Could not get db connection");
throw new RuntimException("Could not get db connection");
}
synchronized (con.activityLock) {
con.connection.setAutoCommit(false);
persistInTransaction(con.connection);
con.connection.commit();
}
} catch (Exception e) {
logger.log(Level.SEVERE, "Failed to persist data:", e);
throw new RuntimeException(e);
} finally {
if(con != null) {
//Close con.connection silently.
}
}
}
/**
* Method for persisting data within a transaction. If any SQLExceptions
* occur they are logged and the transaction is rolled back.
*
* In the scope of the method there is a logger object available that any
* errors/warnings besides sqlException that you want to log.
*
* @param con
* Connection ready for use, do not do any transaction handling
* on this object.
* @throws SQLException
* Any SQL exception that your code might throw. These errors
* are logged. Any exception will rollback the transaction.
*
*/
abstract protected void persistInTransaction(Connection con) throws SQLException;
}
以及 DBConnection 结构：
final public class DBConnection {
public final Connection connection;
public final String activityLock;
public DBConnection(Connection connection, String activityLock) {
this.connection = connection;
this.activityLock = activityLock;
}
}
I tumbled into this problem when working with a multi-threaded Java program that was using a Sqllite database. It uses file locking so I had to make sure that only one thread was doing work at the same time.
I basically ended up with using synchronized. When the ConnectionFactory returns a db connection, it also returns a lock object that one should lock when using the connection. So you could do synchronization lock manually, or create a subclass of the class below which does it for you:
/**
* Subclass this class and implement the persistInTransaction method to perform
* an update to the database.
*/
public abstract class DBOperationInTransaction {
protected Logger logger = Logger.getLogger(DBOperationInTransaction.class.getName());
public DBOperationInTransaction(ConnectionFactory connectionFactory) {
DBConnection con = null;
try {
con = connectionFactory.getConnection();
if(con == null) {
logger.log(Level.SEVERE, "Could not get db connection");
throw new RuntimException("Could not get db connection");
}
synchronized (con.activityLock) {
con.connection.setAutoCommit(false);
persistInTransaction(con.connection);
con.connection.commit();
}
} catch (Exception e) {
logger.log(Level.SEVERE, "Failed to persist data:", e);
throw new RuntimeException(e);
} finally {
if(con != null) {
//Close con.connection silently.
}
}
}
/**
* Method for persisting data within a transaction. If any SQLExceptions
* occur they are logged and the transaction is rolled back.
*
* In the scope of the method there is a logger object available that any
* errors/warnings besides sqlException that you want to log.
*
* @param con
* Connection ready for use, do not do any transaction handling
* on this object.
* @throws SQLException
* Any SQL exception that your code might throw. These errors
* are logged. Any exception will rollback the transaction.
*
*/
abstract protected void persistInTransaction(Connection con) throws SQLException;
}
And the DBConnection struct:
final public class DBConnection {
public final Connection connection;
public final String activityLock;
public DBConnection(Connection connection, String activityLock) {
this.connection = connection;
this.activityLock = activityLock;
}
}
Offhand, I think you would have to lock the table before you query it. This will force sequential operation of your threads. Your threads should then be prepared for the fact that they will have to wait for the lock and of course, the lock acquisition might time out. This could introduce quite a bottleneck into your application as well as your threads will all have to queue up for database resources.
发布评论
评论（5）
这应该主要在数据库内通过配置所需的事务隔离 级别。然后最重要的是，您需要选择锁定策略（乐观或悲观）。
如果没有事务隔离，您将很难仅在 Java 域中确保事务完整性。特别是考虑到即使当前仅从 Java 应用程序访问数据库，这种情况将来也可能会发生变化。
现在至于选择哪个隔离级别，从您的描述来看，您似乎需要最高的隔离级别，可序列化。然而，在实践中这往往
由于大量的锁定，成为真正的性能消耗者。因此，您可能需要重新评估您的要求，以针对您的具体情况找到隔离和性能的最佳平衡点。
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.
如果您想从数据库中 SQL SELECT 一行，然后更新同一行，作为 Java 开发人员，您有 2 个选择。
使用 ROWLOCK 进行选择，或者其他任何方式
行锁语法适合您
特定的数据库。
选择行，进行处理，
就在你准备好之前
更新，使用 ROWLOCK 再次选择该行即可查看
如果任何其他线程进行了更改。
如果两个 SELECTS 返回相同的
值，更新。如果没有，抛出一个
错误或重新进行处理。
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 the
row lock syntax is for your
particular data base.
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.
您面临的问题是事务隔离。
似乎您需要让每个线程锁定 where 子句中相关的行，这需要可序列化的隔离。
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.
在使用使用 Sqllite 数据库的多线程 Java 程序时，我遇到了这个问题。它使用文件锁定，因此我必须确保只有一个线程同时在工作。
我基本上最终使用了同步。当 ConnectionFactory 返回一个数据库连接时，它还会返回一个锁对象，在使用该连接时应该锁定该对象。因此，您可以手动执行同步锁定，或者创建下面的类的子类来为您执行同步锁定：
以及 DBConnection 结构：
I tumbled into this problem when working with a multi-threaded Java program that was using a Sqllite database. It uses file locking so I had to make sure that only one thread was doing work at the same time.
I basically ended up with using synchronized. When the ConnectionFactory returns a db connection, it also returns a lock object that one should lock when using the connection. So you could do synchronization lock manually, or create a subclass of the class below which does it for you:
And the DBConnection struct:
顺便说一句，我认为您必须在查询之前锁定表。这将强制线程按顺序运行。然后，您的线程应该做好准备，因为它们必须等待锁，当然，锁获取可能会超时。这可能会给您的应用程序带来相当大的瓶颈，并且您的线程都必须排队等待数据库资源。
Offhand, I think you would have to lock the table before you query it. This will force sequential operation of your threads. Your threads should then be prepared for the fact that they will have to wait for the lock and of course, the lock acquisition might time out. This could introduce quite a bottleneck into your application as well as your threads will all have to queue up for database resources.