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

Система управления базами данных 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 мы можем создавать транзакции с именем, которые одновременно являются вложенными транзакциями и контрольными точками.

   2.19. Транзакции

   Язык запросов Transact-SQL взял свое название от слова транзакция. Я думаю, что Microsoft не зря сконцентрировало на этом понятии особое внимание, ведь транзакции действительно являются очень мощным средством управления базой данных.

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

   Рассмотрим классическую задачу – банковскую проводку. Допустим, что у нас есть таблица из двух полей – номер счета в банке и сумма денег на этом счету. Нам необходимо перевести деньги с одного счета на другой. Для этого нужно выполнить запрос UPDATE, чтобы уменьшить сумму первого счета на нужную сумму. После этого выполняем UPDATE, чтобы увеличить значение второго счета. Все вроде бы нормально. А что, если после уменьшения первого счета выключат свет и сервер не успеет пополнить другой счет? Деньги уже сняты, но никуда не записаны, а значит, они пропали.

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

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

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

   Существует две разновидности транзакций в SQL Server:

   • Скрытые транзакции, каждый оператор, такой как INSERT, UPDATE или DELETE выполняется в транзакции. Неявными транзакциями можно управлять и об этом мы поговорим в разделе 4.1.2;
   • Явные транзакции объявленные пользователем – операторы, сгруппированные в BEGIN TRANSACTION и COMMIT TRANSACTION.

   Очень важно понимать, что транзакции необходимы только при модификации данных, т.е. использовании операторов INSERT, UPDATE или DELETE. Простая выборка SELECT не изменяет данных, и запоминать или откатывать нечего. Нет, выполнять операции выборки в транзакции можно, но если транзакция не изменяет данные, то незачем ее вообще начинать.

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

   • ALTER DATABASE
   • BUCKUP LOG
   • CREATE DATABASE
   • DROP DATABASE
   • RECONFIGURE
   • RESTORE DATABASE
   • RESTORE LOG
   • UPDATE STATISTICS

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

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

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

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

   Начало транзакции в MS SQL Server имеет следующий синтаксис:

   Опция Transaction name указывает имя транзакции определенное пользователем. Опция WITH MARK указывает, что транзакция маркирована в журнале транзакций.

   По завершению транзакции, изменения необходимо запомнить в базе данных. Для этого используется команда COMMIT:

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

   Очень важно понимать, если начата транзакция и изменены какие-то записи, то эти записи блокируются, пока транзакция не будет завершена. Давайте посмотрим это на примере, заодно познакомимся с самой командой. Выполните следующие команды в Query Analyzer:

   Теперь откройте еще одну копию программы или установите новое соединение, выбрав меню File/Connect (Файл/Соединиться). В новом окне напишем и выполним следующий запрос:

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

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

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

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

   Теперь откатываем транзакцию, выполняя команду ROLLBACK TRANSACTION. Снова выполняем запрос SELECT и видим, что данные вернулись на родину. Транзакция удачно отклонена и физического удаления из базы данных не произошло. Почему мы в этот раз без проблем смогли просмотреть таблицу, а из другой сессии просмотр изменяемой в транзакции таблицы не доступен? Блокировки происходит для всех сессий кроме той, которая выполняет транзакцию. В листинге 2.8 показан весь код эксперимента с подробными комментариями.

   Листинг 2.8. Пример эксперимента с удалением данных в транзакции

   Все эти команды нужно выполнять в одном и том же окне. К тому же, если в одном окне (сессии) вы начали транзакцию, то именно в этом окне вы должны ее завершить (COMMIT) или откатить (ROLLBACK).

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

   Теперь посмотрим еще один пример в листинге 2.9.

   Листинг 2.9. Пример работы с транзакциями

   Тут достаточно много действий, поэтому давайте их рассмотрим поэтапно:

   1. Начинаем транзакцию;
   2. Добавляем запись о покупке товара с названием Картофель;
   3. Обновить цену картофеля, увеличив ее до 15 рублей;
   4. Завершаем транзакцию, запоминая изменения;
   5. Обновляем цену до 17 руб.;
   6. Откатываем транзакцию;
   7. Просматриваем содержимое таблицы.

   Что произошло с содержимым таблицы? Запись о картофеле добавлена, а значит, все что было до запоминания изменений (шаг 4) выполнено удачно. А вот цена равна 17-ти рублям. Почему? Неужели на шаге 6 мы не откатили изменение цены? Да, отката не произошло, потому что новая транзакция не начиналась. На шаге 1 мы начали транзакцию, а на шаге 4 завершили. Новая транзакция не начиналась, а значит откатывать нечего и шаг 6 завершиться ошибкой:

   The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

   Запрос ROLLBACK TRANSACTION не имеет соответствующего BEGIN TRANSACTION.

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

   Для каждого оператора BEGIN TRANSACTION должен быть только один оператор COMMIT TRANSACTION или ROLLBACK TRANSACTION.

   А что если внутри транзакции начать новую транзакцию? Результат неожиданный и давайте его увидим на примере (см. листинг 2.10).

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

   Логика запроса следующая:

   • Начать транзакцию;
   • Вставить строку;
   • Начать транзакцию;
   • Обновить таблицу;
   • Откатить транзакцию;
   • Запомнить изменения.

   По логике вещей, на шаге 5 мы должны были откатить вторую транзакцию (т.е. изменение таблицы), а на шаге 6 запоминаем транзакцию 1, в которой происходит добавление записи. Посмотрите содержимое таблицы. Ни добавления, ни тем более изменения. Почему? Если посмотреть сообщения, которые выдал сервер, то вы увидите, что на шаге 6 произошла ошибка о том, что нет соответствующего начала транзакции и нечего начинать. Получается, что оператор ROLLBACK TRANSACTION откатывает все начатые транзакции.

   Но это не значит, что невозможно использовать вложенные транзакции. Просто откатывать транзакции нельзя на один шаг назад. Если заменить оператор ROLLBACK TRANSACTION на COMMIT, то ошибки не будет.

   Посмотрим на листинг 2.11. В нем показан такой же пример, но с именованными транзакциями и без отката.

   Листинг 2.11. Использование вложенных транзакций

   В данном примере после операторов BEGIN TRANSACTION и COMMIT TRANSACTION указывается имя T1 и T2. Таким образом, мы идентифицируем транзакции и завершаем их в обратном порядке объявлению.

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

   Минимум, что необходимо указать – это сам оператор и имя точки сохранения. Например, следующий оператор создает точку сохранения с именем point1:

   В листинге 2.12 показан пример, в котором наконец-то создаем строку, изменяем ее и откатываем только изменение, а не всю транзакцию, вместе с добавлением строки.

   Листинг 2.12. Откат до определенной точки

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

   1. Начинаем транзакцию;
   2. Добавляем строку;
   3. С помощью оператора SAVE TRAN сохраняем состояние таблицы. Точнее сказать, ставим точку в журнале, ведь пока все изменения происходят только в журнале транзакций;
   4. Обновляем цену последней добавленной строки;
   5. Восстанавливаем состояние таблицы на точку сохранения, установленную на третьем шаге. В этот момент из журнала транзакций удаляется запись о необходимости обновить цену, а остается только запись о необходимости добавить строку;
   6. Запоминаем изменения, а в журнале транзакций находиться только добавление строки и именно это сохраняется в таблице товаров.

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

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

   Транзакции

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

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

   В этой статье показано, как проблемы, связанные с одновременным конкурентным доступом, можно решить посредством транзакций. Здесь дается вводное представление о свойствах транзакций, называемых свойствами ACID (Atomicity, Consistency, Isolation, Durability — атомарность, согласованность, изолированность, долговечность), обзор инструкций языка Transact-SQL, применяемых для работы с транзакциями, и введение в журналы транзакций.

   Модели одновременного конкурентного доступа

   Компонент Database Engine поддерживает две разные модели одновременного конкурентного доступа:

   пессимистический одновременный конкурентный доступ;

   оптимистический одновременный конкурентный доступ.

   В модели пессимистического одновременного конкурентного доступа для предотвращения одновременного доступа к данным, которые используются другим процессом, применяются блокировки. Иными словами, система баз данных, использующая модель пессимистического одновременного конкурентного доступа, предполагает, что между двумя или большим количеством процессов в любое время может возникнуть конфликт и поэтому блокирует ресурсы (строку, страницу, таблицу), как только они потребуются в течение периода транзакции. Модель пессимистического одновременного конкурентного доступа устанавливает блокировку с обеспечением разделяемого доступа, иначе немонопольную блокировку (shared lock) на считываемые данные, чтобы никакой другой процесс не мог изменить эти данные. Кроме этого, механизм пессимистического одновременного конкурентного доступа устанавливает монопольную блокировку (exclusive lock) на изменяемые данные, чтобы никакой другой процесс не мог их считывать или модифицировать.

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

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

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

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

   Неявная транзакция — задает любую отдельную инструкцию INSERT, UPDATE или DELETE как единицу транзакции.

   Явная транзакция — обычно это группа инструкций языка Transact-SQL, начало и конец которой обозначаются такими инструкциями, как BEGIN TRANSACTION, COMMIT и ROLLBACK.

   Понятие транзакции лучше всего объяснить на примере. Допустим, в базе данных SampleDb сотруднику «Василий Фролов» требуется присвоить новый табельный номер. Этот номер нужно одновременно изменить в двух разных таблицах. В частности, требуется одновременно изменить строку в таблице Employee и соответствующие строки в таблице Works_on. Если обновить данные только в одной из этих таблиц, данные базы данных SampleDb будут несогласованны, поскольку значения первичного ключа в таблице Employee и соответствующие значения внешнего ключа в таблице Works_on не будут совпадать. Реализация этой транзакции посредством инструкций языка Transact-SQL показана в примере ниже:

   Согласованность данных, обрабатываемых в примере, можно обеспечить лишь в том случае, если выполнены обе инструкции UPDATE либо обе не выполнены. Успех выполнения каждой инструкции UPDATE проверяется посредством глобальной переменной @@error. В случае ошибки этой переменной присваивается отрицательное значение и выполняется откат всех выполненных на данный момент инструкций транзакции.

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

   Свойства транзакций

   Транзакции обладают следующими свойствами, которые все вместе обозначаются сокращением ACID (Atomicity, Consistency, Isolation, Durability):

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

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

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

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

   Инструкции Transact-SQL и транзакции

   Для работы с транзакциями язык Transact-SQL предоставляет некоторые инструкции. Инструкция BEGIN TRANSACTION запускает транзакцию. Синтаксис этой инструкции выглядит следующим образом:

   В параметре transaction_name указывается имя транзакции, которое можно использовать только в самой внешней паре вложенных инструкций BEGIN TRANSACTION/COMMIT или BEGIN TRANSACTION/ROLLBACK. В параметре @trans_var указывается имя определяемой пользователем переменной, содержащей действительное имя транзакции. Параметр WITH MARK указывает, что транзакция должна быть отмечена в журнале. Аргумент description — это строка, описывающая эту отметку. В случае использования параметра WITH MARK требуется указать имя транзакции.

   Инструкция BEGIN DISTRIBUTED TRANSACTION запускает распределенную транзакцию, которая управляется Microsoft Distributed Transaction Coordinator (MS DTC — координатором распределенных транзакций Microsoft). Распределенная транзакция — это транзакция, которая используется на нескольких базах данных и на нескольких серверах. Поэтому для таких транзакций требуется координатор для согласования выполнения инструкций на всех вовлеченных серверах. Координатором распределенной транзакции является сервер, запустивший инструкцию BEGIN DISTRIBUTED TRANSACTION, и поэтому он и управляет выполнением распределенной транзакции.

   Инструкция COMMIT WORK успешно завершает транзакцию, запущенную инструкцией BEGIN TRANSACTION. Это означает, что все выполненные транзакцией изменения фиксируются и сохраняются на диск. Инструкция COMMIT WORK является стандартной формой этой инструкции. Использовать предложение WORK не обязательно.

   Язык Transact-SQL также поддерживает инструкцию COMMIT TRANSACTION, которая функционально равнозначна инструкции COMMIT WORK, с той разницей, что она принимает определяемое пользователем имя транзакции. Инструкция COMMIT TRANSACTION является расширением языка Transact-SQL, соответствующим стандарту SQL.

   В противоположность инструкции COMMIT WORK, инструкция ROOLBACK WORK сообщает о неуспешном выполнении транзакции. Программисты используют эту инструкцию, когда они полагают, что база данных может оказаться в несогласованном состоянии. В таком случае выполняется откат всех произведенных инструкциями транзакции изменений. Инструкция ROOLBACK WORK является стандартной формой этой инструкции. Использовать предложение WORK не обязательно. Язык Transact-SQL также поддерживает инструкцию ROLLBACK TRANSACTION, которая функционально равнозначна инструкции ROOLBACK WORK, с той разницей, что она принимает определяемое пользователем имя транзакции.

   Инструкция SAVE TRANSACTION устанавливает точку сохранения внутри транзакции. Точка сохранения (savepoint) определяет заданную точку в транзакции, так что все последующие изменения данных могут быть отменены без отмены всей транзакции. (Для отмены всей транзакции применяется инструкция ROLLBACK.) Инструкция SAVE TRANSACTION в действительности не фиксирует никаких выполненных изменений данных. Она только создает метку для последующей инструкции ROLLBACK, имеющей такую же метку, как и данная инструкция SAVE TRANSACTION.

   Использование инструкции SAVE TRANSACTION показано в примере ниже:

   Единственной инструкцией, которая выполняется в этом примере, является первая инструкция INSERT. Для третьей инструкции INSERT выполняется откат с помощью инструкции ROLLBACK TRANSACTION b, а для двух других инструкций INSERT будет выполнен откат инструкцией ROLLBACK TRANSACTION a.

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

   Как вы уже знаете, каждая инструкция Transact-SQL всегда явно или неявно принадлежит к транзакции. Для удовлетворения требований стандарта SQL компонент Database Engine предоставляет поддержку неявных транзакций. Когда сеанс работает в режиме неявных транзакций, выполняемые инструкции неявно выдают инструкции BEGIN TRANSACTION. Это означает, что для того чтобы начать неявную транзакцию, пользователю или разработчику не требуется ничего делать. Но каждую неявную транзакцию нужно или явно зафиксировать или явно отменить, используя инструкции COMMIT или ROLLBACK соответственно. Если транзакцию явно не зафиксировать, то все изменения, выполненные в ней, откатываются при отключении пользователя.

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

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

   Начало явной транзакции помечается инструкцией BEGIN TRANSACTION, а окончание — инструкцией COMMIT или ROLLBACK. Явные транзакции можно вкладывать друг в друга. В таком случае, каждая пара инструкций BEGIN TRANSACTION/COMMIT или BEGIN TRANSACTION/ROLLBACK используется внутри каждой такой пары или большего количества вложенных транзакций. (Вложенные транзакции обычно используются в хранимых процедурах, которые сами содержат транзакции и вызываются внутри другой транзакции.) Глобальная переменная @@trancount содержит число активных транзакций для текущего пользователя.

   Инструкции BEGIN TRANSACTION, COMMIT и ROLLBACK могут использоваться с именем заданной транзакции. (Именованная инструкция ROLLBACK соответствует или именованной транзакции, или инструкции SAVE TRANSACTION с таким же именем.) Именованную транзакцию можно применять только в самой внешней паре вложенных инструкций BEGIN TRANSACTON/COMMIT или BEGIN TRANSACTION/ROLLBACK.

   Журнал транзакций

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

   Компонент Database Engine сохраняет все эти записи, в особенности значения до и после транзакции, в одном или более файлов, которые называются журналами транзакций (transaction log). Для каждой базы данных ведется ее собственный журнал транзакций. Таким образом, если возникает необходимость отмены одной или нескольких операций изменения данных в таблицах текущей базы данных, компонент Database Engine использует записи в журнале транзакций, чтобы восстановить значения столбцов таблиц, которые существовали до начала транзакции.

   Журнал транзакций применяется для отката или восстановления транзакции. Если в процессе выполнения транзакции еще до ее завершения возникает ошибка, то система использует все существующие в журнале транзакций исходные значения записей (которые называются исходными образами записей (before image)), чтобы выполнить откат всех изменений, выполненных после начала транзакции. Процесс, в котором исходные образы записей из журнала транзакций используются для отката всех изменений, называется операцией отмены записей (undo activity).

   В журналах транзакций также сохраняются преобразованные образы записей (after image). Преобразованные образы — это модифицированные значения, которые применяются для отмены отката всех изменений, выполненных после старта транзакции. Этот процесс называется операцией повторного выполнения действий (redo activity) и применяется при восстановлении базы данных.

   Каждой записи в журнале транзакций присваивается однозначный идентификатор, называемый порядковым номером журнала транзакции (log sequence number — LSN). Все записи журнала, являющиеся частью определенной транзакции, связаны друг с другом, чтобы можно было найти все части этой транзакции для операции отмены или повтора.