Транзакции (Azure Synapse Analytics)
Транзакция — это группа инструкций одной или нескольких баз данных, которые либо полностью фиксируются, либо полностью откатываются. Транзакции атомарны, согласованы, изолированы и устойчивы (atomic, consistent, isolated, durable — ACID). Если транзакция выполнена успешно, все инструкции в ней фиксируются. Если транзакция завершается ошибкой, то если хотя бы одна инструкция в группе завершается ошибкой, выполняется откат всей группы.
Начало и конец транзакции зависят от параметра AUTOCOMMIT и инструкций BEGIN TRANSACTION, COMMIT и ROLLBACK. Azure Synapse Analytics поддерживает следующие типы транзакций:
Явные транзакции начинаются с инструкции BEGIN TRANSACTION и заканчиваются инструкцией COMMIT или ROLLBACK.
Транзакции с автофиксацией автоматически запускаются в рамках сеанса и не начинаются с инструкции BEGIN TRANSACTION. Если для параметра AUTOCOMMIT установлено значение ON, каждая инструкция выполняется в транзакции, и явные инструкции COMMIT или ROLLBACK не требуются. Если для параметра AUTOCOMMIT установлено значение OFF, для определения результата транзакции требуется инструкция COMMIT или ROLLBACK. В Azure Synapse Analytics транзакции с автофиксацией выполняются сразу после инструкции COMMIT или ROLLBACK или после инструкции SET AUTOCOMMIT OFF.
Синтаксис
Аргументы
BEGIN TRANSACTION
Отмечает начальную точку явной транзакции.
COMMIT [ WORK ]
Отмечает завершение явной транзакции или транзакции с автофиксацией. Эта инструкция вызывает изменения в транзакции, чтобы всегда быть зафиксированной в базе данных. Инструкция COMMIT идентична инструкциям COMMIT WORK, COMMIT TRAN и COMMIT TRANSACTION.
ROLLBACK [ WORK ]
Выполняет откат транзакции на начало транзакции. Никакие изменения транзакции не фиксируются в базе данных. Инструкция ROLLBACK идентична инструкциям ROLLBACK WORK, ROLLBACK TRAN и ROLLBACK TRANSACTION.
SET AUTOCOMMIT < ON | OFF >
Определяет метод запуска и завершения транзакций.
ON
Каждая инструкция выполняется в своей транзакции, явные инструкции COMMIT или ROLLBACK не требуются. Явные транзакции разрешены, когда для параметра AUTOCOMMIT установлено значение ON.
OFF
Azure Synapse Analytics автоматически запускает транзакцию, если она еще не выполняется. Все последующие инструкции выполняются в рамках транзакции, и инструкции COMMIT или ROLLBACK необходимы для определения результата транзакции. Сразу после фиксации или отката транзакции в этом режиме значение OFF сохраняется, а Azure Synapse Analytics запускает новую транзакцию. Явные транзакции не разрешены, если AUTOCOMMIT имеет значение OFF.
Если изменить параметр AUTOCOMMIT в активной транзакции, этот параметр не повлияет на текущую транзакцию и вступит в силу только после завершения транзакции.
Если для параметра AUTOCOMMIT установлено значение ON, выполнение другой инструкции SET AUTOCOMMIT ON не будет иметь результата. Подобным образом, если для параметра AUTOCOMMIT установлено значение OFF, выполнение другой инструкции SET AUTOCOMMIT OFF не будет иметь результата.
SET IMPLICIT_TRANSACTIONS < ON | OFF >
Включает те же режимы, что и SET AUTOCOMMIT. Присвоение параметру SET IMPLICIT_TRANSACTIONS значения ON устанавливает для соединения режим неявных транзакций. Значение OFF возвращает подключение в режим автофиксации. Дополнительные сведения см. в разделе SET IMPLICIT_TRANSACTIONS (Transact-SQL).
Разрешения
Для выполнения инструкций, связанных с транзакциями, не нужны конкретные разрешения. Разрешения необходимы для запуска инструкций внутри транзакции.
Обработка ошибок
Если выполнить инструкции COMMIT или ROLLBACK без активной транзакции, возникает ошибка.
Если выполнить инструкцию BEGIN TRANSACTION во время выполнения транзакции, возникает ошибка. Это может произойти, если инструкция BEGIN TRANSACTION выполняется после успешного запуска инструкции BEGIN TRANSACTION или для сеанса установлено SET AUTOCOMMIT OFF.
Если из-за ошибки (кроме ошибки во время выполнения инструкции) выполнение явной транзакции невозможно, Azure Synapse Analytics автоматически выполняет ее откат и освобождает ресурсы, выделенные для этой транзакции. Например, если сетевое подключение клиента к экземпляру Azure Synapse Analytics разорвано или если клиент вышел из приложения, то после получения экземпляром уведомления от сети о разрыве подключения выполняется откат всех незафиксированных транзакций для этого подключения.
Если ошибка во время выполнения инструкции возникает в пакетной службе, Azure Synapse Analytics действует согласованно с параметром SQL ServerXACT_ABORT, установленным в ON, и выполняется откат всей транзакции. Дополнительные сведения о параметре XACT_ABORT см. в разделе SET XACT_ABORT (Transact-SQL).
Общие замечания
Сеанс может одновременно выполнять только одну транзакцию. Точки сохранения и вложенные транзакции не поддерживаются.
Обязанностью программиста на языке SQL является вызов инструкции COMMIT только если логически верны все данные, относящиеся к транзакции.
Если сеанс закрывается до завершения транзакции, транзакция откатывается.
Управление режимами транзакций выполняется на уровне сеанса. Например, если один сеанс запускает явную транзакцию или устанавливает для параметра AUTOCOMMIT значение OFF или для параметра IMPLICIT_TRANSACTIONS значение ON, это не влияет на режимы транзакции в других сеансах.
Ограничения
Нельзя произвести откат транзакции после вызова инструкции COMMIT, так как измененные данные уже стали частью базы данных.
Azure Synapse Analytics не поддерживает механизм общего доступа к транзакциям. Это означает, что в любой момент времени только один сеанс может работать с транзакцией в системе.
Режим блокировки
В Azure Synapse Analytics используется блокировка для обеспечения целостности транзакций и согласованности баз данных, когда несколько пользователей одновременно обращаются к одним и тем же данным. Блокировка используется в явных и неявных транзакциях. Каждая транзакция запрашивает блокировку разных типов ресурсов, например таблиц или баз данных, от которых эта транзакция зависит. Все блокировки Azure Synapse Analytics выполняются на уровне таблицы или более высоком уровне. Блокировка не дает другим транзакциям изменять ресурсы, чтобы избежать ошибок в транзакции, запросившей блокировку. Каждая транзакция снимает свои блокировки, если больше не зависит от заблокированных ресурсов. Явные транзакции сохраняют блокировки до завершения транзакции — ее фиксации или отката.
Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)
A. Использование явной транзакции
Б. Откат транзакции
В приведенном ниже примере демонстрируется результат отката транзакции. В этом примере инструкция ROLLBACK приведет к откату инструкции INSERT, но созданная таблица будет по-прежнему существовать.
В. Настройка параметра AUTOCOMMIT
В следующем примере для параметра AUTOCOMMIT устанавливается значение ON .
В следующем примере для параметра AUTOCOMMIT устанавливается значение OFF .
Транзакции и механизмы их контроля
Транзакция это последовательное выполнение операций чтения и записи. Окончанием транзакции может быть либо сохранение изменений (фиксация, 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) < TS(Tj), то Ti ждет, иначе Ti откатывается и начинается заново с той же временной меткой.
Если молодая транзакция захватила ресурс, а более старая запрашивает тот же ресурс, то старшей транзакции позволено ожидать. Если более старая транзакция захватила ресурс, то молодая транзакция запрашивающая этот ресурс будет откачена.
Если TS(Ti) < TS(Tj), то Tj откатывается и начинается заново с той же временной меткой, иначе Ti ждет.
Если более молодая транзакция захватила ресурс, а более старая транзакция запрашивает этот же ресурс, то молодая транзакция будет откачена. Если более старая транзакция захватила ресурс, то более молодой транзакции, запрашивающей этот ресурс позволено ожидать. Выбор жертвы основанный на старшинстве предотвращает появление взаимоблокировок, но откатывает транзакции которые не находятся в состоянии взаиомблокировки. Проблема заключается в том, что транзакции могут откатываться много раз, т.к. более старая транзакция может долго удерживать ресурс.
Пессимистическое решение проблемы взаимоблокировок не позволяет транзакции начать выполнение если есть риск возникновения взаимоблокировки
Для обнаружения взаимоблокировки строится граф (граф ожидания, wait-for-graph), вершины которого транзакции, а ребра направлены от транзакций ожидающих освобождения данных к транзакции захватившим эти данные. Считается что взаимоблокировка произошла, если граф имеет зацикленность. Построение графа ожидания, особенно в распределенных БД, дорогостоящая процедура.
Двухфазная блокировка — предотвращение взаимоблокировок путем захвата всех ресурсов используемых транзакцией в начале транзакции и освобождения их в конце
Все блокирующие операции должны предшествовать первой разблокирующей. Имеет две фазы — Growing Phase при которой происходит накопление захватов и Shrinking Phase при которой происходит освобождение захватов. При невозможности захвата одного из ресурсов транзакция начинается сначала. Возможна ситуация когда транзакция не сможет захватить требуемые ресурсы, например если несколько транзакций будут конкурировать за одни ресурсы.
Двухфазный коммит обеспечивает выполнение коммита на всех репликах БД
Каждая БД вносит информацию о данных которые будут изменены в лог и отвечает координатору ОК (Voting Phase). После того как все ответили ОК координатор отсылает сигнал обязывающий всех произвести коммит. После коммита сервера отвечают ОК, если хоть один не ответил ОК, то координатор отсылает сигнал отмены изменений всем серверам (Completion Phase).
Метод временных меток
Более старая транзакция откатывается при попытке доступа к данным, задействованным более молодой транзакцией
Каждой транзакции назначается временная метка TS соответствующая времени начала выполнения. Если Ti старше Tj, то TS(Ti) < TS(Tj).
Когда транзакция откатывается, ей назначается новая временная метка. Каждый объект данных Q задействованный транзакцией помечается двумя метками. W-TS(Q) — временная метка самой молодой транзакции, успешно выполнившей запись над Q. R-TS(Q) — временная метка самой молодой транзакции, выполнившей запись чтения над Q.
Когда транзакция T запрашивает чтение данных Q возможны два варианта.
Если TS(T) < W-TS(Q), то есть данные были обновлены более молодой транзакцией, то транзакция T откатывается.
Если TS(T) >= W-TS(Q), то чтение выполняется и R-TS(Q) становится MAX(R-TS(Q), TS(T)).
Когда транзакция T запрашивает изменение данных Q возможны два варианта.
Если TS(T) < R-TS(Q), то есть данные уже были прочитаны более молодой транзакцией и если произвести изменение, то возникнет конфликт. Транзакция T откатывается.
Если TS(T) < W-TS(Q), то есть транзакция пытается перезаписать более новое значение, транзакция T откатывается. В остальных случаях изменение выполняется и W-TS(Q) становится равным TS(T).
Не требуется дорогостоящего построения графа ожидания. Более старые транзакции зависят от более новых, следовательно в графе ожидания нет циклов. Нет взаимоблокировок, поскольку транзакции не ожидают, а сразу откатываются. Возможны каскадные откаты. Если Ti откатилась, а Tj прочитала данные которые изменила Ti, то Tj тоже должна откатиться. Если при этом Tj уже была закоммичена, то возникнет нарушения принципа устойчивости.
Одно из решений каскадных откатов. Транзакция выполняет все операции записи в конце, причем остальные транзакции обязаны ожидать завершения этой операции. Транзакции ожидают коммита перед чтением.
SQL — Транзакции
От автора: транзакция — это единица работы, которая выполняется в отношении базы данных. Транзакции SQL — это единицы работы или последовательности действий, выполненных в логическом порядке: вручную или автоматически с помощью какой-либо программы базы данных.
Транзакция — это осуществление одного или нескольких изменений базы данных. Например, если вы создаете, обновляете или удаляете запись из таблицы, вы выполняете в этой таблице транзакцию. Важно контролировать транзакции, чтобы обеспечить целостность данных и обрабатывать ошибки базы данных.
Практически вы собираете множество SQL-запросов в группу, и они будут выполняться вместе как часть транзакции.
Свойства транзакций
Транзакции имеют следующие четыре стандартных свойства, обычно обозначаемых аббревиатурой ACID.
Атомарность – обеспечивает, чтобы все операции входящие в единицу работы были завершены успешно. В противном случае транзакция прерывается в момент сбоя, и все предыдущие операции возвращаются в прежнее состояние.
Бесплатный курс по PHP программированию
Освойте курс и узнайте, как создать веб-приложение на PHP с полного нуля
Согласованность — обеспечивает, чтобы база данных надлежащим образом изменяла состояние при успешной транзакции.
Изолированность — позволяет транзакциям работать независимо друг от друга и прозрачно.
Долговечность — гарантирует, что результат совершенной транзакции сохранится в случае сбоя системы.
Управление транзакциями
Для управления транзакциями используются следующие команды.
COMMIT — сохранить изменения.
ROLLBACK — отменить изменения.
SAVEPOINT — создает точки сохранения в группах транзакций.
SET TRANSACTION — помещает имя в транзакцию.
Команды управления транзакциями
Команды управления транзакциями используются только с командами DML, такими как — INSERT, UPDATE и DELETE. Они не могут использоваться при создании таблиц или их удалении, поскольку эти операции автоматически фиксируются в базе данных.
Команда COMMIT
Команда COMMIT — это транзакционная команда, используемая для сохранения изменений внесенных транзакцией в базу данных. Команда COMMIT сохраняет все транзакции в базе данных с момента выполнения последней команды COMMIT или ROLLBACK.