oracle - 需要帮助理解 SELECT ... FOR UPDATE 导致死锁的行为

Question

我有两个并发事务执行这段代码(为了说明目的进行了简化):

@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，而另一个事务应该等待 A 的提交，然后再执行 SELECT。但是，这段代码是死锁的。我哪里错了？

Answer 1

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

参见 this section在概念手册中了解更多信息。

要解决此问题，请为任何未编制索引的外键添加索引。如果列名是标准的，这样的脚本可以帮助您找到潜在的候选者:

--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';