Транзакции

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

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

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

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

Может сначала добавлять деньги на новый аккаунт, а потом снимать? Ну тогда в случае ошибки мы подарим счастливчику деньги и не спишем, а это уже невыгодно.

Нужно гарантировать, что обе операции будут выполнены полностью или не выполнены вовсе, если в процессе произошла ошибка.

Итак, транзакция – это какая-то фигня, которая гарантирует, что все команды выполнены или не выполнены. И мы должны как-то сказать, где начинается эта фигня, а где заканчивается. Как я уже сказал в Oracle любое обновление данных кажется создает эту фигню, которую все называют транзакцией, но можно и вручную указать начало с помощью команды START TRANSACTION. В MySQL для этого используется BEGIN WORK или START TRANSACTION, а в MS SQL это BEGIN TRANSACTION. Не знаю, зачем каждая база данных использует свой вариант команды, но вот такая печалька. Я все примеры показываю на MySQL, поэтому везде, где я буду использовать START TRANSACTION, вы должны заменять эту команду на BEGIN TRANSACTION, если работаете с MS SQL Server.

В MySQL транзакцию можно начать с помощью BEGIN WORK или START TRANSACTION, но в чем разница? Согласно документации MySQL разницы нет, а BEGIN WORK – это псевдоним к START TRANSACTION.

Итак, наша логика перевода денег должна выглядеть так:

START TRANSACTION;
  СНЯТЬ ДЕНЬГИ С АККАУНТА 1; 
  ДОБАВИТЬ ДЕНЬГИ НА АККАУНТ 2;
COMMIT или ROLLBACK

Если мы выполним COMMIT, то оба изменения попадут в базу данных. Если выполнить ROLLBACK, то все изменения будут потеряны.

Это и все, всего три команды:

- начать транзакцию START TRANSACTION для MySQL или BEGIN TRANSACTION для MS SQL Server

- зафиксировать изменения COMMIT

- отменить изменения ROLLBACK

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

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

START TRANSACTION;

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

INSERT team (name) VALUES ('sdfsdf');

Проверим, вставилась ли запись или нет, просто выбирая все записи из таблицы:

SELECT * FROM team;

Вы должны увидеть новую запись с именем команды 'sdfsdf'.

Теперь подключимся к базе данных еще раз, выбираем Database - Connect to Database второй раз.

В первой (слева) закладке я начал транзакцию и вставил запись, а во второй закладке давайте тоже попробуем выполнить SELECT запрос и посмотрим на содержимое таблицы team. Вы не должны увидеть команды с названием sdfsdf.

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

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

Выполните ROLLBACK и теперь попробуйте снова посмотреть на содержимое таблиц. На какой закладке вы не выполняли бы запрос, вы больше изменения не увидите, они полностью отменены.

Попробуем еще раз выполнить те же команды, но на этот раз не будем откатывать изменения, а зафиксируем их с помощью COMMIT:

START TRANSACTION;
INSERT team (name) VALUES ('sdfsdf');
COMMIT

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

Уровни изоляции транзакций

Уровень изоляции можно устанавливать для базы данных и для текущей сессии. Для базы данных меняется в настройках и тут скорей всего понадобиться перезапускать сервер базы данных. Опять же, я все базы данных знать не могу, поэтому говорить не буду. Мы будем менять уровень изоляции только для текущей сессии (соединение к базе данных) и рассмотрим только mysql. Тут нужно выполнить команду:

SET SESSION TRANSACTION ISOLATION LEVEL УРОВЕНЬ ИЗОЛЯЦИИ;

Если вы работаете с MS SQL Server, то команда выглядит точно также, только слово SESSION нужно опустить, то есть в нем выполняем команду:

SET TRANSACTION ISOLATION LEVEL УРОВЕНЬ ИЗОЛЯЦИИ;

Посмотреть текущий уровень можно с помощью команды:

SHOW VARIABLES LIKE 'tx_isolation';

Это команда не совсем SQL

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

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

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

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

По умолчанию в MySQL используется REPEATABLE READ – работает почти как READ COMMITTED, только еще более жестко, потому что транзакция влияет еще и на чтение данных. Например, вы начинаете транзакцию и читаете данные из базы данных:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM team;

Теперь в другом окне попробуйте вставить новую строку и вернитесь в первое окно с транзакцией и попробуйте прочитать данные. При изоляции READ COMMITTED вы увидите новую строку, а при REPEATABLE READ – нет. В этом режиме гарантируется, что при первом чтении вы как бы фиксируете данные и после этого повторные чтения будут возвращать тот же результат. Именно чтение фиксирует данные. Подчеркиваю, именно "как бы" фисксируете, потому что реальной фиксации нет.

Этот уровень изоляции значит, что если сделать что-то типа:

СОЕДИНЕНИЕ 1: Начинаем транзакцию
СОЕДИНЕНИЕ 2: Вставляет данные 1
СОЕДИНЕНИЕ 1: Выбирает данные 
СОЕДИНЕНИЕ 2: Вставляет данные 2

В этом случае вставленные данные 1 будут видны в соединении/сессии 1. А вот вторая вставка видна не будет, потому что соединение 1 к этому моменту уже прочитала данные, зафиксировало состояние и последующие чтения должны возвращать тот же результат.

В остальном REPEATABLE READ – работает также, как и READ COMMITTED. Если где-то внутри транзакции вставлены или изменены данные, вы их не увидите.

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

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

Давайте посмотрим его на практике. В одном окне выполняем вставку в транзакции:

START TRANSACTION;
INSERT team (name) VALUES ('sdfsdf');

Данные еще не зафиксированы и по умолчанию мы их не должны видит. Но давайте в другой закладке с другим подключением выполним:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM team;

Отлично, мы увидели данные, которые не подтверждены.

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

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

Это еще более жестокий вровень, который не разрешает другим соединениям/сессиям модифицировать данные, потому что SELECT создает range lock.

Допустим в одной сессии мы начинаем транзакцию и выбираем данные из базы:

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM team;

Теперь попробуйте в другой сессии вставить новую запись в таблицу.

INSERT TEAM VALUES ('test')

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

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

Этот уровень не поддерживается в MySQL. При начале транзакции данные как бы фиксируются. В отличии от REPEATABLE READ нам не нужно читать данные, чтобы зафиксировать что-то, достаточно просто начать транзакцию, и мы будем видеть данные на момент старта.

Мертвые блокировки DEADLOCK

Вернем назад уровень изоляции repeatable read:

SET session transaction isolation level repeatable read;

Теперь в одном окне создаем транзакцию и выполняем обновление таблицы Team:

START TRANSACTION;
    update team 
    set name = 'dfgerdg' 
    where teamid = 1;

В результате обновляемая запись в таблице team будет заблокирована.

В другом окне с другой сессией тоже создаем транзакцию и обновляем таблицу Person:

START TRANSACTION;
    update person 
    set firstname = 'sdfsdfds' 
    where personid = 1;

В результате обновляемая запись в таблице person будет заблокирована.

Каждая транзакция блокирует по одной записи. А что если теперь в первой транзакции попытаться обновить заблокированную запись в таблице Person:

update person 
set firstname = 'sdfsdfds' 
where personid = 1;

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

А что если теперь второе окно попытается обновить team:

update team set name = 'dfgerdg' where teamid = 1;

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

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

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