Уровни изоляции транзакций в SQL. Шпаргалка
Материал этой статьи послужил основой для одного из параграфов главы "Транзакции, изоляция и блокировки", входящей в книгу "СУБД для программиста. Базы данных изнутри". Для более глубокого понимания механизмов см. параграфы "Уровни SQL-92", "Блокировки", "Взаимные блокировки процессов (deadlock)", "Версии данных", "Проявления эффектов изоляции" и "Толстые транзакции".
Статья также была опубликована в журнале «Мир ПК», № 07, 2009
Истоки проблемы
Напомним, что транзакцией называется упорядоченное множество действий, которые выполняются или все вместе или не выполняются вовсе. В применении к SQL транзакция включает в себя один и более операторов языка. Уровни изоляций транзакций с разной степенью обеспечивают целостность данных при их одновременной обработке множеством процессов (пользователей). Наиболее простой вариант: один пользователь записывает информацию на уровне записи таблицы, другой пытается ее прочитать. При отсутствии изоляции второй пользователь может прочитать данные незавершенной транзакции. Ситуация усложняется, если изменениям (модификации, вставке, удалению) подвержена не одна запись, а несколько, возможно даже в разных таблицах.
Прежде чем приступить к экспериментам на практике давайте кратко перечислим особенности уровней изоляции согласно стандарту ANSI SQL-92.
Незавершенное (черновое) чтение (read uncommitted)
Минимальный уровень изоляции, гарантирует только физическую целостность при записи данных. Процессы-читатели могут считывать данные незавершенной транзакции процесса-писателя.
Подтвержденное чтение (read committed)
Процессы-читатели не могут считывать данные незавершенной транзакции, но процессы-писатели
могут изменять уже прочитанные читателем данные.
Повторяемое чтение (repeatable read)
Повторное чтение данных вернет те же значения, что и в начале транзакции. При этом процессы-писатели могут вставлять новые записи, имеющие статус "фантома" при незавершенной транзакции.
Версионный срез (snapshot)
Процессы-читатели не ждут завершения транзакций писателей, а считывают данные, точнее их версию, по состоянию на момент начала своей транзакции.
Сериализуемость (serializable)
Максимальный уровень изоляции, гарантирует неизменяемость данных другими процессами до завершения транзакции.
Испытания
Рассмотрим поведение системы в типовых случаях на простом примере. Для проведения эксперимента воспользуемся СУБД MS SQL Server 2005 или 2008.
Подготовка
Создадим на сервере базу данных с названием Test и выполним на ней несколько SQL-скриптов. В версии 2005 появился механизм версионности, нам он также понадобится,
но вначале нужно включить его на нашей базе данных.
USE master
ALTER DATABASE Test SET ALLOW_SNAPSHOT_ISOLATION ON
GO
USE test
Создаем "подопытные" таблицы для тестов и заполним их данными. Предположим, что мы собираем поступающую с датчиков информацию в таблицу DevicesData. Поле DeviceId содержит идентификатор устройства, а поле Value - последнее полученное значение.
CREATE TABLE DevicesData (
DeviceId int not null,
Value int not null,
CONSTRAINT PK_DevicesData PRIMARY KEY (DeviceId)
)
GO
Для наглядности опытов таблица должна быть достаточно велика. Пусть общее количество датчиков будет равно миллиону, заполним их текущие значения нулями.
TRUNCATE TABLE DevicesData
DECLARE @n int
SET @n = 999
DECLARE @List TABLE (n int)
WHILE @n >= 0 BEGIN
INSERT INTO @List (n)
SELECT @n
SET @n = @n - 1
END
INSERT INTO DevicesData (DeviceId, Value)
SELECT A.n * 1000 + B.n, 0
FROM @List A CROSS JOIN @List B
GO
Проверки
В SQL Server Management Studio откроем два окна для запросов к нашей базе данных Test.
Завершенное чтение (read committed)
Установим для каждого процесса уровень изоляции READ COMMITTED. В первом окне запустим процесс-писатель, который меняет значение поля Value у двух случайным образом выбранных записей в таблице. Первое значение увеличивается на 1, второе уменьшается на 1. Изначально все значения поля Value в таблице равны нулю, значит и их сумма также будет равно нулю. Таким образом, после завершения каждой транзакции сумма значений поля Value в таблице будет оставаться равной нулю. Во втором окне запустим процесс-читатель, подсчитывающий эту самую сумму значений поля Value.
Процесс 1 (писатель)
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @Id int
WHILE 1 = 1 BEGIN
SET @Id = 500000 * rand()
BEGIN TRANSACTION
UPDATE DevicesData SET Value = Value + 1 WHERE DeviceId = @Id
UPDATE DevicesData SET Value = Value - 1 WHERE DeviceId = 500000 + @Id
COMMIT
WAITFOR DELAY '00:00:00.100'
END
Процесс 2 (читатель)
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT SUM(Value) FROM DevicesData
-----------
-1
Нетрудно убедиться, что выбранный уровень не обеспечивает логической целостности. Если при запущенном "Процесс 1" в другом окне вручную запустить несколько раз "Процесс 2", то возвращаемые выборкой значения будут отличаться от нуля. Если же "Процесс 1" прервать, то "Процесс 2" снова покажет нулевую сумму.
Повторяемое чтение (repeatable read)
Чтобы избежать подобной проблемы, повысим уровень до повторяемого чтения, установив REPEATABLE READ. Повторив наш предыдущий эксперимент, можно убедиться в этом: процесс-читатель всегда возвращает нулевую сумму. Если же посмотреть накладываемые сервером блокировки, то можно увидеть многочисленные разделяемые блокировки уровня страниц (page shared lock), по сути на запись блокируется вся таблица.
Процесс 1 (писатель)
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
DECLARE @Id int
WHILE 1 = 1 BEGIN
SET @Id = 500000 * rand()
BEGIN TRANSACTION
UPDATE DevicesData SET Value = Value + 1 WHERE DeviceId = @Id
UPDATE DevicesData SET Value = Value - 1 WHERE DeviceId = 500000 + @Id
COMMIT
WAITFOR DELAY '00:00:00.100'
END
Процесс 2 (читатель)
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT SUM(Value) FROM DevicesData
EXEC sp_lock
COMMIT
-----------
0
Версионный срез (snapshot)
Версионный срез позволяет нам решить проблему блокировок за счет разрешения другим транзакциям изменять данные. Если мы установим уровень SNAPSHOT и повторим эксперимент, то многочисленные разделяемые блокировки уровня страниц, препятствующих записи данных, исчезнут. Таким образом, версионный срез представляет собой очень хорошее средство для генерации отчетов или экспорта данных, так как гарантирует для читателя непротиворечивость и целостность данных в каждый момент времени, но при этом не препятствует работе других пишущих транзакций.
Снова повторяемое чтение и фантомы
Повторяемое чтение гарантирует, что при повторной выборке одних и тех же данных внутри транзакции мы получим одни и те же их значения. Уровень препятствует другим транзакциям изменять уже прочитанные значения, но при этом допускается чтение новых записей, созданных другой транзакцией, так называемых "фантомов".
Процесс 1 (читатель)
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT SUM(Value) FROM DevicesData WHERE DeviceId > 999000
WAITFOR DELAY '00:00:03'
SELECT SUM(Value) FROM DevicesData WHERE DeviceId > 999000
COMMIT
Процесс 2 (писатель)
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
INSERT INTO DevicesData (DeviceId, Value) VALUES (1000000, 111)
WAITFOR DELAY '00:00:00.100'
COMMIT
-----------
0
-----------
111
Чтобы не блокировать таблицу, запустим читателя (Процесс 1) на небольшом диапазоне записей, а в паузе между двумя чтениями этого диапазона запустим Процесс 2, добавляющий новую запись. В результате первая выборка вернет 0, а вторая - 111.
Изменив уровень до SNAPSHOT, можно увидеть, что чтение в обоих выборках возвращает ноль, однако запись все равно будет добавлена конкурирующей транзакцией. Если наша цель - не получение среза данных для отчета, а обеспечение логической целостности при работе нескольких процессов-писателей, то уровень версионного среза нам помочь не может. Например, не обеспечивается добавление записи по ранее вычисленному условию.
Процесс 1 (писатель)
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
DECLARE @SumVal int
BEGIN TRANSACTION
SELECT @SumVal = SUM(Value) FROM DevicesData WHERE DeviceId > 999000
WAITFOR DELAY '00:00:03'
IF (@SumVal) = 0
INSERT INTO DevicesData (DeviceId, Value) VALUES (1000000, 111)
COMMIT
Процесс 2 (писатель)
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
INSERT INTO DevicesData (DeviceId, Value) VALUES (1000000, 111)
WAITFOR DELAY '00:00:00.100'
COMMIT
Msg 2627, Level 14, State 1, Line 18
Violation of PRIMARY KEY constraint 'PK__DevicesData__51BA1E3A'. Cannot insert duplicate key in object 'dbo.DevicesData'.
The statement has been terminated.
Сериализуемость (serializable) или полная изоляция
Уровень сериализуемости SERIALIZABLE гарантирует, что все затронутые в транзакции данные не будут изменены другими транзакциями. На этом уровне появление "фантомов" исключается, поэтому становятся возможными такие сложные конкурентные операции, как, например, добавление записей в диапазон значений ключа с предварительной проверкой целостности. На практике такая обработка требуется в учетных системах, когда условием добавления операции, например, списания товара со склада, является предварительная проверка неотрицательности его остатка по сумме всех предыдущих операций.
Если повторить наш предыдущий пример с уровнем SERIALIZABLE, то ошибка добавления записи возникнет уже в Процессе 2.
Краткий итог
Тема уровней изоляции транзакций является важнейшей при обработке данных в любой многопользовательской среде, одним из примеров которой является СУБД. Реляционные СУБД на уровне стандарта SQL 92 предоставляют разработчику несколько уровней, обладающих четко определенными характеристиками и поведением. Более высокие уровни изоляции уменьшают возможности параллельной обработки данных и повышают риск взаимной блокировки процессов. Поэтому корректное использование уровней в зависимости от задач приложений всегда является выбором разработчика в зависимости от требований к обеспечению логической целостности данных, к скорости и к возможности параллельной многопользовательской обработки.