Управление транзакциями
Концепция транзакции лежит в основе реляционной парадигмы. Транзакция состоит из одной или нескольких DML команд и следующей командой или ROLLBACK или COMMIT. Возможно использовать команду SAVEPOINT для определённого управления внутри транзакции. Перед рассмотрением синтаксиса необходимо рассмотреть концепцию транзакций. Связанная с этой темой это тема согласованного чтения; это реализуется автоматически на уровне Oracle сервера, но некоторые программисты могут управлять им с помощью SELECT команд.
Механизм Oracle для обеспечения транзакционной целостности основан на сочетании сегментов отмены изменений и файла журнала логов: этот механизм бесспорно лучший из всех созданных на сегодняшний день и полностью удовлетворяет международным стандартам обработки данных. Производители других БД реализуют стандарт своими собственными другими способами. Вкратец, любая реляционная база данных должна удовлетворять тесту ACID: должны быть гарантированы атомарность (A – atomicity), согласованность (C – consistency), изолированность (I – isolation) и долговечность (D – durability).
Aтомарность
Принцип атомарности гласит что либо все части транзакции должны быть выполнены успешны либо ни одна из них. Например если бизнес-аналитик утвердил правило что при смене зарплаты сотрудника обязательно изменяется уровень сотрудника то ваша атомарная транзакция будет сосять из двух частей. БД должна гарантировать что будут применены либо оба изменения, либо ни одного. Если только одно изменения будет успешно то у вас появится сотрудник чья зарплата несовместима с его уровнем: повреждение данных в терминах бизнеса. Если что-нибудь (вообще что-нибудь) пошло не так до подтверждения транзакции, БД должна гарантировать что вся работа совершённая до этого момента от начала транзакции будет отменена: это должно работать автоматически. Несмотря на то что атомарность транзакции звучит как что-то маленькое – транзакции могут быть долгими и очень важными. Рассмотрим другой пример, в бухгалтерской книге не может быть данных на пол-месяца Августа и пол-месяца Сентября: закрытие месяца с точки зрения бизнеса одна атомарная транзакция, которая может обрабатывать миллионы строк и тысячи таблицы и работать несколько часов (или отменяться если что-то пошло не так). Отмена транзакции может быть ручной (выполнив команду ROLLBACK) но она должна быть автоматической и неотменяемой в случае ошибки.
Согласованность
Принцип согласованности данных гласит что результат запроса должен быть согласован с состояним базы данных на момент старта работы запроса. Преставим простой запрос которые считает среднее значение столбца в таблице. Если таблица большая, это займёт достаточно долгое время для прохода по всем строкам таблицы. Если другие пользователи в это время обновляют данные пока запрос выполняется, должен ли запрос брать новые значения или старые? Должен ли результат запроса учитывать строки которые были добавлены или не учитывать строки которые были удалены? Принцип согласованности требует чтобы БД гарантировала что любые изменения после старта запроса не были видны для этого запроса; запрос должен вернуть среднее значение столбца на момент когда запрос был запущен, вне зависимости от того как долго длился запрос и какие изменения были над данными. Оракл гарантирует что если запрос выполнен успешно – результат будет согласованным. Как бы там ни было, если администратор базы данных не настроил базу данных соотвествующим образом, запрос может не выполнится: возникнет знаменитая ошибка “ORA-1555 snapshot too old”. Раньше было очень сложно решить такие ошибки, но в последних версиях администратор легко может решать эти ситуации.
Изолированность
Принцип изолированности гласит что незаконченная (неподтверждённая транзакция) должна быть невидима для остального мира. Пока транзакция в процессе только сессия которая выполняет эту транзакцию видит зименения. Все остальные сессии должны видеть неизменённые данные. Почему так? Во первых, транзакция может целиком не выполниться до конца (помним про принцип атомарности и согласованности) и поэтому никто не должен видеть изменения которые могут быть отменены. Во вторых во время действия транзакции данные (в терминах бизнеса) бессвязные: для нашего примера обновления зарплаты будет существовать промежуток времени когда зарплата изменена, а уровень ещё нет. Изолированность транзакций требудет чтобы база данных прятала текущие транзакции от других пользователей: они будут видеть данные до изменений пока транзакция выполняется, а затем сразу будут видеть все изменения как согласованный набор данных. Oracle гарантирует изолированность транзакций: нет способа для сессии (отличной от той что делает изменения) увидеть неподтверждённые данные. Чтение неподтверждённых данных (известное как грязное чтение dirty read) не позволяется Oracle (несмотря на то что некоторые другие БД позволяют).
Долговечность
Принцип долговечности указывает на то, что если транзакция успешно завершена, то должно быть невозможно потерять эти данные. Во время работы транзакции принцип изолированности требует чтобы никто кроме сессии выполняющей изменения не видел их. Но как только транзакция успешно завершила работу, изменения должны стать доступны для всех и база данных должна гарантировать что они не будут потеряны. Oracle выполняет это требование путём записывания всех векторов изменений в файлы логов перед тем как изменения подтверждены. Применив этот логи изменений к резервным копиям, всегда возможно повторить любые изменения которые были выполнены в момент остановки или повреждения базы данных. Конечно данные могут быть потеряны из-за ошибок пользователей, таких как выполнение неверных DML запросов или удаление таблиц. Но с точки зрения Oracle и администратора базы данных, такие события тоже транзакции: согласно принципу долговечности их нельзя отменить.
Выполнение SQL запросов
Весь язык SQL состоит из около дюжины команд. Сейчас нас интересуют команды: SELECT, INSERT, UPDATE и DELETE.
Выполнение команды SELECT
Команда SELECT получает данные. Выполнение команды SELECT это процесс состоящий из нескольких этапов: серверный процесс выполняющий запрос проверит существует ли необходимые блоки данных в памяти, в буфере кэша. Если они там, то выполнение может продолжаться, иначе серверный процесс должен найти данные на диске и скопировать их в буфер кэша.
EXAM TIP
Always remember that server processes read blocks from datafiles into the database buffer cache, DBWn writes blocks from the database buffer cache to the datafiles.
Когда блоки с нужными для выполнения запроса данными находятся в кэш буфере любые дополнительные процессы (такие как сортировка и аггрегирование) продолжаются в PGA сессии. Когда выполнение завершено, результат возвращается пользовательскому процессу.
Как это связано с тестом ACID? Для согласованности, если запрос обнаружит что блок данных изменился с момента старта запроса, серверный процесс найдёт сегмента отката (отмены изменений или сегмент undo) соответствующий этому изменению, найдёт старую версию данных и (для текущего запроса) отменит изменение. Таким образом изменения которые произошли после начала запроса будут не видны. Похожим образом гарантируется изолированность транзакций, несмотря на то что изолированность основана и на подтверждённых изменениях. Честно говоря, если данные необходимые для отмены изменений не существуют больше в сегменте отката – этот механизм не сработает. Отсюда и следует ошибка “snapshot too old”.
На рисунке 8-4 показан путь обработки запроса SELECT
Шаг 1 это передача пользовательского запроса от пользовательского процесса к серверному. Серверный процесс просматривает буфер кэш на наличие нужных блоков и если они в буфере то переходит к шагу4. Если нет то шаг 2 находит блоки в файлах данных и шаг 3 копирует данные в буфер. Шаг 4 передает данные сервеному процессу где может быть дополнительная обработка перед тем как шаг 5 вернёт результат запроса пользовательскому процессу.
Выполнение команды UPDATE
Для любой команды DML необходимо работать с блоками данных и блоками отката (undo blocks), а также создавать лог изменений (redo): A,C и I принципы теста ACIDS требуют создания данных отката; D требует создание данных повтора изменений (redo).
Exam tip
Undo не противоположна redo! Redo защищает все изменения блоков, вне зависимости это изменения блока таблицы, индекса или сегмента отката. Для redo — undo сегмент такой же сегмент как таблцы и все изменения должны быть долговечны (durable)
Первый шаг при выполнении DML команды такой же как и при выполнении команды SELECT: необходимые блоки должны быть найдены в кэф буфере или скопированы с файлов данных в буфер. Единственное отличие это то что дополнительно требуется пустой (или устаревший – expired) блок отката. Затем выполнение становится сложнее чем при команде SELECT.
Вначале блокировки должны быть указаны для все строк и соотвествующих индексов которые будут задействованы в процессе.
Потом создаются данные redo: серверный процесс записывае в логи буфера вектора изменений которые будут применены к данным. Redo данные создаются и для изменений блока данных и для изменений блока отката: если столбец в строке будет обновлен то rowid и новое значение записывается в буфер лога (изменение которое будет применено к блоку таблицы), а также старое значение столбца (изменение для блока отката). Если столбец это часть ключа индекса – то изменения в индексе тоже будут записаны в буфер лога, вместе с изменениями которые будут сделаны в блоке отката для защиты изменений индекса.
После того как все redo данные созданы, обновляются данные в буфер кэше: блок данных обновляется на новую версию с измененным столбцом, а старая версия записывается в блок отката. С этого момента до подтверждения транзакции, все запросы от других сессий обращающиеся к этой строке будут перенаправлены на блок отката. Только сессия которая делает UPDATE будет видеть актуальную версию строки а блоке таблицы. Такой же принцип применяется для всех связанных индексов.
Выполнение команд INSERT и DELETE
Концептуально INSERT и DELETE управляются в той же манере как и UPDATE. Вначале происходит поиск нужных блоков в буфере и если их нет то они копируются в память.
Redo создается точно так же: все вектора изменений которые будут применены к данным и блокам отката вначале записываются в буфер лога. Для команды INSERT вектор изменений блока таблицы (и возможно блоков индекса) это байты которые составляют новую строку (и возможно новый ключ индекса). Вектор для блока отката это rowid новой строки. Для команды DELETE вектор для блока отката это вся строка.
Ключевым отличием между командами INSERT и UPDATE является количество данных для отката. Когда строка добавляется единственными данными для отката будет запись rowid в блок отката, потому что для отмены команды INSERT единственная информация нужная Oracle это rowid строки и может быть создана команда
delete from table_name where rowid=rowd_id_of_new_row;
Выполнение этой команды отменит изменение.
Для команды DELETE вся строка (которая может быть несколько килобайт) должна быть записана в блок undo, и тогда удаление может быть отменено при необходимости путём генерации запроса который заново добавил полностью строку в таблицу.
Начало и конец транзакции
Сессия начинает транзакция в момент когда она выполняет любую DML команду. Транзакция продолжается сколько угодно следующих DML команд пока сессия не выполнит команду ROLLBACK или COMMIT. Только подтвеждённые изменения станут гарантированными и будут доступны для других сессий. Невозможно начать транзакцию внутри транзакции. Стандарт SQL не разрешает пользователям начать транзакцию, а затем начать новую перед завершение первой. Это можно сделать используя PL/SQL (язык Oracle третьего поколеняи), но не стандартным SQL.
Командами управления транзакциями являются команды COMMIT, ROLLBACK и SAVEPOINT. Также могут возникнуть другие обстоятельства помимо явного вызовая команды COMMIT или ROLLBACK которые немедленно прекращают транзакцию
- Выполнение DDL или DCL команды
- Завершение польховательского процесса (к примеру пользователь вышел из программы SQL *Plus или SQL Developer)
- Клиентская сессия «умерла»
- Проблемы в системе
Если пользователь выполняет DDL команду (CREATE, ALTER или DROP) иди DCL команду (GRANT или REVOKE) то активная транзакция (если она сущесвтует) будет подтверждена. Так происходит потому что команды DDL и DCL сами являются транзакциями. Так как в SQL невозможно создать вложенные транзакции, если у пользователя уже выполнялась какая либо транзакция, все команды пользователя будут подтверждены вместе с командой DDL или DCL.
Если вы начали транзакцию выполнив DML запроса, а затем закрыли программу без явного указания COMMIT или ROLLBACK до выхода, транзакция будет прекращена – но прекращена с подтверждением или отменой целиком зависит от программы. У разных программ может быть разное поведение в зависимости от того как вы закончили работу в программе. Например в Windows обычно можно выйти из программы выбрав пункты меню File – Exit или нажав на крестик в правом верхнем углу. Программист мог обработать по разному эти способы завершения и в первом случае указать COMMIT, а во втором ROLLBACK. В любом случае это будет контролируемый выход.
Если клиентская сессия отказывает по какой-либо причине – база данных всагда отменит транзакцию. Такие отказы могут быть по разным причинам: пользовательский процесс мог быть «убит» диспетчером, проблемы с сетью или поломка пользовательской машины. В любом случае не было явно указана команда COMMIT или ROLLBACK и БД нужно решить что случилось. В таком случае сессия «убивается» и активная транзакция отменяется. И точно так-же база данных ведёт в себя в случае проблем на стороне сервера. Если база данных была закрыта аварийно то при следующем старте все транзакции которые были начаты но явно не завершены будут отменены.
Управление транзакциями: COMMIT, ROLLBACK, SAVEPOINT и SELECT FOR UPDATE
Oracle начинает транзакцию в момент запуска первой DML команды. Транзакция длится до вызова команды ROLLBACK или COMMIT. Команда SAVEPOINT не является частью SQL стандарта и в реальности является легким способом для программиста чтобы отменить изменения частично в обратном порядке.
COMMIT
Выполнение команды COMMIT это тот момент когда многие люди (и даже некоторые администраторы БД) показывают непонимание архитектуры Oracle. Когда вы выполняете COMMIT всё что происходит физически это LGWR записывает буфер логов на диск. DBWn не делает абсолютно ничего. Это одно из самых важных свойств Oracle для достижения высокой производительность БД.
Exam tip
Что делает DBWn в момент выполнения команды COMMIT? Ответ: абсолютно ничего
Чтобы сделать транзакцию долговечной всё что нужно это записать изменения которые были сделаны в процессе транзакции на диск: нет необходимости в актуальных данных на диске. Если изменения записаны, в виде многих копий логов изменений на диске, то даже в случае повреждения базы все транзакции могут быт повторены восстановив резеврную копию данных до ошибки и применив изменения из логов. На данный момент надо понимать тот факт что COMMIT всего лишь очищает буфер лога на диск и помечает транзакцию как выполненную. Вот почему транзакции в которой были задействованы миллионы обновлений в тысячах файлов в течение нескольких часов могут подветрждаться за долю секунды. Так как LGWR записывает логи практически в режиме реального времени, то виртуально все изменения транзакции уже записаны на диск. Когда вы выполняете COMMIT, LGWR тут же записывает лог на диск: ваша сессия будет ожидать пока запись не закончится. Время задержки будет равно времени которое занимает запись последних данных из буфера логов, что обычно занимает несколько миллисекунд. Потом ваша сессия может продолжать работу и все остальные сессии не будут перенаправлятьяс на данные в сегменте отката при обращении к обновлённым данным, если только принцип согласованности не требует этого. Вектора изменений, записываемыe в лог повтора изменений, это все изменения: применяемых и к блокам данных (таблиц и индексов) и к блокам отката.
EXAM TIP
Лог redo включает все изменения: применяемые к сегментам данным и к сегментам undo для потдвержденных и неподтвержденных транзакций
Самое непонятное это то что redo записывается LGWR в файлы будет содержать и подтвержденные и неподтвержденные транзакции. Даже больше, в любой момент DBWn может записать а может и не записать измененные блоки сегментов данных или сегментов отката в файлы данных для подтверждённых и неподтверждённых транзакций. То есть ваша БД на диске противоречива: файлы данных могут хранить данные неподтверждённых транзакций и в них могут отсутствовать подтверждённые изменения. Но в любой момент, в случае проблемы, в файле логов на диске достаточно информации чтобы повторить подтверждённые транзакции которые пропущены в файлах данных (используя изменения для блоков данных) и восстановить сегменты отката (используя изменения блоков отката) нужные для отмены всех неподтверждённых транзакций которые записаны в файлы данных.
Exam tip
Лбая DDL команда, а также GRANT или REVOKE подтвердят текущую транзакцию
ROLLBACK
Пока транзакция в процессе, Oracle хранит образ данных до начала транзакции. Этот образ используется другими сессиями которые обращаются к данным участвующим в транзакции. Также он используется для отменты транзакции автоматически если что-то пойдёт не так или сессия отменит транзакцию.
Синтаксис для отмены транзакции
ROLLBACK [TO SAVEPOINT savepoint];
Состояние данных перед отменой транзакции содержит изменения, но информация нужная для отмены этих изменений доступна. Эта информация используется другими сессиями для выполнения принципа изолированности. ROLLBACK транзакции отменит все изменения восстановив образ данных до начала транзакции: все добавленные строки будут удалены, все удалённые строки восстановлены, все строки в которых менялись значения вернутся к исходному состоянию. Другие сессии даже не будут знать что что-то происходило, они никогда не видели изменений. А сессия которая инициировала транзакцию после отмены будет видеть данные такими какими они были до начала транзакции.
SAVEPOINT
Точка сохранения позволяет программистам устанавливать флаг в транзакции которые затем можно использовать для контроля эффекта отмены транзакции. Вместо отмены всей транзакции и её завершения, становится возможным отменить изменения сделанные после конкретного флага но оставить изменения сделанные до этого флага. Действие транзакции в этот момент продолжается: транзакция не подтверждена, всё ещё можно отменить транзакцию целиком и изменения не видны для других сессий.
Синтаксис команды
SAVEPOINT savepoint
Такая команда создаёт точку в транзакции которая может быть использована в дальнейшем в команде ROLLBACK. На следующей таблице видно количество строк в таблице видимое разным сессиям во время работы транзакции в разные моменты времени. Используемая таблица назвается TAB и у неё один столбец
В примере с выполнены две транзакции: первая завершена командой COMMIT а вторая ROLLBACK. Видно что использование точек сохранения влияет только внутри транзакции для той сессии которая инициировала транзакцию: вторая сессия не видит ничего что не подтверждено.
SELECT FOR UPDATE
Последняя команда для управления транзакциями это SELECT FOR UPDATE. Oracle, по умолчанию, предоставляет наивысший уровень параллелизма: чтение данных не блокирует запись, запись не блокирует изменение. Другими словами нет проблемы если одна сессия пытается считать данные которые другая сессия изменяет и наоборот. Но иногда вам может понадобиться изменить такое поведение и предотвратить возможность изменения данных которые считаны сессией.
Типичное поведение приложений это выборка данных с помощью команды SELECT, отображение данных пользователю для просмотра и возможность изменения этих данных. Так как Oracle поддерживает параллельную работу пользователей то ничто не мешает другому пользователю получить те же данные. Если обе сессии попробует сделать какие-либо изменения, то могут возникнуть странные ситуации. Следующий пример показывает такую ситуацию
Вот что увидит первый пользователь (прелположим что используется SQL *Plus)
Такой результат немного смущает пользователя. Чтобы решить эту проблему можно заблокировать строки которые вернул запрос
select * from regions for update;
Директива FOR UPDATE приведёт к блокировке таблиц которые возвращает запрос. Другие сессии не смогут изменить данные и таким образом последующие изменения будут успешны: другие сессии не смогут изменить данные. То есть у одной сессии будет согласованное чтение данных, но ценой за это будет то, что другие сессии «зависнут» если они попытаются изменить данные которые заблокированы (другие сессии могут читать эти данные).
Блокировка строк вызванная командой FOR UPDATE будет длиться пока сессия не выполнит команду COMMIT или ROLLBACK. Команду завершения транзакции необходимо выполнить даже если вы не запускали каких-либо DML команд.
Так называемый «авто-коммит»
Чтоб завершить обзор как обрабатывается управление транзакциями надо рассеять все сомнения о так называемом “auto-commit” или неявном подтверждении (implicit commit). Вы будете часто слышать что Oracle автоматически подтвердит. Первый случай это предыдущий случай когда вы выполнили команду DDL, другая ситуация когда пользователь вышел из программы такой как SQL *Plus.
На самом деле всё очень просто. Не существует такого понятия как авто-коммит. Когда вы выполняете DDL команду, то работает обычный COMMIT которые встроен в команду DDL. Но что проиходит когда вы выходите из программы? Если вы используете SQL Plus в Windows и выполняете команду DML а затем команду EXIT (EXIT это команду SQL *Plus а не SQL), ваша транзакция будет подтверждена. Это потому что разработчики SQL *Plus встроили вызов команды COMMIT в команду EXIT. Если же вы нажмёте на красный крест в правом верхнем углу – то произойдёт вызов команды ROLLBACK. Так происходит потому что опять же разработчики SQL *Plus запрограммировали такое поведение программы. В другой операционной системе поведение программы SQL Plus может быть другим, единственный способ узнать это – это протестировать программу (или прочитать исходный код что в случае программы SQL Plus невозможно если вы не работаете в Oracle надо этой программой).
В SQL *Plus есть команда SET AUTOCOMMIT ON. Вызов этой команды указывает SQL *Plus на то как обрабатывать пользовательские запросы: SQL *Plus добавит вызов команды COMMIT после любой DML команды. Таким образом все запросы будут подтверждаться как только они выполнены. Но опять же всё это происходит полностью на стороне пользовательского процесса; у базы данных нет никакого авто-коммита, и все долго-работающие изменения будут изолированы от других сессий пока запрос не выполнится успешно. Даже в таком случае если вы запустите долгий запрос на выполнение, потом к примеру завершите пользовательский процесс через диспетчер задач то PMON обнаружит сессию «призрак» и отменит транзакцию.