Oracle и SQL Server для DBA из PostgreSQL: соответствия, RCSI и эскалация на 6 250 блокировках

Приходишь на проект с Oracle или SQL Server, а привычки из PostgreSQL ведут себя странно: SELECT ждёт чужой UPDATE, массовое обновление вдруг блокирует всю таблицу, '' в Oracle оказывается NULL. Причина в том, что те же задачи (версии строк, журнал, снимок, очистка) решены другими механизмами, и у каждого свой предел. Сторона PostgreSQL здесь не пересказывается: версии строк и снимок, журнал WAL, VACUUM и блокировки описаны в соседних статьях. Код Oracle и SQL Server закрыт, их сторона дана по документации с номерами версий. Из двух движков запущен только SQL Server 2022: два опыта, в одном из которых блокировки считаются до и после эскалации.

Стенд

docker network create dba-oracle-sqlserver-for-postgres-dba-net
docker run -d --name dba-oracle-sqlserver-for-postgres-dba-mssql --network dba-oracle-sqlserver-for-postgres-dba-net --memory 2g \
  -e ACCEPT_EULA=Y -e 'MSSQL_SA_PASSWORD=Pg_Dba_2026!x' -e MSSQL_PID=Developer mcr.microsoft.com/mssql/server:2022-latest
docker run -d --name dba-oracle-sqlserver-for-postgres-dba-pg --network dba-oracle-sqlserver-for-postgres-dba-net --memory 2g \
  -e POSTGRES_PASSWORD=pg postgres:18

Через ~20 секунд в SQL Server можно заходить: docker exec <имя> /opt/mssql-tools18/bin/sqlcmd -C -S localhost -U sa -P 'Pg_Dba_2026!x' -W. В обоих движках одна и та же таблица orders(id int PRIMARY KEY, n int) на 20 000 строк, n = 0. Версии из SELECT @@VERSION и select version():

вывод
Microsoft SQL Server 2022 (RTM-CU27-GDR) (KB5126934) - 16.0.4305.5 (X64)
	Developer Edition (64-bit) on Linux (Ubuntu 22.04.5 LTS) <X64>
PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit

Опыт 1: сколько блокировок держит UPDATE и когда они сливаются в одну

Документация SQL Server 2022 называет порог: эскалация возможна, когда один оператор взял не меньше 5 000 блокировок на одной таблице, а проверка идёт на каждых 1 250 новых блокировках. Блокировки видны в sys.dm_tran_locks. Транзакция обновляет первые 4 000 строк, потом первые 6 000, и в обоих случаях выводится, что она держит:

BEGIN TRAN;
UPDATE dbo.orders SET n = 1 WHERE id <= 4000;
SELECT resource_type, request_mode, COUNT(*) AS locks FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID AND resource_database_id = DB_ID() AND resource_type <> 'DATABASE'
GROUP BY resource_type, request_mode ORDER BY resource_type, request_mode;
ROLLBACK;

вывод (id <= 4000)
resource_type request_mode locks
KEY           X            4000
OBJECT        IX           1
PAGE          IX           9

На 4 000 строках транзакция держит 4 010 блокировок: по одной X на ключ плюс намерения IX на таблицу и страницы. Тот же запрос с id <= 7000:

вывод (id <= 7000)
resource_type request_mode locks
OBJECT        X            20

Ключей и страниц больше нет, осталась блокировка таблицы X. Двадцать строк — это одна блокировка в двадцати разделах: на машине с большим числом логических CPU SQL Server включает lock partitioning, IX берёт в одном разделе, а X — во всех (руководство по блокировкам); здесь cpu_count = 20 в sys.dm_os_sys_info. Где именно граница, показывает перебор числа обновлённых строк с подсчётом всех блокировок транзакции:

вывод (строк обновлено → блокировок)
5000 → 5012 (не сливались)    6230 → 6245    6232 → 6247    6234 → 6249    6236 → 20 (слились)

При 6 234 обновлённых строках блокировок 6 249 и они построчные, при 6 236 строках уже одна таблица. Порог срабатывает на 6 250 блокировках, а не на 5 000 (на 5 000 строках было 5 012 блокировок без эскалации): проверка идёт только на каждых 1 250 новых блокировках, и 6 250 — первая проверка, на которой у оператора набралось 5 000 блокировок на таблице. Для миграции важнее другое: оператор с чуть более чем 6 000 строк держит таблицу целиком, а оператор на 4 000 строк в опыте остался построчным.

Тот же UPDATE на 20 000 строках в PostgreSQL:

BEGIN;
UPDATE orders SET n = 1 WHERE id <= 20000;
SELECT locktype, mode, count(*) FROM pg_locks WHERE pid = pg_backend_pid() GROUP BY 1,2 ORDER BY 1,2;
ROLLBACK;

вывод
   locktype    |       mode       | count
---------------+------------------+-------
 relation      | AccessShareLock  |     1
 relation      | RowExclusiveLock |     2
 transactionid | ExclusiveLock    |     1
 virtualxid    | ExclusiveLock    |     1

Блокировок строк в pg_locks нет совсем: RowExclusiveLock два раза — таблица и её первичный ключ, AccessShareLock берёт сам запрос к pg_locks. Строки помечены в заголовке кортежа, поэтому эскалировать нечего (README.tuplock, REL_18_6):

Locking tuples is not as easy as locking tables or other database objects.
The problem is that transactions might want to lock large numbers of tuples at
any one time, so it's not possible to keep the locks objects in shared memory.
To work around this limitation, we use a two-level mechanism.  The first level
is implemented by storing locking information in the tuple header: a tuple is
marked as locked by setting the current transaction's XID as its XMAX, and
setting additional infomask bits to distinguish this case from the more normal
case of having deleted the tuple.  When multiple transactions concurrently
lock a tuple, a MultiXact is used; see below.  This mechanism can accommodate
arbitrarily large numbers of tuples being locked simultaneously.

Цена обратная: в PostgreSQL число блокируемых строк ничем не ограничено, но их xmax нужно потом убирать — это работа VACUUM и горизонта xmin.

Опыт 2: читатель за писателем

Одна транзакция меняет строку id = 1 и 8 секунд не завершается (WAITFOR DELAY), в это время второй сеанс читает эту строку с LOCK_TIMEOUT 2000. Сначала обычный READ COMMITTED, затем подсказка NOLOCK:

вывод
Msg 1222, Level 16, State 51, Server 9566ee0398c8, Line 3
Lock request time out period exceeded.
mode   n
NOLOCK 99

Обычное чтение уткнулось в блокировку писателя и через 2 секунды получило ошибку 1222. NOLOCK вернул 99 — значение, которое писатель потом откатил: READUNCOMMITTED/NOLOCK разрешает грязное чтение. Привычка «у нас читатели не ждут» переносится в SQL Server не через NOLOCK, а через READ_COMMITTED_SNAPSHOT (RCSI). Включаем и повторяем тот же сценарий:

ALTER DATABASE t SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
SELECT n FROM dbo.orders WHERE id = 1;
SELECT reserved_page_count, reserved_space_kb FROM sys.dm_tran_version_store_space_usage WHERE database_id = DB_ID('t');

вывод
mode n
RCSI 0
reserved_page_count reserved_space_kb
16                  128

Читатель сразу получил 0, последнюю подтверждённую версию: она лежит в version store (16 страниц, 128 КБ), и его предел — место в tempdb, о чём статья про хранение версий уже говорит. В PostgreSQL тот же сценарий (писатель держит UPDATE ... WHERE id = 1 шесть секунд, читатель с lock_timeout = '2s') вернул read committed|0 без ожидания: это поведение по умолчанию, версия лежит в самой таблице (как устроен снимок). В Oracle по документации читатель не блокирует писателя и писатель не блокирует читателя; недостающую версию Oracle берёт из undo.

Соответствия для чтения чужой системы

Все ячейки Oracle и SQL Server взяты из документации, ссылки на страницы — в источниках. Строки с числами подкреплены опытами выше.

Размер страницы сверен запросами EXEC sp_spaceused 'dbo.orders' с reserved_page_count из sys.dm_db_partition_stats и current_setting('block_size') в PostgreSQL:

вывод
reserved_page_count = 65     reserved = 520 KB     (SQL Server: 520 / 65 = 8 КБ)
block_size = 8192                                  (PostgreSQL)

Строка про '' проверена в обоих запускаемых движках: PostgreSQL вернул f, SQL Server — empty is not NULL. Для Oracle это записано в справочнике по NULL: символьное значение нулевой длины считается NULL. Следствие для миграции: WHERE col = '' в Oracle не находит ничего, а NOT NULL-колонка отвергнет пустую строку, которую PostgreSQL принимал.

Что из этого следует

• В Oracle старые версии лежат в undo, а не в таблице, поэтому роста таблицы от версий нет; вместо него у долгого отчёта может случиться ORA-01555 (undo перезаписан раньше, чем отчёт дочитал), а UNDO_RETENTION — минимальный срок хранения, не гарантия.
• В SQL Server без RCSI отчёт и UPDATE ждут друг друга. Не лечи это NOLOCK (он вернул значение, которого не будет): включай RCSI и закладывай место под version store.
• Массовые правки в SQL Server режь пачками меньше порога: 4 000 строк держались построчно, а на 6 250 блокировках они превращались в блокировку таблицы.
• Планы чини тем, что есть в движке: pg_hint_plan в PostgreSQL, SQL plan baselines в Oracle, Query Store в SQL Server. Прежде чем закреплять, убедись, что оценка строк, а не сам план, сломала запрос.

Чек-лист

1. Узнай, включён ли READ_COMMITTED_SNAPSHOT на базах SQL Server: SELECT name, is_read_committed_snapshot_on FROM sys.databases.
2. Найди в коде NOLOCK и убери там, где результат попадает в деньги или отчёты.
3. Для длинных UPDATE в SQL Server считай блокировки через sys.dm_tran_locks и держи пачку заметно ниже порога.
4. В Oracle проверь UNDO_RETENTION и самые долгие отчёты, прежде чем искать «потерянные» данные.
5. Перед переносом схемы найди '', которые в Oracle стали NULL.

Убрать стенд: docker rm -f -v dba-oracle-sqlserver-for-postgres-dba-mssql dba-oracle-sqlserver-for-postgres-dba-pg && docker network rm dba-oracle-sqlserver-for-postgres-dba-net.