需要帮助了解SELECT ... FOR UPDATE导致死锁的行为 [英] Need help understanding the behaviour of SELECT ... FOR UPDATE causing a deadlock

问题描述

我有两个并发事务执行这部分代码(为便于说明而简化):

I have two concurrent transactions executing this bit of code (simplified for illustration purposes):

@Transactional
public void deleteAccounts() {
    List<User> users = em.createQuery("select u from User", User.class)
                         .setLockMode(LockModeType.PESSIMISTIC_WRITE)
                         .getResultList();
    for (User user : users) {
        em.remove(user);
    }
}

我的理解是，其中一个事务(例如事务A)应首先执行SELECT，锁定所需的所有行，然后继续执行DELETE，而另一个事务应在执行SELECT之前等待A的提交.但是，此代码处于死锁状态.我在哪里错了?

My understanding is that one of the transactions, say transaction A, should execute the SELECT first, lock all the rows it needs and then go on with the DELETEs while the other transaction should wait for A's commit before performing the SELECT. However, this code is deadlocking. Where am I wrong?

推荐答案

USER表中可能有很多引用它的外键.如果其中任何一个未建立索引，Oracle将在从父表删除该行的同时锁定整个子表.如果多个语句同时运行(即使对于不同的用户)，则相同的子表将被锁定.由于这些递归操作的顺序无法控制，因此多个会话可能会以不同的顺序锁定相同的资源，从而导致死锁.

The USER table probably has a lot of foreign keys referring to it. If any of them are un-indexed Oracle will lock the entire child table while it deletes the row from the parent table. If multiple statements run at the same time, even for a different user, the same child tables will be locked. Since the order of those recursive operations cannot be controlled it is possible that multiple sessions will lock the same resources in a different order, causing a deadlock.

请参见本节概念手册以获取更多信息.

See this section in the Concepts manual for more information.

要解决此问题，请将索引添加到所有未索引的外键.如果列名是标准的，则类似这样的脚本可以帮助您找到潜在的候选人:

To resolve this, add indexes to any un-indexed foreign keys. If the column names are standard a script like this could help you find potential candidates:

--Find un-indexed foreign keys.
--
--Foreign keys.
select owner, table_name
from dba_constraints
where r_constraint_name = 'USER_ID_PK'
    and r_owner = 'THE_SCHEMA_NAME'
minus
--Tables with an index on the relevant column.
select table_owner, table_name
from dba_ind_columns
where column_name = 'USER_ID';

这篇关于需要帮助了解SELECT ... FOR UPDATE导致死锁的行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！