如何避免两个不同的线程从 DB 读取相同的行（Hibernate 和 Oracle 10g）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27968877/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to avoid two different threads read the same rows from DB (Hibernate and Oracle 10g)
提问by user2799534
Let's suppose I got two different threads, T1 and T2, accessing concurrently the same database and fetching data from the same table.
假设我有两个不同的线程，T1 和 T2，同时访问同一个数据库并从同一个表中获取数据。
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:
我目前正在使用 Hibernate 和 Oracle 10g。这些是我到目前为止的解决方案：
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.
以编程方式锁定行。第一个读取它的人将一些“锁定”列设置为真，但如果第一个线程在没有将行设置为“已处理”的情况下死亡，则很可能发生死锁。
使用悲观锁。我尝试过 LockMode.UPGRADE 但这似乎没有帮助，因为我仍然能够同时从两个线程读取数据。
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; }
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.
谢谢。
采纳答案by Vlad Mihalcea
You need to use PESSIMISTIC_WRITEat query time:
您需要在查询时使用PESIMISTIC_WRITE：
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 UPDATEworks:
由于您使用的是 Oracle，这就是SELECT FOR UPDATE 的工作方式：
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.)
SELECT ... FOR UPDATE 锁定行和任何关联的索引条目，就像您为这些行发出 UPDATE 语句一样。其他事务被阻止更新这些行、执行 SELECT ... LOCK IN SHARE MODE 或读取某些事务隔离级别的数据。一致读取忽略对读取视图中存在的记录设置的任何锁定。（旧版本的记录不能被锁定；它们是通过在记录的内存副本上应用撤消日志来重建的。）
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_UNCOMMITTEDisolation 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:
因此，如果 T1 在某些行上获得了排他锁，则在 T1 提交或回滚之前，T2 将无法读取这些记录。如果 T2 使用READ_UNCOMMITTED隔离级别，那么 T2 将永远不会阻塞锁定记录，因为它只是使用撤消日志来重建数据，就像查询开始时一样。与 SQL 标准相反，Oracke READ_UNCOMMITTED 将：
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.
为了提供一致或正确的答案，Oracle 数据库将创建包含该行的块的副本，因为它在查询开始时就存在......实际上，Oracle 数据库绕过修改后的数据——它绕过它读取，重建它来自撤消（也称为回滚）段。无需等待事务提交即可返回一致且正确的答案。
