Транзакції та блокування

Як база зберігає дані цілісними за паралельної роботи: що гарантує транзакція, чим відрізняються рівні ізоляції, як влаштовані блокування й версії рядків у SQL Server і PostgreSQL і як писати код, що переживає дедлоки та конфлікти. Для навантажених OLTP-систем це одна з головних тем співбесіди.

Перед цією темою: SQL і T-SQL, Індекси й плани запитів.

Junior+

Що таке транзакція і що означає ACID?

Відповідь

Що це

Транзакція — послідовність операцій, яку база виконує як одну логічну одиницю роботи. Класичний приклад — переказ: списати 100 з рахунку A і зарахувати на рахунок B.

Властивість	Що гарантує
Atomicity, атомарність	виконується все або нічого
Consistency, узгодженість	після транзакції дотримано ключів, `CHECK`, зовнішніх ключів
Isolation, ізоляція	паралельні транзакції не бачать проміжних станів одна одної
Durability, довговічність	зафіксоване переживе збій сервера

Як користуватися

BEGIN TRAN відкриває транзакцію, COMMIT фіксує зміни, ROLLBACK скасовує їх. Без BEGIN TRAN кожен оператор — окрема транзакція.

Типова помилка

Вважати, що будь-яка помилка відкочує транзакцію повністю. У SQL Server порушення CHECK за замовчуванням скасовує лише один оператор, і наступний COMMIT зафіксує решту.

Як влаштовано

Режими. Автокоміт — за замовчуванням у SQL Server і SqlClient. Явна транзакція — BEGIN TRAN … COMMIT. Неявна — SET IMPLICIT_TRANSACTIONS ON: транзакція відкривається сама, а закривати її треба вручну.
Вкладеність. Справжніх вкладених транзакцій немає. Внутрішній BEGIN TRAN збільшує @@TRANCOUNT, внутрішній COMMIT лише зменшує його, а ROLLBACK відкочує все до зовнішнього BEGIN TRAN. Частковий відкат — до точки збереження SAVE TRAN.
Помилки. Без XACT_ABORT порушення ключа або CHECK перериває лише оператор, транзакція лишається відкритою. З XACT_ABORT ON будь-яка помилка виконання відкочує всю транзакцію. У тригерах він увімкнений за замовчуванням.

Пастки

Таймаут команди. Клієнт перериває батч, але транзакція лишається відкритою й тримає блокування, доки з’єднання не закриють або не візьмуть із пулу знову. XACT_ABORT ON закриває і цей випадок.
ROLLBACK у процедурі. Усередині чужої транзакції він обнуляє @@TRANCOUNT, і код, що викликає, отримує помилку 266 про розбіжність лічильника.

Правило

Процедура з транзакцією починається з SET XACT_ABORT ON, тіло — у BEGIN TRY, у CATCH — IF @@TRANCOUNT > 0 ROLLBACK і THROW.

Під капотом

Write-ahead logging. Зміна спершу потрапляє в журнал, потім у сторінку даних у пам’яті. Під час COMMIT сервер чекає запису логу на диск (очікування WRITELOG), а брудні сторінки пізніше пишуть checkpoint і lazy writer.
Відновлення. Після збою сервер читає журнал: повторює зафіксоване (redo) і відкочує незафіксоване (undo). Так забезпечуються A і D.
ADR. Із SQL Server 2019, за замовчуванням вимкнений. Версіонує зміни в persistent version store, тому відкат і відновлення майже миттєві навіть після довгої транзакції, а журнал усікається, попри неї.

Ціна

Кожен коміт — синхронний запис у лог. Мільйон поодиноких INSERT в автокоміті — мільйон скидань журналу; пачки по кілька тисяч рядків в одній транзакції помітно швидші.
Довга транзакція тримає блокування, не дає усікати журнал (log_reuse_wait_desc = ACTIVE_TRANSACTION) і без ADR довго відкочується.

Компроміси

Delayed durability (SQL Server 2014+) підтверджує коміт до запису логу на диск: менша затримка, але під час збою втрачаються останні транзакції. У PostgreSQL схожий режим — synchronous_commit = off.
Літера I в ACID — шкала, а не абсолют: рівень ізоляції за замовчуванням допускає частину аномалій заради паралельності.

Як перевірити в продакшені

DBCC OPENTRAN і sys.dm_tran_database_transactions показують найстарішу відкриту транзакцію.
Висока частка WRITELOG у sys.dm_os_wait_stats — ознака надто частих комітів або повільного диска під журналом.

Підсумок: довговічність купується синхронним записом журналу під час коміту, тому розмір транзакції — компроміс між кількістю скидань логу й часом утримання блокувань.

T-SQL

CREATE TABLE dbo.Accounts (Id int PRIMARY KEY, Balance money CHECK (Balance >= 0));
INSERT dbo.Accounts VALUES (1, 50), (2, 0);

-- Пастка: без XACT_ABORT помилка скасовує лише свій оператор
BEGIN TRAN;
UPDATE dbo.Accounts SET Balance = Balance + 100 WHERE Id = 2; -- виконається
UPDATE dbo.Accounts SET Balance = Balance - 100 WHERE Id = 1; -- Msg 547: порушення CHECK
COMMIT; -- зафіксує половину переказу: на рахунку 2 уже 100

-- Правильно: будь-яка помилка відкочує всю транзакцію
SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRAN;
    UPDATE dbo.Accounts SET Balance = Balance - 100 WHERE Id = 1;
    UPDATE dbo.Accounts SET Balance = Balance + 100 WHERE Id = 2;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK; -- переказ відкочено повністю
    THROW;                       -- повернути помилку клієнту
END CATCH;

Що спитають далі

Що буде, якщо в транзакції впаде другий UPDATE із трьох? — Без XACT_ABORT скасується лише він; транзакція відкрита, і COMMIT зафіксує решту.
Чи є в SQL Server вкладені транзакції? — Ні: внутрішній COMMIT зменшує @@TRANCOUNT, ROLLBACK відкочує все; частковий відкат — через SAVE TRAN.
Чому коміт не пише сторінки даних на диск? — Достатньо журналу: за ним сторінки відновляться після збою.
Чому масова вставка в автокоміті повільна? — Кожен оператор — окремий коміт і окреме синхронне скидання журналу.
Що таке delayed durability? — Коміт без очікування запису логу: швидше, але останні транзакції можуть загубитися під час збою.

Джерела

Middle+

Які бувають рівні ізоляції і від яких аномалій вони захищають?

Відповідь

Що це

Рівень ізоляції — налаштування транзакції, яке визначає, наскільки їй заважають паралельні транзакції. Задається командою SET TRANSACTION ISOLATION LEVEL.

Аномалії

Брудне читання — видно зміни, які інша транзакція ще не зафіксувала й може відкотити.
Неповторюване читання — повторне читання рядка дає інше значення: його змінили.
Фантом — повторний запит із тією самою умовою знаходить нові рядки: їх вставили.
Втрачене оновлення — дві транзакції прочитали значення, змінили й записали; запис першої зник.

Як користуватися

У SQL Server за замовчуванням READ COMMITTED: брудного читання немає, решта аномалій можлива.

Типова помилка

Ставити WITH (NOLOCK) «для швидкості». Це READ UNCOMMITTED: запит читає дані, які потім можуть відкотити.

Як влаштовано

У SQL Server без версій рядків рівні різняться блокуваннями читання (S). Блокування запису (X) на будь-якому рівні живуть до кінця транзакції.

READ UNCOMMITTED — читання без S-блокувань.
READ COMMITTED — S-блокування знімається одразу після читання рядка.
REPEATABLE READ — S-блокування прочитаних рядків тримаються до кінця транзакції.
SERIALIZABLE — плюс діапазонні блокування ключів: у прочитаний діапазон не можна вставити рядок.

Коли що обирати

Рівень	Захищає від	Лишається
READ UNCOMMITTED	—	усе
READ COMMITTED	брудного читання	неповторюване читання, фантоми, втрачене оновлення
REPEATABLE READ	+ неповторюваного читання	фантоми; втрачене оновлення стає дедлоком
SERIALIZABLE	+ фантомів	нічого, але найбільше блокувань

Пастки

Lost update на READ COMMITTED. SELECT Qty, розрахунок у застосунку й UPDATE SET Qty = @new втрачають паралельні зміни. Атомарний UPDATE ... SET Qty = Qty - @n безпечний.
Дедлок конверсії. На REPEATABLE READ обидві транзакції тримають S на рядку й чекають X одна одної.
Версії рядків. Окрім чотирьох рівнів стандарту, у SQL Server є RCSI і SNAPSHOT — про них наступне питання.

Правило

Лишайте READ COMMITTED, а захист від конкретної аномалії додавайте точково: атомарним оператором, хінтом на одну таблицю або перевіркою версії рядка.

Під капотом

Стандарт SQL визначає рівні через заборонені аномалії, а не через механізм. SQL Server за замовчуванням реалізує їх блокуваннями, PostgreSQL — знімками даних, тому однакові назви поводяться по-різному: REPEATABLE READ у PostgreSQL не допускає й фантомів.
Діапазонне блокування ставиться на ключі індексу. Без відповідного індексу SERIALIZABLE блокує весь просканований діапазон, по суті таблицю.
Блокувальний READ COMMITTED не гарантує узгодженості навіть одного оператора: якщо під час сканування в рядка змінюється ключ індексу, його можна прочитати двічі або пропустити.

Ціна

Що довше живуть S-блокування, то більше очікувань LCK_M_* і дедлоків конверсії.
SERIALIZABLE платить діапазонними блокуваннями й відкатами жертв дедлоку.

Компроміси

Рівень підвищують для однієї таблиці хінтом: WITH (HOLDLOCK) — це SERIALIZABLE лише для неї.
SET TRANSACTION ISOLATION LEVEL усередині процедури діє до її виходу, потім повертається рівень коду, що її викликав.
Рівень сесії задає і клієнт: TransactionScope у .NET за замовчуванням відкриває SERIALIZABLE.

Як перевірити в продакшені

sys.dm_exec_sessions.transaction_isolation_level показує рівень кожної сесії: 2 — READ COMMITTED, 4 — SERIALIZABLE.
У графі дедлоку процес має атрибут isolationlevel. Несподіваний serializable (4) зазвичай веде до TransactionScope з налаштуваннями за замовчуванням.

Підсумок: назва рівня — контракт про аномалії, а його ціна залежить від реалізації; у SQL Server це час життя блокувань читання.

T-SQL

-- База без RCSI. Сесія 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN;
SELECT Qty FROM dbo.Stock WHERE ProductId = 1; -- 10

-- Сесія 2 (автокоміт)
UPDATE dbo.Stock SET Qty = 5 WHERE ProductId = 1; -- не чекає: S-блокування вже зняли

-- Сесія 1
SELECT Qty FROM dbo.Stock WHERE ProductId = 1; -- 5: неповторюване читання
COMMIT;

-- З REPEATABLE READ сесія 2 чекала б COMMIT сесії 1,
-- і обидва SELECT повернули б 10

Що спитають далі

Який рівень за замовчуванням у SQL Server і в Azure SQL Database? — READ COMMITTED; в Azure SQL Database він за замовчуванням працює на версіях рядків (RCSI).
Чи захищає REPEATABLE READ від фантомів? — У SQL Server ні, потрібен SERIALIZABLE; у PostgreSQL — так.
Як уникнути lost update, не підвищуючи рівень? — Атомарний UPDATE ... SET x = x - @n, UPDLOCK під час читання або перевірка rowversion.
Чи пришвидшує NOLOCK запис? — Ні: блокування запису тримаються до кінця транзакції на будь-якому рівні.
Чи можна підвищити рівень для однієї таблиці? — Так, хінтами REPEATABLEREAD або SERIALIZABLE (HOLDLOCK).

Джерела

Middle+

Що таке RCSI і SNAPSHOT у SQL Server і як вони співвідносяться з MVCC у PostgreSQL?

Відповідь

Що це

Версіонування рядків: під час зміни рядка SQL Server зберігає його попередню зафіксовану версію. Читач бере цю версію, а не чекає зняття блокування.

Режим	Що бачить читання
RCSI, опція `READ_COMMITTED_SNAPSHOT`	дані, зафіксовані до початку оператора
SNAPSHOT, опція `ALLOW_SNAPSHOT_ISOLATION`	дані, зафіксовані до початку транзакції

Навіщо

Без версій SELECT на READ COMMITTED чекає, доки транзакція, що пише, завершиться. Звіт про продажі не має стояти в черзі за оформленням замовлень.

Як користуватися

RCSI вмикають для всієї бази, код змінювати не треба.
SNAPSHOT дозволяють для бази, а транзакція запитує його явно: SET TRANSACTION ISOLATION LEVEL SNAPSHOT.

Типова помилка

Думати, що версії прибирають усі блокування. Два UPDATE одного рядка й далі чекають одне на одного.

Як влаштовано

За ввімкненої опції кожна зміна копіює попередню версію рядка в сховище версій і позначає її номером транзакції.
RCSI бере знімок на початок оператора. SNAPSHOT — на перше звернення до даних після BEGIN TRAN, а не на сам BEGIN TRAN.
Читачі ставлять лише Sch-S на таблицю, без блокувань рядків і сторінок.
UPDATE на RCSI шукає рядки з U-блокуваннями, як звичайний READ COMMITTED, і конфліктів не дає. На SNAPSHOT він перевіряє, чи не змінив рядок хтось після початку знімка, і в разі конфлікту падає з помилкою 3960.

Коли що обирати

Ознака	RCSI	SNAPSHOT
Знімок	на оператор	на транзакцію
Конфлікт запису	немає, записувач чекає	помилка 3960, потрібен повтор
Зміна коду	не потрібна	`SET TRANSACTION ISOLATION LEVEL SNAPSHOT`

Пастки

Код покладався на блокувальне читання. «Прочитати залишок і вирішити» раніше чекало на незавершений запис, а тепер бачить стару версію. Такі місця захищають хінтом READCOMMITTEDLOCK або UPDLOCK.
Увімкнення RCSI вимагає, щоб у базі не було інших з’єднань: зазвичай WITH ROLLBACK IMMEDIATE у вікно обслуговування.
DDL під SNAPSHOT. Метадані не версіонуються: зміна схеми іншою сесією дає помилку 3961.

Правило

RCSI — розумний вибір за замовчуванням для OLTP. SNAPSHOT — для довгих узгоджених читань на кшталт звітів або там, де готові повторювати транзакцію при 3960.

Під капотом

Версіонування додає до 14 байт до рядка під час зміни, звідси розщеплення сторінок і ріст таблиці.
Без ADR версії лежать у tempdb, з ADR (SQL Server 2019+) — у persistent version store (PVS) самої бази.
Версії живуть, доки потрібні найстарішій активній транзакції. Одна забута відкрита транзакція роздуває tempdb або PVS.

SQL Server і PostgreSQL

Задача	SQL Server	PostgreSQL
Знімок на оператор	RCSI, опція бази	READ COMMITTED, за замовчуванням
Знімок на транзакцію	SNAPSHOT, конфлікт — 3960	REPEATABLE READ, конфлікт — 40001
Повна серіалізовність	SERIALIZABLE на блокуваннях	SERIALIZABLE (SSI), помилка 40001
Старі версії	tempdb або PVS	у самій таблиці, чистить VACUUM

Компроміси

Write skew. Знімок не ловить конфлікт, коли транзакції за однією умовою змінюють різні рядки. Двоє чергових лікарів перевіряють «на зміні щонайменше двоє» й обидва йдуть — на зміні нікого. У SQL Server потрібен SERIALIZABLE або UPDLOCK на прочитані рядки, у PostgreSQL — SERIALIZABLE.
Читання довгим ланцюжком версій повільніше за читання поточного рядка.

Як перевірити в продакшені

sys.dm_tran_version_store_space_usage — обсяг версій у tempdb за базами, sys.dm_tran_persistent_version_store_stats — PVS з ADR.
sys.dm_tran_active_snapshot_database_transactions — найстаріші транзакції, які утримують версії.

Підсумок: версії міняють блокування читання на місце й виявлення конфліктів, а головний ворог обох схем — довгі транзакції, які не дають чистити старі версії.

T-SQL

ALTER DATABASE Shop SET ALLOW_SNAPSHOT_ISOLATION ON;
-- RCSI: ALTER DATABASE Shop SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

-- Сесія 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
SELECT Qty FROM dbo.Stock WHERE ProductId = 1; -- 10: знімок узято тут

-- Сесія 2 (автокоміт)
UPDATE dbo.Stock SET Qty = 7 WHERE ProductId = 1; -- не чекає на сесію 1

-- Сесія 1
SELECT Qty FROM dbo.Stock WHERE ProductId = 1; -- 10: версія зі знімка
UPDATE dbo.Stock SET Qty = Qty - 1 WHERE ProductId = 1;
-- Msg 3960: Snapshot isolation transaction aborted due to update conflict...
-- Транзакцію відкочено, її треба повторити повністю

Що спитають далі

Чи блокують одне одного записувачі за RCSI? — Так, UPDATE бере U- і X-блокування як зазвичай; версії прибирають лише конфлікт читання й запису.
Коли починається знімок SNAPSHOT-транзакції? — Під час першого звернення до даних після BEGIN TRAN, а не в момент BEGIN TRAN.
Що таке write skew і чи ловить його SNAPSHOT? — Дві транзакції за однією прочитаною умовою змінюють різні рядки; SNAPSHOT цього не ловить.
Де зберігаються версії з увімкненим ADR? — У persistent version store усередині самої бази, а не в tempdb.
Чим REPEATABLE READ у PostgreSQL схожий на SNAPSHOT? — Знімок на транзакцію й помилка серіалізації 40001 під час конфлікту запису.

Джерела

Middle+

Як влаштовані блокування й дедлоки в SQL Server і як їх уникати?

Відповідь

Що це

Блокування — позначка, що транзакція працює з ресурсом. Транзакція з несумісним блокуванням чекає.

Блокування	Хто ставить	З чим сумісне
S, розділюване	читання	з іншими S
X, ексклюзивне	`INSERT`, `UPDATE`, `DELETE`	ні з чим

Як виникає дедлок

Транзакція A змінила замовлення й тримає X на його рядку.
Транзакція B змінила залишок товару й тримає X на ньому.
A хоче залишок, B — замовлення: обидві чекають вічно.

SQL Server знаходить цикл, відкочує транзакцію-жертву й повертає їй помилку 1205.

Як уникати

Звертайтеся до таблиць в одному порядку.
Тримайте транзакції короткими, без очікування користувача.

Типова помилка

Не обробляти 1205. Транзакцію-жертву вже відкочено, і без повтору операція губиться.

Як влаштовано

Гранулярність. Блокуються ключ індексу або рядок, сторінка, таблиця. Перед X на рядку сервер ставить IX на сторінку й таблицю, тому запиту на блокування таблиці не треба перевіряти кожен рядок.
Режими. S — читання, U — читання з наміром змінити, X — зміна, IS і IX — намірні, Sch-S і Sch-M — стабільність і зміна схеми.
Сумісність. S сумісне з S і U, U — з S, але не з U, X — ні з чим.
Дедлок. Монітор блокувань знаходить цикл очікувань за секунди й відкочує жертву з помилкою 1205.

Пастки

Дедлок конверсії. Дві транзакції на REPEATABLE READ прочитали рядок (S) і обидві хочуть X. UPDLOCK під час читання змушує другу чекати одразу.
Дедлок за двома індексами. SELECT тримає S на ключі некластерного індексу й іде в кластерний, а UPDATE тримає X на кластерному й змінює некластерний. Допомагає покривальний індекс або RCSI.
NOLOCK. Це READ UNCOMMITTED: незафіксовані дані, пропущені або задвоєні рядки під час сканування, помилка 601 під час переміщення даних. Дедлоки між записувачами він не прибирає.

Правило

Один порядок доступу до таблиць, короткі транзакції без зовнішніх викликів, індекси під умови UPDATE і DELETE, повтор транзакції при 1205.

Під капотом

Ескалація спрацьовує, коли оператор набирає щонайменше 5000 блокувань на одному посиланні на таблицю або блокування займають надто багато пам’яті. Рядки й сторінки замінюються блокуванням таблиці, а з LOCK_ESCALATION = AUTO — партиції.
Монітор шукає цикли раз на 5 секунд, а після знайденого дедлоку — частіше, аж до 100 мс. Жертва — сесія з меншим DEADLOCK_PRIORITY, за рівності — транзакція, яку дешевше відкотити.
Сесія system_health увімкнена за замовчуванням і пише xml_deadlock_report: процеси, ресурси, режими, тексти запитів, рівні ізоляції.

Ціна

Ескалація до таблиці зупиняє решту записувачів. Масові зміни ріжуть на пачки по кілька тисяч рядків.
NOLOCK не знімає конфліктів записувачів і дає хибні звіти, а RCSI прибирає конфлікт читачів і записувачів без брудних даних.

Компроміси

Optimized locking (SQL Server 2025, в Azure SQL Database завжди ввімкнена) знімає блокування рядка одразу після зміни й тримає до кінця транзакції одне блокування її ідентифікатора. Потрібен ADR, повну користь дає з RCSI, ескалація майже зникає.
LOCK_ESCALATION = DISABLE і прапорці трасування 1211 та 1224 прибирають ескалацію ціною пам’яті, аж до помилки 1204.

Як перевірити в продакшені

Хто кого блокує — blocking_session_id і wait_type виду LCK_M_X у sys.dm_exec_requests, деталі — у sys.dm_tran_locks.
Довгі очікування — blocked process report (blocked process threshold) в Extended Events.
Дедлоки — граф із system_health, ескалації — подія lock_escalation.

Підсумок: блокування — скінченний спільний ресурс; дедлоки діагностують за графом і усувають у коді та індексах, а повтор лише страхує.

T-SQL

-- Сесія 1
BEGIN TRAN;
UPDATE dbo.Orders SET Status = 2 WHERE OrderId = 1;     -- X на рядок замовлення

-- Сесія 2
BEGIN TRAN;
UPDATE dbo.Stock SET Qty = Qty - 1 WHERE ProductId = 1; -- X на рядок залишку
UPDATE dbo.Orders SET Status = 3 WHERE OrderId = 1;     -- чекає на сесію 1

-- Сесія 1
UPDATE dbo.Stock SET Qty = Qty - 1 WHERE ProductId = 1; -- чекає на сесію 2: цикл
-- За кілька секунд одна із сесій отримує Msg 1205:
-- Transaction (Process ID 53) was deadlocked on lock resources with another process
-- and has been chosen as the deadlock victim. Rerun the transaction.
-- Виправлення: в обох транзакціях спершу Stock, потім Orders

T-SQL: діагностика · Senior

-- Хто кого блокує просто зараз
SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time, t.text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0;

-- Останні дедлоки з кільцевого буфера system_health
SELECT x.ev.value('@timestamp', 'datetime2') AS deadlock_time,
       x.ev.query('.') AS deadlock_report
FROM (SELECT CAST(t.target_data AS xml) AS target_data
      FROM sys.dm_xe_session_targets AS t
      JOIN sys.dm_xe_sessions AS s ON s.address = t.event_session_address
      WHERE s.name = N'system_health' AND t.target_name = N'ring_buffer') AS d
CROSS APPLY d.target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(ev)
ORDER BY deadlock_time DESC;

Що спитають далі

Що таке U-блокування і навіщо воно? — Читання з наміром змінити: сумісне з S, але не з U, тому два «читачі-записувачі» не потрапляють у дедлок конверсії.
Як SQL Server обирає жертву дедлоку? — За DEADLOCK_PRIORITY, за рівного пріоритету — транзакцію, яку дешевше відкотити.
Коли трапляється ескалація блокувань? — Від 5000 блокувань одного оператора на одному посиланні на таблицю або за нестачі пам’яті під блокування.
Де взяти граф дедлоку, якщо нічого не налаштовували? — У сесії system_health: подія xml_deadlock_report пишеться за замовчуванням.
Чому NOLOCK — не рішення? — Він міняє очікування на хибні дані й не допомагає під час конфліктів записувачів; для читачів є RCSI.

Джерела

Senior+

Оптимістична чи песимістична конкуренція: як захистити дані й повторювати транзакції з .NET?

Відповідь

Що це

Песимістична — «конфлікт імовірний»: заблокувати рядок, змінити, зафіксувати. Решта чекає.
Оптимістична — «конфлікт рідкісний»: прочитати без блокувань, а під час запису перевірити, що рядок не змінився.

Як користуватися

Додайте в таблицю стовпець RowVer rowversion — SQL Server змінює його під час кожної зміни рядка.
Прочитайте рядок разом із RowVer.
Оновлюйте з умовою WHERE Id = @id AND RowVer = @rowVer.
Змінилося 0 рядків — хтось устиг раніше: перечитайте дані й повідомте користувача.

Типова помилка

Тримати транзакцію відкритою, поки користувач редагує форму. Блокування висять хвилинами, хоча тут потрібен оптимістичний підхід.

Як влаштовано

rowversion — 8 байт із лічильника бази, зростає під час кожного INSERT і UPDATE рядка. Синонім timestamp застарів. В EF Core такий стовпець — concurrency token, конфлікт приходить як DbUpdateConcurrencyException; подробиці — у темі EF Core.
UPDLOCK — U-блокування під час читання до кінця транзакції: другий такий самий читач чекає, звичайний — ні.
UPDLOCK, HOLDLOCK — плюс діапазонне блокування: у перевірений діапазон ніхто не вставить рядок. Так роблять «якщо немає — вставити» без дублів.
Атомарний оператор часто кращий за обидва: UPDATE ... SET Qty = Qty - @n WHERE Id = @id AND Qty >= @n і перевірка @@ROWCOUNT.

Коли що обирати

Ситуація	Підхід
Користувач редагує хвилинами	оптимістичний, `rowversion`
Гарячий рядок: залишок, лічильник	атомарний `UPDATE` або `UPDLOCK`
Черга задач у таблиці	`UPDLOCK, READPAST`; у PostgreSQL `FOR UPDATE SKIP LOCKED`

Пастки

Помилки 1205 і 3960 уже відкотили транзакцію — повторюють її повністю.
Таймаут блокування 1222 скасовує лише оператор, а транзакція лишається відкритою: її відкочують явно.

Під капотом

Ознака	`DbTransaction`	`TransactionScope`
Підключення	передається в кожну команду	з’єднання входить саме під час `Open`
Рівень за замовчуванням	рівень сесії, зазвичай READ COMMITTED	SERIALIZABLE, таймаут 1 хвилина
`await` усередині	працює	потрібен `TransactionScopeAsyncFlowOption.Enabled`
Два з’єднання	не можна	ескалація в розподілену транзакцію

Розподілена транзакція йде через MSDTC. У .NET 7+ вона працює лише на Windows і після TransactionManager.ImplicitDistributedTransactions = true, інакше — виняток.

Компроміси

Повтор. Повторюють усю одиницю роботи: нове з’єднання, нову транзакцію, заново прочитані дані. Кількість спроб обмежують, паузу дають із джитером, інакше жертви зіткнуться знову. EnableRetryOnFailure в EF Core вважає 1205, 3960 і 1222 тимчасовими помилками.
Побічні ефекти. HTTP-виклик або повідомлення всередині блоку, що повторюється, підуть двічі. Транзакція БД не охоплює зовнішні системи, для них є outbox і саги — тема інтеграцій.
Невідомий результат коміту. Якщо зв’язок обірвався під час COMMIT, клієнт не знає, чи зафіксовано транзакцію. Повтор безпечний для ідемпотентної операції, наприклад з унікальним ключем RequestId у таблиці операцій.

Як перевірити в продакшені

Метрики повторів і помилок 1205 та 3960: їхнє зростання — привід шукати причину в графі дедлоку, а не додавати спроби.
RangeS-S у sys.dm_tran_locks і serializable у графах дедлоків — слід TransactionScope з налаштуваннями за замовчуванням.

Підсумок: обирають між очікуванням і повтором, а повтор надійний, лише коли повторюється вся транзакція й операція ідемпотентна.

T-SQL

-- @id, @price, @rowVer, @n — параметри процедури
-- Оптимістично: оновити, лише якщо рядок ніхто не змінював
UPDATE dbo.Products SET Price = @price
WHERE ProductId = @id AND RowVer = @rowVer;
IF @@ROWCOUNT = 0
    THROW 50001, 'Concurrency conflict: reload and retry.', 1;

-- Песимістично: «якщо немає — вставити» без дублів
SET XACT_ABORT ON;
BEGIN TRAN;
IF EXISTS (SELECT 1 FROM dbo.Stock WITH (UPDLOCK, HOLDLOCK) WHERE ProductId = @id)
    UPDATE dbo.Stock SET Qty = Qty + @n WHERE ProductId = @id;
ELSE
    INSERT dbo.Stock (ProductId, Qty) VALUES (@id, @n); -- діапазон заблоковано: дубль не вставить ніхто
COMMIT;

C#: повтор при дедлоку

using System.Data;
using Microsoft.Data.SqlClient;

var cs = "Server=.;Database=Shop;Integrated Security=true;TrustServerCertificate=true";

// Повторюється вся транзакція: нове з'єднання, нова транзакція
await WithRetryAsync(async () =>
{
    await using var conn = new SqlConnection(cs);
    await conn.OpenAsync();
    await using var tx = (SqlTransaction)await conn.BeginTransactionAsync(IsolationLevel.ReadCommitted);
    await using var cmd = new SqlCommand(
        "UPDATE dbo.Stock SET Qty = Qty - 1 WHERE ProductId = @id AND Qty > 0", conn, tx);
    cmd.Parameters.AddWithValue("@id", 1);
    await cmd.ExecuteNonQueryAsync();
    await tx.CommitAsync();
});

static async Task WithRetryAsync(Func<Task> action, int attempts = 3)
{
    for (var i = 1; ; i++)
    {
        try { await action(); return; }
        // 1205 — жертва дедлоку, 3960 — конфлікт SNAPSHOT: сервер уже відкотив транзакцію
        catch (SqlException ex) when (ex.Number is 1205 or 3960 && i < attempts)
        {
            await Task.Delay(Random.Shared.Next(50, 200) * i); // пауза з джитером
        }
    }
}

C#: TransactionScope

using System.Transactions;
using Microsoft.Data.SqlClient;

var cs = "Server=.;Database=Shop;Integrated Security=true;TrustServerCertificate=true";

// Без опцій: SERIALIZABLE і таймаут 1 хвилина; без AsyncFlow транзакція губиться після await
using (var scope = new TransactionScope(
    TransactionScopeOption.Required,
    new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted, Timeout = TimeSpan.FromSeconds(30) },
    TransactionScopeAsyncFlowOption.Enabled))
{
    await using var conn = new SqlConnection(cs);
    await conn.OpenAsync(); // з'єднання саме входить в ambient-транзакцію
    await using var cmd = new SqlCommand("UPDATE dbo.Stock SET Qty = Qty - 1 WHERE ProductId = 1", conn);
    await cmd.ExecuteNonQueryAsync();

    // Друге одночасно відкрите з'єднання тут спричинило б ескалацію в розподілену транзакцію
    scope.Complete(); // без Complete — відкат під час Dispose
}

Що спитають далі

Чим UPDLOCK відрізняється від XLOCK? — U сумісне з S, тому звичайні читачі не чекають; X блокує і їх.
Чому IF NOT EXISTS ... INSERT без хінтів дає дублі? — Між перевіркою та вставкою інша сесія встигає вставити той самий рядок; UPDLOCK, HOLDLOCK закривають діапазон.
Який рівень ізоляції в new TransactionScope()? — SERIALIZABLE і таймаут 1 хвилина; задавайте TransactionOptions явно.
Що повторювати при 1205: оператор чи транзакцію? — Усю транзакцію: сервер уже відкотив її повністю.
Чому 1222 підступніша за 1205? — Транзакція після неї лишається відкритою, і без явного відкату наступні оператори виконаються в ній.

Джерела