Oracle тупик продолжает повторяться на той же записи

Question

У меня проблема со следующим запросом:

SELECT o.OID, o.DOSSIER_KEY, o.DOSSIER_TYPE 
FROM TVM04_VMAX_TEMP_RIGHTS      
WHERE o.DOSSIER_KEY = ? 
AND o.DOSSIER_TYPE = ? FOR UPDATE

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

Я действительно получаю Внутреннее исключение: java.sql.SQLException: ORA-00060: во время ожидания ресурса обнаружена взаимоблокировка, но вероятность того, что в нашей системе другой tx снова получает доступ к той же записи, каждый раз, когда я запускаю запрос также, кажется, мне близко к 0.

Это мой дамп файла Oracle:

Deadlock graph:
                   ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds      waits
TX-00400007-008c00ec       304     786     X            300     757           X
TX-00070016-0002dae6       300     757     X            304     786           X

session 786: DID 0001-0130-011DAD37     session 757: DID 0001-012C-00B1E41F 
session 757: DID 0001-012C-00B1E41F     session 786: DID 0001-0130-011DAD37 

Rows waited on:
Session 786: obj - rowid = 0003921D - AAA5IdAAMAAHjdyAAG
(dictionary objn - 234013, file - 12, block - 1980274, slot - 6)
Session 757: obj - rowid = 0003921D - AAA5IdAARAACfC6AAz
(dictionary objn - 234013, file - 17, block - 651450, slot - 51)

----- Information for the OTHER waiting sessions -----
Session 757:
sid: 757 ser: 387 audsid: 18983600 user: 64/WLSP01
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 300 O/S info: user: oracle, term: UNKNOWN, ospid: 42730046
image: oracle@H50A450
client details:
O/S info: user: weblogic, term: unknown, ospid: 1234
machine: H53AD20 program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
current SQL:
SELECT o.OID, o.DOSSIER_KEY, o.DOSSIER_TYPE FROM TVM04_VMAX_TEMP_RIGHTS     o    WHERE o.DOSSIER_KEY = :1  AND o.DOSSIER_TY
PE = :2  FOR UPDATE

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=489bnqugb9wsz) -----
SELECT o.OID, o.DOSSIER_KEY, o.DOSSIER_TYPE FROM TVM04_VMAX_TEMP_RIGHTS o    WHERE o.DOSSIER_KEY = :1  AND o.DOSSIER_TYPE
 = :2  FOR UPDATE
===================================================

Есть ли у вас подсказка, почему и как это могло произойти?

Заранее большое спасибо!

Fabio

Answer 1

Я бы предложил использовать предложение 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>

Так, department = 10 были заблокированы сеансом 1, а затем department = 20 заблокированы сессией 2.

Кроме того, посмотрите мой ответ здесь для лучшего понимания тупиков. Пожалуйста, прочтите Понимание Oracle Deadlock.