Часто приходилось сталкиваться с тем, что люди прекрасно понимают, что такое транзакции в базе данных и для чего они нужны, но при этом не всегда умеют ими правильно пользоваться. Безусловно, для достижения 80-го уровня сакрального знания нужно иметь не один год опыта и прочесть множество толстенных книг по SQL. Поэтому в этой статье я даже не буду пытаться описать всё, что может быть связано с транзакциями в MS SQL. Я хочу затронуть один простой, но очень важный вопрос, который разработчики часто упускают из вида – уровни изоляции транзакций.
Несмотря на то, что тема очень проста, во многих источниках она освящается плохо – информации либо очень мало, либо очень много. Т.е. прочитав 5-6 кратких теоретических определений невозможно их применить на практике. Для уверенного понимания предмета статьи нужно обращаться к специализированной литературе, но там информации на столько много, что далеко не каждый может уделить необходимое время для её усваивания.
Сегодня я хочу поделиться своим простым рецептом, который помог мне раз и на всегда запомнить особенности уровней изоляции транзакций и по сей день помогает без проблем принимать взвешенные решения о выборе необходимого уровня. Далее, я постараюсь копнуть чуть глубже и рассказать, как MS SQL Server реализует уровни изоляции.
Секрет предлагаемого способа запоминания в том, что краткая теория будет сопровождаться простыми практическими примерами, которые мне были бы на много понятней, чем подробное описание.
И так, для понимания различий в уровнях изоляции необходимо разобраться с нежелательными побочными эффектами, которые могут возникать, если транзакции будут не изолированы друг от друга. Поняв специфику этих эффектов, нам останется только посмотреть, от каких эффектов защищает каждый отдельно взятый уровень. После этого я уверен, что тема изоляции транзакций вам навсегда перестанет казаться чем-то заоблачно сложным.
Побочные эффекты параллелизма.
Все операции в базе происходят не мгновенно и при одновременном изменении данных различными пользователями возможны следующие побочные эффекты:- Потерянное обновление (lost update)
- «Грязное» чтение (dirty read)
- Неповторяющееся чтение (non-repeatable read)
- Фантомное чтение (phantom reads)
Далее, эти эффекты рассматриваются подробно и приводятся SQL скрипты, показывающие проблему на практике. Я настоятельно рекомендую попробовать выполнить их и увидеть проблему «в живую», но для этого нужно сначала подготовить ваш сервер. Шаги по подготовки и особенности запуска скриптов описаны ниже.
Требования для запуска скриптов
- Первым нужно запускать скрипт для транзакции №1, а затем сразу же скрипт для транзакции №2 (не позднее чем через 10 секунд после начала выполнения первого скрипта).
- В базе должна существовать таблица с именем Table1 и колонками Id и Value. В ней ожидается наличие одной строки:
- Для создания таблицы и наполнения её данными можно запустить следующий скрипт.
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Table1')) DROP TABLE Table1 CREATE TABLE Table1 (Id INT IDENTITY, Value INT) INSERT INTO Table1 (Value) VALUES(1)
- Так же данный скрипт желательно выполнить перед рассмотрения каждого примера. Это будет гарантировать идентичность получаемых результатов с теми, что описаны ниже.
Потерянное обновление (lost update)
Эффект проявляется при одновременном изменении одного блока данных разными транзакциями. Причём одно из изменений может теряться.Данная формулировка может по-разному интерпретироваться.
Потерянное обновление – Интерпретация №1
Две транзакции выполняют одновременно UPDATE для одной и той же строки, и изменения, сделанные одной транзакцией, затираются другой.
| Транзакция 1 | Транзакция 2 | |
UPDATE Table1 SET Value = Value + 5 WHERE Id = 1; SELECT Value FROM Table1 WHERE Id = 1; | UPDATE Table1 SET Value = Value + 7 WHERE Id = 1; SELECT Value FROM Table1 WHERE Id = 1; | |
Результат: | Value = 6 | Value = 8 |
Почему так происходит?
Прежде чем выполнить обновление, обе транзакции читают значение в колонке Value – оно равно 1. Предположим, что транзакция 2 успевает записать значение первой, тогда новое значение в колонке Value будет 8 (1+7). Затем транзакция 1 так же вычисляет новое значение, но для расчёта использует ранее вычитанное значение (1). В итоге после завершения транзакции 1 в колонке Value окажется 6 (1+5), а не 13 (1+7+5).
К счастью в MS SQL данный сценарий невозможен, потому что даже самый низкий уровень изоляции предотвращает такую ситуацию и результатом всегда будет 13, а не 8. Потерянное обновление – Интерпретация №2
Сценарий аналогичен первому, но значение Value вычитывается во временную переменную
Транзакция 1 | Транзакция 2 | |
BEGIN TRAN; DECLARE @Value INT; SELECT @Value = Value FROM Table1 WHERE Id = 1; WAITFOR DELAY '00:00:10'; UPDATE Table1 SET Value = @Value + 5 WHERE Id = 1; COMMIT TRAN; SELECT Value FROM Table1 WHERE Id = 1; | BEGIN TRAN; DECLARE @Value INT; SELECT @Value = Value FROM Table1 WHERE Id = 1; UPDATE Table1 SET Value = @Value + 7 WHERE Id = 1; COMMIT TRAN; SELECT Value FROM Table1 WHERE Id = 1; | |
Результат: | Value = 6 | Value = 8 |
«Грязное» чтение (dirty read)
Это такое чтение, при котором могут быть считаны добавленные или изменённые данные из другой транзакции, которая впоследствии не подтвердится (откатится).Так как данный эффект возможен только при минимальном уровне изоляции, а по умолчанию используется более высокий уровень изоляции (READ COMMITTED), то в скрипте чтения данных уровень изоляции будет явно установлен как READ UNCOMMITTED. Если вернуть уровень изоляции по умолчанию (READ COMMITTED) для транзакции 2, то поведение поменяется.
Транзакция 1 | Транзакция 2 | |
BEGIN TRAN; UPDATE Table1 SET Value = Value * 10 WHERE Id = 1; WAITFOR DELAY '00:00:10'; ROLLBACK; SELECT Value FROM Table1 WHERE Id = 1; | SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN TRAN; SELECT Value FROM Table1 WHERE Id = 1; COMMIT TRAN; | |
Результат для READ UNCOMMITTED | Value = 1 | Value = 10 |
Результат для READ COMMITTED | Value = 1 | Value = 1 |
Мы видим, что внутри второй транзакции было вычитано значение 10, которое никогда не было успешно сохранено в базу (оно было отклонено командой ROLLBACK).
Неповторяющееся чтение (non-repeatable read)
Проявляется, когда при повторном чтении в рамках одной транзакции, ранее прочитанные данные, оказываются изменёнными. Данный эффект может наблюдаться при уровне изоляции ниже, чем REPEATABLE READ.Транзакция 1 | Транзакция 2 | |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED --SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN; SELECT Value FROM Table1 WHERE Id = 1; WAITFOR DELAY '00:00:10'; SELECT Value FROM Table1 WHERE Id = 1; COMMIT; | BEGIN TRAN; UPDATE Table1 SET Value = 42 WHERE Id = 1; COMMIT TRAN; | |
Результат для READ COMMITTED | Value = 1 Value = 42 | Мгновенное выполнение |
Результат для REPEATABLE READ | Value = 1 Value = 1 | Ожидание завершения транзакции 1 |
Фантомное чтение (phantom reads)
Можно наблюдать, когда одна транзакция в ходе своего выполнения несколько раз выбирает множество строк по одним и тем же критериям. При этом другая транзакция в интервалах между этими выборками добавляет или удаляет строки, или изменяет столбцы некоторых строк, используемых в критериях выборки первой транзакции, и успешно заканчивается. В результате получится, что одни и те же выборки в первой транзакции дают разные множества строк. Данный эффект можно наблюдать, когда уровень изоляции ниже чем SERIALIZABLE.Транзакция 1 | Транзакция 2 | |||||||||||
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN; SELECT * FROM Table1 WAITFOR DELAY '00:00:10' SELECT * FROM Table1 COMMIT; | BEGIN TRAN; INSERT INTO Table1 (Value) VALUES(100) COMMIT TRAN; | |||||||||||
Результат для REPEATABLE READ |
первый SELECT:
второй SELECT:
| Мгновенное выполнение | ||||||||||
Результат для SERIALIZABLE |
первый SELECT:
второй SELECT:
| Ожидание завершения транзакции 1 |
Уровни изоляции
Понимая смысл побочных эффектов, очень просто разобраться в назначении каждого уровня изоляции, т.к. они отличаются между собой количеством побочных эффектов.Потерянное обновление | Грязное чтение | Неповторяющееся чтение | Фантомное чтение | |
Read uncommitted | Нет /Есть (*) | Есть | Есть | Есть |
Read committed Read committed Snapshot (**) | Нет /Есть (*) | Нет | Есть | Есть |
Repeatable read | Нет | Нет | Нет | Есть |
Serializable Snapshot (**) | Нет | Нет | Нет | Нет |
(*) – эффект присутствует только в случае, если он трактуется согласно описанию в разделе «Потерянное обновление – Интерпретация №2».
(**) – для данных уровней изоляция достигается не при помощи блокировок, а при помощи создания копии изменяемых данных, которые на время транзакции помещаются в tempdb; подробней тут.
Теперь, разобравшись в назначении каждого уровня, вы уже готовы к более осмысленному использованию транзакций. Но я бы не останавливался на достигнутом. Далее, материал будет представлять чуть меньшую практическую ценность, но при этом он не будет менее полезный. Когда-то Ли Кэмпбел однажды отлично сказал: «Вы должны понимать как минимум на один уровень абстракции ниже того уровня, на котором программируете». Именно поэтому, понимание реализации позволит максимально глубоко разобраться в теме и вы сможете правильно и эффективно пользоваться предлагаемым инструментом.
Реализация уровней изоляции транзакций
Как вы могли видеть в предыдущем разделе, существует два способа поддержания изоляции:- Основанный на блокировке ресурсов
- Основанный на создании версионной копии ресурсов.
Режимы, основанные на создании копии данных, достаточно просты для понимания и думаю не требуют особого внимания. При желании углубиться в детали их реализации, я могу предложить обратиться к не плохому описанию на msdn. Я же хочу рассмотреть, как реализован механизм, основанный на блокировках. Принцип его действия будет понятен из классификации блокировок и описания различий в работе.
Но прежде, чем мы сможем ввести первую классификацию, нам необходимо вспомнить некоторые моменты организации памяти в MS SQL Server.
Организация памяти в MS SQL
Все данные в базе разбиты на страницы, которые в свою очередь формируют экстенты.Страница – занимает 8Kb, первые 96 байт из которых являются заголовком и содержат описание страницы.
Типы страниц (отличаются по типу хранимой информации):
- Data - данные таблицы (за исключением колонок переменного и очень большого размера);
- Index – индексы;
- Text/Image – колонки переменного и очень большого размера
- Другие типы, содержащие вспомогательную информацию
Экстент – основная единица управления пространством (64Kb), содержащая последовательные 8-м страниц (8*8Kb). Бывают однородные (Uniform) и смешанные (Mixed) экстенты. В однородных содержаться страницы одного типа, а в смешанных – разного типа. По мере появления новых страниц одного типа в составе смешанных экстентов, сервер старается создавать однородные экстенты, перемещая страницы между смешанными экстентами.
Теперь, зная, как организована память в сервере, можно ввести первую классификацию блокировок, а именно по типу блокируемого ресурса.
Разновидности блокировок по типу блокируемых ресурсов
Блокируемые ресурсы:- Конкретная строка в таблице
- Ключ (один или несколько ключей в индексе)
- Страница
- Экстент
- Таблица и все относящиеся к ней данные (индексы, ключи, данные)
- База (блокируется, когда меняется схема базы)
Эскалация блокировок
Это процесс, который направлен на оптимизацию работы сервера, позволяющий заменять множество низкоуровневых блокировок одной или несколькими блокировками более высокого уровня. Например, если у нас создано множество блокировок уровня строки, и все строки принадлежат одной странице, то сервер, в целях экономии ресурсов, может заменить их все одной блокировкой уровня страницы.
Разновидности блокировок по режиму блокирования
Совмещаемая (Shared) блокировка
Используются для операций считывания (SELECT) и предотвращают изменение (UPDATE, DELETE) заблокированного ресурса. Как следует из названия, данная блокировка может быть совмещена с другими блокировками (совмещаемыми или блокировками обновления, описаны ниже). Это значит, что если транзакция T1 считывает данные и устанавливает совмещаемые блокировки на считываемые строки, то другая транзакция T2 может так же установить блокировки на те же строки, не дожидаясь снятия блокировок транзакцией T1.
В зависимости от уровня изоляции транзакции, блокировка может быть снята, как только данные считаны (Read Committed), либо же удерживаться до конца транзакции (Repeatable Read и выше). Так же блокировку можно удерживать до конца транзакции, указав в запросе соответствующие табличные подсказки (например, HOLDLOCK, SERIALIZABLE и т.д.)
В случае, когда блокировки снимаются по мере чтения данных, они могут быть сняты даже до момента завершения запроса (SELECT). Т.е. если мы выбираем 10 строк и установлено 10 совмещаемых блокировок уровня строки, то, как только считаны данные первой строки, её блокировка снимается, не дожидаясь считывания оставшихся 9 строк.
Монопольная (Exclusive) блокировка
Используется для операций изменения данных (UPDATE, DELETE). Данная блокировка не может быть установлена, если существуют какие-либо другие блокировки на ресурсе, т.е. команда будет ждать снятия всех существующих блокировок. Будучи успешно установленной, данная блокировка не позволяет установку новых блокировок любых типов, т.е. все запросы, пытающиеся получить доступ к заблокированному ресурсу, будут ожидать снятия монопольной блокировки.
Блокировка обновления (Update)
Что бы понять, почему потребовалось ввести данный тип блокировок, давайте рассмотрим подробней процесс обновления данных. Логически, его можно разделить на два этапа:
- Поиск данных для обновления
- Обновление найденных данных.
Мы уже знаем, что для корректного обновления данных необходимо установить монопольную (exclusive) блокировку. Но, если монопольная блокировка будет установлена с самого начала выполнения запроса (на этапе поиска данных), то мы сделаем невозможным даже чтение данных из других транзакций. Поэтому на первом этапе (поиск данных) лучше будет установить разделяемую блокировку и только если данные найдены, то преобразовать её в монопольную и произвести изменение. Это позволит другим транзакциям избежать ожидания при чтении данных, пока транзакция ищет данные для обновления.
Вроде всё отлично с предлагаемым подходом. Создаём разделяемую блокировку на первом этапе и преобразуем её в монопольную на втором. Производительность улучшена и все счастливы. Но увы тут есть подвох. Если, по описанному выше алгоритму, две различные транзакции будут одновременно пытаться произвести обновление одних и тех же данных, то мы гарантированно получаем взаимоблокировку (Deadlock). Ниже показано как она возникает.
Поэтому потребовался новый режим блокировки – блокировка обновления (Update). Он ведёт себя как что-то среднее между совмещаемой (Shared) и монопольной (Exclusive) блокировкой. «Монопольность» заключается в том, что на ресурсе может быть только одна блокировка обновления, а «совмещаемость» в том, что на этапе поиска данных блокировка может совмещаться с другими совмещаемыми блокировками.
Примечание: блокировка обновления (update) используется не только для операций UPDATE, но и для удаления данных (DELETE) на этапе поиска удаляемых данных. В случае вставки новых строк в таблицу с кластерным индексом INSERT, данный тип блокировки также применим на этапе поиска правильного положения в индексе; когда положение найдено, блокировка обновления (update) преобразуется в монопольную блокировку индекса и происходит вставка новой строки.
Блокировки с намерением (Intent)
Данный тип блокировок не представляет собой особый режим. Он служит для оптимизации работы алгоритма установки блокировок, описанных выше.
В основе лежит простая идея. Перед установкой низкоуровневых блокировок (уровня строки или страницы), мы всегда сначала устанавливаем блокировку намерения (Intent) на более высоком уровне – на уровне таблицы. Если такой блокировки нет, то мы можем избежать проверки наличия уже существующих блокировок на интересующих нас ресурсах (строках, страницах) и сразу их установить. Если она есть, то можно более оптимально принять решение о возможности установки определённой блокировки низкого уровня. Например, если существует совмещаемая блокировка с намерением, то нет смысла пытаться получить эксклюзивную блокировку на уровне таблицы.
В зависимости от типа низкоуровневых блокировок можно выделить следующие типы блокировок с намерением:- Блокировка с намерением совмещаемого доступа (IS)
- Блокировка с намерением монопольного доступа (IX)
- Совмещаемая блокировка с намерением монопольного доступа (SIX)
- Блокировка с намерением обновления (IU)
- Совмещаемая блокировка с намерением обновления (SIU)
- Блокировка обновления с намерением монопольного доступа (UIX)
Блокировки схем (Schema)
Данные блокировки служат для предотвращения изменения структуры базы данных (таблиц, представлений, индексов и т.д.) и подразделяются на два вида:
- Блокировка изменения схемы (Sch-M) – устанавливается при обновлении схемы таблицы и на время существования запрещает любой доступ к данным таблицы
- Блокировка стабильности схемы (Sch-S) – устанавливается при выполнении запросов; данная блокировка добавляется ко всем объектам схемы (схема таблицы, индексы и т.п.), которые задействованы в запросе и на время существования предотвращает их изменение
Блокировка массового обновления (Bulk update)
Позволяет поддерживать несколько одновременных потоков массовой загрузки данных в одну и ту же таблицу и при этом запрещает доступ к таблице любым другим процессам, отличным от массовой загрузки данных.
Устанавливается при использовании:- bcp утилиты
- команды BULK INSERT
- указана подсказка TABLOCK
- установлен параметр «блокировка таблицы при массовой загрузке (table lock on bulk load)» при помощи хранимой процедуры sp_tableoption
Блокировка диапазона ключа (Range)
Данная блокировка, позволяет предотвратить проблему появления фантомов, путём блокирования диапазона выбранных строк. Т.е. для выбранного набора строк будет гарантированно отсутствие появления новых строк (подпадающих под критерий запроса), а также удаление строк из выбранного набора. На основе данной блокировки реализован уровень изоляции SERIALIZABLE. Более подробно тут.Совместимость блокировок
Как уже упоминалось выше, некоторые типы блокировки могут быть успешно установлены на одном и том же ресурсе. Другие же, напротив, будут ожидать завершение всех блокировок. Ниже представлена полная матрица совместимости, которая показывает, может ли конкретный тип блокировки быть установлен, при наличии уже имеющихся блокировок.Например, нас интересует может ли быть установлена разделяемая блокировка (Shared), если на ресурсе установлена монопольная блокировка (Exclusive). Для этого мы находим строку, соответствующую запрашиваемой блокировке (выделена синей рамкой) и находим значение в соответствующем столбце (выделено красной рамкой). В нашем примере мы видим значение «К», что говорит о конфликте, т.е. разделяемая блокировка (S) будет вынуждена ждать, пока с ресурса не будет снята монопольная блокировка (X).
нет ни одного примера, непонятно ничего
ОтветитьУдалитьА приведенные SQL скрипты не примеры?
Удалить