Блокировки и нюансы MSSQL

Блокировки

В MSSQL есть следующие уровни изоляции данных:

read uncommitted (неподтвержденное чтение - грязное чтение) - транзакции нет
read committed (подтвержденное чтение) - транзакция видит только закомиченные данные
repeatable read (повторное чтение) - транзакция при повтором чтении может получить другие данные (влияние сторонней транзакции)
serializable (упорядоченный или сериализуемый) - транзакция при повтором чтении получает одинаковые данные
read committed snapshot (подтвержденное чтение с включенным параметром READ_COMMITTED_SNAPSHOT)
snapshot (изоляция моментального снимка)

Различия

Если с 1 и 2 уровнем все понятно, то с остальными не так все просто.

Рассмотрим уровни изоляции на примере, для этого предположим, у нас есть таблица t в которой есть одна строка:

INSERT INTO t (v) VALUES (1)

Repeatable read (повторяемость чтения)

хорошо: UPDATE транзакции 2 не влияет на транзакцию 1
плохо: INSERT, DELETE транзакции 2 влияют на транзакцию 1 - в результате чего SELECT в транзакции 1 возвратит данные основанные на измененной информации:

Транзакция 1	Транзакция 2
SET TRANSACTION ISOLATION LEVEL ... BEGIN TRAN; SELECT v FROM t ... в этот момент выполняется транзакция 2 SELECT v FROM t COMMIT;	BEGIN TRAN; UPDATE t SET v = 2 WHERE v = 1 COMMIT TRAN;
Результат при COMMITTED: первый SELECT: 1 второй SELECT: 2	Транзакция 2 не ждет завершения транзакции 1
Результат при REPEATABLE READ: первый SELECT: 1 второй SELECT: 1	Транзакция 2 ждет завершения транзакции 1

Транзакция 1

Транзакция 2

SET TRANSACTION ISOLATION LEVEL ...
BEGIN TRAN;
SELECT v FROM t
... в этот момент выполняется транзакция 2
SELECT v FROM t
COMMIT;

BEGIN TRAN;
UPDATE t SET v = 2 WHERE v = 1
COMMIT TRAN;

Результат при COMMITTED:

первый SELECT: 1
второй SELECT: 2

Транзакция 2 не ждет завершения транзакции 1

Результат при REPEATABLE READ:

первый SELECT: 1
второй SELECT: 1

Транзакция 2 ждет завершения транзакции 1

Serializable (упорядочиваемость) - транзакции полностью изолируются друг от друга, каждая выполняется так, как будто параллельных транзакций не существует

Транзакция 1	Транзакция 2
SET TRANSACTION ISOLATION LEVEL ... BEGIN TRAN; SELECT COUNT() FROM t ... в этот момент выполняется транзакция 2 SELECT COUNT() FROM t COMMIT;	BEGIN TRAN; INSERT INTO t (v) VALUES(1) COMMIT TRAN;
Результат при REPEATABLE READ: первый SELECT: 1 второй SELECT: 2	Транзакция 2 не ждет завершения транзакции 1
Результат при SERIALIZABLE: первый SELECT: 1 второй SELECT: 1	Транзакция 2 ждет завершения транзакции 1

Транзакция 1

Транзакция 2

SET TRANSACTION ISOLATION LEVEL ...
BEGIN TRAN;
SELECT COUNT(*) FROM t
... в этот момент выполняется транзакция 2
SELECT COUNT(*) FROM t
COMMIT;

BEGIN TRAN;
INSERT INTO t (v) VALUES(1)
COMMIT TRAN;

Результат при REPEATABLE READ:

первый SELECT: 1
второй SELECT: 2

Транзакция 2 не ждет завершения транзакции 1

Результат при SERIALIZABLE:

первый SELECT: 1
второй SELECT: 1

Транзакция 2 ждет завершения транзакции 1

Подведем итог

Уровень изоляции:	COMMITTED	REPEATABLE_READ	SERIALIZABLE
Изменения строки таблицы видны в других транзакциях	да	нет	нет
Добавление/удаление строк производимых в транзакции, видны в других транзакциях	да	да	нет

Нюансы

По-умолчанию включен режим read committed (подтвержденное чтение), при котором операции insert, update, delete блокируют затрагиваемые строки в таблице, поэтому операция select встает в очередь ожидания разблокировки (пока ресурс не освободится), но, у операции select есть 2 хинта (указателя), благодаря которым можно из заблокированной таблицы вычитывать строки:

WITH(READPAST) - читать зафиксированные строки (будут игнорироваться строки подвергающиеся добавлению/изменению/удалению в транзакциях сторонних сессий) - блокирует вычитываемые строки (накладывая S блокировку)

WITH(NOLOCK) - читать незафиксированные строки (грязные данные) - не блокирует вычитываемые строки (блокировки не накладываются)

Если нужен WITH(NOLOCK) для всех запросов транзакции, тогда нужно перед началом транзакции указать уровень блокировки READ UNCOMMITTED, например так:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN;

Как блокировать

Существуют следующие виды блокировок:

Вид	Какие операции блокирует	Что именно блокирует
Exclusive lock (X)	Insert, Update, Delete	блокировка страницы или строки
Shared lock (S)	Insert, Update, Delete, Select	блокировка страницы или строки
Update lock (U)	Insert, Update, Delete	блокировка страницы или строки, которая заблокирована с помощью Shared lock (S)
Intent locks (I):		намерение о блокировки таблицы целиком, может быть следующим:
– Intent exclusive (IX)	Insert, Update, Delete	см. выше
– Intent shared (IS)	Insert, Update, Delete, Select	см. выше
– Intent update (IU)	Insert, Update, Delete	см. выше

К примеру, чтобы исключить простейший случай дедлока, перед UPDATE нужных ID-строк, хорошей практикой считается - блокировка этих ID-строк с помощью SELECT-a:

SELECT id FROM my_table WITH(UPDLOCK,READPAST) WHERE id IN (1,2,...)

и затем сверить кол-во возвращенных ID-строк (если кол-во возвращенных SELECT-ом строк равно кол-ву ID-строк в операторе IN, то все указанные строки были заблокированы в рамках текущей транзакции - BEGIN TRAN должен быть вызван до SELECT-a блокировки).

Источник: 1 - 2 - 3

03.04.2019 10:50