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

Чем тяжелее, тем удачливее,
Эта статья собрана на GitHubJavaCommunity, Есть обмен интервью, статьи серии анализа исходного кода, добро пожаловать на сбор, как
GitHub.com/cc Ask Me-Contact/Jav…

предисловие

На собеседовании вы в основном будете задавать вопросы о транзакциях базы данных.Если вы ничего не знаете или отвечаете только на поверхностные знания, то собеседование в основном бесполезно.Чтобы пройти собеседование гладко, проблемы с транзакциями MySql. чтобы быть понятым, поэтому я обобщаю знания о версии транзакций Mysql в соответствии с онлайн-информацией и консолидирую знания о транзакциях.

дела

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

Характеристики транзакций (ACID)

атомный (Atomicity): Транзакция представляет собой неотделимую единицу работы, либо все завершаются успешно, либо все терпят неудачу.Если SQL-оператор в транзакции не удается выполнить, выполняемый оператор также необходимо откатить, и база данных возвращается в состояние до транзакции.
последовательность(Consistency): Транзакция не может нарушить целостность данных и согласованность бизнеса. Например, при банковском переводе, независимо от того, успешна транзакция или нет, общая сумма денег с обеих сторон остается неизменной.
изоляция (Isolation): Видимость данных, обрабатываемых транзакцией, для других транзакций устанавливается (обычно невидима) до ее фиксации.
Упорство (Durability): после совершения транзакции внесенные изменения будут сохранены навсегда, и данные не будут потеряны из-за сбоев системы.

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

Так как же реализована функция ACID для транзакций? Давайте проанализируем его, сначала посмотрим на характеристики сделки.

атомный (`Atomicity`)

Во-первых, давайте взглянем на атомарность транзакций и посмотрим, как она реализована?

атомный (Atomicity): Транзакция — это неотделимая единица работы, которая либо завершается успешно, либо завершается неудачно.

атомный (Atomicity) Реализация неотделима от журналов транзакций MySQLundo logТип журнала, когда транзакцию необходимо откатить, состояние базы данных необходимо откатить к началу транзакции, то есть все SQL-запросы, которые были успешно выполнены, нужно отменить. Такundo logсыграли ключевую роль:

Когда транзакция изменяет базу данных, InnoDB генерирует соответствующийundo log; если транзакция не удалась или вызываетrollback, вызывая откат транзакции, вы можете использоватьundo logИнформация в откатывает данные до того, как они были до модификации.

Такundo logЧто тогда? Как записывается каждая операция изменения данных?

журнал отмены (журнал отката)

undo log(журнал отката): дачасть(segment)способ записи, каждыйundoОперация занимаетundo log segment. Почему соответствующие данные записываются при изменении данных?undo logШерстяная ткань? Его цель состоит в том, чтобы:

Чтобы обеспечить атомарность данных, версия перед транзакцией записывается для отката,
пройти черезmvcc+undo logРеализует повторяющиеся уровни изоляции чтения и чтения транзакций innodb.

в,undo logРазделен на:

insert undo log :insertсгенерированный в процессе эксплуатацииundo log,
update undo log: правильноdeleteа такжеupdateв результате операцииundo log

данные измененыundo logКак это было записано?

потому чтоinsertЗапись операции видна только самой транзакции, но не другим транзакциям. Поэтому следуетundo logЕго можно удалить сразу после совершения транзакции, делать этого не нужно.purgeдействовать,

а такжеDeleteОперация фактически удаляет не строку данных в транзакции, а операцию Удалить пометку, которая помечается в записи.Delete_Bit, не удаляя запись. Это своего рода «поддельное удаление», просто пометка, для настоящей работы по удалению требуется фонpurgeнить для завершения.

updateЕсть два случая:updateЯвляется ли столбец столбцом первичного ключа.

Если это не столбец первичного ключа, вundo logКак устроена прямая обратная запись вupdateиз. которыйupdateосуществляется напрямую.
Если это первичный ключевой столбец,updateВыполните в два этапа: сначала удалите строку, затем вставьте строку целевой строки.

а такжеinsert undo log разные,update undo loglog, когда транзакция зафиксируется, innodb не удалит ее сразуundo log, что будет соответствовать транзакцииundo logПоместите его в список удаления и передайте в будущемpurgeветка для удаления.

Потому что это может быть использовано позжеundo log, если уровень изоляцииrepeatable readКогда транзакция считывает последнюю зафиксированную версию строки при открытии транзакции, до тех пор, пока транзакция не завершится, версия строки не может быть удалена (т. е.undo logнельзя удалить) иundo logВыделенные страницы можно повторно использовать для уменьшения объема памяти и повышения производительности.

Примечание. Две основные функции потока очистки: очистка страницы отмены и очистка строк данных, помеченных Delete_Bit на странице.

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

изоляция (`Isolation`)

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

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

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

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

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

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

** Грязное чтение: ** чтение данных, которые еще не зафиксированы другими транзакциями, транзакция A считывает данные, обновленные транзакцией B, а затем B откатывает операцию, после чего данные, считанные A, являются грязными данными.

**Неповторяющееся чтение (акцент делается на модификации): **В транзакции одно и то же чтение выполняется дважды, потому что другая транзакция изменила данные, что приводит к несоответствию между двумя результатами до и после того, как транзакция А читает одно и то же несколько раз Данные, транзакция B обновляет и фиксирует данные в процессе многократного чтения транзакцией A, что приводит к противоречивым результатам, когда транзакция A считывает одни и те же данные несколько раз.

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

В чем разница между неповторяемым чтением и фантомным чтением?

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

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

Однако этот метод обработки не может заблокировать данные вставки, поэтому, когда транзакция A предварительно прочитала данные, транзакция B будетinsertДанные отправляются, и оказывается, что транзакция A найдет необъяснимо больше данных, которые являются фантомными, и их нельзя избежать блокировками строк.

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

Уровень изоляции транзакции	грязное чтение	неповторяемое чтение	галлюцинации
читать незафиксированные	да	да	да
неповторяемое чтение	нет	да	да
повторяемое чтение	нет	нет	да
сериализовать	нет	нет	нет

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

Итак, давайте рассмотрим принцип блокировок: как использовать блокировки для достижения изоляции транзакций?

замок механизм

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

MySQL в основном делится на три типа (уровня) механизмов блокировки:

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

И разные механизмы хранения поддерживают разные механизмы блокировки, в основном анализируют блокировки InnoDB.

Блокировки InnoDB

InnoDB реализует следующие два типа блокировки строк.

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

дляUPDATE,DELETE,INSERTоперации InnoDB автоматически добавит эксклюзивную блокировку (X) к используемому набору данных; для обычных операторов SELECT InnoDB не добавит никаких блокировок,

А поскольку механизм InnoDB позволяет сосуществовать блокировкам строк и блокировкам таблиц, реализует механизм многогранулярных блокировок и использует блокировки намерений для реализации механизма блокировки таблиц,

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

Преднамеренные блокировки (IS, IX) автоматически добавляются перед операциями с данными InnoDB и не требуют вмешательства пользователя. Его значение таково: когда транзакция хочет заблокировать таблицу, она может сначала определить, существует ли блокировка намерения, и если она существует, то может быстро вернуться к таблице и не может включить блокировку таблицы, иначе ей нужно подождать.

Среди них совместимость четырех замков выглядит следующим образом

Текущий режим блокировки/совместим ли он/запросить режим блокировки	X	IX	S	IS
X	конфликт	конфликт	конфликт	конфликт
IX	конфликт	совместимый	конфликт	совместимый
S	конфликт	конфликт	совместимый	совместимый
IS	конфликт	совместимый	совместимый	совместимый

Если режим блокировки, запрошенный транзакцией, совместим с текущей блокировкой, InnoDB предоставляет запрошенную блокировку транзакции; в противном случае, если они несовместимы, транзакция ожидает снятия блокировки.

Блокировки строк InnoDB

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

临键锁(next-key), чтобы предотвратить фантомное чтение. По индексу он делится на один за другимслева открыто справа закрытоинтервал. При выполнении запроса диапазона, если индекс найден и данные могут быть извлечены, интервал, в котором находится запись, и ее следующий интервал блокируются.

фактически,Следующий ключ=Блокировка записи+Гэп-замки,

Когда мы извлекаем данные с условиями диапазона вместо условий равенства и запрашиваем общую или эксклюзивную блокировку, InnoDB блокирует записи индекса существующих записей данных, которые соответствуют условиям диапазона; для значений ключа, которые находятся в диапазоне условий, но не существует запись, называемая разрывом (GAP).
Когда используется уникальный индекс и существует запись для точного запроса, используйтеБлокировка записи

Где отражено конкретное использование? Как показано ниже:

Запрос диапазона, запись существует

Когда запись не существует (будь то эквивалентный запрос или запрос диапазона), следующий ключ вырождается вГэп-лок

Когда условие является точным соответствием (то есть когда это запрос на равенство), и запись существует, и это уникальный индекс,Следующий ключвыродиться вБлокировка записи

Когда условие является точным совпадением (то есть, когда это запрос на равенство) и запись существует, но не является уникальным индексом,Следующий ключДанные с точными значениями будут увеличиватьсяБлокировка записиДанные в интервале до и после значения точности будут увеличиватьсяГэп-лок.

Как использовать блокировки для решения проблем параллелизма

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

Блокировки X решают проблему грязного чтения
Блокировка S решает неповторяющееся чтение
Блокировка ключей Pro для устранения фантомного чтения

`Multiversion concurrency control` (`MVCC`Многоверсионный контроль параллелизма)

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

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

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

А MVCC есть только вREPEATABLE READа такжеREAD COMMITIEDОн будет работать только при двух уровнях изоляции,Среди них суть реализации MVCC заключается в сохранении моментального снимка данных в определенный момент времени.Какие операции являются чтением моментальных снимков?

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

Снимок прочитан, чтение моментального снимка innodb, чтение данных будет состоять из двух частей: кеш (исходные данные) + отмена (данные до модификации транзакции)

обычныйselect,Напримерselect * from table where ?;

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

UPDATE
DELETE
INSERT
SELECT … LOCK IN SHARE MODE
SELECT … FOR UPDATE

Среди них, читающих в настоящее время, толькоSELECT … LOCK IN SHARE MODEПомимо добавления блокировки S (общая блокировка) к записи чтения, к блокировке X (эксклюзивная блокировка) добавляются другие операции.

Так как же MVCC работает на уровне изоляции RR.

На уровне изоляции RR конкретная работа MVCC

ВЫБЕРИТЕ операцию, InnoDB следует следующим двум правилам:

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

INSERT: InnoDB сохраняет текущий номер транзакции как номер версии строки для каждой вновь вставленной строки.

DELETE: InnoDB сохраняет номер текущей транзакции в качестве идентификатора удаления строки для каждой удаляемой строки.

UPDATE: Чтобы вставить новую строку, InnoDB сохраняет текущий номер транзакции как номер версии строки и сохраняет текущий номер транзакции в исходной строке как идентификатор удаления строки >.

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

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

Упорство (`Durability`)

Упорство (Durability): после фиксации транзакции внесенные изменения будут сохранены навсегда, и данные не будут потеряны из-за сбоев системы.

И ключом к его реализации являетсяredo logПри выполнении SQL сохранить выполненный оператор SQL в указанный файл журнала при выполненииrecoveryповторно выполнитьredo logЗарегистрированные SQL-операции.

Такredo logКак этого добиться?

redo log

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

Буфер журнала (буфер журнала повторов) в памяти Buffer Pool, эта часть журнала является энергозависимой;
Файл журнала повторов на диске, эта часть журнала является постоянной.

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

Чтобы обеспечить устойчивость транзакции, когда транзакция фиксируется, она вызываетfsyncинтерфейсная параredo logпочистить диск (т.е.redo log bufferзаписать лог на дискredo log file), частота обновления устанавливаетсяinnodb_flush_log_at_trx_commitпеременная для управления:

0: данные, записанные на диск, обновляются каждую секунду, при сбое системы 1 секунда данных будет потеряна;
1: транзакция записывается на диск каждый раз, когда она фиксируется;
2: Flush записывает на диск каждую секунду, но отличается от 0.

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

последовательность(`Consistency`)

последовательность(Consistency): Транзакция не может нарушить целостность данных и непротиворечивость бизнеса:

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

Как это обеспечивает согласованность данных?

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

Атомарность: операторы либо полностью выполняются, либо не выполняются, что является основной особенностью транзакции.Сама транзакция определяется атомарностью; реализация в основном основана на журнале отмен.
Постоянство: гарантирует, что данные не будут потеряны из-за простоя и других причин после фиксации транзакции; реализация в основном основана на журнале повторов.
Изоляция: убедитесь, что выполнение транзакции не зависит от других транзакций, насколько это возможно; уровень изоляции InnoDB по умолчанию — RR, а реализация RR в основном основана на механизме блокировки (включая блокировку следующего ключа), MVCC (включая скрытую столбцы данных и цепочка версий на основе журнала отмены, ReadView)

Суммировать

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

Транзакция MySQL NDB Cluster не соответствует требованиям по надежности и изоляции;
Уровень изоляции транзакций InnoDB по умолчанию — повторяемое чтение, что не удовлетворяет требованиям изоляции;
Уровень изоляции транзакций Oracle по умолчанию — READ COMMITTED, что не обеспечивает изоляции.

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

Спасибо за ваши лайки, если не нравится ставьте лайк и поддержите
Наконец, WeChat ищет «Ccww Technology Blog», чтобы посмотреть больше статей, и добро пожаловать на волну.