В 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 последовательно выполняются команды:
Третья сессия обновила 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Выполняем те же команды, в той же последовательности:
Однако, результат получился другой - третья сессия обновила все строки, кроме четвёртой строки:
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) и порядок просмотра, блокировки, обновления строк зависит от метода доступа: без индекса - в порядке расположения первых версий строк в цепочке версий, с индексом - в порядке выборки ссылок на версии строк в индексе.
Детальное описание перечитывания строк в Oracle Database: https://ru.scribd.com/document/664443628/Write-Consistency
Взаимоблокировка
Посмотрим пример взаимоблокировки при параллельном обновлении строк. Повторим команды и добавим в 1 сессии обновление двух строк:
В Oracle Database тоже будет обнаружена взаимоблокировка, команда в первой сессии отменится и третья сессия сможет обновить 4 строки.
Заключение
В PostgreSQL команда UPDATE, столкнувшись с заблокированной строкой, ждёт получения блокировки, затем перечитывает эту строку и, если она подпадает под условие WHERE команды UPDATE, то обновляет эту строку. Если строка не подпадает под условие обновления, то снимает блокировку, пропускает эту строку и переходит к следующей строке. При этом строки, которые уже были обновлены, остаются заблокированными и обновлёнными.
В Oracle Database команда UPDATE, столкнувшись с заблокированной строкой, ждёт получения блокировки, затем перечитывает все строки заново, кроме тех, которые были обновлены командой. Если команда ещё раз столкнётся с заблокированной строкой, то повторно перечитывает все строки, которые ещё не обновила.
Комментарии (23)
Mausglov
10.01.2026 15:47запишите и запомните, потому что понять это невозможно
Тут других вариантов не остаётся. Инженеры PostgreSQL выбрали так, инженеры Oracle - этак. Каждый из вариантов по-своему справедлив.
WaitEvent
10.01.2026 15:47PostgreSQL doesn't have this (because it would require implicit savepoints for each statement, and savepoints are expensive in PostgreSQL) and restarts only the reading of the row. This results in inconsistent snapshots (results with rows from two states), but it still fits the SQL standard definition of read committed (which requires reading only the committed changes, ignoring that in MVCC databases, they can come from different commit times).
OlegIct Автор
10.01.2026 15:47никаких откатов строк нет в PostgreSQL, точки сохранения не нужны. Это проверяется в PostgreSQL pageinspect и не приведено, чтобы не переусложнять. В Oracle Database блокировки со строк не снимаются.
WaitEvent
10.01.2026 15:47верно у пг нет микроткатов, там просто inconsistent snapshots. оракл же реализовал микрооткаты
WaitEvent
10.01.2026 15:47вот и выросло поколение детей, ничего не слышавших про микрорестарт.
https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:11504247549852
OlegIct Автор
10.01.2026 15:47утрируете :) Там путаница в терминах (введён термин "restart", микрорестарта и микроотката нет), применяется триггер, автономная транзакция, которыми нельзя доказать, что физически был откат строки. В конце концов предъявили Кайту "which seems to be the opposite of what you say in your blog. Could you please clarify". Для защиты чести Кайта был привлечен Стив Адамс, который увел тему в сторону "My oracle-l post was showing another reason why BEFORE ROW triggers should be avoided". Изоборетённый Кайтом "перезапуск" путает (блокировки строк не снимаются), "перечитывание" более корректно.
WaitEvent
10.01.2026 15:47нет там путаницы, Кайт обозвал это mini-rollback, на то они и мини, что не снимают блокировок. но тут то дело не в жонглировании терминов, а в наличии самого механизма. мсскл (на rc snapshot) блокировки предикатов накладывает, убивая параллельность и перфоменс, оракл mini-rollback устраивает, а пг ничего не делает и просто забивает на консистентность.
пост Кайта тогда открыл портал в ад и несколько лет каждый считал своим долгом хоть что-то ляпнуть на тему этих mini-rollback, т.к. формально они противоречили утверждению в документации (в доке утверждалось, что на RC запрос видит данные на момент старта запроса). автономные триггеры же лишь инструмент, помогающий понять что там под капотом происходит.
но это все лирика, тут важно что у пг без mini-rollback результат не другой, а неконсистентный.
OlegIct Автор
10.01.2026 15:47В том и дело, что даже у Кайта со товарищи "согласия нет" и что делать разработчикам, как не запутаться в целостностях.
Слово "неконситентный" излишне пугает, но пугающего нет: если в команде используется одна таблица, то неконсистентность (= отсутствие целостности по чтению = consistent read = выдача строк не на один момент времени) не играет роли.
В PostgreSQL неконсистентность есть в более неприятном месте: если в SELECT вызывается функция VOLATILE и в ней SELECT, то эти два SELECT выдают строки на разные моменты времени.
Про "на уровне Read Commited запрос видит данные на момент старта запроса" - упрощённое правило. Плюс в текстах опускают слова. Например, "a SELECT query (without a
FOR UPDATE/SHAREclause)" сокращают до "query".WaitEvent
10.01.2026 15:47в одной таблице у пг тоже играет роль.
когда-то у меня был документ с деталями по ораклу, вот он наверное
https://www.scribd.com/document/664443628/Write-Consistency
Akina
Думаю, совершенно необходимо дать подробное объяснение, почему для запроса
в случае, когда {column2}="id", выполняется блокировка (сессия 2), тогда как при {column2}="n" блокировки то нет (сессия 1), то есть (сессия 3). Потому что это имхо самый неочевидный в статье момент.
Да и вообще, я вижу "как", но не очень вижу "почему". Я бы даже сказал, что совсем не вижу. А ценность выводов в духе "запишите и запомните, потому что понять это невозможно" мне кажется не очень высокой.
Mausglov
можете как-то развернуть свой вопрос? Во второй сессии выполняется блокировка, потому что UPDATE был, а COMMIT-а ещё не было. Более общая формула: блокируются те строки, которые изменены, и блокировка держится до завершения или отката транзакции.
Для первой сессии нигде не говорится про блокировки, потому что, кроме последнего случая в Oracle, эта транзакция не мешает другим транзакциям, и сама от них не зависит.
OlegIct Автор
Согласен. Ваши комментарии во всех темах профессиональны. Про блокировки по id и n подводных камней нет - срока блокируется, если WHERE истинно для неё. Первичный ключ роли не играет. Условия update выбраны, чтобы команды выглядели просто.
Строка, на которой заблокировался UPDATE обязана перечитыватся, чтобы не допустить феномена lost update, она перечитывается в обеих базах и они обе соответствуют стандарту SQL. Но Oracle добавляет от себя перечитывание всех незаблокированных строк (заблокированные не играют роли, так как другие сесии их не могут менять). Вкратце, это не дает преимуществ, как написал Mausglov. Это примерно как "что быстрее" shutdown transactional или immediate - без разницы, как повезёт, хотя кажется, что transactional как то "получше". Не вкратце, надо тратить время и текст бы усложнился. Цель статьи - описать просто и коротко, чтобы читатели запомнили важный нюанс про перечитывание. Если удастся просто сформулировать следствия этой разницы, то допишу. Основное: в Oracle, обычно, select for update используют (особо не задумываясь, потому что так принято), в PostgreSQL перечитывание только одной заблокированной строки позволяет быстро работать и без for update, то есть база хорошо работает с любыми командами.
Akina
Это понятно. Товарищ чуть ниже совершенно правильно пишет - by design, и застрелись.
Признаться, я лично считаю, что запоминание этого нюанса - не имеет смысла. Ну то есть нужно помнить, что разница - есть. Но не более. Это знание может потребоваться разве что при переносе БД с одной СУБД на другую, ну и в некоторых ещё более экзотических случаях вроде гетерогенного зоопарка. Может быть, даже для рядового пользователя было бы полезнее две отдельные статьи - одна про одну СУБД, вторая про другую,- ведь подавляющее большинство интересуют тонкости поведения одной СУБД, а не различия в поведении двух разных СУБД, процент тех, кто настолько глубоко работает сразу с несколькими СУБД, невелик. И тогда в отдельных статьях можно было бы ещё и уделить немножко внимания пояснениям, почему в этой СУБД именно так, а не иначе. Хотя всё равно by design не избежать.
OlegIct Автор
Про перечитывание строки стоит знать, так как обычно запоминают, что "база данных гарантирует целостность по чтению для одиночных запросов" и "одиночный запрос использует моментальный снимок (snapshot)", предполагая, что данные выдаются на один момент времени для всех команд. А это оказывается верным только для одиночного SELECT без for update/share и volatile функций. Для остальных команд не всегда.