并发 - 数据库访问

发布于 2024-11-05 07:12:39 字数 526 浏览 1 评论 0原文

我需要处理检索待处理记录并将其更新为“处理中”状态作为工作单元。我想确保下面的代码支持并发，并且其他线程等待直到我当前的线程被处理。实现这一目标的最佳方法是什么？

public Collection<Object> processPendingMessages() {
    Collection<Object> messages = null;
    //Retrieve pending messages
    messages = messageDAO.getPendingMessages(Direction.INBOUND);
    //Update pending messages to Inprocess
    if (messages!=null && messages.size()>0) {
        messageDAO.updateMessagesToInprocess(messages); 
    } 
    return messages;
}

非常感谢您的意见。谢谢。

原文

I need to process retrieving pending records and updating them to 'InProcess' state as a unit-of-work. I would like to make sure below code supports concurrency and other threads wait until my current thread is processed. What is the best way to achieve this?

public Collection<Object> processPendingMessages() {
    Collection<Object> messages = null;
    //Retrieve pending messages
    messages = messageDAO.getPendingMessages(Direction.INBOUND);
    //Update pending messages to Inprocess
    if (messages!=null && messages.size()>0) {
        messageDAO.updateMessagesToInprocess(messages); 
    } 
    return messages;
}

Your input is highly appreciated.
Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

守望孤独 2024-11-12 07:12:39

您不想仅通过使用（同步）来处理 jvm 级别的 DBAccess 并发性。在您的情况下，访问是对数据库的访问，而仅通过 jvm 级别的同步是不可能实现的。您还需要处理数据库连接级别的并发性，如下所示。就 DBaccess 而言，您还没有提到 DAO 层中的实现类型，但无论它是什么，以下 JDBC 隔离级别也应该对您的实现有意义，无论它是什么（JDBC、JPA 和Hibernate 或 EJB 等）。通读下面的隔离级别并确定哪一个最适合您的应用程序。祝您实施一切顺利。

事务隔离级别指定哪些数据对于事务内的语句可见作为事务单元。这些级别通过定义针对同一目标数据源的事务之间可能进行的交互，直接影响并发访问的级别。

数据库异常

数据库异常是生成的结果，从单个事务的范围来看似乎不正确，但从所有事务的范围来看却是正确的。不同类型的数据库异常描述如下：

脏读发生在以下情况：
事务 A 将一行插入表中。
事务 B 读取新行。
事务A回滚。
事务 B 可能已根据事务 A 插入的行对系统进行了工作，但该行从未成为数据库的永久部分。

在以下情况下会发生不可重复读取：
事务A读取一行。
事务 B 更改该行。
事务 A 第二次读取同一行并获取新结果。

幻读发生在以下情况：
事务 A 读取满足 SQL 查询中的 WHERE 子句的所有行。
事务 B 插入满足 WHERE 子句的附加行。
事务 A 重新评估 WHERE 条件并选取附加行。

处理以上异常的隔离级别

JDBC_TRANSACTION_NONE 这是一个特殊常量，指示 JDBC 驱动程序不支持事务。

JDBC_TRANSACTION_READ_UNCOMMITTED 此级别允许事务查看未提交的数据更改。所有数据库异常都可能发生在此级别。

JDBC_TRANSACTION_READ_COMMITTED 在提交事务之前，事务内部所做的任何更改在事务外部都是不可见的。这可以防止脏读的发生。

读取的 JDBC_TRANSACTION_REPEATABLE_READ 行会保留锁，以便在事务未完成时另一个事务无法更改它们。这不允许脏读和不可重复读。幻读仍然是可能的。

JDBC_TRANSACTION_SERIALIZABLE 表针对事务锁定，以便向表添加值或从表中删除值的其他事务无法更改 WHERE 条件。这可以防止所有类型的数据库异常。

setTransactionIsolation 方法可用于更改连接的事务隔离级别。

You dont want to handle the DBAccess concurrency at the jvm level alone by using (synchronized). In your case the access is to the database which will not be possible with mere synchronization at jvm level. You need to handle the concurrency at the database connectivity level too as follows. You haven't mentioned what kind of implementation is in your DAO layer as far as DBaccess is concerned, but what ever it could be, the following isolation levels for JDBC should make sense with your implementation too irrespective of it being (JDBC, JPA with Hibernate or EJB, etc). Read through the isolation levels below and descide which one is good for your application. All the best with your implementation.

Transaction isolation levels specify what data is visible to statements within a transaction as a unit of transaction. These levels directly impact the level of concurrent access by defining what interaction is possible between transactions against the same target data source.

DATABASE ANOMOLIES

Database anomalies are generated results that seem incorrect when looked at from the scope of a single transaction, but are correct when looked at from the scope of all transactions. The different types of database anomalies are described as follows:

Dirty reads occur when:
Transaction A inserts a row into a table.
Transaction B reads the new row.
Transaction A rolls back.
Transaction B may have done work to the system based on the row inserted by transaction A, but that row never became a permanent part of the database.

Nonrepeatable reads occur when:
Transaction A reads a row.
Transaction B changes the row.
Transaction A reads the same row a second time and gets the new results.

Phantom reads occur when:
Transaction A reads all rows that satisfy a WHERE clause on an SQL query.
Transaction B inserts an additional row that satisfies the WHERE clause.
Transaction A re-evaluates the WHERE condition and picks up the additional row.

ISOLATION LEVELS THAT HANDLES EACH OF ABOVE ANOMOLIES

JDBC_TRANSACTION_NONE This is a special constant indicating that the JDBC driver does not support transactions.

JDBC_TRANSACTION_READ_UNCOMMITTED This level allows transactions to see uncommitted changes to the data. All database anomalies are possible at this level.

JDBC_TRANSACTION_READ_COMMITTED any changes made inside a transaction are not visible outside it until the transaction is committed. This prevents dirty reads from being possible.

JDBC_TRANSACTION_REPEATABLE_READ rows that are read retain locks so that another transaction cannot change them when the transaction is not completed. This disallows dirty reads and nonrepeatable reads. Phantom read are still possible.

JDBC_TRANSACTION_SERIALIZABLE tables are locked for the transaction so that WHERE conditions cannot be changed by other transactions that add values to or remove values from a table. This prevents all types of database anomalies.

The setTransactionIsolation method can be used to change the transaction isolation level for a connection.

回复收藏 0 原文

⒈起吃苦の倖褔 2024-11-12 07:12:39

您应该让数据库处理并发性。

在 DAO 中，您可以发出 sql 查询：

// works on oracle.
// query may differ depending on vendor
select * from test where id=? for update

第一个线程将能够获取锁，但后续线程在执行查询时将被阻塞。一旦第一个线程提交/回滚，等待线程将解除阻塞。这样，您就可以让读者一次阅读一篇。

you should let the database handle the concurrency.

in your DAO, you can issue the sql query:

// works on oracle.
// query may differ depending on vendor
select * from test where id=? for update

the first thread will be able to acquire the lock but subsequent threads will block if they execute the query. once the first thread has commit/rollback, the one of the waiting threads will unblock. this way, you can have readers reading one at a time.

回复收藏 0 原文