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

Привет, посетитель сайта 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. Второй подход или второй способ изоляции основан на версионности данных. СУБД создает новую версию строки для транзакции при каждом изменении данных строки. С этой новой версией продолжает работать та транзакция, которая ее создала, но любая другая транзакция видит строку в том, виде, в котором она была зафиксирована. Этот способ гораздо быстрее первого, но требует гораздо большего объема оперативной памяти, так как «новые версии строк» хранятся в оперативной памяти до тех пор, пока они не будут подтверждены.
3. Repeatable read (повторяемость чтения). Уровень изоляции транзакции Repeatable read или повторяемость чтения делает так, что транзакция, изменившая данные, не видит своих изменений, до тех пор, пока они не будут подтверждены. При этом никакая другая транзакция не сможет вносить изменения, пока не будет завершена первая транзакция.
4. Serializable (упорядочиваемость). Уровень изоляции транзакции Serializable или упорядочиваемость – самый высокий уровень изолированности транзакций, описанный в стандарте SQL-92. На этом уровне транзакции не подвержены проблемам фантомного чтения, так как уровень Serializable (упорядочиваемость) делает их, можно сказать, последовательными. На данном уровне транзакции максимально изолированы друг от друга.

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