如何避免两个不同线程从DB读取相同的行（Hibernate和Oracle 10g） [英] How to avoid two different threads read the same rows from DB (Hibernate and Oracle 10g)

问题描述

假设我有两个不同的线程，T1和T2，同时访问同一个数据库并从同一个表中获取数据。

现在在线程启动时，我需要从表中获取数据并将这些行存储到一个集合中，然后我将使用它在别处执行一些工作。我不希望这两个线程能够处理相同的数据，因为它会导致重复（和长时间）的工作。更具体地说，这是一个企业应用程序，需要在启动时加载一些记录并将其存储在集合中以完成一些额外的工作。问题是在集群环境中，这可能会导致两个不同的实例加载相同的数据，因此工作可能会重复。所以我希望行只能由一个实例加载一次。

我该如何避免这种情况？

我目前使用Hibernate和Oracle 10g。这些是我迄今为止的解决方案：

• 以编程方式锁定行。第一个读取它会将某个锁定列设置为true，但如果第一个线程死亡而没有将该行设置为已处理，很可能会发生死锁。




• 使用悲观锁定。我尝试使用LockMode.UPGRADE，但似乎没有帮助，因为我仍然能够同时从两个线程读取数据。
  








  public List< MyObject> getAllNtfFromDb（）{
 Session session = HibernateUtil.getOraclesessionfactory（）。openSession（）; 
 Query q = session.createQuery（
from MyObject n where n.state ='NEW'）; 
列表< MyObject> list =（List< MyObject>）q.list（）; 
 for（int i = 0; i< list.size（）; i ++）
 session.lock（list.get（i），LockMode.UPGRADE）; 
返回列表; 
} 
  




其他提示？我究竟做错了什么？



谢谢。 http://vladmihalcea.com/a-beginners-guide-to-java-persistence-locking/ =nofollow noreferrer> PESSIMISTIC_WRITE 在查询时间：

  Query q = session 
 .createQuery（from MyObject n where n.state ='NEW'）
 .setLockOptions（new LockOptions（LockMode .PESSIMISTIC_WRITE））; 
列表< MyObject> list =（List< MyObject>）q.list（）; 
  

锁定父对象就足够了。僵局不一定会发生。如果持有锁的线程在另一个线程超时等待之前没有释放它，您可能会收到锁获取失败。





由于您使用的是Oracle，因此 SELECT FOR UPDATE 的工作原理：






SELECT ... FOR UPDATE锁定行和任何相关索引
条目，就像您发出UPDATE语句一样为那些行。
其他事务被阻止更新这些行，例如执行
SELECT ... LOCK IN SHARE MODE，或者读取某些
事务隔离级别的数据。一致的读操作将忽略在读取视图中存在的记录上
上设置的任何锁。 （记录
的旧版本不能被锁定;它们通过在记录的
内存副本上应用撤消日志来重新构建。）




因此，如果T1在某些行上获得排他锁，则T2将无法读取这些记录，直到T1提交或回滚。如果T2使用 READ_UNCOMMITTED 隔离级别，那么T2永远不会锁定锁定记录，因为它只是简单地使用撤销日志来重新构建数据，就像查询开始时一样。与SQL标准相反，Oracke READ_UNCOMMITTED将会：






为了提供一致或正确的答案，Oracle数据库将
创建包含此行的块的副本，因为它在
查询开始时存在......实际上，Oracle数据库绕过
修改后的数据 - 它会读取它，并从撤销中重新构建它
（也称为回滚）段。
的一致正确答案可以在不等待交易提交的情况下返回。







Let's suppose I got two different threads, T1 and T2, accessing concurrently the same database and fetching data from the same table.

Now on thread startup I need to fetch data from the table and store the rows into a collection, that I will then use to perform some work elsewhere. I don't want the two threads to be able to process the same data, because it will result in duplicated (and long) work. To be more concrete, this is an enterprise application that needs to load some records at startup and store it in a collection to do some extra work. The problem is that in a clustered environment, this can cause two different instances to load the same data and so the work could be duplicated. So I want the rows to be loaded only once by a single instance.

How can I avoid that scenario?

I'm currently using Hibernate and Oracle 10g. These are my solutions up to now:

• Lock the row programmatically. The first one that reads it sets some "locked" column to true, but deadlock is very likely to occur if the first thread dies without setting the row as "processed".

• Using Pessimistic Locking. I tried with LockMode.UPGRADE but that doesn't seem to help, as I'm still able to read the data from both thread at the same time.

public List<MyObject> getAllNtfFromDb() {
      Session session = HibernateUtil.getOraclesessionfactory().openSession();
      Query q = session.createQuery(
              "from MyObject n where n.state = 'NEW'");
    List<MyObject> list = (List<MyObject>) q.list();
      for (int i=0; i<list.size(); i++)
          session.lock(list.get(i), LockMode.UPGRADE);
return list;
}

Any other hints? What am I doing wrong?

Thanks.

解决方案

You need to use PESSIMISTIC_WRITE at query time:

Query q = session
    .createQuery("from MyObject n where n.state = 'NEW'")
    .setLockOptions(new LockOptions(LockMode.PESSIMISTIC_WRITE));
List<MyObject> list = (List<MyObject>) q.list();

Locking the parent objects is sufficient. Deadlocks won't necessarily occur. You might get a lock acquisition failure if the thread holding the lock doesn't release it prior to another thread from timing out waiting.

Since you are using Oracle, this is how SELECT FOR UPDATE works:

SELECT ... FOR UPDATE locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... LOCK IN SHARE MODE, or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying undo logs on an in-memory copy of the record.)

So if T1 acquired an exclusive lock on some rows, T2 won't be able to read those records until T1 commits or roll-backs. If T2 used a READ_UNCOMMITTED isolation level, then T2 won't ever block on lock records, as it simply uses undo logs to reconstruct data as if it were when the query began. As opposed to the SQL standard, the Oracke READ_UNCOMMITTED will:

To provide a consistent, or correct, answer, Oracle Database will create a copy of the block containing this row as it existed when the query began ... Effectively, Oracle Database takes a detour around the modified data—it reads around it, reconstructing it from the undo (also known as a rollback ) segment. A consistent and correct answer comes back without waiting for the transaction to commit.

这篇关于如何避免两个不同线程从DB读取相同的行（Hibernate和Oracle 10g）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！