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

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)), затем после каждого запроса выполняем проверку наличия ошибок, если же есть ошибки немедленно выполняем откат всех изменений (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);
}

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

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

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

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

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

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

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

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

Источник: https://webformyself.com/tranzakcii-v-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 не существует механизма вложенных транзакций. Одно соединение с БД — одна транзакция. Новая транзакция в пределах одного соединения может начаться только после завершения предыдущей.

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

Блокировки

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

• Разделяемые (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 подтвердить транзакцию до их выполнения.

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

ACID:

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

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

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

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

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

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

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

Результат. Транзакция 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;

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 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”.

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

• Главная
• Прочее

Оценка статьи:

(нет голосов)

Загрузка...

Поделиться с друзьями: