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

Метка: статья в публичном аккаунте "Мы все маленькие лягушки"

а

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

CREATE TABLE t (
    id INT PRIMARY KEY,
    c VARCHAR(100)
) Engine=InnoDB CHARSET=utf8;

Затем вставьте часть данных в эту таблицу:

INSERT INTO t VALUES(1, '刘备');

Данные в таблице теперь выглядят так:

mysql> SELECT * FROM t;
+----+--------+
| id | c      |
+----+--------+
|  1 | 刘备   |
+----+--------+
1 row in set (0.01 sec)

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

MySQLЭто программное обеспечение с архитектурой сервер/клиент.К одному и тому же серверу может быть подключено несколько клиентов.После того, как каждый клиент подключится к серверу, это можно назвать сеансом (Session). Мы можем одновременно вводить различные операторы в разных сеансах, и эти операторы могут обрабатываться как часть транзакции. Разные сеансы могут отправлять запросы одновременно, а это означает, что сервер может одновременно обрабатывать несколько транзакций, что приведет к тому, что разные транзакции будут обращаться к одной и той же записи в одно и то же время. Ранее мы говорили, что у транзакций есть функция, называемая隔离性, теоретически, когда транзакция обращается к определенным данным, другие транзакции должны быть поставлены в очередь, и после того, как транзакция зафиксирована, другие транзакции могут продолжать получать доступ к данным. Но это слишком сильно влияет на производительность, поэтому дядя, разработавший базу данных, предложил различные隔离级别, чтобы максимизировать способность системы одновременно обрабатывать транзакции, но это также за счет определенных隔离性достигать.

ЧИТАТЬ БЕЗ ЗАЯВЛЕНИЙ

еслиТранзакция прочитала данные, измененные другой незафиксированной транзакцией., то это隔离级别просто позвони未提交读(Английское имя:READ UNCOMMITTED), схема выглядит следующим образом:

image_1d6t5hhamcd61qkjk9v1ag8171o7u.png-95.6kB

Как показано выше,Session AиSession BКаждый открыл транзакцию,Session Bлидеры бизнеса вidза1столбец записейcобновить до'关羽',ПотомSession AВ транзакции перейдите к запросу этогоidза1записи, затем в未提交读На уровне изоляции результат запроса'关羽', то есть транзакция прочитала запись, измененную другой незафиксированной транзакцией. но еслиSession BТранзакция откатывается позже, затемSession AТранзакция эквивалентна чтению несуществующих данных, это явление называется脏读, нравится:

image_1d6uqql7n55t1k7mmellrh14a495.png-105.3kB

脏读идет вразрез с последствиями для бизнеса в реальном мире, так что этоREAD UNCOMMITTEDэто очень небезопасно隔离级别.

ПРОЧИТАТЬ СОВЕРШЕНО

еслиТранзакция может только читать данные, измененные другой транзакцией, которая была зафиксирована, и каждый раз, когда другие транзакции изменяют и фиксируют данные, транзакция может запрашивать последнее значение., то это隔离级别просто позвони已提交读(Английское имя:READ COMMITTED), как показано на рисунке:

image_1d6t64lgg1j4mtp818f61n09t6l8o.png-133.1kB

Как видно из рисунка, на шаге 4 за счетSession BТранзакция не была совершена, поэтомуSession AРезультат запроса транзакции в'刘备', а на шаге 6, посколькуSession BТранзакция уже зафиксирована, поэтомуSession BРезультат запроса транзакции в'关羽'.

где-то已提交读Для транзакций на уровне изоляции, пока другие транзакции изменяют значение определенных данных, а затем отправляют их, транзакция будет считывать последнее значение данных, например:

image_1d6urs4l0g799959e1jsj1cvqai.png-170.6kB

мы вSession Bсовершает несколько неявных транзакций вidза1Значение столбца c записи после каждой фиксации транзакции,Session AВсе транзакции в могут просматривать последнее значение. Это явление также называют不可重复读.

ПОВТОРЯЕМОЕ ЧТЕНИЕ

В некоторых бизнес-сценарияхТранзакция может только читать данные, измененные другой зафиксированной транзакцией, но после первого чтения записи, даже если другие транзакции изменяют значение записи и фиксируют, когда транзакция читает запись позже, значение по-прежнему читается первым время, вместо того, чтобы каждый раз читать разные данные. тогда это隔离级别просто позвони可重复读(Английское имя:REPEATABLE READ), как показано на рисунке:

image_1d6useq9aagi9981sm21b011dt4bf.png-171.1kB

Как видно из рисунка,Session AТранзакция в первом чтенииidза1записей, столбецcзначение'刘备', хотя послеSession Bнеявно фиксирует несколько транзакций, каждая из которых изменяет эту запись, ноSession AСтолбцы, прочитанные транзакциями вcЗначение по-прежнему'刘备', что является тем же значением, что и первое чтение.

Сериализация (SERIALIZABLE)

Все три вышеуказанных уровня изоляции допускают одну и ту же запись.读-读,读-写,写-读параллельных операций, если мы не допускаем读-写,写-读для параллельных операций вы можете использоватьSERIALIZABLEУровень изоляции, принципиальная схема выглядит следующим образом:

image_1d6uu0sk41213olj102t1tsa10o9ds.png-122.9kB

Как показано на рисунке, когдаSession BТранзакции в были обновленыidза1после записи, послеSession AКогда сделка вSession BПосле транзакции в коммитахSession AТолько транзакции в результате запроса могут быть получены.

цепочка версий

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

trx_id: каждый раз, когда изменяется запись кластеризованного индекса, соответствующий идентификатор транзакции будет назначен дляtrx_idСкрыть столбцы.
roll_pointer: при каждом изменении записи кластеризованного индекса старая версия будет записываться вundo日志, то этот скрытый столбец эквивалентен указателю, по которому можно найти информацию до модификации записи.

Скажем, наш столtТеперь содержит только одну запись:

mysql> SELECT * FROM t;
+----+--------+
| id | c      |
+----+--------+
|  1 | 刘备   |
+----+--------+
1 row in set (0.01 sec)

Предположим, что идентификатор транзакции, которая вставила запись,80, то принципиальная схема записи в этот момент такова:

image_1d6vemvvn1db6h431ekvsp158m19.png-15kB

Предположим, следующие дваidсоответственно100,200транзакция по этой записиUPDATEЭксплуатация, процесс работы выглядит следующим образом:

image_1d6vfo4g814h019mj1jqb1ggu72o3j.png-106.5kB

Советы: Можно ли перекрестно обновить одну и ту же запись в двух транзакциях? Ха-ха, это не разрешено. После того, как первая транзакция обновит определенную запись, она заблокирует запись. Когда другая транзакция снова обновится, ей нужно дождаться фиксации первой транзакции, а затем блокировку можно снять. Продолжайте обновлять . Эта статья не о блокировках, подробнее о блокировках будет рассказано позже.

Каждый раз, когда в запись вносятся изменения, будет сделана записьundo日志, каждыйundo日志также есть одинroll_pointerАтрибуты(INSERTоперация, соответствующаяundo日志не имеет этого атрибута, так как запись не имеет более ранней версии), они могут бытьundo日志Все они соединены в связанный список, поэтому текущая ситуация похожа на следующий рисунок:

image_1d6vfrv111j4guetptcts1qgp40.png-57.1kB

После каждого обновления записи старое значение будет помещаться вundo日志, даже если это старая версия записи, по мере увеличения количества обновлений все версии будутroll_pointerАтрибуты связаны в связанный список, мы называем этот связанный список版本链, головной узел цепочки версий — это последнее значение текущей записи. Кроме того, каждая версия также содержит соответствующий идентификатор транзакции при создании версии.Эта информация очень важна, и мы будем использовать ее позже.

ReadView

Для использованияREAD UNCOMMITTEDДля транзакций на уровне изоляции достаточно напрямую прочитать последнюю версию записи.SERIALIZABLEДля транзакций уровня изоляции блокировки используются для доступа к записям. Для использованияREAD COMMITTEDиREPEATABLE READДля транзакций уровня изоляции нам нужно использовать то, что мы сказали выше.版本链Теперь главный вопрос:Необходимо определить, какая версия в цепочке версий видна для текущей транзакции.. так дизайнInnoDBДядя предложилReadViewконцепция, этоReadViewВ основном он включает в себя, какие активные транзакции чтения и записи все еще находятся в текущей системе, и помещает их идентификаторы транзакций в список, мы называем этот список какm_ids. Таким образом, при доступе к записи вам нужно только выполнить следующие шаги, чтобы определить, видна ли версия записи:

Если доступная версияtrx_idстоимость имущества меньшеm_idsНаименьший идентификатор транзакции в списке, указывающий, что транзакция, сгенерировавшая эту версию, генерируетReadViewбыла зафиксирована ранее, поэтому эта версия может быть доступна для текущей транзакции.
Если доступная версияtrx_idстоимость имущества больше, чемm_idsСамый большой идентификатор транзакции в списке, указывающий, что транзакция, сгенерировавшая эту версию, генерируетReadViewсоздается позже, поэтому текущая транзакция не может получить доступ к этой версии.
Если доступная версияtrx_idстоимость недвижимости вm_idsМежду наибольшим идентификатором транзакции и наименьшим идентификатором транзакции в списке вам нужно судитьtrx_idЗначение атрибута вm_idsсписок, если есть, описание для созданияReadViewКогда транзакция, сгенерировавшая версию, все еще активна, доступ к версии невозможен; если нет, значит, версия созданаReadViewКогда транзакция, создавшая версию, зафиксирована, к версии можно получить доступ.

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

существуетMySQLсередина,READ COMMITTEDиREPEATABLE READОчень большая разница между уровнями изоляции заключается в том, что они генерируютReadViewСроки другие, давайте посмотрим.

READ COMMITTED --- генерировать ReadView перед каждым чтением данных

Например, есть дваidсоответственно100,200Транзакция выполняется:

# Transaction 100
BEGIN;

UPDATE t SET c = '关羽' WHERE id = 1;

UPDATE t SET c = '张飞' WHERE id = 1;

# Transaction 200
BEGIN;

# 更新了一些别的表的记录
...

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

В этот момент столtсерединаidза1Список версий, полученный из записей, выглядит следующим образом:

image_1d6vgdl0j1c9d16rbelo1deh17324d.png-42.2kB

Предположим, теперь есть применениеREAD COMMITTEDТранзакция на уровне изоляции начинает выполняться:

# 使用READ COMMITTED隔离级别的事务
BEGIN;

# SELECT1：Transaction 100、200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'刘备'

этоSELECT1Процесс выполнения следующий:

в исполненииSELECTоператор сначала создастReadView,ReadViewизm_idsСодержание списка[100, 200].
Затем выберите видимые записи из цепочки версий, как видно из рисунка, столбец для последней версииcСодержание'张飞', эта версияtrx_idзначение100,существуетm_idsсписок, поэтому не соответствует требованиям видимости, согласноroll_pointerПерейти к следующей версии.
следующая версия столбцовcСодержание'关羽', эта версияtrx_idзначение также100, такжеm_idsВ списке, значит, не соответствует требованиям, продолжайте переходить к следующей версии.
следующая версия столбцовcСодержание'刘备', эта версияtrx_idзначение80, меньше, чемm_idsнаименьший идентификатор транзакции в списке100, так что эта версия соответствует требованиям, и окончательная версия, возвращаемая пользователю, — это столбецcза'刘备'запись о.

После этого мы помещаем идентификатор транзакции как100Транзакция фиксируется, например:

# Transaction 100
BEGIN;

UPDATE t SET c = '关羽' WHERE id = 1;

UPDATE t SET c = '张飞' WHERE id = 1;

COMMIT;

Затем перейдите к идентификатору транзакции как200Обновить таблицу в транзакцииtсерединаidЗапись 1:

# Transaction 200
BEGIN;

# 更新了一些别的表的记录
...

UPDATE t SET c = '赵云' WHERE id = 1;

UPDATE t SET c = '诸葛亮' WHERE id = 1;

В этот момент столtсерединаidза1Цепочка версий записей выглядит так:

image_1d6vgrt5jeh2itl5e41ocl944q.png-57.6kB

а затем просто используйтеREAD COMMITTEDВ транзакции уровня изоляции продолжайте находить этот идентификатор как1записи следующим образом:

# 使用READ COMMITTED隔离级别的事务
BEGIN;

# SELECT1：Transaction 100、200均未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'刘备'

# SELECT2：Transaction 100提交，Transaction 200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'张飞'

этоSELECT2Процесс выполнения следующий:

в исполненииSELECTоператор сначала создастReadView,ReadViewизm_idsСодержание списка[200](идентификатор транзакции100эта транзакция уже зафиксирована, поэтому снимок был сделан без нее).
Затем выберите видимые записи из цепочки версий, как видно из рисунка, столбец для последней версииcСодержание'诸葛亮', эта версияtrx_idзначение200,существуетm_idsсписок, поэтому не соответствует требованиям видимости, согласноroll_pointerПерейти к следующей версии.
следующая версия столбцовcСодержание'赵云', эта версияtrx_idзначение200, такжеm_idsВ списке, значит, не соответствует требованиям, продолжайте переходить к следующей версии.
следующая версия столбцовcСодержание'张飞', эта версияtrx_idзначение100,Сравниватьm_idsнаименьший идентификатор транзакции в списке200Он еще меньше, поэтому эта версия соответствует требованиям, а окончательная версия, возвращаемая пользователю, — это столбец.cза'张飞'запись о.

И так далее, если идентификатор транзакции200Запись также отправлена и снова используется здесьREAD COMMITTEDЗапрос таблицы в транзакции уровня изоляцииtсерединаidзначение1, полученный результат'诸葛亮'Сейчас мы не будем анализировать конкретный процесс. Подвести итог:Транзакции, использующие уровень изоляции READ COMMITTED, создают отдельный ReadView в начале каждого запроса..

`REPEATABLE READ`--- Создать ReadView при первом чтении данных

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

Например, есть дваidсоответственно100,200Транзакция выполняется:

# Transaction 100
BEGIN;

UPDATE t SET c = '关羽' WHERE id = 1;

UPDATE t SET c = '张飞' WHERE id = 1;

# Transaction 200
BEGIN;

# 更新了一些别的表的记录
...

В этот момент столtсерединаidза1Список версий, полученный из записей, выглядит следующим образом:

Предположим, теперь есть применениеREPEATABLE READТранзакция на уровне изоляции начинает выполняться:

# 使用REPEATABLE READ隔离级别的事务
BEGIN;

# SELECT1：Transaction 100、200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'刘备'

этоSELECT1Процесс выполнения следующий:

в исполненииSELECTоператор сначала создастReadView,ReadViewизm_idsСодержание списка[100, 200].
Затем выберите видимые записи из цепочки версий, как видно из рисунка, столбец для последней версииcСодержание'张飞', эта версияtrx_idзначение100,существуетm_idsсписок, поэтому не соответствует требованиям видимости, согласноroll_pointerПерейти к следующей версии.
следующая версия столбцовcСодержание'关羽', эта версияtrx_idзначение также100, такжеm_idsВ списке, значит, не соответствует требованиям, продолжайте переходить к следующей версии.
следующая версия столбцовcСодержание'刘备', эта версияtrx_idзначение80, меньше, чемm_idsнаименьший идентификатор транзакции в списке100, так что эта версия соответствует требованиям, и окончательная версия, возвращаемая пользователю, — это столбецcза'刘备'запись о.

После этого мы помещаем идентификатор транзакции как100Транзакция фиксируется, например:

# Transaction 100
BEGIN;

UPDATE t SET c = '关羽' WHERE id = 1;

UPDATE t SET c = '张飞' WHERE id = 1;

COMMIT;

Затем перейдите к идентификатору транзакции как200Обновить таблицу в транзакцииtсерединаidЗапись 1:

# Transaction 200
BEGIN;

# 更新了一些别的表的记录
...

UPDATE t SET c = '赵云' WHERE id = 1;

UPDATE t SET c = '诸葛亮' WHERE id = 1;

В этот момент столtсерединаidза1Цепочка версий записей выглядит так:

а затем просто используйтеREPEATABLE READВ транзакции уровня изоляции продолжайте находить этот идентификатор как1записи следующим образом:

# 使用REPEATABLE READ隔离级别的事务
BEGIN;

# SELECT1：Transaction 100、200均未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'刘备'

# SELECT2：Transaction 100提交，Transaction 200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值仍为'刘备'

этоSELECT2Процесс выполнения следующий:

потому что он был сгенерирован раньшеReadView, поэтому повторно используйте предыдущийReadView,предыдущийReadViewсерединаm_idsсписок[100, 200].
Затем выберите видимые записи из цепочки версий, как видно из рисунка, столбец для последней версииcСодержание'诸葛亮', эта версияtrx_idзначение200,существуетm_idsсписок, поэтому не соответствует требованиям видимости, согласноroll_pointerПерейти к следующей версии.
следующая версия столбцовcСодержание'赵云', эта версияtrx_idзначение200, такжеm_idsВ списке, значит, не соответствует требованиям, продолжайте переходить к следующей версии.
следующая версия столбцовcСодержание'张飞', эта версияtrx_idзначение100,иm_idsСписок содержит значения как100идентификатора транзакции, поэтому эта версия не соответствует требованиям, и то же самое верно для следующего столбцаcСодержание'关羽'версия также не соответствует требованиям. Продолжайте переходить к следующей версии.
следующая версия столбцовcСодержание'刘备', эта версияtrx_idзначение80,80меньше, чемm_idsнаименьший идентификатор транзакции в списке100, так что эта версия соответствует требованиям, и окончательная версия, возвращаемая пользователю, — это столбецcза'刘备'запись о.

то есть дваждыSELECTРезультаты, полученные запросом, повторяются, столбцы записейcзначения'刘备',Это可重复读значение. Если мы затем поместим идентификатор транзакции как200Запись отправлена, а затем использовать ее только сейчасREPEATABLE READВ транзакции уровня изоляции продолжайте находить этот идентификатор как1запись, результат еще'刘备', вы можете сами проанализировать конкретный процесс внедрения.

Резюме MVCC

Из вышеприведенного описания видно, что так называемый MVCC (Multi-Version Concurrency Control, многоверсионный контроль параллелизма) относится к использованиюREAD COMMITTD,REPEATABLE READТранзакции этих двух уровней изоляции выполняются обычным образом.SEELCTПроцесс доступа к цепочке записей версий во время работы, чтобы можно было выполнять различные транзакции.读-写,写-读Операции выполняются одновременно, что повышает производительность системы.READ COMMITTD,REPEATABLE READБольшая разница между двумя уровнями изоляции заключается в том, что генерацияReadViewразные тайминги,READ COMMITTDобщее в каждомSELECTПрежде чем операция создастReadView,иREPEATABLE READНормально только первый разSELECTгенерироватьReadView, и последующие операции запроса повторяют этоReadViewДостаточно.

буклет

Если вы хотите увидеть более продвинутые знания MySQL, вы можете проверить это в буклете:Ссылка на статью «Как работает MySQL: понимание MySQL у истоков». Содержание буклета в основном с точки зрения Xiaobai, с использованием относительно популярного языка для объяснения некоторых основных концепций расширенного MySQL, таких как записи, индексы, страницы, табличные пространства, оптимизация запросов, транзакции и блокировки и т. д. количество слов составляет от 300 000 до 400 000 слов, с сотнями оригинальных иллюстраций. Основная цель состоит в том, чтобы облегчить обычным программистам изучение MySQL для продвинутых пользователей и сделать кривую обучения более плавной.

а

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

ЧИТАТЬ БЕЗ ЗАЯВЛЕНИЙ

ПРОЧИТАТЬ СОВЕРШЕНО

ПОВТОРЯЕМОЕ ЧТЕНИЕ

Сериализация (SERIALIZABLE)

цепочка версий

ReadView

READ COMMITTED --- генерировать ReadView перед каждым чтением данных

REPEATABLE READ--- Создать ReadView при первом чтении данных

Резюме MVCC

буклет

`REPEATABLE READ`--- Создать ReadView при первом чтении данных