предисловие

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

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

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

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

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

Не все транзакции соответствуют характеристикам ACID, например, для баз данных Oracle и SQL Server уровень изоляции по умолчанию — Read COMMITTED, что не соответствует требованиям I (изоляция), а для ядра MySQL NDB Cluster — D. (постоянство) требования.

А (атомарность) - атомарность

Атомарность означает, что транзакция базы данных является неотъемлемой частью.Только если все операции в транзакции выполнены успешно, транзакция считается выполненной успешно.Если операция завершилась неудачно, другие успешные операции также должны быть отброшены.
Взяв в качестве примера сценарий перевода 1000 юаней, процесс перевода представляет собой транзакцию, которая в основном включает следующие два шага:
1. Вычтите 1000 юаней со счета А.
2. Добавьте 1000 юаней на счет B.
Только представьте, если первый шаг будет успешным, то второй шаг провалится, а это означает, что 1000 юаней А исчезнут сразу. Я считаю, что это то, что никто не может принять, поэтому транзакции базы данных должны обеспечивать атомарность.

C (последовательный) - постоянство

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

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

I(Изоляция) - Изоляция

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

Изоляция — относительно абстрактное и сложное понятие, например, насколько далеко мы должны изолировать изоляцию между транзакциями? Поэтому для изоляции стандарт SQL92 определяет 4 уровня изоляции, которые позже вводятся в уровне изоляции транзакций.

D (Durable) — Стойкость

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

управление делами

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

Автофиксация транзакций

Ознакомьтесь с оператором автоматической фиксации:

SHOW VARIABLES LIKE 'autocommit';-- ON表示开启了自动提交
SELECT @@autocommit;-- 1表示开启了自动提交

Выполните следующую инструкцию, чтобы отключить автоматическую фиксацию:

SET autocommit='OFF';
SET @@autocommit = 0;

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

Общие операторы управления транзакциями

• НАЧАТЬ ТРАНЗАКЦИЮ или НАЧАТЬ: отображаемая начальная транзакция. Следует отметить, что для открытия транзакции в хранимой процедуре можно использовать только START TRANSACTION, потому что хранимая процедура изначально имеет синтаксис BEGIN...END, и они будут конфликтовать.
• COMMIT: зафиксировать транзакцию. Также может быть записано как COMMIT WORK.
• ROLLBACK: Откат транзакции. Это также может быть записано как ROLLBACK WORK.
• Идентификатор SAVEPOINT: пользовательская точка сохранения, подходящая для длительных транзакций, может быть отброшена в наше пользовательское местоположение.
• Идентификатор RELEASE SAVEPOINT: удалить определенную точку сохранения, если точки сохранения нет, будет сообщено об ошибке
• Идентификатор ROLLBACK TO [SAVEPOINT]: Откат к указанной точке сохранения.

Разница между COMMIT и COMMIT WORK

Оба они могут отправить транзакцию, разница заключается в операции после отправки транзакции, а также есть ROLLBACK и ROLLBACK WORK, которые в основном контролируются переменной: complete_type, вы можете выполнить следующий sql для просмотра результатов:

SHOW VARIABLES LIKE '%completion_type%';

complete_type имеет следующие три результата:

Возьмите каштан 1:

SET completion_type=1; --1
begin;--2
INSERT test2 VALUES(1,'张1');--3
commit work;--4
INSERT test2 VALUES(2,'张1');--5
select * from test2;--6
rollback;--7
select * from test2;--8

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

Возьмите каштан 2:

SET completion_type=2;
begin;
INSERT test2 VALUES(3,'张1');
commit work;
select * from test2;

Последний оператор возвращает следующий результат:

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

Классификация дел

С теоретической точки зрения дела мы можем разделить на следующие пять категорий:

фиксированная сделка

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

Плоские транзакции с точками сохранения

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

BEGIN;
INSERT test VALUES(1,'张1');
SAVEPOINT A
INSERT test VALUES(2,'张2');
ROLLBACK TO A
COMMIT;

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

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

цепная сделка

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

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

Цепные транзакции могут быть реализованы с помощью параметра завершения_типа выше. Выше приведены примеры способов использования, и здесь они повторяться не будут.

вложенные транзакции

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

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

Распределенная транзакция

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

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

Читать незафиксированные

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

Что такое грязное чтение? См. этот пример ниже:

Слева транзакция 1. Проверьте ее один раз и обнаружите, что имя данных с id 1 — Zhang San. В это время снова приходит транзакция 2, меняющая Zhang San на Li Si, а затем транзакция 1 выполняет еще один запрос и выясняет.Имя Ли Си, то если транзакция 2 в это время откатывается, то есть имя по-прежнему Чжан Сан, но транзакция 1 читает Ли Си, что является грязным чтением.

Чтение зафиксировано

Обозначается как RC. Транзакция может только читать данные, представленные другими транзакциями, то есть в транзакции, если выполняется один и тот же запрос, будет два разных результата. Уровень изоляции базы данных по умолчанию для баз данных Oracle и SQL Server. Этот уровень изоляции решает проблему грязных чтений, но остается проблема неповторяющихся чтений.

Что такое неповторяемое чтение? Давайте еще посмотрим на приведенный выше пример, предположив, что транзакция 2 фиксируется сразу после обновления, а затем результат второго запроса транзакции 1 все еще Li Si, но на этот раз это не считается грязным чтением, потому что транзакция 2 зафиксировано, это называется неповторяемым чтением, поскольку результат двойного запроса одних и тех же данных в транзакции 1 отличается.

Повторяемое чтение

РР короче. Этот уровень изоляции решает проблему неповторяющегося чтения, то есть в одной и той же транзакции, выполняя один и тот же запрос, результаты одинаковы, но на этом уровне будет проблема фантомного чтения (кроме движка InnoDB, который решается движком InnoDB через гэп-блокировки) проблема фантомного чтения).

Что такое фантомное чтение? См. этот пример ниже:

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

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

Сериализуемый (сериализация)

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

Сравнение различных уровней изоляции

Различные уровни изоляции могут решать разные проблемы, как показано на рисунке ниже:

Для незафиксированных чтений и зафиксированных чтений все могут хорошо понимать, что только после того, как одна транзакция зафиксирована, она может быть видна для другой транзакции, но для повторяющихся чтений, как MySQL понимает, что даже если одна транзакция была зафиксирована, она все еще может быть видна для другой транзакции? другая транзакция.Транзакция не видна? Это MVCC, который мы собираемся объяснить далее.

Схема реализации изоляции транзакций

Существует две схемы реализации изоляции транзакций: LBCC и MVCC.

LBCC

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

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

текущее чтение

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

В решении LBCC, если наша бизнес-система больше читает и меньше пишет, это решение сильно повлияет на эффективность, поэтому у нас есть другое решение: MVCC.

MVCC

MVCC, управление многоверсионным параллелизмом, полное английское название: Multi Version Concurrency Control. То есть, когда мы модифицируем данные, мы можем создать снапшот для этих данных, а затем напрямую прочитать этот снимок позже.

Так как же именно реализован MVCC?

Чтобы реализовать механизм MVCC, InnoDB внутренне добавляет два скрытых столбца для каждой строки: DB_TRX_ID и DB_ROLL_PTR (в MySQL также есть скрытый столбец DB_ROW_ID, который используется в качестве первичного ключа, когда таблица InnoDB не имеет первичного ключа).

DB_TRX_ID

Он имеет длину 6 байт и хранит идентификатор транзакции последней транзакции оператора вставки или обновления.

DB_ROLL_PTR

Длина 7 байт, называется: указатель отката. Указатель отката указывает на запись журнала отката, записанную в сегмент отката.При чтении записи запись в журнале отката будет считываться в соответствии с указателем.

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

Снимок прочитан

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

Когда MySQL начинает читать снимки

Давайте сначала рассмотрим пример с уровнем изоляции MySQL по умолчанию RR (обратите внимание, что две таблицы test и test2 в начале являются пустыми таблицами, только с двумя полями id и name).

• Сценарий 1 (первый запрос выполняется после обработки данных транзакцией 1):

• Сценарий 2 (транзакция 1 ничего не делает, транзакция 2 сначала запускает первый запрос)

Из приведенных выше двух сценариев мы можем сделать следующие выводы:
Моментальные снимки уровня изоляции RR не создаются в BEGIN, а только после первого запроса в транзакции, а последующие запросы будут только считывать данные моментального снимка.

• Сценарий 3 (после того, как транзакция 2 сначала выполняет запрос таблицы t1, транзакция 1 затем обрабатывает другие таблицы t2)

Из сценария 3 мы можем сделать вывод:Моментальный снимок уровня изоляции RR предназначен не только для текущих запрошенных данных, но и для всех данных в текущем MySQL (то же самое верно для всех баз данных, если они находятся в одном и том же MySQL).

Механизм запроса MVCC

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

Затем мы конкретизируем абстрактную концепцию, предполагая, что DB_TRX_ID и DB_ROLL_PTR являются целыми числами, а затем выполним демонстрацию запроса:

1. Очистите исходную тестовую таблицу, вставьте две части данных в транзакцию A, в это время DB_TRX_ID (идентификатор транзакции) равен 1, DB_ROLL_PTR (указатель отката равен нулю)

2. В это время транзакция B выполняет запрос, и будет получен вышеуказанный результат.Перед тем, как транзакция 2 будет зафиксирована, снова приходит транзакция C, и транзакция C вставляет данные с id = 3. В это время данные в таблице составляет:

Обратите внимание, что идентификатор транзакции третьего фрагмента данных в настоящее время равен 3, потому что транзакция 2 также будет генерировать идентификатор транзакции.
3. В это время снова запрашивает транзакция B. Согласно тому, что мы узнали выше, мы знаем, что Wang Wu не следует запрашивать в это время, поэтому фактически второй запрос может быть следующим:

select * from test where 事务id<=2-- 因为当前的事务id为2

4. Если в это время снова придет транзакция D, а данные с id=1 будут удалены, то указатель отката исходных данных будет записан как текущий id транзакции: 4, поэтому данные в это время следующие:

5. Вернитесь к транзакции B и продолжите запрос.Должны быть только данные 1 и 2, поэтому он может запросить так:

select * from test where 事务id<=2 and (回滚指针 is null or 回滚指针 >2)

6. Если в это время снова приходит транзакция E, а вторые данные обновляются, в это время будут созданы данные с идентификатором транзакции 5, а указатель отката исходных данных также будет помечен как текущий идентификатор транзакции. :5, то вы получите следующие данные:

Основываясь на приведенных выше догадках, выполните следующий запрос:

select * from test where 事务id<=2 and (回滚指针 is null or 回滚指针 >2)

В это время было обнаружено, что были найдены только 1 и 2 данные.

Два правила для запроса MVCC

Таким образом, общие правила запросов MVCC следующие:
1,Запрашивайте только те данные, идентификатор транзакции которых меньше или равен текущему идентификатору транзакции.(Это равнозначно тому, что если ваша собственная транзакция вставляет часть данных, будут сгенерированы данные с идентификатором текущей транзакции, поэтому они должны включать данные, вставленные самой транзакцией)
2,Запрашивать только те данные, которые не удалены (указатель отката пуст) или чей указатель отката больше идентификатора текущей транзакции.(Это не может быть равно, потому что, если собственная транзакция удаляет часть данных, указатель отката сгенерированных данных является текущим идентификатором транзакции, поэтому данные, удаленные вами, должны быть исключены)

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