Прежде чем приступить к экспериментам на практике, давайте кратко перечислим особенности уровней изоляции согласно стандарту ANSI SQL-92.
Незавершенное (черновое) чтение (read uncommitted) — минимальный уровень изоляции гарантирует только физическую целостность при записи данных. Процессы-читатели могут считывать данные незавершенной транзакции процесса-писателя.
Подтвержденное чтение (read committed) — процессы-читатели не могут считывать данные незавершенной транзакции, но процессы-писатели могут изменять уже прочитанные читателем данные.
Повторяемое чтение (repeatable read) — повторное чтение данных вернет те же значения, что были и в начале транзакции. При этом процессы-писатели могут вставлять новые записи, имеющие статус фантома при незавершенной транзакции.
Сериализуемость (serializable) — максимальный уровень изоляции, гарантирует неизменяемость данных другими процессами до завершения транзакции.
Моментальный срез (snapshot) — данный вид изоляции не входит в рекомендации стандарта SQL 92, но он реализован во многих СУБД. Процессы-читатели не ждут завершения транзакций писателей, а считывают данные, точнее их версию, по состоянию на момент начала своей транзакции.
Испытания
Рассмотрим поведение системы в типовых случаях на простом примере. Для проведения эксперимента воспользуемся СУБД 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» в другом окне вручную запустить несколько раз «Процесс 2», то возвращаемые выборкой значения будут отличаться от нуля. Если же «Процесс 1» прервать, то «Процесс 2» снова покажет нулевую сумму.
Повторяемое чтение (repeatable read)
Чтобы избежать подобной проблемы, повысим уровень до повторяемого чтения, установив REPEATABLE READ. Повторив наш предыдущий эксперимент, легко убедиться в этом: процесс-читатель всегда возвращает нулевую сумму. Если же посмотреть накладываемые сервером блокировки (EXEC sp_lock), то можно увидеть многочисленные разделяемые блокировки уровня страниц (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
Моментальный срез (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
Процесс 1. Результат — 0 — 111
Процесс 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
Чтобы не блокировать таблицу, запустим читателя («Процесс 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
Процесс 1. Результат
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.
Процесс 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
Сериализуемость (serializable), или полная изоляция
Уровень сериализуемости SERIALIZABLE гарантирует, что все затронутые в транзакции данные не будут изменены другими транзакциями. На этом уровне появление фантомов исключается, поэтому становятся возможными такие сложные конкурентные операции, как, например, добавление записей в диапазон значений ключа с предварительной проверкой целостности.
На практике такая обработка требуется в учетных системах, когда условием добавления операции, в частности списания товара со склада, является предварительная проверка положительности его остатка по сумме всех предыдущих операций.
Если повторить наш предыдущий пример с уровнем SERIALIZABLE, то ошибка добавления записи возникнет уже в «Процессе 2».
* * *
Тема уровней изоляции транзакций является важнейшей при обработке данных в любой многопользовательской среде, одним из примеров которой является СУБД. Реляционные СУБД на уровне стандарта SQL 92 предоставляют разработчику несколько уровней, обладающих четко определенными характеристиками и поведением. Более высокие уровни изоляции уменьшают возможности параллельной обработки данных и повышают риск взаимной блокировки процессов. Поэтому корректное использование уровней в зависимости от задач приложений всегда является выбором разработчика в зависимости от требований к обеспечению логической целостности данных, к скорости и к возможности параллельной многопользовательской обработки.