Снова поговорим о транзакциях — анализ обработки транзакций MySQL

MySQL

Базовая концепция/определение транзакции MySQL

Транзакция — это единица выполнения программы, которая обращается к базе данных и обновляет ее; транзакция может содержать один или несколько SQL-операторов, которые либо выполняются, либо не выполняются..

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

Удаленная инструкция SQL
delete from userinfo where ~~~
delete from mail where ~~
delete from article where~~
~~
Если нет обработки транзакции, в процессе вашего удаления возникает ошибка и выполняется только первое предложение, то последствия невообразимы!

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

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

mysql逻辑架构和存储引擎

Механизмы хранения MySQL, поддерживающие транзакции, включают InnoDB, NDB Cluster и т. д. Среди них наиболее широко используется InnoDB, другие механизмы хранения не поддерживают транзакции, такие как MyIsam, Memory и т. д..

характеристики сделок

  • Наименьшая единица работы, которую нельзя разделить; обычно транзакция соответствует полному бизнесу (например, бизнес по переводу банковских счетов, который является наименьшей единицей работы)

  • Полный бизнес требует, чтобы пакеты операторов DML (вставка, обновление, удаление) выполнялись совместно.

  • Транзакции связаны только с операторами DML, или операторы DML имеют транзакции. Это связано с бизнес-логикой.Разные бизнес-логики имеют разное количество операторов DML.

Во-первых, давайте проясним соответствующие знания, связанные с транзакцией:

Транзакции должны иметь характеристики ACID

Так называемая КИСЛОТАAtomic (атомарность)/Consistent (постоянство)/Isolated (изоляция)/Durable (постоянство)Написаны первые буквы четырех слов.

Вообще говоря,Транзакция — это 4 условия, которые должны быть выполнены (ACID)
  • аутмический: транзакция является наименьшей единицей и не может быть разделена. То есть: операторы, составляющие транзакцию, образуют логическую единицу, и только ее часть не может быть выполнена. Транзакция является исполняемой, и необходимо «ничего не делать или делать все!», что означает, что транзакционная часть не может быть выполнена. Даже если транзакция не может быть завершена из-за сбоя, влияние на базу данных должно быть устранено при откате!

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

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

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

    Например: онлайн-покупки, вы можете оформить транзакцию, только выпустив товары со склада и позволив товарам войти в корзину покупок клиента!

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

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

  • Изоляция: Одна транзакция не влияет на другую. То есть: существует изоляция между транзакцией A и транзакцией B. Если несколько транзакций выполняются одновременно, это должно быть так же, как если бы каждая транзакция выполнялась независимо! То есть ни одна транзакция не может увидеть транзакцию в незавершенном состоянии.

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

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

    Изоляция гарантируется MVCC (управление параллелизмом нескольких версий) и Lock (механизм блокировки).

  • Долговечность: Эффект обработки транзакции может быть сохранен навсегда. В свою очередь, транзакции должны выдерживать любые сбои, включая сбои сервера, процесса, связи, носителя и т. д. Успешно выполненная транзакция оказывает долговременное воздействие на базу данных: даже если база данных выйдет из строя, она сможет восстановиться! Постоянство — гарантия транзакции, признак окончания транзакции (данные в памяти сохраняются в файл на жестком диске).

    Например: в процессе банковского перевода состояние счета после перевода должно быть сохранено.

    Постоянство гарантируется Redo Log. Каждый раз, когда данные действительно изменяются, запись будет записываться в журнал повторов. Только когда журнал повторов будет успешно записан, он будет записан в дерево B+. Если питание отключается до фиксации, запись может быть восстановлена ​​через журнал повторов.

Механизм хранения InnoDB имеет два журнала транзакций:

  • журнал повторов (redo log): используется для обеспечения устойчивости транзакций

  • журнал отмены (журнал отката): основа для реализации атомарности и изоляции транзакций

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

InnoDB реализует откат, опираясь на журнал отмены:

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

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

Журнал отмены относится к логическому журналу, в который записывается информация, связанная с выполнением SQL.

Когда происходит откат, InnoDB сделает обратное в соответствии с содержимым журнала отмены:

  • Для каждой вставки выполняется удаление при откате.

  • Для каждого удаления выполняется вставка при откате.

  • Для каждого обновления выполняется обратное обновление при откате, изменении данных обратно.

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

Предыстория существования журнала повторов

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

С этой целью InnoDB предоставляет буфер (Buffer Pool), который содержит отображение некоторых страниц данных на диске в качестве буфера для доступа к базе данных:

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

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

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

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

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

Журнал повторов использует WAL (журнал с опережающей записью, журнал с опережающей записью).Все изменения сначала записываются в журнал, а затем обновляются в буферном пуле, чтобы гарантировать, что данные не будут потеряны из-за простоя MySQL, что соответствует требованиям устойчивости.

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

Есть две основные причины:

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

  • Грязная очистка основана на страницах данных. Размер страницы MySQL по умолчанию составляет 16 КБ, и небольшая модификация на странице должна быть записана на всю страницу, в то время как журнал повторов содержит только ту часть, которая должна быть записана, и недействительный ввод-вывод. сильно снижается..

журнал повторов и бинлог

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

Это работает иначе:

  • журнал повторов используется для восстановления после сбоя, чтобы гарантировать, что время простоя MySQL не повлияет на надежность;

  • Binlog используется для восстановления на момент времени, чтобы гарантировать, что сервер может восстанавливать данные на момент времени.Кроме того, binlog также используется для репликации master-slave.

Различные уровни:

  • журнал повторов реализуется механизмом хранения InnoDB,

  • И binlog реализован серверным уровнем MySQL и поддерживает InnoDB и другие механизмы хранения.

Содержание разное:

  • Журнал повторов — это физический журнал, и его содержимое основано на страницах диска.

  • binlog — логический лог, содержимое — кусок sql.

Время написания разное:

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

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

  • binlog записывается при фиксации транзакции.

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

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

Проблемы согласованности параллелизма

  • грязное чтение: например, если две транзакции выполняют операции с одной и той же записью базы данных параллельно, транзакция A может прочитать незафиксированные данные транзакции B. Например, транзакция B работает с базой данных, но не фиксирует транзакцию.В это время A считывает данные, для которых B не фиксирует транзакцию. Это воплощение грязных чтений.

  • неповторяемое чтение: Например, транзакция A считывает одну и ту же запись несколько раз в одной и той же транзакции.В это время транзакция B изменяет данные, которые читает транзакция A, и отправляет транзакцию, но транзакция A считывает данные, отправленные транзакцией B, что приводит к двум читает Данные несовместимы.

  • галлюцинации: Транзакция A устанавливает для всех данных в таблице данных значение 100. В это время транзакция B вставляет часть данных со значением 200 и отправляет транзакцию. Когда транзакция A изменяет и завершает отправку транзакции, обнаруживается, что есть еще данные, значение которых не равно 100. Это проявление фантомного чтения.

脏读示例不可重复读示例幻读示例

脏读情况不可重复读(Non-Repeatable Reads)幻读流传展示

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

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

  • Читать незафиксированные (RU), когда транзакция не зафиксирована, ее изменения видны другим транзакциям.

    Причина грязных чтений: принцип RU заключается в блокировке записей строк каждого оператора обновления, а не в блокировке всей транзакции, поэтому происходят грязные чтения. RC и RR блокируют всю транзакцию.

  • Чтение подтверждено (RC), после фиксации транзакции ее изменения будут видны другим транзакциям.

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

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

    По умолчанию используется механизм MVCC («непротиворечивое чтение без блокировки») для обеспечения правильности изоляции уровня RR и не заблокирован. Вы можете выбрать блокировку вручную: выберите xxxx для обновления (монопольная блокировка); выберите блокировку xxxx в режиме общего доступа (общая блокировка), которая называется «согласованное чтение блокировки». После использования блокировки можно избежать фантомных чтений на уровне RR. Конечно, чтение MVCC по умолчанию также позволяет избежать фантомных чтений.

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

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

数据库权威专家针对事务的隔离性研究出来了事务的隔离四种级别

Чем строже уровень изоляции транзакций, тем больше потребляется производительность компьютера и ниже эффективность..

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

Уровень изоляции данных по умолчанию, используемый Mysql, — REPEATABLE READ (повторяемое чтение, разрешающее фантомное чтение). Уровень REPEATABLE READ MySql не вызывает фантомных чтений.

Уровень REPEATABLE READ вызовет фантомное чтение, но из-за оптимизации Mysql при использовании выбора по умолчанию MySql использует механизм MVCC, чтобы гарантировать отсутствие фантомного чтения;

Также можно использовать блокировки.При использовании блокировок, например, для обновления (блокировка X), блокировка в режиме общего доступа (блокировка S), MySql будет использовать блокировку Next-Key, чтобы гарантировать отсутствие фантомных чтений.

Первое называется чтением моментального снимка, а второе — текущим чтением.

Mysql по умолчанию использует REPEATABLE READ.

До версии Mysql 5.0 binlog поддерживал только формат STATEMENT! И этот формат имеет ошибки в репликации master-slave при уровне изоляции Read Commited, поэтому Mysql использует Repeatable Read как уровень изоляции по умолчанию!

В чем ошибка? Специально читал"Какой уровень изоляции транзакций следует выбрать MySQL в интернет-проектах?》

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

На чем основана репликация master-slave? Он основан на репликации binlog!

Сколько форматов в binlog?

  1. оператор: записывает измененный оператор SQL

  2. row: записывает фактические изменения данных каждой строки

  3. смешанный: сочетание режимов оператора и строки


Признак того, что сделка открыта? Знак окончания транзакции?

Включите флаг:Любая инструкция DML (вставка, обновление, удаление) выполняется, отмечая открытие транзакции.

флаг завершения (фиксация или откат):

Отправить: успешное завершение, синхронизировать всю историю операций оператора DML и базовые данные жесткого диска одновременно.

Откат: в конце сбоя вся история операций оператора DML будет очищена.

Вещи и базы данных, лежащие в основе данных:

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

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

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

В транзакции есть 4 вида контроля, один коммит, то есть коммит. Одним из них является откат, который является обратным вызовом. Другая — это установленная транзакция, которая устанавливает имя для этой транзакции. Еще одна точка сохранения. Этот элемент управления используется для установки определенной точки, к которой необходимо вернуться.

Анализ MVCC

Полное название MVCC — Multi-Version Concurrency Control, который представляет собой протокол управления параллельным выполнением нескольких версий.

Последовательное чтение без блокировки

Согласованное неблокирующее чтение означает, что механизм хранения InnoDB считывает данные строки в базе данных в текущее время выполнения с помощью управления несколькими версиями (MVCC). Если строка чтения выполняет операцию DELETE или UPDATE, операция чтения не будет ждать освобождения блокировки, как блокировка XS, а будет считывать данные моментального снимка.

MVCC相关的知识

При уровнях изоляции транзакций RC и RR механизм хранения InnoDB использует неблокирующие согласованные чтения. Однако определение данных моментального снимка отличается.На уровне RC для данных моментального снимка несогласованное чтение всегда считывает данные последнего моментального снимка заблокированной строки. На уровне RR для данных моментальных снимков при несогласованном чтении всегда считывается версия данных строки в начале транзакции.

MVCC过程图示

Видно, что шаги 1 и 2 очень просты для понимания, и после того, как транзакция B вставит новый кусок данных на шаге 3, транзакцию A все еще нельзя будет найти на шаге 4, то есть используя характеристики MVCC для выполнения. Когда транзакция B зафиксирована, выходные данные запроса на шаге 5 отличаются для уровней изоляции RC и RR Причина этого также упоминается в другом блоге, поскольку они создают ReadView в разное время.

Но что очень странно, так это то, что на шаге 6 транзакция А обновляет запись, которую она не видит, и тогда запрос может быть запрошен. Многих здесь легко сбить с толку.Невидимость не означает, что запись не существует.Его просто игнорируют с помощью суждения о видимости. После успешного обновления транзакция A, естественно, записывает журнал отмены этой записи.В последующем запросе, поскольку она может видеть оценку видимости своих собственных изменений, ее можно, естественно, запросить. Здесь много терминов, которые нужно внимательно прочитать:Поговорите о характеристиках ACID транзакций механизма хранения MySQL InnoDB.

RR решает такие проблемы, как грязное чтение, неповторяемое чтение и фантомное чтение, используя MVCC..

高性能MySQL MVCC


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

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

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

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

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

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

Как упоминалось ранее, при уровне изоляции RR по умолчанию операция SELECT механизма хранения InnoDB использует согласованные неблокирующие чтения. Однако в некоторых случаях пользователям необходимо явно заблокировать операции чтения базы данных, чтобы обеспечить согласованность логики данных. Механизм хранения InnoDB поддерживает две согласованные блокирующие операции чтения для операторов SELECT.

  • ВЫБЕРИТЕ… ДЛЯ ОБНОВЛЕНИЯ (блокировка X)

  • ВЫБЕРИТЕ … БЛОКИРОВКА В РЕЖИМЕ ОБМЕНА (S lock)

Грязное чтение MySQL InnoDB, неповторяемое чтение, фантомное чтение

грязное чтение

脏读情景分析

Когда транзакция A считывает баланс zhangsan на узле времени T3, она обнаружит, что данные были изменены другими транзакциями, а статус не зафиксирован.

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

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

不可重复读情景分析

Когда транзакция A впервые считывает данные на узле T2, она записывает номер версии данных (номер версии данных записывается в единицах строки), предполагая, что номер версии равен 1; когда транзакция B фиксируется, записи строки Номер версии увеличивается, при условии, что номер версии равен 2.

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

галлюцинации

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

next-keylock — разновидность блокировки строки, которая эквивалентна блокировке записи (record lock) + gap lock (гэп-блокировка); ее особенность в том, что она не только блокирует саму запись (функция блокировки записи), но и блокирует функция блокировки диапазона (блокировка интервала).

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

幻读情景分析

Таким образом, 0 снова считывается во время T5.

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

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


Существует два основных метода обработки транзакций MYSQL.

Используйте начало, откат, фиксацию для достижения

  • begin запускает транзакцию
  • откат транзакции
  • подтвердить транзакцию

Изменить режим автоматической фиксации mysql

Транзакция MYSQL по умолчанию отправляется автоматически, то есть вы отправляете ЗАПРОС, он будет выполнен напрямую! То есть, пока выполняется инструкция DML, транзакция запускается и транзакция фиксируется.

MySQL自动提交模式

Мы можем добиться обработки транзакций, установив autocommit.

  • установить autocommit=0 отключить автофиксацию

  • установите autocommit=1, чтобы включить автофиксацию

Но обратите внимание, что когда вы используете set autocommit=0, все ваши будущие SQL будут обрабатываться как транзакция, пока вы не подтвердите фиксацию или не завершите откат.Обратите внимание, что когда вы завершаете эту транзакцию, вы также открываете новую транзакцию! По первому способу только текущая сделка!
Лично рекомендую использовать первый способ!
Только таблицы данных INNODB и BDB в MYSQL могут поддерживать обработку транзакций! Другие типы не поддерживаются! (Помните!)

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

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

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

-------------------------------------------------------------------------------

create table book
(
book_id unsigned int(10) not null auto_increment,
book_name varchar(100) not null,
book_price float(5, 2) не null, # я предполагаю, что цена каждой книги не превысит 999,99 юаней
book_number int(10) not null,
primary key (book_id)
)
type = innodb; #engine = innodb также работает

-------------------------------------------------------------------------------

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

-------------------------------------------------------------------------------

1. SELECT book_number FROM book WHERE book_id = 123;

book_number больше нуля, подтвердите покупку и обновите book_number

2. UPDATE book SET book_number = book_number - 1 WHERE book_id = 123;

Успешная покупка книги

-------------------------------------------------------------------------------

Для пользователя B его действия немного медленнее, чем у A, а действия, вызванные его процессом покупки, такие же, как у A:

-------------------------------------------------------------------------------

1. SELECT book_number FROM book WHERE book_id = 123;

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

2. UPDATE book SET book_number = book_number - 1 WHERE book_id = 123;

Успешная покупка книги

-------------------------------------------------------------------------------

На первый взгляд операции А и Б были успешными.Они оба купили книги, но на складе была только одна книга.Как они оба могли добиться успеха? Посмотрите на содержимое book_number в таблице данных, оно стало -1, что, конечно, недопустимо (на самом деле, объявление такого типа столбца должно добавить атрибут unsigned, чтобы гарантировать, что он не может быть отрицательным, здесь для иллюстрации проблемы так это не так настроено)

Хорошо, формулировка проблемы ясна, давайте посмотрим, как использовать транзакции для решения этой проблемы, откройте руководство по MySQL, вы увидите, что очень просто использовать транзакции для защиты правильного выполнения вашего SQL, в основном это три оператора: start, подать, откат.

  • Старт: Оператор START TRANSACTION или BEGIN может запустить новую транзакцию.

  • Commit: COMMIT может зафиксировать текущую транзакцию, и изменение становится постоянным изменением.

  • Откат: ROLLBACK может откатить текущую транзакцию и отменить ее изменения.

Кроме того, SET AUTOCOMMIT={0|1} может отключить или включить режим автоматической фиксации по умолчанию для текущего подключения.

-------------------------------------------------------------------------------

Можно ли гарантировать правильность, пока мы обертываем наши операторы SQL операторами транзакций? Например следующий код:

-------------------------------------------------------------------------------

BEGIN;

SELECT book_number FROM book WHERE book_id = 123;

// ...

UPDATE book SET book_number = book_number - 1 WHERE book_id = 123;

COMMIT;

-------------------------------------------------------------------------------

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

-------------------------------------------------------------------------------

BEGIN;

SELECT book_number FROM book WHERE book_id = 123 FOR UPDATE ;

// ...

UPDATE book SET book_number = book_number - 1 WHERE book_id = 123;

COMMIT;

-------------------------------------------------------------------------------

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

Транзакционные операции MySQL

mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表
Query OK, 0 rows affected (0.04 sec)

mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
 
mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
 
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
 
mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
 
mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)
 
mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

Демонстрация кода работы транзакции PHP + MySQL:

<?php
$dbhost = 'localhost:3306';  // mysql服务器主机地址
$dbuser = 'root';            // mysql用户名
$dbpass = '123456';          // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn, "set names utf8");
mysqli_select_db( $conn, 'RUNOOB' );
mysqli_query($conn, "SET AUTOCOMMIT=0"); // 设置为不自动提交，因为MYSQL默认立即执行
mysqli_begin_transaction($conn);            // 开始事务定义

if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(8)"))
{
    mysqli_query($conn, "ROLLBACK");     // 判断当执行失败时回滚
}

if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(9)"))
{
    mysqli_query($conn, "ROLLBACK");      // 判断执行失败时回滚
}
mysqli_commit($conn);            //执行事务
mysqli_close($conn);
?>


Демонстрация кода PHP с использованием AdoDB для работы с транзакциями MySQL:

В реальных LAMP-приложениях PHP обычно использует AdoDB для работы с MySQL. Соответствующий код AdoDB приведен ниже для вашего удобства:

-------------------------------------------------------------------------------

// ...
$adodb -> startTrans ();

// На самом деле, запрос, вызываемый getOne, также можно напрямую поместить в rowLock, это просто для демонстрации того, что эффект может быть более очевидным.
$adodb -> rowLock ( 'book' , 'book_id = 123' );
$bookNumber = $adodb -> getOne ( "SELECT book_number FROM book WHERE book_id = 123" );
$adodb -> execute ( "UPDATE book SET book_number = book_number - 1 WHERE book_id = 123" );
$adodb -> completeTrans ();
// ...
?>

-------------------------------------------------------------------------------

Среди них метод rowLock — это блокировка строки, реализуемая вызовом FOR UPDATE. Вы можете написать &"FOR UPDATE&" непосредственно в операторе SQL, вызываемом $adodb->getOne(), чтобы реализовать функцию блокировки строки, что неплохо. , это правда, но не все базы данных используют синтаксис &"FOR UPDATE&" для реализации функции блокировки строк. Например, Sybase использует синтаксис &"HOLDLOCK&" для реализации функции блокировки строк, поэтому уровень абстракции базы данных должен быть переносимым. ,все же советую использовать rowLock для реализации функции блокировки строк.Насчет переносимости оставьте AdoDB.Ну это немного надумано,поэтому сегодня на этом остановлюсь.

-------------------------------------------------------------------------------

Прикрепил:

В AdoDB есть метод setTransactionMode(), который может установить уровень изоляции транзакции следующим образом:

SetTransactionMode allows you to pass in the transaction mode to use for all subsequent transactions for that connection session. Note: if you have persistent connections and using mysql or mssql, you might have to explicitly reset your transaction mode at the beginning of each page request. This is only supported in postgresql, mssql, mysql with InnoDB and oci8 currently. For example:

$db->SetTransactionMode("SERIALIZABLE");
$db->BeginTrans();
$db->Execute(...); $db->Execute(...);
$db->CommiTrans();

$db->SetTransactionMode(""); // restore to default
$db->StartTrans();
$db->Execute(...); $db->Execute(...);
$db->CompleteTrans();

Supported values to pass in:

* READ UNCOMMITTED (allows dirty reads, but fastest)
* READ COMMITTED (default postgres, mssql and oci8)
* REPEATABLE READ (default mysql)
* SERIALIZABLE (slowest and most restrictive)

You can also pass in database specific values such as 'SNAPSHOT' for mssql or 'READ ONLY' for oci8/postgres.

Оригинальная ссылка:Разговор о транзакциях — Анализ обработки транзакций MySQL — mysql — личный сайт Чжоу Цзюня, обновление будет осуществляться на исходном сайте.Если в тексте есть какие-либо несоответствия, пожалуйста, оставьте сообщение, чтобы сообщить об этом для исправления. Спасибо!

Ссылка на ссылку:

MySQL — Детали транзакции (транзакции)blog.CSDN.net/i_Linux/art…

MySql три точки знаний - индекс, блокировка, транзакцияzhuanlan.zhihu.com/p/59764376

Интервьюер спросил вас: транзакция MYSQL и уровень изоляции, как ответитьzhuanlan.zhihu.com/p/70701037

Разговор о блокировках MySQLzhuanlan.zhihu.com/p/65721606

В этой статье объясняются принципы реализации транзакций MySQL и функции ACID.zhuanlan.zhihu.com/p/56874694

Транзакции MySQL https://www.runoob.com/mysql/mysql-transaction.html


Категории