Транзакции в mysql — для студента

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

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

alt

Узнай стоимость своей работы

Бесплатная оценка заказа!

Оценим за полчаса!

Поэтому в данном уроке мы с Вами рассмотрим транзакции, которые обеспечат 100% выполнение всех запросов в базе данных либо не выполнение их вообще при возникновении ошибок.

Транзакции в mysql - Для студентаТранзакции в mysql - Для студента

1. Постановка задачи

Для сегодняшнего урока я подготовил вот такую HTML страницу:

Транзакции в mysql - Для студента Транзакции в mysql - Для студента

  • Бесплатный курс по PHP программированию
  • Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
  • В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

Как Вы видите — это всего одна страница интернет магазина. А именно страница отображения товаров из каталога – центральный блок, и корзина, в которой уже содержится несколько товаров.

На самом деле – это статическая страница и конечно сам механизм добавления товаров в корзину не работает, так как тема урока совсем другая.

Но тем не менее три товара, которые отображены в корзине – сохранены в сессии и если нажать по ссылке “Оформить заказ”, то заказ на покупку данных товаров будет оформлен.

alt

Узнай стоимость своей работы

Бесплатная оценка заказа!
Читайте также:  Объектно-ориентированные СУБД - база данных и система управления

Оценим за полчаса!

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

Итак, весь контент данного тестового сайта, содержится в базе данных, а именно в таблице magazine. Также в базе содержатся еще три таблицы – otpravleno, user_cash, zakazi.

Таблица otpravleno – используется для оплаченных и как бы отправленных заказов (опять же виртуально), таблица user_cash – хранит виртуальные средства пользователя, как будто у нас на сайте действуют виртуальные денежные средства, которыми можно оплачивать товар (это нужно просто для примера) и наконец, таблица zakazi –все заказы оформленные на сайте. То есть процесс оформления заказа сводится к последовательному выполнению нескольких SQL запросов к базе данных: добавление в таблицу zakazi – данных об оформленном заказе, снятие денежных средств у пользователя за купленные товары (то есть изменение данных в таблице user_cash), добавление в таблицу otpravleno данных об оплаченных товарах и наконец изменение количества товаров на складе(то есть уменьшение количества товаров в таблице magazine, что были куплены). Конечно, в реальном магазине такие запросы не выполняются, но для данного урока такой пример будет как раз кстати.

Теперь давайте кратко рассмотрим исходный код данного сайта.

Итак, основа логики сайта – это файл functions.php, в котором описаны все необходимые для работы сайта функции.

//Подключаемся к серверу базы данных $db = mysqli_connect('localhost','Viktor','1234','for_cart'); exit('Error'.mysqli_error()); //Устанавливаем кодировку запросов mysqli_query($db,»SET NAMES cp1251″);function get_goods($db) { //Запрос на выборку все товаров $sql = «SELECT * FROM magazine»; $result = mysqli_query($db,$sql); for($i = 0;$i $value) {
$sql1 = «INSERT INTO zakazi (title) VALUES ('$value')»;
$result1 = mysqli_query($db,$sql1);

if(!$result1) {
mysqli_rollback($db);
return;
}

$sql2 = «UPDATE user_cash SET cash=cash-(SELECT price FROM magazine WHERE id='$key')»;
$result2 = mysqli_query($db,$sql2);
if(!$result2) {
mysqli_rollback($db);
return;
}

$sql3 = «INSERT INTO otpravleno (ti1tle) VALUES ('$value')»;
$result3 = mysqli_query($db,$sql3);
if(!$result3) {
mysqli_rollback($db);
return;
}

$sql4 = «UPDATE magazine SET kolvo=kolvo-1 WHERE id='$key'»;
$result4 = mysqli_query($db,$sql4);
if(!$result4) {
mysqli_rollback($db);
return;
}
}

mysqli_commit($db);
}

mysqli_autocommit($db,FALSE); foreach($_SESSION['cart'] as $key=>$value) { $sql1 = «INSERT INTO zakazi (title) VALUES ('$value')»; $result1 = mysqli_query($db,$sql1); $sql2 = «UPDATE user_cash SET cash=cash-(SELECT price FROM magazine WHERE id='$key')»; $result2 = mysqli_query($db,$sql2); $sql3 = «INSERT INTO otpravleno (ti1tle) VALUES ('$value')»; $result3 = mysqli_query($db,$sql3); $sql4 = «UPDATE magazine SET kolvo=kolvo-1 WHERE id='$key'»; $result4 = mysqli_query($db,$sql4);

Как Вы видите, выполняем все, о чем я говорил выше, то есть отключаем автоподтверждение выполняемых запросов (mysqli_autocommit($db,FALSE)), затем после каждого запроса выполняем проверку наличия ошибок, если же есть ошибки немедленно выполняем откат всех изменений (mysqli_rollback($db)). Здесь также важно выполнять выход их функции, в нашем случае простой возврат (return), так как если запрос выполнен с ошибкой, нет смысла продолжать выполнение кода функции zakaz. Если же все запросы выполнены успешно, подтверждаем транзакцию (mysqli_commit($db);).

Перед тем как проверить, я предлагаю функцию zakaz описать другим способом, а именно, использовать блоки try-catch для проверки выполненных запросов. Напомню, что блоки try-catch – реализуют механизм обработки исключений языка PHP.

function zakaz($db) {

mysqli_autocommit($db,FALSE);

try {
foreach($_SESSION['cart'] as $key=>$value) {
$sql1 = «INSERT INTO zakazi (title) VALUES ('$value')»;
$result1 = mysqli_query($db,$sql1);

if(!$result1) {
throw new Exception();
}

$sql2 = «UPDATE user_cash SET cash=cash-(SELECT price FROM magazine WHERE id='$key')»;
$result2 = mysqli_query($db,$sql2);
if(!$result2) {
throw new Exception();
}

$sql3 = «INSERT INTO otpravleno (ti1tle) VALUES ('$value')»;
$result3 = mysqli_query($db,$sql3);
if(!$result3) {
throw new Exception();
}

$sql4 = «UPDATE magazine SET kolvo=kolvo-1 WHERE id='$key'»;
$result4 = mysqli_query($db,$sql4);
if(!$result4) {
throw new Exception();
}

}
}
catch(Exception $e) {
mysqli_rollback($db);
}

mysqli_commit($db);
}

mysqli_autocommit($db,FALSE); foreach($_SESSION['cart'] as $key=>$value) { $sql1 = «INSERT INTO zakazi (title) VALUES ('$value')»; $result1 = mysqli_query($db,$sql1); $sql2 = «UPDATE user_cash SET cash=cash-(SELECT price FROM magazine WHERE id='$key')»; $result2 = mysqli_query($db,$sql2); $sql3 = «INSERT INTO otpravleno (ti1tle) VALUES ('$value')»; $result3 = mysqli_query($db,$sql3); $sql4 = «UPDATE magazine SET kolvo=kolvo-1 WHERE id='$key'»; $result4 = mysqli_query($db,$sql4);

Смотрите, весь код цикла мы заключаем в блок try, а в каждом блоке if() мы с Вами генерируем исключение.

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

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

Теперь давайте намеренно допустим ошибку в одном из запросов и выполним наш скрипт.

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

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

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

Транзакции в mysql - Для студента

  1. Бесплатный курс по PHP программированию
  2. Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
  3. В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Источник: https://webformyself.com/tranzakcii-v-mysql/

Введение в транзакции в MySQL

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

Любая транзакция либо выполняется полностью, либо не выполняется вообще.

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

При старте транзакции все последующие изменения сохраняются во временном хранилище. В случае выполнения COMMIT, все изменения, выполненные в рамках одной транзакции, сохранятся в физическую БД. В случае выполнения ROLLBACK произойдет откат и все изменения, выполненные в рамках этой транзакции, не сохранятся.

В MySQL транзакции поддерживаются только таблицами innoDB. Таблицы MyISAM транзакции не поддерживают. В innoDB по умолчанию включен autocommit, это значит, что по умолчанию каждый запрос эквивалентен одной транзакции.

Транзакция начинается со специального запроса «START TRANSACTION», либо «BEGIN». Чтобы закончить транзакцию, нужно либо зафиксировать изменения (запрос COMMIT), либо откатить их (запрос ROLLBACK).

Пример с COMMIT:

set autocommit=0; //отключаем autocommit Start transaction; (также, можно написать BEGIN; ) …какие-то действий с БД (insert, update,delete…) commit; //Фиксация действий, запись их в физическую БД

Пример с ROLLBACK:

set autocommit=0; //отключаем autocommit Start transaction; …какие-то действия с БД (insert, update,delete…) rollback; // отменяем серию действий, не производим запись в физическую БД

В MySQL не существует механизма вложенных транзакций. Одно соединение с БД — одна транзакция. Новая транзакция в пределах одного соединения может начаться только после завершения предыдущей.

Для некоторых операторов нельзя выполнить откат с помощью ROLLBACK. Это операторы языка определения данных (Data Definition Language — DDL). Сюда входят запросы CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME.

Следующие операторы неявно завершают транзакцию (как если бы перед их выпол­нением был выдан COMMIT):

  • ALTER TABLE
  • DROP DATABASE
  • LOAD MASTER DATA
  • SET AUTOCOMMIT = 1
  • BEGIN
  • DROP INDEX
  • LOCK TABLES
  • START TRANSACTION
  • CREATE INDEX
  • DROP TABLE
  • RENAME TABLE
  • TRUNCATE TABLE

Обратите внимание, что в случае SQL ошибки, транзакция сама по себе не откатится. Обычно ошибки обрабатываются уже с помощью sql wrapper’ов в самом приложении, таких как PHP PDO например. Если вы захотите откатывать изменения в случае ошибки прямо в MySQL, можно создать специальную процедуру и уже в ней выполнять ROLLBACK в обработчике:

CREATE PROCEDURE prc_test() BEGIN   DECLARE EXIT HANDLER FOR SQLEXCEPTION   BEGIN     ROLLBACK; //Вот здесь откатываем транзакцию в случае ошибки   END;   START TRANSACTION;     INSERT INTO tmp_table VALUES ('null');   COMMIT; END; CALL prc_test();

Но этот способ скорее просто для ознакомления, а не руководство к действию. Почему? Я крайне не рекомендую так поступать, так как в основном ошибки базы данных обрабатываются с помощью SQL оберток на стороне приложения, таких как PHP PDO например, чтобы оттуда полностью управлять транзакциями.

Рассмотрим практический пример: есть 2 таблицы, пользователи — users и информация о пользователях — user_info. Представим, что нам нужно либо выполнить 3 запроса к базе данных, либо не выполнять их вообще, так как иначе это приведет к сбоям в работе приложения.

Start transaction;
INSERT INTO user (id, nik) VALUES (1, 'nikola');
INSERT INTO user_info (id, id_user, item_name, item_value) VALUES (1, 1, 'Имя', 'Николай');
INSERT INTO user_info (id, id_user, item_name, item_value) VALUES (2, 1, 'Возраст', '24');
commit;

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

Представим, что во время выполнения этой транзакции, другой пользователь создал вторую параллельную транзакцию и сделал запрос SELECT * FROM user после того, как в нашей транзакции был выполнен первый запрос «INSERT INTO user (id, nik) VALUES (1, ‘nikola’)».

Что увидит пользователь второй транзакции? Сможет ли он увидеть вставленную запись даже тогда, когда результаты первой транзакции еще не зафиксировались (не произошел COMMIT)? Или он сможет увидеть изменения только после того, как результаты первой транзакции будут зафиксированы? Оказывается имеют место быть оба варианта. Все зависит от уровня изоляции транзакции.

У транзакций есть 4 уровня изоляции:

  • 0 — Чтение неподтверждённых данных (грязное чтение) (Read Uncommitted, Dirty Read) — самый низкий уровень изоляции. При этом уровне возможно чтение незафиксированных изменений параллельных транзакций. Как раз в этом случае второй пользователь увидит вставленную запись из первой незафиксированной транзакции. Нет гарантии, что незафиксированная транзакция будет в любой момент откачена, поэтому такое чтение является потенциальным источником ошибок.
  • 1 — Чтение подтверждённых данных (Read Committed) — здесь возможно чтение данных только зафиксированных транзакций. Но на этом уровне существуют две проблемы. В этом режиме строки, которые участвуют в выборке в рамках транзакции, для других параллельных транзакций не блокируются, из этого вытекает проблема № 1: «Неповторяемое чтение» (non-repeatable read) — это ситуация, когда в рамках транзакции происходит несколько выборок (SELECT) по одним и тем же критериям, и между этими выборками совершается параллельная транзакция, которая изменяет данные, участвующие в этих выборках. Так как параллельная транзакция изменила данные, результат при следующей выборке по тем же критериям в первой транзакции будет другой.  Проблема № 2 — «Фантомное чтение» — этот случай рассмотрен ниже.
  • 2 — Повторяемое чтение (Repeatable Read, Snapshot) —  на этом уровне изоляции так же возможно чтение данных только зафиксированных транзакций. Так же на этом уровне отсутствует проблема «Неповторяемого чтения», то есть строки, которые участвуют в выборке в рамках транзакции, блокируются и не могут быть изменены другими параллельными транзакциями. Но таблицы целиком не блокируются. Из-за этого остается проблема «фантомного чтения». «Фантомное чтение» — это когда за время выполнения одной транзакции результат одних и тех же выборок может меняться по причине того, что блокируется не вся таблица, а только те строки, которые участвуют в выборке. Это означает, что параллельные транзакции могут вставлять строки в таблицу, в которой совершается выборка, поэтому два запроса SELECT * FROM table могут дать разный результат в разное время при вставке данных параллельными транзакциями.
  • 3 — Сериализуемый (Serializable) — сериализуемые транзакции. Самый надежный уровень изоляции транзакций, но и при этом самый медленный. На этом уровне вообще отсутствуют какие либо проблемы параллельных транзакций, но за это придется платить быстродействием системы, а быстродействие в большинстве случаев крайне важно.

По умолчанию в MySQL установлен уровень изоляции № 2 (Repeatable Read). И, как я считаю, разработчики MySQL не зря сделали по умолчанию именно этот уровень, так как он наиболее удачный для большинства случаев.

С первого раза может показаться, что самый лучший вариант № 3 — он самый надежный, но на практике вы можете испытать большие неудобства из-за очень медленной работы вашего приложения.

Помните, что многое зависит не от того, насколько хорош уровень изоляции транзакций в БД, а от того, как спроектировано ваше приложение.

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

SET TRANSACTION — этот оператор устанавливает уровень изоляции следующей транзакции, глобально либо только для текущего сеанса.

  • SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

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

Вы можете также установить начальный глобальный уровень изоляции для сервера mysqld, запустив его с опцией —transaction-isolation

Источник: https://webistore.ru/sql/tranzakcii-v-mysql/

[Конспект] Про блокировки и транзакции в MySQL

Транзакции в mysql - Для студента

В этом посте я хотел бы собрать воедино информацию о блокировках и транзакциях в MySQL.

Блокировки

Типы блокировок

  • Разделяемые (shared). Блокировки чтения, возможность читать записи есть у всех клиентов, а возможности записи нет. Каждый клиент может поставить такую блокировку одновременно.
  • Монопольные (exclusive). Никто не сможет повесить другую блокировку на данные, а также выполнить изменения.

Стратегии блокировок

  • Табличные блокировки. Блокируется вся таблица, причём блокировка записи двигает блокировки чтения в очереди.
  • Блокировка строк. Более накладной вариант блокировок, поддерживается в InnoDB и Falcon.

Блокировки в InnoDB:

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

Есть два базовых типа блокировок:

  • Shared lock — совместная блокировка, позволяет другим транзакциям читать строку и ставить на нее такую же совместную блокировку, но не позволяет изменять строку или ставить исключительную блокировку.
  • Exclusive lock — исключительная блокировка, запрещает другим транзакциям блокировать строку, а также может блокировать строку как на запись, так и на чтение в зависимости от текущего уровня изоляции (о коих ниже).

Если копнуть глубже, то выяснится, что есть еще 2 типа блокировок, назовем их блокировками «о намерениях». Нельзя просто так взять и заблокировать запись в InnoDB.

Блокировки intention shared и intention exclusive являются блокировками на уровне таблицы и блокируют только создание других блокировок и операции на всей таблице типа LOCK TABLE.

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

InnoDB накладывает блокировки не на сами строки с данными, а на записи индексов. Та или иная блокировка может накладываться на:

  • Record lock — блокировка записи индекса.
  • Gap lock — блокировка промежутка между, до или после индексной записи.
  • Next-key lock — блокировка записи индекса и промежутка перед ней.

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

SELECT… LOCK IN SHARE MODE — блокирует считываемые строки на запись.

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

Если же в момент такого SELECT’а строка уже изменена другой транзакцией, но еще не зафиксирована, то запрос ждет окончания транзакции и затем читает свежие данные.

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

SELECT… FOR UPDATE — блокирует считываемые строки на чтение. Точно такую же блокировку ставит обычный UPDATE, когда считывает данные для обновления.

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

InnoDB обрабатывает взаимоблокировки откатом той транзакции, которая захватила меньше всего монопольных блокировок строк (приблизительный показатель легкости отката).

Нельзя справиться с взаимоблокировками без отката одной из транзакций, частичного либо полного.

Транзакции в MySQL

MySQL предоставляет пользователям две транзакционные подсистемы хранения данных: InnoDB и NDB Cluster. MySQL позволяет устанавливать уровень изолированности с помощью команды SЕТ TRANSACТION ISOLAТION LEVEL, которая начинает действовать со следующей транзакции. Можете настроить уровень изолированности для всего сервера в конфигурационном файле или только для своей сессии.

По умолчанию MySQL работает в режиме АUТОСОММIT. Это означает, что, пока вы не начали транзакцию явно, каждый запрос автоматически выполняется в отдельной транзакции.

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

Обычно это команды языка определения данных (Data Definition Language, DDL), которые вносят изменения в структуру таблиц, например ALТЕR TABLE, но LOCK TABLES и другие директивы также обладают этим свойством.

Если вы используете транзакционные и нетранзакционные таблицы (например, таблицы InnoDB и MyISAM) в одной транзакции, то все будет работать хорошо, пока не произойдет что-то неожиданное, откатить данные из нетранзакционных таблиц невозможно.

ACID:

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

Будучи зафиксированы, внесенные в ходе транзакции изменения становятся постоянными.

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

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

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

Проблема Описание
Lost update — потерянное обновление При одновременном изменении одного блока данных разными транзакциями одно из изменений теряется.
Dirty read — грязное чтение Чтение данных, добавленных или изменённых транзакцией, которая впоследствии не подтвердится.
Non-repeatable read — неповторяемое чтение При повторном чтении в рамках одной транзакции ранее прочитанные данные оказываются изменёнными.
Phantom read — фантомное чтение При повторном чтении в рамках одной транзакции одна и та же выборка дает разные множества строк.

Уровни изоляции:

Название Описание Реализация
READ UNCOMMITED Гарантирует только отсутствие потерянных обновлений. Если несколько параллельных транзакций пытаются изменять одну и ту же строку таблицы, то в окончательном варианте строка будет иметь значение, определенное всем набором успешно выполненных транзакций. Защита от Lost Update. В рамках транзакции Т1 накладывается разделяемая блокировка на изменяемые данные, все остальные транзакции, желающие изменить эти данные, ждут завершения Т1.
READ COMMITED Обеспечивается защита от грязного чтения, тем не менее, в процессе работы одной транзакции другая может быть успешно завершена и сделанные ею изменения зафиксированы. В итоге первая транзакция будет работать с другим набором данных. В рамках транзакции Т1 создается снимок изменяемых строк, с которым будут работать все остальные клиенты до завершения Т1. Если будут несколько изменений одних и тех же строк, то зафиксированы будут только изменения Т1.
REPEATABLE READ Читающая транзакция «не видит» изменения данных, которые были ею ранее прочитаны. При этом никакая другая транзакция не может изменять данные, читаемые текущей транзакцией, пока та не окончена. В рамках транзакции Т1 накладывается монопольная блокировка на считываемые данные, все остальные транзакции, желающие изменить эти данные, ждут завершения Т1.
SERIALIZABLE Транзакции полностью изолируются друг от друга, каждая выполняется так, как будто параллельных транзакций не существует. Параллельным транзакциям данные блокируются даже для чтения.

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

Уровни изоляции InnoDB

REPEATABLE READ

  • Согласованное чтение (SELECT) ничего не блокирует, читает строки из снимка, который создается при первом чтении в транзакции. Одинаковые запросы всегда вернут одинаковый результат.
  • Для блокирующего чтения (SELECT… FOR UPDATE/LOCK IN SHARE MODE), UPDATE и DELETE блокировка будет зависит от типа условия. Если условие уникально (WHERE id=42), то блокируется только найденная индексная запись (record lock). Если условие с диапазоном (WHERE id > 42), то блокируются весь диапазон (gap lock или next-key lock).

READ COMMITED

  • Согласованное чтение ничего не блокирует, но каждый раз происходит из свежего снимка.
  • Блокирующее чтение (SELECT… FOR UPDATE/LOCK IN SHARE MODE), UPDATE и DELETE блокирует только искомые индексные записи (record lock). Таким образом возможна вставка параллельным потоком записей в промежутки между индексами. Промежутки блокируются (gap lock) только при проверках внешних ключей и дублирующихся ключей. Также блокировки просканированных строк (record lock), не удовлетворяющих WHERE, снимаются сразу же после обработки WHERE.

READ UNCOMMITED

  • Все запросы SELECT читают в неблокирующей манере. Изменения незавершенной транзакции могут быть прочитаны в других транзакциях, а изменения эти могут быть еще и впоследствии откачены. Это так называемое «грязное чтение» (несогласованное).
    В остальном все так же, как и при READ COMMITED.

SERIALIZABLE

  • Аналогичен REPEATABLE READ, за исключением одного момента. Если autocommit выключен (а при явном старте транзакции он выключен), то все простые запросы SELECT неявно превращаются в SELECT… LOCK IN SHARE MODE, если включен — каждый SELECT идет в отдельной транзакции. Используется, как правило, для того чтобы превратить все запросы чтения в SELECT… LOCK IN SHARE MODE, если этого нельзя сделать в коде приложения.

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

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

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

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

MVCC — multiversion concurrency control

MVCC сохраняет мгновенный снимок состояния данных в определенный момент времени.

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

Применение для Repeatable Read:

  • SELECT. InnoDB должна проверить каждую строку на соответствие двум критериям.
    • Найти версию строки, по крайней мере такой же старой, как версия транзакции (то есть ее номер версии должен быть меньше номера версии транзакции или равен ему). Это гарантирует, что либо строка существовала до начала транзакции, либо транзакция создала или изменила эту строку.
    • Версия удаления строки должна быть не определена, или ее значение должно быть больше, чем версия транзакции. Это гарантирует, что строка не была удалена до начала транзакции.
  • INSERT. InnoDB записывает текущий номер версии системы вместе с новой строкой.
  • DELEТE. InnoDB записывает текущий номер версии системы как ID удаления строки.
  • UPDATE. InnoDB записывает новую копию строки, используя номер версии системы в качестве версии новой строки. Она также записывает номер версии системы как версию удаления старой строки.

Источник: https://zinvapel.github.io/prog/database/2018/10/04/mysqlc-transaction/

mysql Механизм транзакций — пример запроса— синтаксис

  1. В MySQL существует несколько типов таблиц. Это ISAM, HEAP, MyISAM, InnoDB, BDB и т.д. Транзакционный механизм поддерживают только InnoDB и BDB. Поэтому все таблицы с которыми вы хотите работать через транзакции следует переконвертировать в соответствующий тип. Можно так.
  2. По умолчанию MySQL работает в режиме autocommit.

    Это означает, что результаты выполнения любого SQL-оператора, изменяющего данные, будут сразу сохраняться.
    Режим autocommit можно отключить так:
    SET AUTOCOMMIT=0;

  3. Если необходимо переключиться из режима AUTOCOMMIT только для выполнения одной последовательности команд, то для этого можно использовать команду BEGIN или START TRANSACTION (начиная с версии mysql 4.0.

    11.

    )

  4. Пример SQL-запроса, фиксирующего количество выбранного покупаелем товара в таблице corrections и вносящего изменения в таблицу товаров goods:START TRANSACTION;
    INSERT INTO corrections SET id_goods=:id_goods, number=:number, id_orders=:id_orders
    ON DUPLICATE KEY UPDATE number = number + :number;
    UPDATE goods SET reserve = reserve + :number, available = available — :number WHERE id = :id_goods;
    COMMIT;
  5. Транзакция завершается оператором COMMIT. Изменения сохраняются. В случае ошибки в одном из запросов, изменения не будут сохранены ни в одной таблице.
  6. Если Вам требуется создать более сложный механизм фиксации изменений, используйте команды SAVEPOINT и ROLLBACK TO SAVEPOINT
  7. Следующие операторы неявно завершают транзакцию (как если бы перед их выпол­нением был выдан COMMIT):
    • ALTER TABLE
    • DROP DATABASE
    • LOAD MASTER DATA
    • SET AUTOCOMMIT = 1
    • BEGIN
    • DROP INDEX
    • LOCK TABLES
    • START TRANSACTION
    • CREATE INDEX
    • DROP TABLE
    • RENAME TABLE
    • TRUNCATE TABLE
  8. PHP PDO предлагает свои средства работы с транзакциями. Про них можно почитать здесь.

Представим, что во время выполнения 1-ой транзакции транзакции, другой пользователь создал вторую параллельную транзакцию и сделал запрос SELECT * FROM user после того, как в нашей транзакции был выполнен первый запрос «INSERT INTO user (id, nik) VALUES (1, ‘nikola’)».
Что увидит пользователь второй транзакции?

Сможет ли он увидеть вставленную запись даже тогда, когда результаты первой транзакции еще не зафиксировались (не произошел COMMIT)? Или он сможет увидеть изменения только после того, как результаты первой транзакции будут зафиксированы?

Оказывается имеют место быть оба варианта. Все зависит от уровня изоляции транзакции.

У транзакций есть 4 уровня изоляции:

  • — Чтение неподтверждённых данных (грязное чтение) (Read Uncommitted, Dirty Read) — самый низкий уровень изоляции. При этом уровне возможно чтение незафиксированных изменений параллельных транзакций. Как раз в этом случае второй пользователь увидит вставленную запись из первой незафиксированной транзакции. Нет гарантии, что незафиксированная транзакция будет в любой момент откачена, поэтому такое чтение является потенциальным источником ошибок.
  • 1 — Чтение подтверждённых данных (Read Committed) — здесь возможно чтение данных только зафиксированных транзакций. Но на этом уровне существуют две проблемы. В этом режиме строки, которые участвуют в выборке в рамках транзакции, для других параллельных транзакций не блокируются, из этого вытекает проблема № 1:
    «Неповторяемое чтение» (non-repeatable read) — это ситуация, когда в рамках транзакции происходит несколько выборок (SELECT) по одним и тем же критериям, и между этими выборками совершается параллельная транзакция, которая изменяет данные, участвующие в этих выборках. Так как параллельная транзакция изменила данные, результат при следующей выборке по тем же критериям в первой транзакции будет другой.
    Проблема № 2 — «Фантомное чтение» — этот случай рассмотрен ниже.
  • 2 — Повторяемое чтение (Repeatable Read, Snapshot) — на этом уровне изоляции так же возможно чтение данных только зафиксированных транзакций. Так же на этом уровне отсутствует проблема «Неповторяемого чтения», то есть строки, которые участвуют в выборке в рамках транзакции, блокируются и не могут быть изменены другими параллельными транзакциями. Но таблицы целиком не блокируются. Из-за этого остается проблема «фантомного чтения». «Фантомное чтение» — это когда за время выполнения одной транзакции результат одних и тех же выборок может меняться по причине того, что блокируется не вся таблица, а только те строки, которые участвуют в выборке. Это означает, что параллельные транзакции могут вставлять строки в таблицу, в которой совершается выборка, поэтому два запроса SELECT * FROM table могут дать разный результат в разное время при вставке данных параллельными транзакциями.
  • 3 — Сериализуемый (Serializable) — сериализуемые транзакции. Самый надежный уровень изоляции транзакций, но и при этом самый медленный. На этом уровне вообще отсутствуют какие либо проблемы параллельных транзакций, но за это придется платить быстродействием системы, а быстродействие в большинстве случаев крайне важно.

По умолчанию в MySQL установлен уровень изоляции № 2 (Repeatable Read). И, как я считаю, разработчики MySQL не зря сделали по умолчанию именно этот уровень, так как он наиболее удачный для большинства случаев.

С первого раза может показаться, что самый лучший вариант № 3 — он самый надежный, но на практике вы можете испытать большие неудобства из-за очень медленной работы вашего приложения.

Помните, что многое зависит не от того, насколько хорош уровень изоляции транзакций в БД, а от того, как спроектировано ваше приложение.

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

SET TRANSACTION — этот оператор устанавливает уровень изоляции следующей транзакции, глобально либо только для текущего сеанса.

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

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

Вы можете также установить начальный глобальный уровень изоляции для сервера mysqld, запустив его с опцией —transaction-isolation

Источник:http://webistore.ru/sql/tranzakcii-v-mysql/

Источник: http://fkn.ktu10.com/?q=node/9194

Транзакции и MySQL

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

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

Хорошо, если обратятся они только с чтением, а если они что-то хотят записать туда одновременно? И в этот же момент третий пользователь попытается прочитать эти данные? Как видите неуправляемый доступ к данным чреват проблемами искажения информации. Именно для этих ситуаций было создано понятие транзакций.

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

Транзакции в MySQL

Итак, как организованы транзакции в MySQL? По умолчанию, на движке InnoDB, каждая инструкция воспринимается системой как отдельная транзакция. То есть, после каждого изменения данных – происходит автоматическая запись в физическую базу данных.

  • Это можно остановить командой
  • set autocommit=0;
  • Или включить обратно
  • set autocommit=1;

Проблемы параллельного доступа

  1. При параллельном доступе к данным возникает целый ряд проблем
  2. потерянные обновления (когда вторая транзакция начинается посреди первой, а они пишут и читают одни и те же данные – возникают искажения в записи / чтении);
  3. “грязное чтение” – первая транзакция ещё не зафиксирвала изменения в физической БД, а вторая уже начала чтение (по сути более частный случай потерянных обновлений);
  4. несогласованная обработка – одна транзакция считала значение и начала его обрабатывать, вторая обновила до завершения первой транзакции, в результате искажение информации.
  5. чтение строк фантомов… (вклинивание в длительную транзакцию и искажение)
  6. Эффект фиктивных элементов несколько отличается от предыдущих транзакций тем, что здесь за один шаг выполняется достаточно много операций – чтение одновременно нескольких строк, удовлетворяющих некоторому условию.

Транзакция A дважды выполняет выборку строк с одним и тем же условием. Между выборками вклинивается транзакция B, которая добавляет новую строку, удовлетворяющую условию отбора.

Транзакция A
Время
Транзакция B
Появились строки, которых раньше не было

Выборка строк, удовлетворяющих условию.
(Отобрано n строк)
Вставка новой строки, удовлетворяющей условию.
Фиксация транзакции
Выборка строк, удовлетворяющих условию.
(Отобрано n+1 строк)
Фиксация транзакции

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

Результат. Транзакция A в двух одинаковых выборках строк получила разные результаты.

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

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

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

Read committed (фиксированное чтение) – исключается “грязное чтение”, но другим транзакциям разрешено изменять заблокированные строки.

Repeatable read (повторяемое чтение) – накладывает блокировки на обрабатываемые транзакцией строки и не допускает их изменение другими транзакциями, но не запрещает добавление новых записей, что может привести к появлению строк-фантомов. По умолчанию стоит для всех транзакций.

Serializable (сериализуемость) – самый надежный уровень изоляции, полностью исключающий взаимное влияние транзакций.

Синтаксис в MySQL будет выглядеть таким образом…

Источник: http://digital-flame.ru/2015/08/05/tranzaktsii-i-mysql/

MySQL 8.0 Reference Manual :: 13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements

13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements

START TRANSACTION [transaction_characteristic [, transaction_characteristic] …] transaction_characteristic: {
WITH CONSISTENT SNAPSHOT
| READ WRITE
| READ ONLY
}

BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

These statements provide control over use of transactions:

  • START TRANSACTION or BEGIN start a new transaction.
  • COMMIT commits the current transaction, making its changes permanent.
  • ROLLBACK rolls back the current transaction, canceling its changes.
  • SET autocommit disables or enables the default autocommit mode for the current session.

By default, MySQL runs with autocommit mode enabled. This means that, when not otherwise inside a transaction, each statement is atomic, as if it were surrounded by START
TRANSACTION and COMMIT. You cannot use ROLLBACK to undo the effect; however, if an error occurs during statement execution, the statement is rolled back.

To disable autocommit mode implicitly for a single series of statements, use the START TRANSACTION statement:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.

START TRANSACTION permits several modifiers that control transaction characteristics. To specify multiple modifiers, separate them by commas.

  • The WITH CONSISTENT SNAPSHOT modifier starts a consistent read for storage engines that are capable of it. This applies only to InnoDB. The effect is the same as issuing a START TRANSACTION followed by a SELECT from any InnoDB table. See Section 15.7.2.3, “Consistent Nonlocking Reads”. The WITH
    CONSISTENT SNAPSHOT modifier does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that permits a consistent read. The only isolation level that permits a consistent read is REPEATABLE READ. For all other isolation levels, the WITH CONSISTENT
    SNAPSHOT clause is ignored. A warning is generated when the WITH CONSISTENT SNAPSHOT clause is ignored.
  • The READ WRITE and READ
    ONLY modifiers set the transaction access mode. They permit or prohibit changes to tables used in the transaction. The READ ONLY restriction prevents the transaction from modifying or locking both transactional and nontransactional tables that are visible to other transactions; the transaction can still modify or lock temporary tables.MySQL enables extra optimizations for queries on InnoDB tables when the transaction is known to be read-only. Specifying READ ONLY ensures these optimizations are applied in cases where the read-only status cannot be determined automatically. See Section 8.5.3, “Optimizing InnoDB Read-Only Transactions” for more information.If no access mode is specified, the default mode applies. Unless the default has been changed, it is read/write. It is not permitted to specify both READ WRITE and READ ONLY in the same statement.In read-only mode, it remains possible to change tables created with the TEMPORARY keyword using DML statements. Changes made with DDL statements are not permitted, just as with permanent tables.For additional information about transaction access mode, including ways to change the default mode, see Section 13.3.7, “SET TRANSACTION Statement”.If the read_only system variable is enabled, explicitly starting a transaction with START TRANSACTION READ WRITE requires the CONNECTION_ADMIN or SUPER privilege.

Important

Many APIs used for writing MySQL client applications (such as JDBC) provide their own methods for starting transactions that can (and sometimes should) be used instead of sending a START TRANSACTION statement from the client. See Chapter 28, Connectors and APIs, or the documentation for your API, for more information.

To disable autocommit mode explicitly, use the following statement:

SET autocommit=0;

After disabling autocommit mode by setting the autocommit variable to zero, changes to transaction-safe tables (such as those for InnoDB or NDB) are not made permanent immediately. You must use COMMIT to store your changes to disk or ROLLBACK to ignore the changes.

autocommit is a session variable and must be set for each session. To disable autocommit mode for each new connection, see the description of the autocommit system variable at Section 5.1.8, “Server System Variables”.

BEGIN and BEGIN WORK are supported as aliases of START TRANSACTION for initiating a transaction. START TRANSACTION is standard SQL syntax, is the recommended way to start an ad-hoc transaction, and permits modifiers that BEGIN does not.

The BEGIN statement differs from the use of the BEGIN keyword that starts a BEGIN … END compound statement. The latter does not begin a transaction. See Section 13.6.1, “BEGIN … END Compound Statement”.

Note

Within all stored programs (stored procedures and functions, triggers, and events), the parser treats BEGIN
[WORK] as the beginning of a BEGIN …
END block. Begin a transaction in this context with START
TRANSACTION instead.

The optional WORK keyword is supported for COMMIT and ROLLBACK, as are the CHAIN and RELEASE clauses. CHAIN and RELEASE can be used for additional control over transaction completion. The value of the completion_type system variable determines the default completion behavior. See Section 5.1.8, “Server System Variables”.

The AND CHAIN clause causes a new transaction to begin as soon as the current one ends, and the new transaction has the same isolation level as the just-terminated transaction. The new transaction also uses the same access mode (READ
WRITE or READ ONLY) as the just-terminated transaction.

The RELEASE clause causes the server to disconnect the current client session after terminating the current transaction.

Including the NO keyword suppresses CHAIN or RELEASE completion, which can be useful if the completion_type system variable is set to cause chaining or release completion by default.

Beginning a transaction causes any pending transaction to be committed. See Section 13.3.3, “Statements That Cause an Implicit Commit”, for more information.

Beginning a transaction also causes table locks acquired with LOCK TABLES to be released, as though you had executed UNLOCK
TABLES. Beginning a transaction does not release a global read lock acquired with FLUSH TABLES
WITH READ LOCK.

For best results, transactions should be performed using only tables managed by a single transaction-safe storage engine. Otherwise, the following problems can occur:

  • If you use tables from more than one transaction-safe storage engine (such as InnoDB), and the transaction isolation level is not SERIALIZABLE, it is possible that when one transaction commits, another ongoing transaction that uses the same tables will see only some of the changes made by the first transaction. That is, the atomicity of transactions is not guaranteed with mixed engines and inconsistencies can result. (If mixed-engine transactions are infrequent, you can use SET
    TRANSACTION ISOLATION LEVEL to set the isolation level to SERIALIZABLE on a per-transaction basis as necessary.)
  • If you use tables that are not transaction-safe within a transaction, changes to those tables are stored at once, regardless of the status of autocommit mode.
  • If you issue a ROLLBACK statement after updating a nontransactional table within a transaction, an ER_WARNING_NOT_COMPLETE_ROLLBACK warning occurs. Changes to transaction-safe tables are rolled back, but not changes to nontransaction-safe tables.

Each transaction is stored in the binary log in one chunk, upon COMMIT. Transactions that are rolled back are not logged. (Exception: Modifications to nontransactional tables cannot be rolled back.

If a transaction that is rolled back includes modifications to nontransactional tables, the entire transaction is logged with a ROLLBACK statement at the end to ensure that modifications to the nontransactional tables are replicated.

) See Section 5.4.4, “The Binary Log”.

You can change the isolation level or access mode for transactions with the SET TRANSACTION statement. See Section 13.3.7, “SET TRANSACTION Statement”.

Rolling back can be a slow operation that may occur implicitly without the user having explicitly asked for it (for example, when an error occurs). Because of this, SHOW
PROCESSLIST displays Rolling back in the State column for the session, not only for explicit rollbacks performed with the ROLLBACK statement but also for implicit rollbacks.

Источник: https://dev.mysql.com/doc/en/commit.html

Ссылка на основную публикацию