Что такое транзакция в sql server

Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.

Тема 15: Транзакции в SQL на примере базы данных SQLite: свойства ACID и уровни изоляции транзакций в SQLite3

  • 25.07.2016
  • SQLite библиотека, Базы данных
  • Один комментарий

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Сегодня мы поговорим про транзакции в реляционных базах данных и языке запросов SQL. Транзакция — одна из самых сложных функций СУБД и языка SQL с точки зрения реализации самой СУБД. Транзакции не только обеспечивают целостность данных в базах данных, но и реализуют функции, позволяющие работать нам с файловой системой компьютера при помощи СУБД. Вообще реализация механизмов транзакции зависит от СУБД, в этой записи мы познакомимся в общих чертах с тем, как реализованых транзакции в базах данных под управлением SQLite.

Транзакции в SQL на примере базы данных SQLite: свойства ACID и уровни изоляции транзакций в SQLite3.

Но начнем мы эту запись с того, что дадим ответ на вопрос: «что такое транзакция в SQL?» . Затем мы поговорим о свойствах транзакций в реляционных базах данных, сразу скажем, что свойства транзакций одновременно являются еще и требованиями, их всего четыре и называется это всё дело ACID. Также мы рассмотрим проблемы, которые могут возникать при выполнении нескольких транзакций параллельно и как с этими проблемами бороться при помощи блокировки таблиц и изоляции транзакций. В завершении данной записи мы рассмотрим SQL синтаксис транзакций, реализованный в библиотеки SQLite3 и увидим, что SQLite позволяет давать имена транзакциям и создавать вложенные транзакции.

Что такое транзакция в контексте базы данных и языка SQL?

Давайте разберемся с тем, что такое транзакция в контексте языка SQL и в реляционных базах данных с технической точки зрения. Многие утверждают, что транзакция в базе данных – это есть объекта базы данных, как, например, VIEW или триггер. Другие говорят: нет, транзакция в SQL – это процесс. На мой взгляд правы и те, и другие.

Если смотреть на транзакцию «глазами СУБД», то это объект базы данных, который живет ровно столько, сколько длится тот или иной процесс. Давайте дадим определение термину транзакция. Транзакция – это неделимый процесс, который включает в себя группу последовательных операций (этих операций может быть очень много) над данными в базе данных. Операции в транзакции могут быть либо выполнены целиком и полностью все, либо не выполнены вообще.

При этом соблюдается целостность данных в базе данных, а транзакция выполняется вне зависимости и параллельно от других транзакций. В качестве жизненного примера транзакций в базе данных можно описать следующую ситуацию. Например, вы оплачиваете покупку в магазине, это неделимое действие, которое условно можно разбить на несколько операций:

  1. Кассир пробивает товар.
  2. Вы даете ему карточку.
  3. Кассир вставляет карточку в картоприемник.
  4. Вы вводите пин-код.
  5. Происходит перечисление денег с вашего счета на счет магазина.
  6. Кассир отдает вам карточку с чеком.
  7. Вы забираете товар и уходите.

Довольно простое и незамысловатое действие, которое мы совершаем каждый день. И согласитесь, у этого действия может быть два окончания: либо вы оплатили товар и забрали его, либо вы его не оплатили, и вам его не отдали. Забрать пол телевизора или половину бутылки с газировкой вы не сможете. Это типичный пример транзакций в SQL. Но, как и в любом процессе, в процессе оплаты товара могут возникать различные ошибки. Например, вы ввели пин-код и выключился свет, ничего страшного не произойдет: даже если запрос на перечисление ушел, деньги с вас не спишут, так как транзакция не была завершена успешно и, следовательно, произойдет откат всех операций, соответственно, вы останетесь при деньгах, но товар забрать не сможете.

А теперь представьте, что операция по переводу денег с одного счета на другой делалась бы не единой и неделимой транзакцией, а отдельными операциями: сколько бы тогда возникало ошибок, нарушений целостности данных и конфликтов. Но это не так страшно, куда важнее испорченные нервы покупателя и лиц его обслуживающих.

Итак, транзакция в базе данных – это неделимый процесс, состоящий из нескольких операций, которые могут быть выполнены все вместе целиком и успешно, либо произойдет откат этих операций. Транзакции повышают надежность и быстродействие операций в базе данных. С надежностью всё понятно, но за счет чего происходит увеличение быстродействия.

Мы знаем, что СУБД создает нам абстракцию. На самом деле все данные в базе данных – это обычный файл, лежащий на жестком диске, а СУБД представляет нам этот файл в виде базы данных, таблиц и других не естественных для файловой системы компьютера объектов. Поэтому, когда мы выполняем ту или иную операцию в базе данных, то СУБД, можно сказать, создает соединение с файлом на жестком диске, делает какие-то свои внутренние операции, затем выполняет SQL запрос и закрывает соединение с файлом. И, например, в SQLite каждый запрос к базе данных – это маленькая транзакция, состоящая из одной операции (за исключением команды SELECT).

Но, когда мы явно говорим SQLite о том, что хотим запустить транзакцию, она устанавливает соединение с файлом и не закрывает его до тех пор, пока транзакция не будет выполнена. Таким образом мы можем существенно экономить ресурсы, особенно, когда выполняем очень много однотипных операций, например, когда вставляем несколько сотен/тысяч строк в таблицу базы данных.

Если такую операцию мы будем выполнять без транзакции, то при каждом выполнении команды INSERT, SQLite будет открывать соединение и закрывать его, и эти две операции будут происходить столько раз, сколько вы строк будете вставлять. Если же мы сначала создадим транзакцию, то библиотека SQLite создаст только одно соединение и завершит его, когда транзакция будет завершена.

Четыре свойства транзакции в реляционных базах данных: ACID

У транзакций в реляционных базах данных есть четыре свойства. Можно еще сказать, что это не четыре свойства, а четыре требования к транзакциям в базах данных. Этих четыре требования получили название ACID. Итак, ACID – это четыре свойства транзакции. Каждая буква аббревиатуры ACID – это первая буква того или иного требования: Atomicity, Consistency, Isolation, Durability. В русском языке свойства транзакции имеют аналогичную аббревиатуру: АСИД, это можно расшифровать как: атомарность, согласованность, изолированность и долговечность.

Давайте перечислим четыре свойства транзакции ACID и посмотрим, какие требования предъявляются к транзакциям в базах данных:

  1. Atomicity или свойство атомарности транзакции гарантирует, то что ни одна транзакция в базе данных не будет выполнена частично. Вы не сможете честным путем забрать товар из магазина, отдав треть стоимости товара, а честный продавец не возьмет с вас денег за испорченный или разбитый товар. Поэтому внутри транзакции выполняются, либо все операции, и она успешно завершается, либо, если происходит сбой на одной из операций, происходит откат всех ранее выполненных операций. Таким образом обеспечивается целостность данных и поддерживается их согласованность.
  2. Consistency или требование согласованности базы данных до и после выполнения транзакции. Перед тем, как начинается транзакция, база данных находится в согласованном состояние (в спокойном состояние, чуть ниже это объясню на примере). Когда транзакция завершается, база данных должна находиться так же в согласованном состоянии. Например, вы оплатили покупку, вам пришло уведомление, что списали деньги, но продавец не видит поступивших на счет денег, и не отдает вам товар. Естественно, вы выйдете в этом случае из своего согласованного состояния и будете не очень спокойным (база данных в этом случае будет находиться так же в не согласованном состоянии: деньги с одного счета были списаны, а на другой не зачислены). Обратите внимание: что в процессе выполнения транзакции (когда выполняются операции) база данных может находиться в несогласованном состоянии, но как только транзакция завершена данное состояние недопустимо.
  3. Isolation или свойство изолированности транзакций. Это одно из самых дорогих требований к транзакциям в базе данных. Оно гарантирует то, что параллельно выполняемые транзакции не будут мешать друг другу. Из-за того, что свойство изолированности транзакций забирает большое количество ресурсов, в реальных СУБД созданы различные уровни изоляции транзакций, чем выше этот уровень, тем более изолированы транзакции.
  4. Durability или свойство долговечности транзакции. Перевод durability, как долговечность, в данном случае не совсем точно характеризует требование к транзакции, более точным будет свойство устойчивости транзакции. Требование устойчивости транзакции или долговечности гарантирует то, что база данных останется в согласованном состоянии вне зависимости от проблем на других уровнях модели OSI. Например, вы оплачиваете покупку, а в этот момент в здании выключается свет или происходит обрыв линии связи. База данных должна остаться согласованной в этом случае, то есть деньги должны остаться на вашем счету, но покупку вы забрать не сможете. Если же транзакция была выполнена успешно до возникновения технических проблем, то все устройства, работающие с базой данных, получат данные в согласованном состоянии, как только проблема будет устранена.

Обращу ваше внимание на то, что в каждой реализации СУБД свойства ACID реализуются по-разному. Но результат работы свойств ACID всегда один: база данных всегда находится в согласованном состоянии до и после выполнения транзакции. Если требования ACID выполняются, то транзакции могут работать параллельно, не мешая друг другу. Благодаря требованиям ACID транзакции не выполняются частично и, наконец, если транзакция подтверждена, то она подтверждена и никакие технические сбои этому не помешают.

Проблемы при выполнении параллельных транзакций

Ранее мы упоминали о том, что транзакции должны быть изолированы, а также мы сказали, что полная изоляция транзакции – очень дорогая операция, поэтому было принято разделение по степени изолированности. Чем выше уровень изолированности транзакции, тем выше точность и согласованность данных, но минус в том, что может уменьшится число параллельных транзакций, которые выполняются СУБД.

Также при выполнении параллельных транзакций в базе данных могут возникать некоторые проблемы. Ниже вы найдете список проблем, которые могут возникнуть при параллельных транзакциях:

  1. Потерянное обновление (lost update). Если две или более, запущенных параллельно транзакции пытаются модифицировать одни и те же данные, то все вносимые изменения, кроме первой транзакции, будут потеряны.
  2. Неповторяющееся чтение (non-repeatable read). При повторном чтении в рамках одной транзакции ранее прочитанные данные оказываются изменёнными.
  3. Грязное чтение (dirty read). Данная проблема возникает в том случае, когда вы делаете выборку данных, которые были изменены транзакцией, но в дальнейшем произойдет откат транзакции и эти изменения не подтвердятся.
  4. Фантомное чтение (phantom reads). Представим, что у нас запущено две транзакции, первая лишь читает данные из базы данных, вторая манипулирует данными, например: добавляет строки, удаляет данные или их модифицирует. Допустим, что в первой транзакции условия выборки данных всегда одинаковые, но результаты могут оказаться разными, так как вторая транзакция изменяет данные в таблицах.

Справиться с подобными проблемами при выполнении параллельных транзакций помогают блокировки и изолированность транзакций.

Изолированность транзакций в базе данных. Блокировка SQL таблиц в базах данных

В информатике есть такое понятие как уровни изолированности транзакций, которые помогаю справляться с вышеперечисленными проблемами. Стандарт SQL-92 выделяет четыре уровня изолированности транзакций в реляционных базах данных. Чем выше уровень изолированности, тем меньше проблем может возникнут, а это значит, тем лучше происходит обеспечение целостности данных.

Заметим, что каждый последующий уровень изолированности включает требования предыдущего уровня и добавляет некоторые свои требования (нечто похожее мы наблюдали, когда говорили про нормальные формы в базе данных: третья нормальная форма включает в себя требования второй нормальной формы и первой нормальной формы, плюс накладывает свои требования на отношение).

  1. Read uncommitted (чтение незафиксированных данных). Уровень изолированности Read uncommitted или чтение незафиксированных данных – это самый низший уровень изолированности транзакций. Данный уровень справляется с проблемами потерянного обновления. Обычно этот уровень реализуется путем блокировки таблиц для других транзакций. Например, выполняется первая транзакция и, пока она выполняется, ни одна другая транзакция не может изменять данные в этой таблице, а может их только читать. При этом, как только завершится первая транзакция, таблица станет доступна для второй транзакции, которая может изменять данные. Таким образом при уровне изоляции Read uncommitted транзакции будут выполняться последовательно и ни одно изменение потеряно не будет. Но в то же самое время любая другая транзакция может выполнять чтение данных из этой таблицы (даже тех данных, которые еще не были подтверждены командой COMMIT).
  2. Read committed (чтение фиксированных данных). Данный уровень изолированности транзакций решает проблему грязного чтения данных. Но уровень изолированности Read committed или чтение фиксированных данных может быть реализован двумя способами.
  1. Первый способ заключается в том, что читающая транзакция блокирует считываемые данные и при этом транзакция, выполняющая какие-то изменения не может их совершить до тех пор, пока читающая транзакция не будет завершена. Если же пишущая транзакция началась раньше, то она блокирует данные для читающих транзакций до тех пор, пока изменения не будут подтверждены. Этот способ получил название блокирование или блокирование читаемых и изменяемых данных.
  2. Второй подход или второй способ изоляции основан на версионности данных. СУБД создает новую версию строки для транзакции при каждом изменении данных строки. С этой новой версией продолжает работать та транзакция, которая ее создала, но любая другая транзакция видит строку в том, виде, в котором она была зафиксирована. Этот способ гораздо быстрее первого, но требует гораздо большего объема оперативной памяти, так как «новые версии строк» хранятся в оперативной памяти до тех пор, пока они не будут подтверждены.

Обратите внимание: четыре уровня изолированности транзакций описаны в стандарте SQL-92, каждая СУБД поддерживает разное количество уровней изолированности транзакций (какие-то имеют большее количество уровней, какие-то меньшее), а также в каждой СУБД реализован свой подход к изоляции транзакций. Ниже мы несколько более подробно поговорим о том, как реализованы транзакции в базах данных под управлением библиотеки SQLite.

Синтаксис транзакций в базах данных под управлением SQLite

Мы уже подробно разбирали синтаксис транзакций в базах данных под управлением SQLite3, давайте немного повторим сказанное ранее. Отмети также, что любая SQL команда, за исключением SQL запросов SELECT, будет автоматически начинать транзакцию и завершать ее после выполнения операции.

Начать транзакцию в SQLite

Начать транзакцию в базе данных под управлением SQLite позволяет команда BEGIN TRANSACTION (ключевое слово TRANSACTION необязательное и его можно не присать). Ниже вы можете увидеть общий синтаксис команды BEGIN TRANSACTION.

Начать транзакцию в SQLite

Транзакции в SQLite3 имеют три режима блокировки: DEFERRED, IMMEDIATE, EXCLUSIVE. Также стоит заметить, выполнение свойств ACID в SQLite достигается не только путем блокировок, но еще и при помощи журнализации изменений. Давайте посмотрим, как происходит блокировка данных в этих трех режимах:

  1. DEFERRED – данный режим блокировки является режимом по умолчанию в SQLite. В режиме DEFERRED SQLite начинает блокировать таблицы только после того, как будет начато выполнение какой-либо команды, при этом другие транзакции могут читать данные из таблицы, но не могут их изменять.
  2. IMMEDIATE – в данном режим происходит блокировка базы данных, как только будет выполнена команда BEGIN. При это режим IMMEDIATE в SQLIte допускает, что другие транзакции могут читать данные из базы данных, но не записывать.
  3. EXCLUSIVE – самый высокий уровень блокировки базы данных в SQLite. Режим EXCLUSIVE блокирует базу данных при выполнении команды BEGIN и при этом другие транзакции не могут ни читать данные из базы данных, ни уж тем более изменять данные.

Как видим, SQLite ориентирована на обеспечение целостности данных и согласованность данных при выполнении транзакций. Мне кажется, что данный подход обусловлен тем, что SQLite в первую очередь используется на мобильных устройствах, которые не имеют постоянных источников питания.

Подтвердить транзакцию в SQLite3

Подтвердить изменения, внесенные транзакцией, позволяет ключевая фраза COMMIT TRANSACTION. Синтаксис подтверждения изменений, вносимых транзакцией, вы можете увидеть на рисунке ниже.

Подтвердить транзакцию в SQLite3

Заметим, что у команды COMMIT есть псевдоним END, а ключевое слово TRANSACTION является необязательным и его можно не писать.

Откатить транзакцию в SQLite

Откатить транзакции в базах данных под управлением SQLite позволяет ключевое слово ROLLBACK. Синтаксис команды ROLLBACK в SQLite3 показан на рисунке ниже.

Откатить транзакцию в SQLite

Транзакции в SQLite могут быть вложенными (nested), поэтому откатывать можно не только к началу, но и к контрольной точки (ROLLBACK TO SAVEPOINT), отметим, что ключевое слово TRANSACTION также не является обязательным при выполнении команды ROLLBACK.

Альтернативный синтаксис транзакций в SQLite3: транзакции с именем, вложенные транзакции и контрольные точки

В SQLite есть альтернативный синтаксис транзакций, реализуемый при помощи команды SAVEPOINT, но это не только альтернативный синтаксис транзакций в SQLite, который вы сможете увидеть ниже, но еще и возможность сделать вложенную транзакцию.

Начать вложенную транзакцию с именем

Закрыть вложенную транзакцию

Обратите внимание на некоторые моменты создания транзакций в SQLite при помощи ключевого слова SAVEPOINT:

  1. Ключевое слово SAVEPOINT позволяет создавать вложенные транзакции.
  2. Если мы инициируем транзакцию ключевым словом SAVEPOINT, то у транзакции обязательно должно быть имя, которое может быть неуникальным.
  3. Для успешного подтверждения изменений транзакций, начатых командой SAVEPOINT используется ключевое слово RELEASE. Но, если команда RELEASE применяет к вложенной транзакции, то она просто удаляет контрольную точку, только команда RELEASE, которая будет применена к внешнему SAVEPOINT, будет работать, как COMMIT.
  4. Ключевое слово ROLLBACK TO откатывает все изменения, внесенные после создания контрольной точки и возвращает базу данных в то состояния, в котором она была на момент создания контрольной точки.

Таким образом в базах данных SQLite мы можем создавать транзакции с именем, которые одновременно являются вложенными транзакциями и контрольными точками.

Т ранзакции: Поддержка корректности данных и их восстановление

Транзакции позволяют объединить в одну группу несколько SQL операторов и интерпретировать их как один составной оператор. В этой группе будут выполнены либо все операторы, либо ни один из них не будет выполнен.

В этой главе обсуждаются следующие темы:

· Дается обзор транзакций;

· Объясняется, как использовать групповые операции в транзакции;

· Объясняется, как определить режимы транзакций и уровни изоляции;

· Обсуждается, как сохраненные процедуры и триггеры работают в транзакции;

· Как курсоры работают в транзакциях;

· Отмена и восстановление транзакций.

Что такое транзакции

Транзакция — это механизм объединения одного или нескольких SQL операторов в единое целое. SQL Сервер автоматически выполняет все операторы модификации данных, включая одношаговые изменения, как транзакции. По умолчанию каждый оператор вставки, обновления и удаления выполняется как простая транзакция.

Пользователь может сгруппировать множество SQL операторов в транзакцию с помощью команд begin transaction (начать транзакцию), commit transaction (подтвердить транзакцию), rollback transaction (откатить транзакцию). Команда begin transaction отмечает начало транзакции. Все остальные операторы вплоть до оператора rollback transaction или соответствующего оператора commit transaction включаются в эту транзакцию.

Транзакции позволяют гарантировать следующие свойства:

· Корректность — Одновременные запросы и изменения данных не противоречат друг другу и пользователь никогда не видит и не работает с данными, которые являются только частью некоторого изменения;

· Восстанавливаемость — В случае системного сбоя база данных полностью автоматически восстанавливается.

Для поддержки транзакций, удовлетворяющих SQL стандарту, SQL Сервер предоставляет опции, которые позволяют выбрать режим и уровень изоляции ( isolation level ) транзакции. В приложениях, где используются транзакции, удовлетворяющие стандарту, необходимо устанавливать эти опции в начале каждой сессии. Режимы транзакций и уровни изоляции обсуждается далее в этой главе.

Транзакции и достоверность данных

В многопользовательской среде SQL Сервер должен предотвращать одновременные запросы и попытки одновременного изменения данных со стороны различных пользователей. Это очень важно, поскольку в противном случае данные, обрабатываемые одним запросом, могут быть модифицированы другим пользователем, что может привести к противоречивому результату.

SQL Сервер автоматически устанавливает соответствующий уровень блокировки для каждой транзакции. Можно установить более сильную разделяемую блокировку ( shared locks ) для поочередных запросов, указав ключевое слово holdlock в операторе выбора select .

Пользователь может определить транзакцию, указать SQL Серверу, что последовательность SQL операторов нужно выполнить как единый блок. Эта возможность обсуждается далее в этой главе.

Транзакции и восстановление данных

Транзакции являются как единицей работы, так и единицей восстановления данных. Тот факт, что SQL Сервер выполняет одношаговые (простые) запросы как транзакции означает, что база данных может быть полностью восстановлена в случае возникновения ошибок.

Время восстановления измеряется в секундах и минутах. Можно указать максимально допустимое время восстановления.

SQL операторы, связанные с восстановлением и отменой изменения данных, обсуждаются в разделе «Отмена и восстановление транзакций».

Использование транзакций

Команды begin transaction (начать транзакцию) и commit transaction (закончить транзакцию) указывают SQL Серверу, что последовательность операторов, расположенную между ними, нужно выполнить как единый блок. Команда rollback transaction (откат транзакции) отменяет изменения, произведенные транзакцией, возвращаясь либо к исходному состоянию, в котором находилась система до начала транзакции, либо к состоянию, соответствующему точке сохранения ( save point ). Можно определить точку сохранения внутри транзакции с помощью команды save transaction (сохранить транзакцию).

С помощью этих команд пользователь может влиять на выполнение транзакций. Кроме того, они улучшают производительность, поскольку системные накладные расходы будут связаны с транзакцией, а не с отдельной командой.

Замечание. Группировка большого числа SQL команд в одну большую транзакцию может повлиять на время восстановления. Если SQL Сервер обнаружит ошибку до окончания транзакции, то время восстановления будет больше, поскольку потребуется время на отмену произведенных изменений.

Любой пользователь может определить транзакцию. Для использования транзакций не требуется никаких разрешений.

В следующих разделах обсуждается общие темы, относящиеся к транзакциям, а также команды, связанные с транзакциями, вместе с примерами их использования. Дополнительная информация о транзакциях приводится в Справочном руководстве SQL Сервера.

Команды определения данных в транзакциях

Можно использовать некоторые команды определения данных ( ddl — data definition language ) в транзакциях, если установлена (в состояние true ) опция ddl in tran . В этом случае в текущей базе данных внутри транзакций можно пользоваться такими командами, как creat table ( создать таблицу), alter table (изменить таблицу) и grant (предоставить права). Если опция ddl in tran установлена в базе данных model , то указанные команды можно использовать во всех базах данных, созданных после установке этой опции в базе данных model .

Предупреждение! Только в сценарии create schema объясняется использование команд определения данных. Эти команды блокируют системные таблицы, такие как sysobjects . Поэтому транзакции, в которых есть эти команды, нужно делать короткими.

В частности, следует избегать использования этих команд в транзакциях, работающих с временной базой данных tempdb , чтобы система полностью не остановилась. Поэтому опция ddl in tran всегда должна быть в состоянии false в базе данн ых tempdb .

Чтобы установить опцию ddl in tran , можно использовать, например, следующую команду:

sp_dboption mydb, "ddl in tran", true

Первым параметром здесь указывается название базы данных, в которой устанавливается эта опция. Пользователь должен работать с базой данных master , чтобы выполнить команду sp _ dboption . Любой пользователь может выполнить команду sp _ dboption без параметров, чтобы увидеть текущую установку опций. Тем не менее, чтобы установить опцию нужно быть системным администратором либо владельцем базы данных.

Следующие команды допускаются внутри транзакций пользователя, если установлена опция ddl in tran :

Таблица 17-1: DDL команды, допускаемые в транзакциях

alter table (допускаются все предложения кроме partion и unpartion )

create default

create index

create procedure

create rule

create schema

create table

create trigger

create view

drop default

drop procedure

drop trigger

Системные процедуры, которые изменяют базу данных master или временные таблицы, нельзя использовать в транзакциях пользователей.

Никогда не следует использовать следующие команды внутри транзакций:

Таблица 17-2: DDL команды, которые нельзя использовать в транзакциях

alter table. partition

alter table unpartition

С помощью процедуры sp _ helpdh можно определить текущую установку опции ddl in tran .

Начало и окончание транзакций

Между командами begin transaction (начать транзакцию) commit transaction (закончить (подтвердить) транзакцию) можно расположить любое число SQL операторов и сохраненных процедур. Эти команды имеют следующий синтаксис:

Название транзакции должно удовлетворять правилам, установленным для идентификаторов.

Ключевые слова transaction , tran и work (для команды commit ) являются синонимами, поэтому можно использовать любое из них. Однако transaction и tran являются словами расширенного языка Transact — SQL ; только work является словом стандартного языка SQL .

Схематично транзакция выглядит следующим образом:

Команда commit transaction не выполняется SQL Сервером, если транзакция не является активной.

Откат назад и точки сохранения

Если транзакция должна быть прекращена до того, как она окончится, то все выполненные в ней операторы или процедуры должны быть отменены. Преждевременное прекращение выполнения транзакции может произойти либо из-за появления ошибки, либо по требованию пользователя.

Транзакцию можно прекратить (откатить) в любое время до выполнения команды commit transaction с помощью команды rollback transaction (откатить транзакцию). Используя точки сохранения, можно откатить только часть транзакции. Нельзя откатить транзакцию после ее окончания и подтверждения изменений.

Команда rollback transaction имеет следующий синтаксис:

rollback < transaction | tran | work >[название_транзакции | название_точки]

Точка сохранения — это метка (маркер), которой помечается место отката внутри транзакции.

Точки сохранения вводятся командой save transaction , которая имеет следующий синтаксис:

save < transaction | tran >название_точки_сохранения

Название точки сохранения должно удовлетворять правилам, установленным для идентификаторов.

Если в команде rollback transaction не указывается название транзакции или точки сохранения, то транзакция откатывается до первого оператора begin transaction в пакете.

Далее приводится пример использования команд save transaction и rollback transaction :

begin tran название_транзакции

save tran название_точки_сохранения

rollback tran название_точки_сохранения

Первая команда rollback transaction возвращает вычислительный процесс в состояние, соответствующее точке сохранения внутри транзакции. Вторая команда rollback transaction возвращает процесс в состояние, соответствующее началу транзакции. Если транзакция возвращается к точке сохранения, то она должна в дальнейшем либо закончится, либо будет откачена полностью.

Пока не выполнена команда commit transaction SQL Сервер выполняет все последующие операторы как часть транзакции, до тех пор пока не появится другая команда begin transaction . С этого момента SQL Сервер начинает выполнение новой вложенной ( nested ) транзакции. Вложенные транзакции описываются в следующем разделе.

SQL Сервер не выполняет команды rollback transaction и save transaction и не выдает сообщений об ошибке, если транзакция является неактивной.

Проверка состояния транзакций

Глобальная переменная @@transtate служит для запоминания текущего состояния транзакции. SQL Сервер определяет состояние путем запоминания изменений, возникающих после выполнения операторов. Эта переменная может содержать следующие значения:

Таблица 17-3: Значения переменной @@transtate

Транзакция выполняется. Предыдущий оператор выполнился успешно.

Транзакция закончилась и произошло подтверждение всех произведенных изменений.

Выполнение предыдущего оператора было прервано. Изменений не произошло.

Транзакция прервана и отменены все произведенные изменения.

В транзакции можно использовать значение переменной @@transtate после выполнения оператора (такого как insert ) для проверки правильности его выполнения. В следующем примере значение переменной @@transtate проверяется в процессе выполнения транзакции после оператора вставки и после окончания транзакции:

insert into publishers (pub_id) values (‘9999’)

В следующем примере значение переменной @@transtate проверяется после выполнения ошибочного оператора вставки (который нарушает правило) и после отката всей транзакции:

insert into publishers (pub_id) values (‘7777’)

Msg 552, Level 16, State 1:

A column insert or update conflicts with a rule bound to the column. The command is aborted. The conflict occured in database ‘pubs2’, table ‘publishers’, rule ‘pub_idrule’, column ‘pub_id’.

Однако в противоположность переменной @@error , SQL Сервер не восстанавливает значение переменной @@transtate после каждого оператора. Значение этой переменной изменяется только в процессе выполнения транзакций.

Вложенные транзакции

Можно поместить транзакцию внутри другой транзакции. В этом случае действительное подтверждение изменений происходит после окончания самой внешней транзакции. Внутренняя пара команд begin transaction и commit transaction лишь указывают уровень вложенности. SQL Сервер не фиксирует изменений до те пор, пока не появится самая внешняя команда commit transaction , которая соответствует самой внешней команде begin transaction .

SQL Сервер содержит глобальную переменную @@trancount , в которой запоминается уровень вложенности транзакций. Самая первая явная или неявная команда begin transaction устанавливает значение этой переменной равным 1. Каждая последующая команда begin transaction увеличивает значение этой переменной (на единицу), а команда commit transaction уменьшает его (на единицу). Запуск триггера также приводит к увеличению этой переменной и транзакция начинается с оператора, который запустил триггер. Изменения, сделанные во вложенных транзакциях, не фиксируются ( committed) до тех пор, пока не значение переменной @@trancount не станет равным нулю.

Например, изменения, сделанные в следующих вложенных группах операторов, не подтверждаются до тех пор, пока не выполнится последний оператор commit transaction :

Если во вложенной транзакции встречается оператор rollback transaction без указания названия транзакции или точки сохранения, то всегда откатывается самая внешняя транзакция и выполнение транзакции прекращается.

Пример транзакции пользователя

Следующий пример показывает как можно определить транзакцию пользователя:

begin transaction royalty_change

/* A user sets out to change the royalty split */

/* for the two authors of The Gourmet Microwave. */

/* Since the database would be inconsistent */

/* between the two updates, they must be grouped */

/* into a transaction. */

set royaltyper = 65

from titleauthor, titles

where royaltyper = 75

and titleauthor.title_id = titles.title_id

and title = "The Gourmet Microwave"

set royaltyper = 35

from titleauthor, titles

where royaltyper = 25

and titleauthor.title_id = titles.title_id

and title = "The Gourmet Microwave"

save transaction percent_changed

/* After updating the royaltyper entries for */

/* the two authors, the user inserts the */

/* savepoint "percent_changed," and then checks */

/* to see how a 10 percent increase in the

/* price would affect the authors’ royalty */

set price = price * 1.1

where title = "The Gourmet Microwave"

select (price * royalty * total_sales) * royaltyper

from titles, titleauthor, roysched

where title = "The Gourmet Microwave"

and titles.title_id = titleauthor.title_id

and titles.title_id =roysched.title_id

rollback transaction percent_changed

/* The transaction rolls back to the savepoint */

/* with the rollback transaction command. */

/* Without a savepoint, it would roll back to */

/* the begin transaction. */

Выбор режима транзакции и уровня изоляции

SQL Сервер предоставляет две опции, которые предназначены для выполнения транзакций, удовлетворяющих стандарту языка SQL. Эти опции определяют режим транзакции ( transaction mode) и уровень изоляции (isolation level) . Эти опции необходимо устанавливать в начале каждой сессии, в которой транзакции должны удовлетворять стандарту SQL.

SQL Сервер поддерживает следующие режимы транзакций:

· По умолчанию устанавливается режим, называемый несвязанным ( unchained) или режимом Transact-SQL, в котором для начала транзакции требуется явное указание оператора begin transaction , а для ее окончания требуется указать соответствующий (парный) оператор commit transaction или rollback transaction ;

· Можно установить режим, который в стандарте SQL называется связанным ( chained), когда транзакция начинается неявно при выполнении любого оператора выбора или модификации данных. К этим операторам относятся: delete, insert, open, fetch, select и update . Но для окончания транзакции здесь нужно явно указать оператор commit transaction или rollback transaction .

Можно установить любой их этих режимов, включив опцию chained командой set . Однако не следует смешивать режимы транзакций в своих прикладных программах. Выполнение сохраненных процедур и триггеров может зависеть от режима транзакций, поэтому могут потребоваться дополнительные усилия, чтобы процедура, созданная в одном режиме, могла запускаться в другом режиме.

SQL Сервер поддерживает следующие уровни изоляции транзакций:

· Уровень 0 — На этом уровне предполагается, что каждая транзакция записывает фактические данные. На этом уровне не допускается запись данных в то же место пока транзакция не окончится. Другие транзакции могут считывать не подтвержденные данные;

· Уровень 1 — Здесь предполагается, что в каждой транзакции считываются фактические данные, а не данные, записанные не окончившейся транзакцией. Другими словами, здесь не допускается считывание неподтвержденных данных. Этот уровень изоляции устанавливается по умолчанию SQL Сервером;

· Уровень 3 — Здесь предполагается, что данные, считываемые в процессе выполнения транзакции, не должны изменяться до окончания транзакции. Этот уровень можно установить, указав ключевое слово holdlock в операторе выбора, и тем самым блокировать считываемые данные .

В каждом сеансе работы можно установить командой set уровень изоляции с помощью опции transaction isolation level . В качестве альтернативы можно установить уровень изоляции только для текущего запроса, указав предложение at isolation в операторе выбора select .

В следующих разделах эти возможности будут описаны более детально.

Выбор режима транзакции

Стандарт языка SQL требует чтобы каждый SQL оператор, который выбирает или модифицирует данные, выполнялся внутри транзакции. Транзакция должна начинаться автоматически в начале рабочего сеанса или после окончания предыдущей транзакции при выполнении оператора, обращающегося к данным. Это — связанный режим транзакций.

Этот режим можно установить путем установки опции chained командой set . Например:

Однако эту опцию нельзя устанавливать внутри транзакции. Для возврата в несвязанный режим транзакций следует выключить эту опции, установив ее в состояние off командой set . По умолчанию устанавливается несвязанный режим.

Если установлен связанный режим транзакций , то SQL Сервер неявно выполняет команду begin transaction непосредственно перед следующими операторами, выбирающими или модифицирующими данные: delete, insert, open, fetch, select и update . Например, следующая группа операторов будет выдавать различные результаты в зависимости от используемого режима транзакций:

insert into publishers

values (‘9999’, null, null, null)

delete from publishers where pub_id = ‘9999’

В несвязанном режиме команда rollback затронет только оператор delete , поэтому таблица publishers будет содержать вставленную строку. В связанном режиме оператор insert неявно начинает транзакцию, поэтому откатываться будут все операторы вплоть до начала транзакции, включая и оператор insert .

Хотя в связанном режиме транзакция начинается неявно при выполнении операторов, обращающихся к данным, тем не менее для вложенных транзакций необходимо явно указать оператор begin transaction . После неявного начала транзакции следующие операторы уже не начинают транзакции до тех пор, пока начатая транзакция не будет окончена или отменена. Например, в следующем запросе первая команда commit transaction подтверждает все изменения, сделанные в связанном режиме, поэтому вторая подтверждающая команда является лишней:

insert into publishers

values (‘9999’, null, null, null)

insert into publishers

values (‘9997’, null, null, null)

Замечание: В связанном режиме любой оператор, выбирающий или модифицирующий данные, начинает транзакцию независимо от того насколько успешно он был выполнен. Даже простой оператор select , не обращающийся к таблице, начинает транзакцию.

Режим транзакций можно проверить по значению глобальной переменной @@tranchained . В несвязанном режиме эта переменная устанавливается в 0, а в связанном — в 1.

Выбор уровня изоляции

В стандарте языка SQL от 1992 года определяется четыре уровня изоляции для транзакций. Каждый уровень изоляции определяет (квалифицирует) действия, которые недопустимы при выполнении параллельных ( concurrent) транзакций. Более высокие уровни изоляции включают все ограничения, установленные на более низких уровнях:

· На нулевом уровне запрещается изменение данных со стороны других транзакций, если эти данные модифицируются еще не окончившейся транзакцией. Иначе говоря , другие транзакции блокируются по записи для этих данн ых до тех пор, пока не окончится текущая транзакция. Однако другим транзакциям разрешается считывать еще не подтвержденные данные, что классифицируется как грязное чтение ( dirty reads);

· На первом уровне запрещается грязное чтение. Такое чтение возникает тогда, когда в одной транзакции строка модифицируется, а во второй транзакции эта строка считывается, прежде чем первая транзакция будет окончена или отменена. Если первая транзакция будет откачена (отменена), то информация, полученная во второй транзакции, окажется неверной;

· На втором уровне запрещается неповторяемое чтение ( nonrepeatable reads) . Такое чтение возникает, когда в одной транзакции строка считывается, а во второй транзакции эта строка модифицируется. В этом случае результат считывания будет зависеть от того, какая из транзакций будет окончена первой;

· На третьем уровне запрещаются фантомы ( phantoms). Они возникают, когда в одной транзакции считывается множество строк, удовлетворяющее некоторому условию, а в другой транзакции эти данные модифицируются (одним из операторов модификации данных). Если в первой транзакции будет выполнено повторное считывание при том же условии, то будет получено другое множество строк.

По умолчанию для SQL Сервера устанавливается первый уровень изоляции транзакций. В стандарте языка SQL от 1992 года требуется, чтобы по умолчанию устанавливался третий уровень изоляции для всех транзакций. Это предотвращает грязные чтения, неповторяемые чтения и фантомы. Чтобы установить этот уровень по умолчанию, в SQL Сервере имеется опция transaction isolation level 3 , которую можно установить командой set . Установка этой опции вызывает автоматическое включение блокировки ( holdlock ) во всех операторах выбора select в транзакции. Например:

set transaction isolation level 3

Приложения, которые требуют третьего уровня изоляции, должны устанавливать его в начале каждого рабочего сеанса. Однако, установка этого уровня вызывает блокировку данных при любом считывании в процессе выполнения транзакции. Если к тому же установлен связанный режим транзакций, то установленный уровень изоляции будет действовать при выполнении любого оператора, обращающегося к данным, поскольку при этом будет неявно начинаться транзакция. Это может привести к проблемам при распределенном выполнении ( concurrency problem) некоторых приложений, поскольку длительное время будут действовать много блокировок.

Чтобы вернуться к первому уровню изоляции, который в SQL Сервере устанавливается по умолчанию, следует выполнить команду:

set transaction isolation level 1

При кладные программы , которые не критичны к грязному чтению, можно сделать более быстрыми и уменьшить в них вероятность тупиков ( deadlocks), если установить нулевой уровень изоляции в начале рабочего сеанса. В качестве примера можно привести программу, которая находит среднее значение баланса по всем счетам, записанным в таблице. Весьма вероятно, что это значение будет часто изменяться, поэтому его можно вычислять по таблице на нулевом уровне изоляции. Приложения, которые критичны к согласованности данных, такие, как начисление и снятие денег с конкретного счета в таблице, не должны выполняться на нулевом уровне изоляции.

Запросы, которые выполняются на нулевом уровне изоляции, не предусматривают никаких блокировок во время чтения данных, поэтому они не блокируют другие транзакции, которые записывают данные в то же место, и наоборот. Однако, даже на нулевом уровне изоляции некоторые утилиты (такие как dbcc ) и операторы модификации данных (такие как update ) блокируют чтение данных, чтобы сохранить целостность базы данных и гарантировать правильность считываемых данных.

Глобальная переменная @@isolation указывает на текущий уровень изоляции, установленный для рабочего сеанса. В ответ на запрос этой переменной выдается значение текущего уровня (0,1 или 3). Например:

Дополнительную информацию об уровнях изоляции и блокировках можно посмотреть в Руководстве по оптимизации и настройке SQL Сервера.

Изменение уровня изоляции запроса

Можно изменить уровень изоляции запроса с помощью предложения at isolation в операторах select и readtext . Опции read uncommitted, read committed и serializable предложения at isolation соответствуют следующим уровням изоляции:

Уровень изоляции

read uncommitted

read committed

serializable

Например, в следующих двух операторах запрашивается одна и та же таблица на уровнях изоляции 0 и 3 соответственно:

at isolation read uncommitted

at isolation serializable

Предложение at isolation действует только на один оператор select, readtext или declare cursor , в котором оно указано. SQL Сервер возвращает сообщение о синтаксической ошибке, если предложение at isolation используется в следующих ситуациях:

· Когда оно указано в запросе, содержащем предложение into ;

· Когда оно используется в подзапросе;

· Когда оно используется в операторе create view ( создать вьювер);

· Когда оно используется в операторе insert ;

· Когда оно указано в запросе, содержащем предложение for browse .

Если в запросе есть оператор union (объединить), то предложение at isolation должно указываться после последнего оператора select .

Стандарт языка SQL от 1992 года определяет фразы read uncommitted, read committed и serializable как опции предложения at isolation (а также команды set transaction isolation level ). В языке Transact-SQL для указания уровня изоляции можно также просто указать цифру 0,1 или 3 в предложении at isolation . Чтобы упростить изложение уровней изоляции, в данном руководстве это расширение не будет использоваться.

Третий уровень изоляции можно ввести, указав ключевое слово holdlock в операторе выбора select . Однако, нельзя указывать ключевые слова holdlock , noholdlock или shared в запросе, содержащем предложение at isolation read uncommitted . Когда используются различные способы установки уровня изоляции ключевое слово holdlock является старше, чем предложение at isolation (за исключением нулевого уровня изоляции), которое, в свою очередь, старше установки set transaction isolation level , действующей для рабочего сеанса.

Курсоры и уровни изоляции

Оператор выбора, содержащий предложение at isolation , можно использовать для изменения уровня изоляции при определении курсора. Например:

declare commit_crsr cursor

at isolation read committed

Этот оператор устанавливает для работы с курсором первый уровень изоляции, независимо от уровня изоляции, установленного для транзакций или рабочего сеанса. Если курсор объявляется с нулевым уровнем изоляции, то его можно использовать только для чтения. Нельзя указывать предложение at isolation с опцией read uncommitted в конструкции for update оператора declare cursor .

SQL Сервер устанавливает уровень изоляции курсора, когда он открывается, а не когда он объявляется. В момент открытия курсора его уровень изоляции выбирается, исходя из следующих обстоятельств:

· Если курсор объявлен с указанием уровня изоляции, то этот уровень имеет приоритет перед уровнем изоляции транзакции, в которой он открывается;

· Если курсор объявлен без уровня изоляции, то для него используется уровень изоляции, при котором он был открыт. Если курсор был закрыт, а затем позднее вновь открыт, то для него устанавливается уровень изоляции текущей транзакции.

В последнем случае необходимо отметить, что курсоры некоторых типов (языковые и клиентские), объявленные в транзакции с первым или третьим уровнем изоляции, не могут быть открыты в транзакции с нулевым уровнем изоляции. Более подробно об этих ограничениях и об курсорах различных типов можно посмотреть в Справочном руководстве SQL Сервера.

Сохраненные процедуры и уровни изоляции

Все системные сохраненные процедуры всегда Sybase работают на 1 уровне изоляции, независимо от уровня изоляции транзакции или рабочего сеанса. Сохраненные процедуры пользователя работают с уровнем изоляции транзакции, в которой они вызываются. Если уровень изоляции изменяется внутри сохраненной процедуры, то он действует только во время выполнения этой процедуры.

Триггеры и уровни изоляции

Поскольку триггеры запускаются во время выполнения операторов модификации данных (таких как insert ), то все триггеры выполняются либо на уровне изоляции транзакции, либо на первом уровне изоляции, в зависимости от того, какой из этих уровней больше. Таким образом, если триггер был запущен в транзакции нулевого уровня, то SQL Сервер перед выполнением его первого оператора устанавливает для этого триггера первый уровень изоляции.

Транзакции в сохраненных процедурах и триггерах

Транзакции можно использовать внутри сохраненных процедур и триггеров, а также внутри операторных пакетов. Если транзакция в пакете или сохраненной процедуре вызывает другую сохраненную процедуру или триггер, которые в свою очередь содержат транзакцию, то вторая транзакция будет вложена в первую.

Первая явная или неявная (в связанном режиме) команда begin transaction начинает транзакцию в пакете, сохраненной процедуре или триггере. Каждая последующая команда begin transaction увеличивает уровень вложенности на единицу. Каждая последующая команда commit transaction уменьшает уровень вложенности, пока не будет достигнут 0. После этого SQL Сервер заканчивает всю транзакцию. Команда rollback transaction отменяет всю транзакцию вплоть до первой команды begin transaction независимо от уровня вложенности и числа промежуточных процедур и триггеров.

В процедурах и триггерах число команд begin transaction должно соответствовать числу команд commit transaction . Это также относится к сохраненным процедурам, использующим связанный режим. Первый оператор, который неявно начинает транзакцию также должен иметь соответствующий закрывающий оператор commit transaction .

Следующий рисунок иллюстрирует использование транзакций внутри вложенных процедур.

Рисунок 17-1: Вложенные транзакции

Команда rollback transaction в сохраненной процедуре не влияет на последующие операторы процедуры или пакета, которые расположены за оператором вызова этой процедуры. Другими словами, SQL Сервер выполнит оставшиеся операторы процедуры или пакета. Однако команда rollback transaction в триггере отменяет выполнение всего пакета, поэтому оставшиеся операторы в этом случае не выполняются.

Например, в следующем пакете вызывается процедура myproc , в которой расположен оператор rollback transaction :

update titles set .

insert into titles .

delete titles where .

Если этот оператор будет выполнен, то операторы обновления ( update) и вставки ( insert) отменяются и транзакция прерывается. После этого SQL Сервер продолжит выполнение пакета и выполнит оператор удаления ( delete). Однако, если с таблицей связан вставляющий триггер, в котором была выполнена команда rollback transaction , то отменяется выполнение всего пакета, и следовательно в этом случае оператор удаления не будет выполняться. Например:

update authors set .

insert into authors .

delete authors where .

Имеются определенные требования к режимам транзакций и уровням изоляции в сохраненных процедурах, которые описываются в следующем разделе. На триггера не влияет текущий режим транзакций, поскольку они всегда вызываются как часть оператора модификации данных.

Режимы транзакций в сохраненных процедурах

Сохраненные процедуры, в которых используется несвязанный режим транзакций, могут быть несовместимы с транзакциями, использующими связанный режим, и наоборот.

Например, следующая сохраненная процедура является правильной при использовании связанного режима транзакций:

create proc myproc

insert into publishers

values (‘9999’, null, null, null)

Программа, выполняющаяся в несвязанном режиме транзакций, может оказаться ошибочной, если она обратиться к этой процедуре, поскольку команда commit не имеет парной себе команды begin . Могут также возникнуть следующие проблемы:

· Приложения, которые начинаются в связанном режиме, могут образовать очень длинные транзакции, которые невозможно выполнить, или на весь сеанс работы заблокировать данные. Это приводит к снижению производительности SQL Сервера;

· Время выполнения вложенных транзакций может оказаться непредсказуемым. Это может привести к различным результатам в зависимости от режима транзакций.

Как правило, приложения, использующие определенный режим транзакций, должны вызывать процедуры, написанные для того же режима. Исключением из этого правила являются системные процедуры фирмы Sybase (сюда не включается процедура sp_procxmode , описанная ниже), которые можно вызывать в любом режиме. Если во время выполнения системной процедуры нет активных транзакций, то SQL Сервер выключает связанный режим на время выполнения этой процедуры. Перед выходом из процедуры он восстанавливает тот режим, который был установлен до входа в процедуру.

SQL Сервер помечает ( tags) все процедуры ("связанная" или "несвязанная") в зависимости от режима транзакций сеанса, в котором они были созданы. Это помогает избежать проблем, связанных с несоответствием режимов транзакций. Сохраненную процедуру, помеченную как "связанная", нельзя выполнять в рабочем сеансе, использующим несвязанный режим транзакций, и наоборот.

Предупреждение! Когда используется режимы транзакций, нужно следить за последствиями, которые может вызвать в приложении каждая установка.

Установка режимов транзакций в сохраненных процедурах

С помощью системной процедуры sp_procxmode можно изменить пометку ( tag value), связанную с сохраненной процедурой. Чтобы указать, что данная процедура может исполняться в любом режиме, ей можно присвоить пометку " anymode" ( любой режим) с помощью процедуры sp_procxmode . Например:

sp_procxmode byroyalty, "anymode"

Если вызвать процедуру sp_procxmode без аргументов, то выдается список режимов транзакций для всех сохраненных процедур в текущей базе данных:

procedure name transaction mode

(8 rows affected, return status = 0)

Процедуру sp_procxmode можно использовать только в несвязанном режиме транзакций.

Чтобы изменить режим транзакций процедуры, необходимо иметь права системного администратора, владельца базы данных или владельца процедуры.

Использование курсоров в транзакциях

По умолчанию SQL Сервер не изменяет состояние курсора (открытый или закрытый), когда транзакция заканчивается через подтверждение или откат. Однако, стандарт SQL связывает открытый курсор с его активной транзакцией. Подтверждение или откат транзакции автоматически закрывает любой открытый в ней курсор.

Чтобы соответствовать этому стандарту SQL , в SQL Сервере предусмотрена опция close on endtran ( закрыть по окончанию транзакции), устанавливаемая командой set . Кроме того, в связанном режиме SQL Сервер автоматически начинает транзакцию, когда открывается курсор, и закрывает этот курсор, когда транзакция подтверждается или откатывается.

Например, следующая последовательность операторов по умолчанию вызовет сообщение об ошибке:

open cursor test

open cursor test

Если же будет установлена опция close on endtran или опция chained ( связанный режим), то состояние курсора изменится с открытого на закрытое после команды подтверждения ( commit). После этого его можно снова открыть.

Любые исключающие блокировки, установленные для курсора в транзакции, сохраняются до окончания транзакции. Это также справедливо при разделяющих блокировках, когда используется ключевое слово holdlock , предложение at isolation serializable или при установке опции set isolation level 3 . Однако, если опция close on endtran не установлена, то курсор остается открытым после окончания транзакции и текущая страницы остается блокированной. Объем блокированных данных может продолжать увеличиваться по мере загрузки новых строк.

Резервирование и восстановление в транзакциях

Каждое изменение в базе данных, произведенное отдельным оператором обновления или группой операторов, автоматически регистрируется в системной таблице syslogs . Эта таблица также называется журналом регистрации транзакций ( transaction log).

Некоторые команды, такие как очистка таблицы ( truncate table ) , копирование большого объема данных в таблицу без индексов, команды select into, writetext , dump transaction with no_log , изменяют базу данных, но не регистрируются в журнале.

В журнале транзакций регистрируются все операторы модификации данных в том порядке, в каком они выполнялись. Когда начинается транзакция, то это событие регистрируется в журнале. По мере выполнения каждый оператор модификации данных регистрируется в журнале.

Каждое изменение всегда сначала регистрируется в журнале прежде чем происходит изменение в самой базе данных. Такой тип регистрации, называемый предваряющим ( write-ahead log), гарантирует полное восстановление базы данных в случае появления ошибки.

Ошибки могут возникать из-за неисправностей аппаратуры, проблем окружения, проблем, возникающих в системном или прикладном программном обеспечении, а также программном или принудительном со стороны пользователя прекращении транзакций.

В любом из этих случаев журнал транзакций служит для восстановления состояния базы данных, путем выполнения операторов из журнала в обратном порядке, начиная с резервной копии, созданной по команде dump .

Для восстановления после ошибки необходимо также откатить транзакции, которые в момент появлении ошибки были выполнены не полностью, поскольку частично выполненные транзакции могут привести к некорректному изменению базы данных. Законченные транзакции нужно выполнить еще раз, если нет гарантии, что они были записаны на системное устройство.

Если в момент появления ошибки выполнялась длинная (по времени) транзакция, которая не была подтверждена, то время восстановления может быть сравнимо с временем, затраченным на выполнение этой транзакции. Такая ситуация может также возникнуть, если оператор begin transaction не имеет парного оператора commit transaction или rollback transaction . Это не позволяет SQL Серверу зафиксировать какие-либо изменения и увеличивает время восстановления.

Динамическое копирование ( dump ) позволяет резервировать состояние базы данных и журнала транзакций. Нужно чаще резервировать журнал транзакций. Чем чаще создается резервная копия ( backup) данных, тем меньше времени нужно на их восстановление в случае появления системной ошибки.

Владелец каждой базы данных или пользователь наделенный правом OPER несут ответственность за резервное копирование базы данных и ее журнала транзакций с помощью команды dump , хотя право копирования может быть передано другим пользователям. Однако, право на выполнение команды load (загрузка) по умолчанию принадлежит владельцу базы данных и не может передаваться.

После загрузки базы данных SQL Сервер управляет всеми аспектами процесса ее восстановления. SQL Сервер также автоматически управляет контрольными точками ( checkpoint), т.е. точками в которых гарантируется присутствие всех измененных страниц данных на устройстве базы данных. Пользователь может явно ввести точку с помощью команды checkpoint .

Дополнительную информацию об этом можно прочитать в Справочном руководстве SQL Сервера и в Руководстве системного администратора SQL Сервера.

Транзакции и блокировка транзакций

В этой лекции рассматриваются основы использования транзакций и блокировки транзакций. Вы узнаете, что такое транзакция , какими свойствами в Microsoft SQL Server 2000 должны обладать транзакции, чтобы их можно было использовать, как использовать режимы транзакций для указания начала и конца транзакции и как фиксировать (завершать) транзакции и выполнять их " откат " (отмену). Мы рассмотрим типы и режимы блокировки, которые используются в SQL Server при выполнении транзакций, а также концепции блокирования и взаимоблокировки . Лекция заканчивается описанием некоторых подсказок блокировки на уровне таблиц, которые вы можете использовать в ваших транзакциях.

Что такое транзакция?

Транзакция – это набор операций, который выполняется как один логический блок. Использование транзакций позволяет SQL Server обеспечивать определенный уровень целостности и восстанавливаемости данных. Журнал транзакций, который должна иметь каждая база данных, поддерживает запись всех транзакций, которые осуществляют любой тип модификации в базе данных (вставка, обновление или удаление). SQL Server использует этот журнал транзакций для восстановления данных в случае ошибок или отказов системы.

Целостность транзакции зависит, в частности, от программиста SQL. Программист должен знать, когда начинать и когда заканчивать транзакцию и в какой последовательности, чтобы модификации данных обеспечивали логическую согласованность и содержательность данных. Мы рассмотрим в последующих разделах, как начинать и заканчивать транзакции. А теперь, когда вы знаете, что такое транзакция, рассмотрим свойства, которыми должна обладать допустимая для использования транзакция.

ACID-свойства

Чтобы транзакцию можно было считать допустимой для использования, она должна отвечать четырем требованиям. Эти требования называют ACID-свойствами. " ACID " – это сокращение от "atomicity (атомарность), consistency (согласованность), isolation (изолированность) и durability (устойчивость)". В SQL Server включены механизмы, помогающие обеспечивать соответствие транзакций каждому из этих требований.

Атомарность

SQL Server обеспечивает, что в случае успешного выполнения транзакции фиксируются все модификации данных этой транзакции (в виде группы), а в случае неудачного выполнения транзакции не фиксируется ни одна из модификаций; иными словами, SQL Server обеспечивает атомарность своих транзакций. Транзакция должна выполняться как элементарная ( атомарная) операция – отсюда термин "атомарность". Чтобы транзакция считалась успешно выполненной, должен быть выполнен каждый шаг (или оператор) этой транзакции. При неудачном выполнении одного из шагов вся транзакция считается неуспешной, и происходит отмена всех модификаций, внесенных с момента начала транзакции. SQL Server содержит механизм управления транзакциями, который автоматически определяет результат завершения транзакции (успешный или неуспешный) и при необходимости (в случае неуспешной транзакции) отменяет любые модификации данных.

Согласованность

SQL Server обеспечивает также согласованность ваших транзакций. Согласованность означает, что после окончания транзакции все данные остаются в согласованном состоянии (сохраняется целостность данных) – будь то успешно или неуспешно завершенная транзакция. Перед началом любой транзакции база данных должна быть в согласованном состоянии, а это означает поддержку целостности данных и правильность внутренних структур, таких как индексы B-деревьев и двунаправленные списки. После выполнения транзакции база данных тоже должна быть в согласованном состоянии – в новом состоянии для успешной транзакции или в том же состоянии, что и перед началом транзакции в случае неуспешного завершения.

Согласованность также является свойством в управлении транзакциями, которое поддерживается в SQL Server. Если ваши данные являются согласованными, а в ваших транзакциях поддерживается логическая согласованность и целостность данных, то SQL Server обеспечит согласованность данных после любой транзакции. Используя репликацию данных в распределенной среде, вы можете задавать различные уровни согласованности от конечной сходимости транзакций, т.е. скрытой согласованности, до непосредственной согласованности транзакций. Уровень согласованности будет зависеть от типа используемой вами репликации. Более подробную информацию о репликациях см. в лекциях "Репликация в Microsoft SQL Server: обзор типов репликации и репликация моментальных снимков" , "Репликация транзакций" и "Репликация слиянием" .

Изолированность

Изолированность означает, что каждая транзакция действует так же, как если бы она была единственной в системе; иными словами, модификации, выполняемые в одной транзакции, изолируются от модификаций, выполняемых в другой параллельно выполняемой транзакции. Тем самым на любую транзакцию не влияет никакое значение, изменяемое другой транзакцией, пока это изменение не будет зафиксировано. В случае неуспешной транзакции ее модификации не будут оказывать влияния, поскольку будет выполнен откат (отмена) соответствующих изменений. SQL Server позволяет вам задавать уровень изолированности для ваших транзакций. Характер изолируемости транзакций зависит от указанного вами уровня изолированности.

Уровни изолированности

SQL Server поддерживает четыре уровня изолированности. Уровень изолированности – значение, определяющее уровень, при котором в транзакции допускаются несогласованные данные, т.е. степень изолированности одной транзакции от другой. Более высокий уровень изолированности повышает точность данных, но при этом может снижаться количество параллельно выполняемых транзакций. С другой стороны, более низкий уровень изолированности позволяет выполнять больше параллельных транзакций, но снижает точность данных. Указываемый вами уровень изолированности для сеанса SQL Server определяет блокирующее поведение всех операторов SELECT, выполняемых во время этого сеанса (если только вы не задали другой уровень изолированности). Блокирующее поведение описано в разделе "Блокировка транзакций" ниже в этой лекции. Ниже описано четыре уровня изолированности – от низшего до высшего:

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *