SET TRANSACTION
В Oracle пользователю разрешается выполнять несколько операторов изменения данных как одну логическую единицу работы, заканчиваемую или сохранением результатов в базе данных, или их отменой. Транзакция начинается с первого исполняемого SQL-оператора пользователя и заканчивается явной фиксацией (завершением) или откатом со стороны пользователя.
Транзакции обрабатываются группой управляющих средств, с помощью которых пользователь может выдать команду INSERT, UPDATE или DELETE, указывающую начало серии команд изменения данных. После внесения изменений в базу данных пользователь может сохранить данные в базе, закончив транзакцию явным образом. Если же в любой момент транзакции возникает ошибка, пользователь может отменить изменения в базе данных, возвратив ее в состояние, в котором она находилась до начала транзакции. Транзакции создаются посредством двух различных элементов базы данных Oracle. Первый – это набор команд, определяющий начало, контрольные точки и окончание транзакции, а второй – специальный механизм блокирования, предотвращающий изменение информации строк базы данных несколькими пользователями одновременно.
SET TRANSACTION
Транзакции описываются следующими командами:
- SETTRANSACTION – начинает транзакцию и устанавливает ее базовые характеристики.
- COMMIT – заканчивает текущую транзакцию сохранением изменений в базе данных и начинает новую транзакцию
- ROLLBACK – заканчивает текущую транзакцию отменой изменений в базе данных и начинает новую транзакцию
- SAVEPOINT – устанавливает контрольные точки (точки прерывания) для транзакции, разрешая неполный откат.
Команда SET TRANSACTION является необязательной, так как транзакция начинается:
- после регистрации пользователя в Oracle с помощью SQL*Plus и исполнения им первой команды
- после выдачи команды ROLLBACK или COMMIT, заканчивающей транзакцию
- после выхода пользователя из системы
- в результате аварии системы
- после выдачи команды описания данными, например ALTER DATABASE
Команда COMMIT в обработке транзакций представляет тот момент времени, когда пользователь уже внес необходимые изменения как одну логическую группу, а поскольку ошибок не произошло, он готов к сохранению результатов своей работы.
Выдачей команды COMMIT в базе данных еще и неявно закрывается текущая транзакция и начинается новая. Откат транзакции выполняется не только в результате выдачи команды ROLLBACK, но и неявно, когда выполнение оператора по той или иной причине заканчивается неудачно или когда пользователь отменяет оператор командой CTRL-C.
Установка контрольных точек с помощью команды SAVEPOINT позволяет разбить весь объем работы, выполняемой транзакцией, на отдельные сегменты. Откат можно производить только до конкретной точки сохранения, оставляя предшествующие изменения в силе. Точки сохранения очень полезны в ситуациях, когда необходимо восстановить часть незавершенной транзакции.
Следующий пример показывает, что в программном блоке сохраняются только те изменения, которые были внесены до описания точки сохранения:
Как установить контрольную точку sql
SAVEPOINT — определить новую точку сохранения в текущей транзакции
Синтаксис
Описание
SAVEPOINT устанавливает новую точку сохранения в текущей транзакции.
Точка сохранения — это специальная отметка внутри транзакции, которая позволяет откатить все команды, выполненные после неё, и восстановить таким образом состояние на момент установки этой точки.
Параметры
Имя, назначаемое новой точке сохранения. Ранее существующая точка с таким именем оказывается недоступной, пока не будут освобождены установленные позже одноимённые точки.
Замечания
Для отката к установленной точке сохранения предназначена команда ROLLBACK TO . Чтобы уничтожить точку сохранения, сохраняя изменения, произведённые после того, как она была установлена, применяется команда RELEASE SAVEPOINT .
Точки сохранения могут быть установлены только внутри блока транзакции. В одной транзакции можно определить несколько точек сохранения.
Примеры
Установление точки сохранения и затем отмена действия всех команд, выполненных после установленной точки:
Показанная транзакция вставит в таблицу значения 1 и 3, но не 2.
Этот пример показывает, как установить и затем уничтожить точку сохранения:
Данная транзакция вставит значения 3 и 4.
Этот пример показывает, как использовать точки сохранения с одним именем:
Данная транзакция сначала откатит строку 3, затем строку 2.
Совместимость
Стандарт SQL требует, чтобы точка сохранения уничтожалась автоматически, когда устанавливается другая точка сохранения с тем же именем. В PostgreSQL старая точка сохранения остаётся, хотя при откате или уничтожении будет выбираться только самая последняя. (После уничтожения последней точки командой RELEASE SAVEPOINT доступной для команд ROLLBACK TO SAVEPOINT и RELEASE SAVEPOINT становится следующая.) В остальном оператор SAVEPOINT полностью соответствует стандарту.
Understanding SQL Server Transaction Savepoints
SQL Server savepoints are used to roll back transactions to a specified point. In the other words, this lets you roll back part of the transaction instead of the entire transaction. So, if we are rolling back transactions to a specific savepoint, only statements after the savepoint and before the rollback command will be rolled back. In this tip, we will illustrate the usage of savepoints and the behavior of transactions with savepoints.
Solution
Savepoints can be useful when it is necessary to roll back part of a SQL Server transaction. This is usually the case when there is a low possibility of error in part of the transaction, and the prior validation of the operation’s accuracy is too costly. Savepoints can be also used in stored procedures to be able to successfully manage transactions in the nesting processes.
We are going to illustrate the usage of savepoints with examples and note some important facts about them. Before that, we will create a test environment:
Setting a SQL Server Transaction Savepoint
To set a savepoint within a transaction, the SAVE TRANSACTION <savepoint_name> command is used, SAVE TRAN <savepoint_name> syntax is also acceptable. The <savepoint_name> can be an identifier with a maximum of 32 characters. It can also be a user-defined variable with a char, varchar, nchar, nnvarchar data type and must contain a name conforming to the rules of identifiers.
In the following example, the setting and usage of a savepoint is illustrated:
As we can see, only the first insert statement committed, because the transaction was rolled back to the savepoint before the second insert:
In the next example, the transaction rolls back to the second savepoint whose name is set using a variable:
The result will be the same, if we use “ROLLBACK TRANSACTION @vSecondInsert” or “ROLLBACK TRANSACTION SecondInsert”.
Using SQL Server transaction savepoints with the same savepoint name
As mentioned above, a savepoint name can have a maximum of 32 characters. If a savepoint variable is used instead of a savepoint name and a string with more than 32 characters is assigned to it, only the first 32 characters will define the savepoint name and the remaining characters will be ignored.
It is likewise possible to have savepoints with the same names, but in the case of a rollback to the savepoint, the transaction will be rolled back to the latest SAVE TRANSACTION using that name. Here is an example.
In the example above, duplicate savepoint names are used and the transaction rolled back to the second savepoint, therefore the result will be the following:
It is worth mentioning that, in case of rolling back the transaction to the savepoint, locks acquired after the savepoint will be released (but escalations and conversions are exceptions).
Rolling Back to the SQL Server Savepoint and @@TRANCOUNT Variable
Finally, it is essential to understand the impact that rolling back to a savepoint will have on @@TRANCOUNT variable. The short answer – it will not change the @@TRANCOUNT, however, we will discuss the details below.
A ROLLBACK statement will set the value of @@TRANCOUNT variable back to 0 if it is used to roll back the whole transaction without referencing a savepoint. However, if a transaction is rolled back to the savepoint, the @@TRANCOUNT variable is not changed.
The example below illustrates this phenomenon:
From this example, it can be observed that before starting the transaction, the @@TRANCOUNT value is 0. It is incremented by 1 by starting each transaction and it is reduced by 1 in case of any COMMIT. In the case of a ROLLBACK to the savepoint, the value of the @@TRANCOUNT is not changed. However, a general ROLLBACK statement that rolls back all transaction will set the value of the @@TRANCOUNT to 0.
Here is the output from the above code.
Conclusion
All in all, savepoints can be useful in transactions, so understanding their meaning, features and behavior will be very helpful to you as you build applications, data import/export routines and data conversions.
WAL в PostgreSQL: 3. Контрольная точка
Мы уже познакомились с устройством буферного кеша — одного из основных объектов в разделяемой памяти, — и поняли, что для восстановления после сбоя, когда содержимое оперативной памяти пропадает, нужно вести журнал предзаписи.
Нерешенная проблема, на которой мы остановились в прошлый раз, состоит в том, что неизвестно, с какого момента можно начинать проигрывание журнальных записей при восстановлении. Начать с начала, как советовал Король из Алисы, не получится: невозможно хранить все журнальные записи от старта сервера — это потенциально и огромный объем, и такое же огромное время восстановления. Нам нужна такая постепенно продвигающаяся вперед точка, с которой мы можем начинать восстановление (и, соответственно, можем безопасно удалять все предшествующие журнальные записи). Это и есть контрольная точка, о которой сегодня пойдет речь.
Контрольная точка
Каким свойством должна обладать контрольная точка? Мы должны быть уверены, что все журнальные записи, начиная с контрольной точки, будут применяться к страницам, записанным на диск. Если бы это было не так, при восстановлении мы могли бы прочитать с диска слишком старую версию страницы и применить к ней журнальную запись, и тем самым безвозвратно повредили бы данные.
Как получить контрольную точку? Самый простой вариант — периодически приостанавливать работу системы и сбрасывать все грязные страницы буферного и других кешей на диск. (Заметим, что страницы только записываются, но не вытесняются из кеша.) Такие точки будет удовлетворять условию, но, конечно, никто не захочет работать с системой, постоянно замирающей на неопределенное, но весьма существенное время.
Поэтому на практике все несколько сложнее: контрольная точка из точки превращается в отрезок. Сперва мы начинаем контрольную точку. После этого, не прерывая работу и по возможности не создавая пиковых нагрузок, потихоньку сбрасываем грязные буферы на диск.
Когда все буферы, которые были грязными на момент начала контрольной точки, окажутся записанными, контрольная точка считается завершенной. Теперь (но не раньше) мы можем использовать момент начала в качестве той точки, с которой можно начинать восстановление. И журнальные записи вплоть до этого момента нам больше не нужны.
Выполнением контрольной точки занимается специальный фоновый процесс checkpointer.
Продолжительность записи грязных буферов определяется значением параметра checkpoint_completion_target. Он показывает, какую часть времени между двумя соседними контрольными точками будет происходить запись. Значение по умолчанию равно 0.5 (как на рисунках выше), то есть запись занимает половину времени между контрольными точками. Обычно значение увеличивают вплоть до 1.0 для большей равномерности.
Рассмотрим подробнее, что происходит при выполнении контрольной точки.
Сначала процесс контрольной точки сбрасывает на диск буферы статуса транзакций (XACT). Поскольку их немного (всего 128), они записываются сразу же.
Затем начинается основная работа — запись грязных страниц из буферного кеша. Как мы уже говорили, сбросить все страницы одномоментно невозможно, поскольку размер буферного кеша может быть значительным. Поэтому сперва все грязные на текущий момент страницы помечаются в буферном кеше в заголовках специальным флагом.
А затем процесс контрольной точки постепенно проходит по всем буферам и сбрасывает помеченные на диск. Напомним, что страницы не вытесняются из кеша, а только записываются на диск, поэтому не нужно обращать внимания ни на число обращений к буферу, ни на его закрепление.
Помеченные буферы могут также быть записаны и серверными процессами — смотря кто доберется до буфера первым. В любом случае при записи снимается установленный ранее флаг, так что (для целей контрольной точки) буфер будет записан только один раз.
Естественно, в ходе выполнения контрольной точки страницы продолжают изменяться в буферном кеше. Но новые грязные буферы не помечены флагом и процесс контрольной точки не должен их записывать.
В конце своей работы процесс создает журнальную запись об окончании контрольной точки. В этой записи содержится LSN момента начала работы контрольной точки. Поскольку контрольная точка ничего не записывает в журнал в начале своей работы, по этому LSN может находиться любая журнальная запись.
Кроме того, в файле $PGDATA/global/pg_control обновляется указание на последнюю пройденную контрольную точку. До того, как контрольная точка завершится, pg_control указывает на предыдущую контрольную точку.
Чтобы посмотреть на работу контрольной точки, создадим какую-нибудь таблицу — ее страницы попадут в буферный кеш и будут грязными:
Запомним текущую позицию в журнале:
Теперь выполним контрольную точку вручную и убедимся, что в кеше не осталось грязных страниц (как мы говорили, новые грязные страницы могут появляться, но в нашем случае никаких изменений в процессе выполнения контрольной точки не происходило):
Посмотрим, как контрольная точка отразилась в журнале:
Здесь мы видим две записи. Последняя из них — запись о прохождении контрольной точки (CHECKPOINT_ONLINE). LSN начала контрольной точки указан после слова redo, и эта позиция соответствует журнальной записи, которая в момент начала контрольной точки была последней.
Ту же информацию мы найдем и в управляющем файле:
Восстановление
Теперь мы готовы уточнить алгоритм восстановления, намеченный в прошлой статье.
Если в работе сервера произошел сбой, то при последующем запуске процесс startup обнаруживает это, посмотрев в файл pg_control и увидев статус, отличный от «shut down». В этом случае выполняется автоматическое восстановление.
Сначала процесс восстановления прочитает из того же pg_control позицию начала контрольной точки. (Для полноты картины заметим, что, если присутствует файл backup_label, то запись о контрольной точке читается из него — это нужно для восстановления из резервных копий, но это тема для отдельного цикла.)
Далее он будет читать журнал, начиная с найденной позиции, последовательно применяя журнальные записи к страницам (если в этом есть необходимость, как мы уже обсуждали в прошлый раз).
В заключение все нежурналируемые таблицы перезаписываются с помощью образов в init-файлах.
На этом процесс startup завершает работу, а процесс checkpointer тут же выполняет контрольную точку, чтобы зафиксировать на диске восстановленное состояние.
Можно сымитировать сбой, принудительно остановив сервер в режиме immediate.
(Ключ —skip-systemctl-redirect нужен здесь из-за того, что используется PostgreSQL, установленный в Ubuntu из пакета. Он управляется командой pg_ctlcluster, которая на самом деле вызывает systemctl, а она уже вызывает pg_ctl. Со всеми этими обертками название режима по пути теряется. А ключ —skip-systemctl-redirect позволяет обойтись без systemctl и сохранить важную информацию.)
Проверим состояние кластера:
При запуске PostgreSQL понимает, что произошел сбой и требуется восстановление.
Необходимость восстановления отмечается в журнале сообщений: database system was not properly shut down; automatic recovery in progress. Затем начинается проигрывание журнальных записей с позиции, отмеченной в «redo starts at» и продолжается до тех пор, пока удается получать следующие журнальные записи. На этом восстановление завершается в позиции «redo done at» и СУБД начинает работать с клиентами (database system is ready to accept connections).
А что происходит при нормальной остановке сервера? Чтобы сбросить грязные страницы на диск, PostgreSQL отключает всех клиентов и затем выполняет финальную контрольную точку.
Запомним текущую позицию в журнале:
Теперь аккуратно останавливаем сервер:
Проверим состояние кластера:
А в журнале обнаружим единственную запись о финальной контрольной точке (CHECKPOINT_SHUTDOWN):
(Страшным фатальным сообщением pg_waldump всего-навсего хочет сказать о том, что дочитал до конца журнала.)
Снова запустим экземпляр.
Фоновая запись
Как мы выяснили, контрольная точка — один из процессов, который записывает грязные страницы из буферного кеша на диск. Но не единственный.
Если обслуживающему процессу (backend) потребуется вытеснить страницу из буфера, а страница окажется грязной, ему придется самостоятельно записать ее на диск. Это нехорошая ситуация, приводящая к ожиданиям — гораздо лучше, когда запись происходит асинхронно, в фоновом режиме.
Поэтому в дополнение к процессу контрольной точки (checkpointer) существует также процесс фоновой записи (background writer, bgwriter или просто writer). Этот процесс использует тот же самый алгоритм поиска буферов, что и механизм вытеснения. Отличий по большому счету два.
- Используется не указатель на «следующую жертву», а свой собственный. Он может опережать указатель на «жертву», но никогда не отстает от него.
- При обходе буферов счетчик обращений не уменьшается.
- содержат измененные данные (грязные),
- не закреплены (pin count = 0),
- имеют нулевое число обращений (usage count = 0).
Настройка
Процесс контрольной точки обычно настраивается из следующих соображений.
Сначала надо определиться, какой объем журнальных файлов мы можем себе позволить сохранять между двумя последовательными контрольными точками (и какое время восстановления нас устраивает). Чем больше, тем лучше, но по понятным причинам это значение будет ограничено.
Далее мы можем посчитать, за какое время при обычной нагрузке будет генерироваться этот объем. Как это делать, мы уже рассматривали (надо запомнить позиции в журнале и вычесть одну из другой).
Это время и будет нашим обычным интервалом между контрольными точками. Записываем его в параметр checkpoint_timeout. Значение по умолчанию — 5 минут — явно слишком мало, обычно время увеличивают, скажем, до получаса. Повторюсь: чем реже можно позволить себе контрольные точки, тем лучше — это сокращает накладные расходы.
Однако возможно (и даже вероятно), что иногда нагрузка будет выше, чем обычная, и за указанное в параметре время будет сгенерирован слишком большой объем журнальных записей. В этом случае хотелось бы выполнять контрольную точку чаще. Для этого в параметре max_wal_size мы указываем общий допустимый объем журнальных файлов. Если фактический объем будет получаться больше, сервер инициирует внеплановую контрольную точку.
Сервер хранит журнальные файлы не только за прошедшую завершенную контрольную точку, но и за текущую еще не завершенную. Поэтому для получения общего объема надо умножать
объем между контрольными точками на (1 + checkpoint_completion_target). А до версии 11 — на (2 + checkpoint_completion_target), поскольку PostgreSQL хранил файлы и за позапрошлую контрольную точку тоже.
Таким образом, большая часть контрольных точек происходит по расписанию: раз в checkpoint_timeout единиц времени. Но при повышенной нагрузке контрольная точка вызывается чаще, при достижении объема max_wal_size.
Важно понимать, что значение max_wal_size может быть превышено:
- Параметр max_wal_size — только пожелание, но не жесткое ограничение. Может получиться и больше.
- Сервер не имеет права стереть журнальные файлы, еще не переданные через слоты репликации, и еще не записанные в архив при непрерывном архивировании. Если этот функционал используется, необходим постоянный мониторинг, потому что можно легко переполнить память сервера.
Процесс фоновой записи имеет смысл настраивать после того, как настроена контрольная точка. Совместно эти процессы должны успевать записывать грязные буферы до того, как они потребуются обслуживающим процессам.
Процесс фоновой записи работает циклами максимум по bgwriter_lru_maxpages страниц, засыпая между циклами на bgwriter_delay.
Количество страниц, которые будут записаны за один цикл работы, определяется по среднему количеству буферов, которые запрашивались обслуживающими процессами с прошлого запуска (при этом используется скользящее среднее, чтобы сгладить неравномерность между запусками, но при этом не зависеть от давней истории). Вычисленное количество буферов умножается на коэффициент bgwriter_lru_multiplier (но при любом раскладе не будет превышать bgwriter_lru_maxpages).
Значения по умолчанию: bgwriter_delay = 200ms (скорее всего слишком много, за 1/5 секунды много воды утечет), bgwriter_lru_maxpages = 100, bgwriter_lru_multiplier = 2.0 (пытаемся реагировать на спрос с опережением).
Если процесс совсем не обнаружил грязных буферов (то есть в системе ничего не происходит), он «впадает в спячку», из которой его выводит обращение серверного процесса за буфером. После этого процесс просыпается и опять работает обычным образом.
Мониторинг
Настройку контрольной точки и фоновой записи можно и нужно корректировать, получая обратную связь от мониторинга.
Параметр checkpoint_warning выводит предупреждение, если контрольные точки, вызванные переполнением размера журнальных файлов, выполняются слишком часто. Его значение по умолчанию — 30 секунд, и его надо привести в соответствие со значением checkpoint_timeout.
Параметр log_checkpoints (выключеный по умолчанию) позволяет получать в журнале сообщений сервера информацию о выполняемых контрольных точках. Включим его.
Теперь поменяем что-нибудь в данных и выполним контрольную точку.
В журнале сообщений мы увидим примерно такую информацию:
Тут видно, сколько буферов было записано, как изменился состав журнальных файлов после контрольной точки, сколько времени заняла контрольная точка и расстояние (в байтах) между соседними контрольными точками.
Но, наверное, самая полезная информация — это статистика работы процессов контрольной точки и фоновой записи в представлении pg_stat_bgwriter. Представление одно на двоих, потому что когда-то обе задачи выполнялись одним процессом; затем их функции разделили, а представление так и осталось.
Здесь, в числе прочего, мы видим количество выполненных контрольных точек:
- checkpoints_timed — по расписанию (по достижению checkpoint_timeout),
- checkpoints_req — по требованию (в том числе по достижению max_wal_size).
Важная информация о количестве записанных страниц:
- buffers_checkpoint — процессом контрольной точки,
- buffers_backend — обслуживающими процессами,
- buffers_clean — процессом фоновой записи.
Еще для настройки фоновой записи пригодится maxwritten_clean — это число показывает, сколько раз процесс фоновой записи прекращал работу из-за превышения bgwriter_lru_maxpages.