Если UPDATE столкнулся с заблокированной строкой

В PostgreSQL и Oracle Database команда UPDATE, столкнувшаяся с заблокированной строкой, ведёт себя по-разному. В статье рассматривается, как выполняется UPDATE в этих базах данных. Это может быть полезно при миграции кода приложения между этими базами данных.

UPDATE в PostgreSQL

Создание таблицы с несколькими строками:

drop table t;
create table t (id bigserial primary key, n numeric default 1);
insert into t(n) values (1),(2),(1),(2),(1),(2);

Начальное состояние таблицы:

select * from t;
 id | n 
----+---
  1 | 1
  2 | 2
  3 | 1
  4 | 2
  5 | 1
  6 | 2
(6 rows)

В трёх сессиях утилиты psql последовательно выполняются команды:

Как работает UPDATE в PostgreSQL

Третья сессия обновила 2 и 6 строки:

select * from t order by id;
 id | n 
----+---
  1 | 2
  2 | 3
  3 | 2
  4 | 1
  5 | 2
  6 | 3
(6 rows)

В PostgreSQL команда UPDATE пропускает строки, которые не подпадают под её условие WHERE (в примере третья сессия пропустила 1,3 строки), обновляет незаблокированные строки (третья сессия обновила 2 строку), а столкнувшись с блокировкой строки, ждёт получения блокировки (4 строка). После получения блокировки перечитывает строку, перепроверяя условие WHERE (после обновления 2 сессией, 4 строка перестала удовлетворять условию). Если строка не соответствует условию, то пропускает её (третья сессия не стала обновлять 4 строку), если соответствует — обновляет.

Блокировки с уже обновленных строк не снимаются, команда UPDATE продолжает работать. В команде UPDATE нельзя указать порядок просмотра строк (нет выражения ORDER BY) и порядок просмотра, блокировки, обновления строк зависит от метода доступа: без индекса - в порядке расположения первых версий строк в цепочке версий, с индексом - в порядке выборки ссылок на версии строк в индексе.

UPDATE в Oracle Database

Создание аналогичной таблицы, как в примере для PostgreSQL:

drop table t;
create table t (id number primary key, n number default 1);
insert into t values (1,1);
insert into t values (2,2);
insert into t values (3,1);
insert into t values (4,2);
insert into t values (5,1);
insert into t values (6,2);
commit;

Начальное состояние таблицы такое же, как в предыдущем примере для PostgreSQL:

select * from t;

ID    N
-- ----
 1    1
 2    2
 3    1
 4    2
 5    1
 6    2

Выполняем те же команды, в той же последовательности:

Как работает UPDATE в Oracle Database

Однако, результат получился другой - третья сессия обновила все строки, кроме четвёртой строки:

select * from t order by id;
ID    N
-- ----
 1    3
 2    3
 3    3
 4    1
 5    3
 6    3
6 rows selected.

Как и в PostgreSQL, команда UPDATE пропустила строки, которые не подпадают под её условие WHERE (в примере третья сессия пропустила 1,3 строки), обновила незаблокированные строки (третья сессия обновила 2 строку), а столкнувшись с блокировкой строки, стала ждать получения блокировки (4 строка).

В отличие от PostgreSQL, команда UPDATE, после получения блокировки перечитывает все строки (кроме тех, которые уже обновила и заблокировала), перепроверяя условие WHERE. Если строка не соответствует условию, то пропускает её, если соответствует - блокирует и обновляет. В примере, 3 сессия обновила все строки, кроме 4.

Если, в процессе перечитывания, команда натолкнется на заблокированную строку, то снова начнет перечитывать все необновлённые строки заново. При большом числе обновляемых строк и вероятности их изменения одновременно работающими сессиями, команда UPDATE в Oracle Database будет многократно перечитывать одни и те же строки.

Как и в PostgreSQL, блокировки с уже обновленных строк не снимаются; в команде UPDATE нельзя указать порядок просмотра строк (нет выражения ORDER BY) и порядок просмотра, блокировки, обновления строк зависит от метода доступа: без индекса - в порядке расположения первых версий строк в цепочке версий, с индексом - в порядке выборки ссылок на версии строк в индексе.

Взаимоблокировка

Посмотрим пример взаимоблокировки при параллельном обновлении строк. Повторим команды и добавим в 1 сессии обновление двух строк:

deadlock при обновлнении строк

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

Заключение

В PostgreSQL команда UPDATE, столкнувшись с заблокированной строкой, ждёт получения блокировки, затем перечитывает эту строку и, если она подпадает под условие WHERE команды UPDATE, то обновляет эту строку. Если строка не подпадает под условие обновления, то снимает блокировку, пропускает эту строку и переходит к следующей строке. При этом строки, которые уже были обновлены, остаются заблокированными и обновлёнными.

В Oracle Database команда UPDATE, столкнувшись с заблокированной строкой, ждёт получения блокировки, затем перечитывает все строки заново, кроме тех, которые были обновлены командой. Если команда ещё раз столкнётся с заблокированной строкой, то повторно перечитывает все строки, которые ещё не обновила.