в какой транзакции расположен отчет мотивационная модель
На пути к правильным SQL транзакциям (Часть 1)
Мне часто приходилось сталкиваться с тем, что люди прекрасно понимают, что такое транзакции в базе данных и для чего они нужны, но при этом не всегда умеют ими правильно пользоваться. Безусловно, для достижения 80-го уровня сакрального знания нужно иметь не один год опыта и прочесть множество толстенных книг по SQL. Поэтому в этой статье я даже не буду пытаться описать всё, что может быть связано с транзакциями в MS SQL. Я хочу затронуть один простой, но очень важный вопрос, который разработчики часто упускают из вида – уровни изоляции транзакций.
Несмотря на то, что тема очень проста, во многих источниках она освящается плохо – информации либо очень мало, либо очень много. Т.е. прочитав 5-6 кратких теоретических определений невозможно их применить на практике. Для уверенного понимания предмета статьи нужно обращаться к специализированной литературе, но там информации на столько много, что далеко не каждый может уделить необходимое время для её усваивания.
Сегодня я хочу поделиться своим простым рецептом, который помог мне раз и на всегда запомнить особенности уровней изоляции транзакций и по сей день помогает без проблем принимать взвешенные решения о выборе необходимого уровня.
Секрет предлагаемого способа запоминания в том, что краткая теория будет сопровождаться простыми практическими примерами, которые мне были бы на много понятней, чем подробное описание.
И так, для понимания различий в уровнях изоляции необходимо разобраться с нежелательными побочными эффектами, которые могут возникать, если транзакции будут не изолированы друг от друга. Поняв специфику этих эффектов, нам останется только посмотреть, от каких эффектов защищает каждый отдельно взятый уровень. После этого, я уверен, что тема изоляции транзакций вам навсегда перестанет казаться чем-то заоблачно сложным.
Побочные эффекты параллелизма
Для создания таблицы и наполнения её данными можно запустить следующий скрипт.
Так же данный скрипт желательно выполнить перед рассмотрением каждого примера. Это будет гарантировать идентичность получаемых результатов с теми, что описаны ниже.
Транзакции и механизмы их контроля
Транзакции
Транзакцией называется последовательность операций над данными имеющая начало и конец
Транзакция это последовательное выполнение операций чтения и записи. Окончанием транзакции может быть либо сохранение изменений (фиксация, commit) либо отмена изменений (откат, rollback). Применительно к БД транзакция это нескольких запросов, которые трактуются как единый запрос.
Транзакции должны удовлетворять свойствам ACID
Атомарность. Транзакция либо выполняется полностью либо не выполняется вовсе.
Согласованность. При завершении транзакции не должны быть нарушены ограничения накладываемые на данные (например constraints в БД). Согласованность подразумевает, что система будет переведена из одного корректного состояния в другое корректное.
Изолированность. Параллельно выполняемые транзакции не должны влиять друг на друга, например менять данные которые использует другая транзакция. Результат выполнения параллельных транзакций должен быть таким, как если бы транзакции выполнялись последовательно.
Устойчивость. После фиксации изменения не должны быть утеряны.
Журнал транзакций
Журнал хранит изменения выполненные транзакциями, обеспечивает атомарность и устойчивость данных в случае сбоя системы
Журнал содержит значения, которые данные имели до и после их изменения транзакцией. Write-ahead log strategy обязывает добавлять в журнал запись о предыдущих значениях до начала, а о конечных после завершения транзакции. В случае внезапной остановки системы БД читает лог в обратном порядке и отменяет изменения сделанные транзакциями. Встретив прерванную транзакцию БД выполняет ее и вносит изменения о ней в журнал. Находясь в состоянии на момент сбоя, БД читает лог в прямом порядке и возвращает изменения сделанные транзакциями. Таким образом сохраняется устойчивость транзакций которые уже были зафиксированы и атомарность прерванной транзакции.
Простое повторное выполнение ошибочных транзакций недостаточно для восстановления.
Пример. На счету у пользователя 500$ и пользователь решает снять их через банкомат. Выполняются две транзакции. Первая читает значение баланса и если на балансе достаточно средств выдает деньги пользователю. Вторая вычитает из баланса нужную сумму. Допустим, произошел сбой системы и первая операция не выполнилась, а вторая выполнилась. В этом случае мы не можем повторно выдать деньги пользователю без возврата системы в изначальное состояние с положительным балансом.
Уровни изоляции
Чтение фиксированных данных (Read Committed)
Проблема грязного чтения (Dirty Read) заключается в том, что транзакция может прочесть промежуточный результат работы другой транзакции.
Пример. Начальное значение баланса 0$. Т1 добавляет к балансу 50$. Т2 считывает значение баланса (50$). Т1 отменяет изменения и завершается. T2 продолжает выполнение располагая неверными данными о балансе.
Решением является чтение фиксированных данных (Read Committed) запрещающее читать данные, измененные транзакцией. Если транзакция A изменила некоторый набор данных, то транзакция B при обращении за этими данными вынуждена ожидать завершения транзакции A.
Повторяемое чтение (Repeatable Read)
Проблема потерянных изменений (Lost Updates). Т1 сохраняет изменения поверх изменений Т2.
Пример. Начальное значение баланса 0$ и две транзакции одновременно пополняют баланс. T1 и T2 читают баланс равный 0$. Затем T2 прибавляет 200$ к 0$ и сохраняет результат. T1 прибавляет 100$ к 0$ и сохраняет результат. Итоговый результат 100$ вместо 300$.
Проблема неповторяемого чтения (Unrepeatable read). Повторное чтение одних и тех же данных возвращает разные значения.
Пример. Т1 читает значение баланса равное 0$. Затем Т2 добавляет к балансу 50$ и завершается. Т1 повторно читает данные и обнаруживает несоответствие с предыдущим результатом.
Повторяемое чтение (Repeatable Read) гарантирует что повторное чтение вернет тот же результат. Данные прочитанные одной транзакцией запрещено менять в других до завершения транзакции. Если транзакция A прочла некоторый набор данных, то транзакция B при обращении за этими данными вынуждена ожидать завершения транзакции A.
Упорядоченное чтение (Serializable)
Проблема фантомного чтения (Phantom Reads). Два запроса выбирающие данные по некоему условию возвращают разные значения.
Пример. T1 запрашивает количество всех пользователей баланс которых больше 0$ но меньше 100$. T2 вычитает 1$ у пользователя с балансом 101$. T1 повторно выполняет запрос.
Упорядоченное чтение (Serializable). Транзакции выполняются как полностью последовательные. Запрещается обновлять и добавлять записи, подпадающие под условия запроса. Если транзакция A запросила данные всей таблицы, то таблица целиком замораживается для остальных транзакций до завершения транзакции A.
Планировщик (Scheduler)
Устанавливает очередность в которой должны выполняться операции при параллельно протекающих транзакциях
Обеспечивает заданный уровень изолированности. Если результат выполнения операций не зависит от их очередности, то такие операции коммутативны (Permutable). Коммутативны операции чтения и операции над разными данными. Операции чтения-записи и записи-записи не коммутативны. Задача планировщика чередовать операции выполняемые параллельными транзакциями так, чтобы результат выполнения был эквивалентен последовательному выполнению транзакций.
Механизмы контроля параллельных заданий (Concurrency Control)
Оптимистический основан на обнаружении и разрешении конфликтов, пессимистический на предотвращении возникновения конфликтов
При оптимистическом подходе несколько пользователей получают в свое распоряжение копии данных. Первый завершивший редактирование сохраняет изменения, остальные же должны осуществить слияние изменений. Оптимистический алгоритм позволяет конфликту произойти, но система должна восстановиться после конфликта.
При пессимистическом подходе первый пользователь захвативший данные препятствует получению данных остальным. Если конфликты редки разумно выбрать оптимистическую стратегию, так как она обеспечивает более высокий уровень параллелизма.
Блокировка (Locking)
Если одна транзакция заблокировала данные, то остальные транзакции при обращении к данным обязаны ждать разблокировки
Блок может накладываться на базу данных, таблицу, ряд или аттрибут. Совместный захват (Shared Lock) может быть наложен на одни данные несколькими транзакциями, разрешает всем транзакциям (включая наложившую) чтение, запрещает изменение и монопольный захват. Монопольный захват (Exclusive Lock) может быть наложен только одной транзакцией, разрешает любые действия наложившей транзакции, запрещает любые действия остальным.
Взаимоблокировкой считается ситуация когда транзакции оказываются в режиме ожидания, длящемся бесконечно долго
Пример. Первая транзакция ждет освобождения данных захваченных второй, в то время как вторая ждет освобождения данных, захваченных первой.
Оптимистическое решение проблемы взаимоблокировок позволяет взаимоблокировке произойти, но затем восстанавливает систему откатывая одну из транзакций, участвующих во взаимоблокировке
С определенной периодичностью производится поиск взаимоблокировок. Один из способов обнаружения — по времени, то есть считать что взаимоблокировка произошла если транзакция выполняется слишком долго. Когда взаимоблокировка найдена, то одна из транзакций откатывается, что дает возможность другим транзакциям участвующим во взаимоблокировке завершиться. Выбор жертвы может быть основан на стоимости транзакций или их старшинстве (Wait-Die и Wound-wait схемы).
Каждой транзакции T присваивается временная метка TS содержащая время начала выполнения транзакции.
Если TS(Ti) = W-TS(Q), то чтение выполняется и R-TS(Q) становится MAX(R-TS(Q), TS(T)).
Когда транзакция T запрашивает изменение данных Q возможны два варианта.
Теория транзакций с примерами из Microsoft SQL Server
Думаю, многие из вас работали с транзакциями и представляют, как применить к базе данных консистентную последовательность операций. Сегодня мы узнаем, что происходит с транзакцией, когда мы отправляем ее в СУБД. Мы познакомимся с классической теорией транзакций и тем, какие существуют подходы для формирования корректных расписаний. Кроме того, постараемся связать эту теорию с практикой на примере известной СУБД Microsoft SQL Server. (Сегодня будет много информации, приготовьтесь!)
Сериализуемые расписания
Транзакции
Начнем с определения того, что такое транзакция:
В MS SQL Server существует 2 типа транзакций:
Для примера рассмотрим транзакцию, обновляющую значения сразу в двух таблицах. В результате ее выполнения либо оба значения будут удачно записаны, либо, в случае отката транзакции, не будет записано ни одно.
Аномалии транзакций
При параллельном выполнении транзакций возникают различные проблемы, связанные с логикой работы с операциями. Рассмотрим наиболее распространенные из них на примерах из SQL сервера:
1) Потерянное обновление. При обновлении поля двумя транзакциями одно из изменений теряется.
| Транзакция 1 | Транзакция 2 |
|---|---|
| SELECT x FROM tbl WHERE y=1; | SELECT x FROM tbl WHERE y=1; |
| UPDATE tbl SET x=5 WHERE y=1; | |
| UPDATE tbl SET x=3 WHERE y=1; |
2) Грязное чтение. Чтение данных, полученных в результате действия транзакции, которая после этого откатится.
| Транзакция 1 | Транзакция 2 |
|---|---|
| SELECT x FROM tbl WHERE y=1; | |
| UPDATE tbl SET x=x+1 WHERE y=1; | |
| SELECT x FROM tbl WHERE y=1; | |
| ROLLBACK; |
3) Неповторяющееся чтение. Возникает, когда в течение одной транзакции при повторном чтении данные оказываются перезаписанными.
| Транзакция 1 | Транзакция 2 |
|---|---|
| SELECT x FROM tbl WHERE y=1; | SELECT x FROM tbl WHERE y=1; |
| UPDATE tbl SET x=x+1 WHERE y=1; | |
| COMMIT; | |
| SELECT x FROM tbl WHERE y=1; |
4) Фантомное чтение. Отличие от предыдущей аномалии в том, что при повторном чтении одна и та же выборка дает разные множества строк.
| Транзакция 1 | Транзакция 2 |
|---|---|
| SELECT SUM(x) FROM tbl; | |
| INSERT INTO tbl (x, y) VALUES (5, 3); | |
| SELECT SUM(x) FROM tbl; |
Вычислительная модель
Для того, чтобы избегать проблем, описанных выше, необходима модель теории транзакций. Она включает:
Сегодня мы будем рассматривать транзакции в контексте страничной модели. В этой модели база данных представляется как набор независимых страниц ` (x, y, z) `, над которыми возможны две атомарные операции: read и write с полным или частичным порядком внутри транзакции.
Критерии корректности
Одним из ключевых понятий теории транзакций является термин серийное расписание. История называется серийной, если для любых двух транзакций либо все операции первой предшествуют всем операциям второй, либо наоборот.
Такие расписания не могут привнести никаких проблем при выполнении транзакций, но крайне неэффективны. Цель: определить эквивалентность на множестве транзакций и считать корректными те из них, которые эквивалентны серийному. Среди важных требований к критериям корректности выделим: эффективную проверяемость и достаточно большую мощность множества корректных расписаний.
Семантика Эрбрана
С помощью этого понятия определяются последующие сериализуемости. Семантика Эрбрана основывается на двух предположениях:
Выпишем семантику Эрбрана для страницы x. Для этого, начиная с последней операции записи, раскрываем операции в соответствии с правилами выше. Нулевая транзакция выполняется первоначально и инициализирует значения базы данных.
| Расписание | Семантика Эрбрана |
|---|---|
| r1(x) r2(y) w1(x) w3(z) r2(x) w2(z) w2(x) | f2x(f1x(f0x)), f2y(f0y)) |
Сериализуемость по конечному состоянию
Расписания эквивалентны по конечному состоянию, если:
Расписание называется сериализуемым по конечному расписанию (FSR), если оно эквивалентно серийному по конечному состоянию. Пример неэквивалентных расписаний:
| Расписание | Семантика Эрбрана |
|---|---|
| r1(x) r2(y) w1(y) w2(y) c1 c2 | f2y(f0y()) |
| r1(x) w1(y) r2(y) w2(y) c1 c2 | f2y(f1y(f0y())) |
Этот тип сериализуемости решает проблему потери обновления, но с другими аномалиями справиться он не в силах.
Сериализуемость по видимому состоянию
В дополнение к условиям предыдущей эквивалентности для эквивалентности по видимому состоянию требуется, чтобы все операции чтения в расписаниях следовали после одинаковых операций записи. Пример расписания, сериализуемого по видимому (VSR), но не по конечному состоянию: ` w_1(x) r_2(x) r_2(y) w_1(y) c_1 c_2 `
VSR по-прежнему не может справиться с аномалиями грязного чтения, так как не учитывает оборванные транзакции. Кроме того, проверка расписания на эту сериализуемость является NP-полной.
Сериализуемость по конфликтам
Диспетчеры и протоколы
Функционирование диспетчера
В системах управления базами данных важную роль играет планировщик (диспетчер). В его функции входит преобразование произвольного расписания, полученного от менеджера транзакций (МТ), в эквивалентное серийное, передача менеджеру данных и возврат результатов обратно МТ.
В MS SQL Server, так же как и во многих других СУБД есть два вида конкурентного доступа (протоколов):
Протокол Two Phase Locking
Протокол двухфазного блокирования основывается на том правиле, что любая операция установки блокировки предшествует любой операции снятия блокировки внутри одной транзакции. В результате возникают две фазы обработки блокировок. Что касается корректности, протокол генерирует расписания, сериализуемые по конфликтам.
Для таблиц помимо разделяемой и монопольной можно также использовать три других типа блокировок:
Проблемы 2PL
Одной из главных проблем блокирующих протоколов является возникновение тупиков (взаимоблокировок). Под тупиками понимаются ситуации, когда две транзакции блокируют друг друга. Небольшой пример:
Кроме того, существует еще один способ борьбы с тупиками. Для преждевременного завершения транзакции можно установить ограничение по времени. Если время ожидания блокировки превышает это ограничение, транзакция обрывается. В SQL сервере используется следующий синтаксис: SET LOCK_TIMEOUT 4000
Snapshot Isolation
На практике применяется модификация этого протокола с использованием блокировок. Они устанавливаются только на запись, а при невозможности установить блокировку транзакция обрывается. В Microsoft SQL сервере этому протоколу соответствует одноименный уровень изоляции. О том, что это такое читайте в следующем разделе.
Уровни изоляции в MS SQL Server
Уровень изоляции задает степень защищенности выбираемых транзакцией данных от возможности изменения другими транзакциями. Другими словами, они позволяют указать, какие аномалии транзакций могут иметь место, а какие необходимо избежать.
Read Uncommitted
Самая простая форма изоляции между транзакциями. Этот уровень не использует никакие блокировки, и, следовательно, совершенно не изолирует операции чтения от других транзакций. Из описанных в начале поста аномалий Read Uncommitted допускает три: грязное чтение, неповторяемое чтение и фантомы.
Read Committed
На этом уровне изоляции транзакция, читающая строку, проверяет только наличие монопольной блокировки для данной строки. В случае отсутствия блокировки транзакция извлекает строку с использованием уже разделяемой блокировки. Таким образом предотвращается чтение транзакцией данных, которые не были подтверждены и которые могут быть позже отменены. После того, как данные были прочитаны, их можно изменять другими транзакциями.
Применяемые этим уровнем изоляции разделяемые блокировки отменяются сразу же после обработки данных. При таком подходе к параллельному выполнению транзакций остаются аномалии неповторяемого и фантомного чтения.
Repeatable Read
В отличие от предыдущего уровня изоляции, Repeatable Read вешает разделяемые блокировки на все считываемые данные и удерживает их до тех пор, пока транзакция не будет подтверждена или отменена. Поэтому в этом случае многократное выполнение запроса внутри транзакции всегда будет возвращать один и тот же результат. Недостатком этого уровня изоляции является дальнейшее ухудшение одновременного конкурентного доступа, поскольку период времени, в течение которого другие транзакции не могут обновлять те же самые данные, значительно дольше, чем в случае уровня Read Committed.
Тем не менее, этот уровень изоляции не препятствует другим инструкциям вставлять новые строки, которые включаются в последующие операции чтения, вследствие чего могут появляться фантомы.
Serializable
Уровень изоляции Serializable является самым строгим, потому что он не допускает возникновения всех четырех аномалий конкурентного доступа, перечисленных в начале поста. Этот уровень устанавливает блокировку на всю область данных, считываемых соответствующей транзакцией. Поэтому этот уровень изоляции также предотвращает вставку новых строк другой транзакцией до тех пор, пока первая транзакция не будет подтверждена или отменена.
Read Committed Snapshot
Последние два уровня используются в оптимистичном контексте. Read Committed Snapshot применяется на уровне инструкции, что означает, что что любая другая транзакция будет читать зафиксированные значения в том виде, в каком они существуют на момент начала этой инструкции. Для выборки строк для обновлений этот уровень изоляции возвращает версии строк в фактические данные и устанавливает на выбранных строках блокировки обновлений. Реальные строки данных, которые требуется изменить, получают монопольные блокировки.
Snapshot
Уровень изоляции Snapshot предоставляет изоляцию на уровне транзакций, что означает, что любая другая транзакция будет читать подтвержденные значения в том виде, в каком они существовали непосредственно перед началом выполнения транзакции этого уровня изоляции. Кроме этого, транзакция уровня изоляции Snapshot будет возвращать исходное значение данных до завершения своего выполнения, даже если в течение этого времени оно будет изменено другой транзакцией. Поэтому другая транзакция сможет читать модифицированное значение только после завершения выполнения транзакции уровня изоляции Snapshot.
Заключение
Сегодня мы познакомились с самыми основами теории транзакций и посмотрели, какие из них нашли свое применение в промышленной СУБД. Пост основывался на материалах лекций Новикова Б. А. и книге Душана Петковича Microsoft SQL Server 2012.
Written on November 25th, 2017 by Alexey Kalina
Модели транзакций
Транзакцией называется последовательность операций, производимых над базой данных и переводящих базу данных из одного непротиворечивого (согласованного) состояния в другое непротиворечивое (согласованное) состояние.
Следующий пример, который связан с принятием заказа в фирме на изготовление компьютера. Компьютер состоит из комплектующих, которые сразу резервируются за данным заказом в момент его формирования. Тогда транзакцией будет вся последовательность операций, включающая следующие операции :
Еще один пример, который весьма характерен для учебных заведений. При длительной болезни преподавателя или при его увольнении перед администрацией кафедры встает задача перераспределения всей нагрузки, которую ведет преподаватель, по другим преподавателям кафедры. Видов нагрузки может быть несколько: чтение лекций и проведение занятий по текущему расписанию, руководство квалификационными работами бакалавров, руководство дипломными проектами специалистов, руководство магистерскими диссертациями, индивидуальная научно-исследовательская работа со студентами. И для каждого вида нагрузки необходимо найти исполнителей и назначить им дополнительную нагрузку.
Свойства транзакций. Способы завершения транзакций
Существуют различные модели транзакций, которые могут быть классифицированы на основании различных свойств, включающих структуру транзакции, параллельность внутри транзакции, продолжительность и т. д.
Возможны два варианта завершения транзакции. Если все операторы выполнены успешно и в процессе выполнения транзакции не произошло никаких сбоев программного или аппаратного обеспечения, транзакция фиксируется.
Фиксация транзакции — это действие, обеспечивающее запись на диск изменений в базе данных, которые были сделаны в процессе выполнения транзакции.
До тех пор пока транзакция не зафиксирована, допустимо аннулирование этих изменений, восстановление базы данных в то состояние, в котором она была на момент начала транзакции. Фиксация транзакции означает, что все результаты выполнения транзакции становятся постоянными. Они станут видимыми другим транзакциям только после того, как текущая транзакция будет зафиксирована. До этого момента все данные, затрагиваемые транзакцией, будут «видны» пользователю в состоянии на начало текущей транзакции.
Если в процессе выполнения транзакции случилось нечто такое, что делает невозможным ее нормальное завершение, база данных должна быть возвращена в исходное состояние. Откат транзакции — это действие, обеспечивающее аннулирование всех изменений данных, которые были сделаны операторами SQL в теле текущей незавершенной транзакции.
В первых версиях коммерческих СУБД была реализована модель транзакций ANSI/ISO. В дальнейшем в СУБД SYBASE была реализована расширенная модель транзакций, которая включает еще ряд дополнительных операций. В модели SYBASE используются следующие четыре оператора:
Принципы выполнения транзакций в расширенной модели транзакций представлены на рис. 11.2. На рисунке операторы помечены номерами, чтобы нам удобнее было проследить ход выполнения транзакции во всех допустимых случаях.
Конечно, расширенная модель транзакции, предложенная фирмой SYBASE, поддерживает гораздо более гибкий механизм выполнения транзакций. Точки сохранения позволяют устанавливать маркеры внутри транзакции таким образом, чтобы имелась возможность отмены только части работы, проделанной в транзакции. Целесообразно использовать точки сохранения в длинных и сложных транзакциях, чтобы обеспечить возможность отмены изменения для определенных операторов. Однако это обусловливает дополнительные затраты ресурсов системы — оператор выполняет работу, а изменения затем отменяются; обычно усовершенствования в логике обработки могут оказаться более оптимальным решением.