Тупик в Oracle

Я получаю тупик при выполнении следующих запросов в Oracle

update MYTABLE 
   set COLUMN1 = COLUMN1 + 589 
 where COLUMN1 >= 7988 
   and COLUMN1 <= 7988

update MYTABLE 
   set COLUMN1 = COLUMN1 + 660 
 where COLUMN1 >= 7840

Почему я получу тупик, когда оба запроса выполняются в двух отдельных транзакциях? Я хочу сказать, что второй запрос будет ждать, пока первый запрос не завершит свою транзакцию, когда для изоляции установлено значение READCOMMITTED, верно?

Есть ли какой-нибудь намек, который я могу применить, чтобы избежать тупика?

Примечание. COLUMN1 не является PK в этой таблице, однако приложение гарантирует отсутствие дубликатов.


sql oracle database-deadlocks
person user3374369    schedule 03.03.2014    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Да, те заявления, которые вы представили, могут привести к тупику. Один из самых простых сценариев:

  1. Сеанс 1 выдает первый оператор update;
  2. Сеанс 2 выдает второй оператор update;
  3. Сеанс 1 выдает второй оператор update;

Пример:

-- Session #1 locks a subset of rows
SQL> update mytable 
        set column1 = column1 + 589 
      where column1 >= 7988 
        and column1 <= 7988;

1 row updated.

                                           -- session #2
                                           -- trying to lock subset of rows
                                           -- locked by the first session update
                                          SQL> update mytable 
                                                  set column1 = column1 + 660 
                                                where column1 >= 7840;


-- session #1
-- trying to lock subset of rows that are locked
-- by session #2 update, that in turn's
-- trying to lock the subset of rows 
-- locked by session #1 update statement
SQL> update mytable 
        set column1 = column1 + 660 
      where column1 >= 7840;


                                          -- session #2
                                          -- thus deadlock
                                          update mytable 
                                              set column1 = column1 + 660 
                                            where column1 >= 7840
                                                              *
                                          ERROR at line 1:
                        ORA-00060: deadlock detected while waiting for resource

Когда вы создаете оператор update или delete, используя предложение where, оператор становится оператором, состоящим из двух частей, состоящих из частей для чтения и записи. Часть чтения резервирует строки for update, а запись изменяет их. Таким образом, оператор update первого сеанса блокирует некоторый набор данных для обновления, изменяет их и сохраняет их заблокированными до тех пор, пока вы не выполните commit или rollback, оператор update второго сеанса блокирует свою часть строк, а первый сеанс пытается заблокировать ту часть данных, которая обрабатывается. заблокирован вторым, таким образом, тупик. Если вы выдаете этот оператор обновлений в одном сеансе, вообще не должно быть тупиковой блокировки. Но когда вы выполняете эти операторы обновления более чем в одном сеансе одновременно без фиксации или отката, будьте готовы к исключению ORA-00060.

Каждая взаимоблокировка сопровождается созданием файла предупреждения. Проверьте файл, чтобы получить более подробную информацию о взаимоблокировке.

Есть ли какой-нибудь намек, который я могу применить, чтобы избежать тупика?

Нет, такой подсказки нет.

person Nick Krasnov    schedule 03.03.2014

arrow_upward
2
arrow_downward

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

Кроме того, проверьте ответ в этот вопрос на SO.

Проверьте там блокировку, проверив таблицу dba_lock. См. документы здесь.

person Patrick Hofman    schedule 03.03.2014
comment
Но если первая транзакция блокирует всю таблицу, не будет ли вторая просто блокироваться до тех пор, пока первая не снимет эти блокировки? - person Alex Poole; 03.03.2014
comment
@AlexPoole: я думаю, вы правы, если только Oracle не блокируется на уровне блоков. - person Patrick Hofman; 03.03.2014
comment
Нет, Патрик, в этой ситуации (присутствует условие where) вся таблица не будет заблокирована, будет заблокировано только подмножество строк. Во-вторых, наличие или отсутствие индекса не поможет избежать взаимоблокировки в этой ситуации (наличие индекса может помочь, когда между таблицами и индексом, созданным для столбца внешнего ключа, существует отношение master\detail). - person Nick Krasnov; 03.03.2014