Oracle Deadlock при обновлении

У меня есть база данных, которая используется некоторыми людьми, и сервис для обновления личных данных одновременно.

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

Возможно ли, что я могу определить эту ситуацию? Вероятно, служба обновлений может отправить заявление следующим образом:

UPDATE person 
  SET email_address = 'new.email@company.com' WHERE person_id='1234567'
ON LOCKED ERROR;

Этот оператор должен получить ошибку, если в этой строке есть блокировка.

Или я могу настроить сервер Oracle для отправки кода ошибки через определенное время, если блокировка строки не завершается?

Спасибо за помощь.

2 ответа

Решение

Вы можете сделать это в два этапа; запросить таблицу с FOR UPDATE NOWAIT, который выдаст исключение, если строка уже заблокирована, а затем выполнит обновление (и зафиксирует), если не произойдет ошибка:

SELECT * FROM person WHERE person_id = 1234567 FOR UPDATE NOWAIT;
UPDATE person SET email_address = 'new.email@company.com' WHERE person_id='1234567';

Если вы сделаете это из двух сессий, без фиксации, то вторая, которая запустит select, увидит:

ORA-00054: ресурс занят и получен с указанным значением NOWAIT или истекло время ожидания

Вы также можете иметь тайм-аут с WAIT <time>:

SELECT * FROM person WHERE person_id = 1234567 FOR UPDATE WAIT 3;
UPDATE person SET email_address = 'new.email@company.com' WHERE person_id='1234567';

В этом случае второй абонент получит сообщение об ошибке, если первый не принял / откатил в течение 3 секунд:

ORA-30006: ресурс занят; приобрести с истекшим временем ожидания WAIT

Если вы вызываете это через JDBC или OCI и т. Д., Возможно, вам понадобятся отдельные вызовы типа "back-to-back" в одном и том же сеансе, причем первый вызов обрабатывает исключение, если оно было сгенерировано.

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

В идеале читатели не блокируют писателей, а писатели не блокируют читателей.

То, что вы описываете, не является сценарием DEADLOCK. Когда сеанс выполняет обновление, он получает монопольную блокировку на уровне строк, и другой сеанс, пытающийся обновить эти строки, должен ждать, пока блокировка не будет снята COMMIT/ROLLBACK.

Тупиковая ситуация возникает, когда два или более сеансов ждут друг друга для блокировки.

Или я могу настроить сервер Oracle для отправки кода ошибки через определенное время, если блокировка строки не завершается?

Чтобы проверить сеанс блокировки и класс ожидания, вы можете запросить представление v$session:

select sid,
       status,
       program,
       sql_id, 
       state, 
       wait_class, 
       blocking_session_status,
       event 
from v$session;

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

Смотрите аналогичный вопрос, на который я ответил здесь /questions/11266166/tupiki-v-oracle/11266170#11266170

Подробная демонстрация и примеры взаимоблокировок см. В разделе Понимание взаимоблокировки Oracle.

Если вы используете FOR UPDATE NOWAIT, Oracle не позволит вам обновить эти строки и выдать следующую ошибку:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Например,

Сессия 1:

SQL> SELECT empno, deptno
  2    FROM emp  WHERE
  3   deptno = 10
  4  FOR UPDATE NOWAIT;

     EMPNO     DEPTNO
---------- ----------
      7782         10
      7839         10
      7934         10

SQL>

Сессия 2:

SQL> SELECT empno, deptno
  2    FROM emp  WHERE
  3   deptno in (10, 20)
  4  FOR UPDATE NOWAIT;
  FROM emp  WHERE
       *
ERROR at line 2:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Если пойти дальше, если вы хотите избежать обновления уже заблокированных строк, вы можете использовать предложение FOR UPDATE SKIP LOCKED, чтобы другие сеансы не извлекали строки для обновления, которые уже заблокированы.

Например,

Сессия 1:

SQL> SELECT empno, deptno
  2    FROM emp  WHERE
  3   deptno = 10
  4  FOR UPDATE NOWAIT;

     EMPNO     DEPTNO
---------- ----------
      7782         10
      7839         10
      7934         10

SQL>

Сессия 2:

SQL> SELECT empno, deptno
  2    FROM emp  WHERE
  3   deptno in (10, 20)
  4  FOR UPDATE NOWAIT;
  FROM emp  WHERE
       *
ERROR at line 2:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Теперь давайте пропустим строки, заблокированные сеансом 1.

SQL> SELECT empno, deptno
  2    FROM emp  WHERE
  3   deptno IN (10, 20)
  4  FOR UPDATE SKIP LOCKED;

     EMPNO     DEPTNO
---------- ----------
      7369         20
      7566         20
      7788         20
      7876         20
      7902         20

SQL>

Таким образом, отдел был заблокирован сеансом 1, а затем отдел был заблокирован сеансом 2.

Посмотрите этот похожий вопрос о том, как избежать обновлений в уже заблокированных строках. Взаимная блокировка Oracle повторяется для той же записи.

Другие вопросы по тегам