Нужна помощь в понимании поведения SELECT ... для обновления, вызывающего тупик

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 заблокирует всю дочернюю таблицу, удалив строку из родительской таблицы. Если несколько операторов выполняются одновременно, даже для другого пользователя, одни и те же дочерние таблицы будут заблокированы. Поскольку порядок этих рекурсивных операций не может контролироваться, возможно, что несколько сеансов заблокируют одни и те же ресурсы в другом порядке, что приведет к взаимоблокировке.

См. Этот раздел в руководстве Concepts для получения дополнительной информации.

Чтобы решить эту проблему, добавьте индексы к любым неиндексированным внешним ключам. Если имена столбцов стандартны, такой скрипт может помочь вам найти потенциальных кандидатов:

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

Answer 2

Когда вы используете PESSIMISTIC_WRITE, JPA обычно транслирует его в SELECT FOR UPDATE, это делает блокировку в базе данных, не обязательно для строки, это зависит от базы данных и от того, как вы конфигурируете блокировку, по умолчанию блокировка выполняется на странице или блокируется не для строки, поэтому проверьте документацию базы данных, чтобы подтвердить, как ваша база данных выполняет блокировку, также вы можете изменить ее, чтобы применить блокировку для строки. Когда вы вызываете метод deleteAccounts, он запускает новую транзакцию, и блокировка будет активна до фиксации (или отката) транзакции, в этом случае, когда метод завершится, если другая транзакция захочет получить такую ​​же блокировку, она не сможет, и я думаю, Вот почему у вас есть мертвая блокировка, я предлагаю вам попробовать другой механизм, возможно, оптимистическую блокировку или блокировку по сущности.

Вы можете попробовать установить тайм-аут на блокировку таким образом:

em.createQuery("select u from User", User.class)
.setLockMode(LockModeType.PESSIMISTIC_WRITE)
.setHint("javax.persistence.lock.timeout", 5000)
.getResultList();

Я нашел хорошую статью, которая лучше объясняет эту ошибку, она вызвана базой данных:

Oracle автоматически обнаруживает взаимоблокировки и устраняет их, откатывая одну из транзакций / операторов, участвующих в взаимоблокировке, высвобождая, таким образом, один набор ресурсов / данных, заблокированных этой транзакцией. При откате сеанса будет обнаружена ошибка Oracle: ORA-00060: во время ожидания ресурса обнаружена тупиковая ситуация. Oracle также выдаст подробную информацию в файле трассировки в каталоге UDUMP базы данных.

Чаще всего эти взаимоблокировки вызваны приложениями, которые включают в себя обновление нескольких таблиц в одной и той же транзакции, и несколько приложений / транзакций одновременно работают с одной и той же таблицей. Этих взаимных блокировок с несколькими таблицами можно избежать, блокируя таблицы в одном и том же порядке во всех приложениях / транзакциях, таким образом предотвращая состояние взаимоблокировки.