Рубрики
Uncategorized

Примечания по базовым знаниям MySQL (транзакция)

Автор оригинала: David Wong.

PS: содержание знаний и материалы, собранные в этой статье, взяты из колонки “SQL должен знать, должен знать” журнала geek time

Движок InnoDB MySQL поддерживает транзакции, а MyISAM не поддерживает транзакции;

Четыре характеристики транзакции: Acid

  1. А То есть атомарность. Понятие атома неделимо. Его можно понимать как основную единицу материи и основную единицу операций обработки данных. Другими словами, он либо полностью выполняется, либо не выполняется вообще;
  2. C , последовательность. Согласованность означает, что база данных перейдет из исходного согласованного состояния в другое согласованное состояние после операции транзакции. То есть, когда транзакция зафиксирована или когда транзакция откатывается, ограничение целостности базы данных не может быть нарушено;
  3. Я Изоляция. Это означает, что каждая транзакция независима друг от друга и не будет зависеть от выполнения других транзакций. Другими словами, транзакция невидима для других транзакций до ее совершения;
  4. D – Это долговечность. Изменение данных после отправки транзакции является постоянным. Даже в случае системного сбоя, такого как сбой системы или сбой носителя данных, модификация данных по-прежнему эффективна. Потому что, когда транзакция будет завершена, журнал базы данных будет обновлен. В это время система может восстановиться до состояния последнего успешного обновления через журнал.

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

Общие операционные отчеты о транзакциях

  1. Начать транзакцию или начать используется для явного открытия транзакции.
  2. Фиксация: фиксация транзакции. Когда транзакция зафиксирована, изменения в базе данных являются постоянными.
  3. Откат или откат до [точки сохранения], что означает откат транзакции. Это означает отмену всех незавершенных изменений в процессе или откат транзакции до точки сохранения.
  4. Точка сохранения: создайте точку сохранения в транзакции, чтобы облегчить последующий откат точки сохранения. В транзакции может быть несколько точек сохранения.
  5. Отпустите точку сохранения: удалите точку сохранения.
  6. Установите транзакцию, чтобы установить уровень изоляции транзакции.

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

  • Завершение?, что является значением по умолчанию. То есть, когда мы выполним коммит, мы зафиксируем транзакцию. Когда мы выполняем следующую транзакцию, нам нужно использовать команду запустить транзакцию или начать ее запускать.
CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
Insert into test select 'off';
COMMIT;
Insert into test select 'Zhang Fei';
Insert into test select 'Zhang Fei';
ROLLBACK;
SELECT * FROM test;

Результат выполнения (1 строка данных):

  • Тип завершения. В этом случае, когда мы фиксируем транзакцию, это эквивалентно выполнению цепочки команд, то есть открытию транзакции цепочки, то есть, когда мы фиксируем транзакцию, мы откроем транзакцию с тем же уровнем изоляции (уровень изоляции будет описан в следующем разделе).
CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
SET @@completion_type = 1;
BEGIN;
Insert into test select 'off';
COMMIT;
Insert into test select 'Zhang Fei';
Insert into test select 'Zhang Fei';
ROLLBACK;
SELECT * FROM test;

Результаты выполнения (2 строки данных):

  • Completion_type, в данном случае, и release, то есть, когда мы зафиксируем, мы автоматически отключимся от сервера.
  • Исключения, которые могут быть устранены с помощью уровня изоляции, показаны в следующей таблице:

Характеристики трех видов аномальных состояний: 1. Грязное чтение: считывание незафиксированных данных других транзакций. (сосредоточение внимания на незафиксированных данных) 2. Неповторяемое считывание: при считывании определенных данных обнаруживается, что результаты двух считываний различны, то есть одно и то же содержимое не считывается. Это связано с тем, что существуют другие транзакции, которые одновременно изменяют или удаляют эти данные. (сосредоточьтесь на изменении, обновлении или удалении данных) 3. Фантомное чтение: транзакция a получает n фрагментов данных в соответствии с запросом условия, но в это время транзакция B изменяет или добавляет M фрагментов данных, которые соответствуют условию запроса транзакции a, так что, когда транзакция a запрашивает снова, будет найдено N + M фрагментов данных, что приведет к фантомному чтению. (сосредоточьтесь на добавлении данных, вставьте)

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

Моделирование аномальных условий регистрироваться не будет

Блокировки в MySQL

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

  • Оптимистичный замок

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

  • Пессимистичный замок

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

  • Общая блокировка (блокировка чтения, блокировка s)

Общая блокировка означает совместное использование одной и той же блокировки для ресурса для нескольких различных транзакций. Если к ресурсу добавлена общая блокировка, ресурс может быть прочитан сам по себе. Другие люди также могут прочитать ресурс (или добавить общую блокировку, то есть общая блокировка разделяет несколько воспоминаний), но ее нельзя изменить. Если вы хотите изменить его, вы должны подождать, пока не будут сняты все общие блокировки. Синтаксис: выберите * из блокировки таблицы в режиме общего доступа;

  • Эксклюзивная блокировка (блокировка записи, блокировка X)

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

  • Блокировка строк

Блокировка строки предназначена для блокировки строки данных. Объектом операции является строка в таблице данных (общая блокировка и исключительная блокировка могут быть блокировкой строк или блокировкой таблицы, в зависимости от диапазона блокировки данных, строки или всей таблицы). Технология Mvcc широко используется, но она не может быть использована в MyISAM. Блокировка уровня строк реализована механизмом хранения MySQL вместо сервера MySQL. Однако блокировка на уровне строк имеет высокие накладные расходы и высокий параллелизм.

Существует три способа блокировки строк InnoDB: 1. Блокировка записи: блокировка записи в одной строке; 2. Блокировка промежутка: блокировка диапазона (промежутка между индексами), но не самой записи, чтобы предотвратить нереальное чтение; 3. Блокировка следующим ключом: блокировка диапазона, включая саму запись, эквивалентна блокировке пробелов + блокировке записи, которая может предотвратить нереальное считывание

  • Замок для часов

Блокировка таблицы предназначена для блокировки таблицы. Объектом операции является таблица данных. Большинство политик блокировки MySQL поддерживаются (MySQL InnoDB распространен), что обеспечивает наименьшие системные издержки, но наименьший параллелизм. Если транзакция t добавляет блокировку чтения ко всей таблице, другие транзакции доступны для чтения и недоступны для записи. Если он добавляет блокировку записи, другие транзакции не могут быть добавлены, удалены или изменены.

  • Блокировка намерения

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

Mvcc MySQL (управление параллелизмом нескольких версий)

  1. Через mvcc чтение и запись не могут блокировать друг друга, то есть чтение не блокирует запись, запись не блокирует чтение, что может повысить производительность параллельной обработки транзакций.
  2. Уменьшите вероятность тупиковой ситуации. Это связано с тем, что mvcc использует оптимистичный метод блокировки. Его не нужно блокировать при чтении данных. Для операций записи он блокирует только необходимые строки.
  3. Решите проблему последовательного чтения. Согласованное чтение также называется чтением моментального снимка. Когда мы запрашиваем снимок базы данных в определенный момент времени, мы можем видеть только результат обновления фиксации транзакции до этого момента времени, но не результат обновления фиксации транзакции после этого момента времени.

Чтение моментального снимка и текущее чтение

  • Чтение моментального снимка

Простой выбор без блокировки относится к чтению моментального снимка:

SELECT * FROM table WHERE ...
  • Текущее значение

Текущее значение-это чтение последних данных, а не исторической версии данных. Заблокированный выбор, добавление, удаление или изменение данных будут считываться в данный момент:

SELECT * FROM table LOCK IN SHARE MODE;
SELECT * FROM table FOR UPDATE;
INSERT INTO table values ...;
DELETE FROM table WHERE ...;
UPDATE table SET ...;

Ядро mvcc: журнал отмены (MV) + просмотр чтения (CC)

Данные mvcc в InnoDB включают в себя Номер версии транзакции 、 Скрытые столбцы в записях строк и Отменить журнал 。

  • Номер версии транзакции

Каждый раз при открытии транзакции мы будем получать идентификатор транзакции (номер версии транзакции) из базы данных. Идентификатор транзакции растет сам по себе. С помощью размера идентификатора мы можем определить временную последовательность транзакций.

  • Скрытые столбцы в записях строк
  1. Идентификатор строки БД: скрытый идентификатор строки, используемый для создания кластеризованного индекса по умолчанию. Если мы не указываем кластеризованный индекс при создании таблицы данных, InnoDB будет использовать этот скрытый идентификатор для создания кластеризованного индекса. Кластеризация индекса может повысить эффективность поиска данных.
  2. БД? TRX? ИДЕНТИФИКАТОР: идентификатор транзакции данных, который является последним идентификатором транзакции для вставки или обновления данных.
  3. БД? Ролл? PTR: указатель отката, то есть информация журнала отмены, указывающая на эту запись.
  • Отменить Запись в журнал

InnoDB сохраняет снимок записи строки в журнале отмены. Мы можем найти их в сегменте отката, как показано на следующем рисунке:

Из рисунка видно, что указатель отката объединяет все записи моментального снимка строки данных через структуру связанного списка. Запись каждого снимка сохраняет текущий идентификатор DB ﹐ TRX﹐, который также является идентификатором транзакции данных, обрабатываемых в этот момент времени. Таким образом, если мы хотим найти исторический снимок, мы можем найти его, пройдя по указателю отката.

  • Просмотр для чтения

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

Представление для чтения обладает несколькими важными свойствами:

  1. TRX? ИДЕНТИФИКАТОРЫ, коллекция идентификаторов транзакций, активных в настоящее время системой.
  2. Идентификатор нижнего предела, самый большой идентификатор транзакции в активной транзакции.
  3. Идентификатор верхнего предела, наименьший идентификатор транзакции в активной транзакции.
  4. Создайте идентификатор транзакции представления чтения.

Как показано на рисунке, идентификатор TRX представляет собой набор TRX2, trx3, trx5 и trx8. Максимальный идентификатор активной транзакции (идентификатор нижнего предела) равен trx8, а минимальный идентификатор активной транзакции (идентификатор верхнего предела) – TRX2.

Предположим, что создатель текущей транзакции с идентификатором “TRX” хочет прочитать запись строки, идентификатор транзакции которой равен идентификатору “TRX”, возникнут следующие ситуации:

  1. Если TRX ﹤ ID < up ﹤ limit ﹤ ID, то есть эта запись строки была зафиксирована до создания этих активных транзакций, то эта запись строки видна для этой транзакции.
  2. Если TRX? Идентификатор > активный максимальный идентификатор транзакции (низкий? Предел? ID), это означает, что запись строки не видна для текущей транзакции до тех пор, пока не будут созданы эти активные транзакции.
  3. Если идентификатор верхнего предела < ИДЕНТИФИКАТОР TRX < идентификатор нижнего предела, идентификатор TRX транзакции, в котором находится запись строки, может быть все еще активен при создании идентификатора TRX создателя транзакции, поэтому нам нужно пройти в наборе идентификаторов TRX. Если идентификатор TRX существует в наборе идентификаторов TRX, нам нужно доказать, что идентификатор TRX транзакции все еще активен и невидим. В противном случае, если идентификатор TRX не существует в наборе идентификаторов TRX, идентификатор TRX транзакции зафиксирован и запись строки видна.

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

  1. Сначала получите номер версии самой транзакции, то есть идентификатор транзакции (creator_trx_id);
  2. Использовать создателя? TRX? Идентификатор для просмотра на чтение;
  3. Запросите данные и сравните их с номером версии транзакции в режиме чтения;
  4. Если правило просмотра для чтения не выполняется, вам необходимо сделать исторический снимок из журнала отмены;
  5. Наконец, возвращаются данные, соответствующие правилам.

В InnoDB mvcc считывает данные через журнал отмены + представление чтения. Журнал отмены сохраняет исторические снимки, в то время как правила просмотра чтения помогают нам судить о том, видна ли текущая версия данных.

  • Когда уровень изоляции зафиксирован для чтения, каждый запрос select в транзакции получит представление для чтения. Как показано в таблице:

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

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

Решение InnoDB для нереального чтения: блокировка следующего ключа + mvcc

  • Даже если принят режим mvcc, при отправке чтения произойдет нереальное считывание.

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

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

Мы видим, что когда мы хотим вставить игрока Алекса Аллена (рост 2,16 метра), транзакция B завершится и не сможет вставить данные. Это связано с тем, что следующая блокировка ключа заблокирует диапазон с высотой > 2,08, поэтому данные, соответствующие этому диапазону, не могут быть вставлены. Транзакция запрашивает диапазон условий, и нереального считывания не будет.

Читайте ещё по теме: