что такое транзакция в mysql

На пути к правильным SQL транзакциям (Часть 1)

Мне часто приходилось сталкиваться с тем, что люди прекрасно понимают, что такое транзакции в базе данных и для чего они нужны, но при этом не всегда умеют ими правильно пользоваться. Безусловно, для достижения 80-го уровня сакрального знания нужно иметь не один год опыта и прочесть множество толстенных книг по SQL. Поэтому в этой статье я даже не буду пытаться описать всё, что может быть связано с транзакциями в MS SQL. Я хочу затронуть один простой, но очень важный вопрос, который разработчики часто упускают из вида – уровни изоляции транзакций.
Несмотря на то, что тема очень проста, во многих источниках она освящается плохо – информации либо очень мало, либо очень много. Т.е. прочитав 5-6 кратких теоретических определений невозможно их применить на практике. Для уверенного понимания предмета статьи нужно обращаться к специализированной литературе, но там информации на столько много, что далеко не каждый может уделить необходимое время для её усваивания.
Сегодня я хочу поделиться своим простым рецептом, который помог мне раз и на всегда запомнить особенности уровней изоляции транзакций и по сей день помогает без проблем принимать взвешенные решения о выборе необходимого уровня.

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

Побочные эффекты параллелизма

Для создания таблицы и наполнения её данными можно запустить следующий скрипт.

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

Источник

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

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

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

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

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

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

Это можно остановить командой

Или включить обратно

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

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

– потерянные обновления (когда вторая транзакция начинается посреди первой, а они пишут и читают одни и те же данные – возникают искажения в записи / чтении);

–“грязное чтение” – первая транзакция ещё не зафиксирвала изменения в физической БД, а вторая уже начала чтение (по сути более частный случай потерянных обновлений);

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

–чтение строк фантомов… (вклинивание в длительную транзакцию и искажение)

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Транзакции в базе данных MySQL

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

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

Немногое в этой статье характерно именно для MySQL. Если вы уже знако­мы с транзакциями ACID, можете спокойно перейти к подразделу «Транзакции в MySQL».

Банковское приложение является классическим примером, демонстрирующим необходимость транзакций. Представьте банковскую базу данных с двумя табли­цами: checking (текущие счета) и savings (сберегательные счета). Чтобы перевести 200 долларов с текущего счета Джейн на ее сберегательный счет, вам нужно сделать по меньшей мере три шага.

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

Но сами по себе транзакции — это еще не все. Что произойдет в случае сбоя сервера базы данных во время выполнения четвертой строки? Кто знает. Клиент, вероят­но, потеряет 200 долларов. А если другой процесс вклинится между выполнением строк 3 и 4 и снимет весь остаток с текущего счета? Банк предоставит клиенту кредит 200 долларов, даже не зная об этом.

Транзакций недостаточно, пока система не прошла тест ACID. Аббревиатура ACID расшифровывается как atomicity, consistency, isolation и durability (атомарность, со­гласованность, изолированность и долговечность). Это тесно связанные критерии, которым должна соответствовать правильно функционирующая система обработки транзакций.

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

Как и в случае увеличения детализации блокировок, оборотной стороной усиленной безопасности является увеличение объема работы сервера базы. Сервер базы данных с транзакциями ACID также требует больших мощности процессора, объема памяти и дискового пространства, чем сервер без них. Как мы уже отмечали, это тот самый случай, когда архитектура подсистем хранения данных MySQL является вашим со­юзником. Вы сами можете решить, требует ли приложение использования транзак­ций. Если они не нужны, вы можете добиться большей производительности, выбрав для некоторых типов запросов нетранзакционную подсистему хранения данных. С помощью команды LOCK TABLES можно установить нужный уровень защиты без использования транзакций. Все в ваших руках.

Уровни изолированности

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

Все подсистемы хранения данных реализуют уровни изолированности немного по-разному, и они не всегда будут соответствовать вашим ожиданиям, если вы привыкли к другой СУБД (здесь не будем вдаваться в подробности). Следует ознакомиться с руководствами по тем подсистемам хранения данных, которые вы решите использовать.

Вкратце рассмотрим четыре уровня изолированности.

В табл. 1 приведена сводка различных уровней изолированности и указаны недо­статки, присущие каждому из них.

Таблица 1. Уровни изолированности ANSI SQL

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

Возможность

чернового

чтения

Возможность неповторя ющегося чтения

Источник

SQL — Транзакции

Дата публикации: 2017-12-11

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

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

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

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

Транзакции имеют следующие четыре стандартных свойства, обычно обозначаемых аббревиатурой ACID.

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

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

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

Изолированность — позволяет транзакциям работать независимо друг от друга и прозрачно.

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

Управление транзакциями

Для управления транзакциями используются следующие команды.

COMMIT — сохранить изменения.

ROLLBACK — отменить изменения.

SAVEPOINT — создает точки сохранения в группах транзакций.

SET TRANSACTION — помещает имя в транзакцию.

Команды управления транзакциями

Команды управления транзакциями используются только с командами DML, такими как — INSERT, UPDATE и DELETE. Они не могут использоваться при создании таблиц или их удалении, поскольку эти операции автоматически фиксируются в базе данных.

Команда COMMIT

Команда COMMIT — это транзакционная команда, используемая для сохранения изменений внесенных транзакцией в базу данных. Команда COMMIT сохраняет все транзакции в базе данных с момента выполнения последней команды COMMIT или ROLLBACK.

Источник

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

Oct 4, 2018 • zinvapel

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

Блокировки

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

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

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

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

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

Если копнуть глубже, то выяснится, что есть еще 2 типа блокировок, назовем их блокировками «о намерениях». Нельзя просто так взять и заблокировать запись в InnoDB. Блокировки intention shared и intention exclusive являются блокировками на уровне таблицы и блокируют только создание других блокировок и операции на всей таблице типа LOCK TABLE. Наложение такой блокировки транзакцией лишь сообщает о намерении данной транзакции получить соответствующую совместную или исключительную блокировку строки.

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

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

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

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

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

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

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

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

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

База данных должна всегда переходить из одного непротиворечивого состояния в последующее.

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник