Блокировка MySQL

Существует много концепций блокировок в MySQL, вы часто можете их услышать: оптимистические блокировки, пессимистические блокировки, блокировки строк, блокировки таблиц, блокировки Gap (блокировки пробелов), блокировки MDL (блокировки метаданных), блокировки намерения, блокировки чтения, блокировки записи. , общие блокировки, монопольные блокировки. Этот тип блокировки заставляет людей думать, поэтому я читаю некоторые блоги, некоторые говорят об оптимистической блокировке, пессимистической блокировке, некоторые говорят о блокировке чтения, блокировке записи, так что оптимистическая блокировка и пессимистическая блокировка, кажется, понимают, блокировка чтения и блокировка записи кажутся понимаю, но я до сих пор не знаю, как его использовать, и я не знаю, имеет ли оптимистическая блокировка какое-либо отношение к блокировке чтения и блокировке записи? Прочитав множество статей, я постепенно понял взаимосвязь между ними, поэтому написал эту статью, чтобы разобраться в своих идеях. Возможности ограничены, ошибки неизбежны, обращайтесь к ним по мере необходимости.

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

Читайте замки и напишите замки

Прежде всего, блокировки чтения также имеют имя, называемое разделяемыми блокировками, а блокировки записи также имеют соответствующее имя, называемое исключительными блокировками, что означает, что разделяемые блокировки и блокировки чтения — это одно и то же, а исключительные блокировки и блокировки записи — одно и то же. Блокировки чтения и записи — это блокировки на уровне реализации системы, а также самые основные блокировки. Блокировки чтения и блокировки записи также являются свойствами блокировок.Например, в блокировках строк есть блокировки записи строк и блокировки чтения строк. В блокировках MDL также есть блокировки записи MDL и блокировки чтения MDL. Отношения блокировки между блокировками чтения и блокировками записи следующие: Y означает сосуществование, X означает взаимное исключение.

	блокировка чтения	блокировка записи
блокировка чтения	Y	X
блокировка записи	X	X

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

В предыдущей статье была представлена ​​модель хранения MySQL.Для механизма InnoDB используется индекс дерева B+.Предполагая, что необходимо заблокировать всю таблицу, необходимо добавить блокировку к каждому узлу всего дерева B+.Очевидно, это очень неэффективная практика. Поэтому MySQL предлагает концепцию преднамеренной блокировки, которая означает, что если вы хотите заблокировать узел, вы должны добавить преднамеренные блокировки ко всем его узлам-предкам. Существуют более сложные схемы блокировки по намерению.Если вы хотите узнать больше, вы можете прочитать книгу «Вероятность систем баз данных».

Блокировки таблицы и блокировки строк

Блокировки таблиц и блокировки строк — это два типа блокировок с разной степенью детализации блокировки. В дополнение к блокировкам таблиц и строк существуют более детальные блокировки — глобальные блокировки.

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

Блокировка стола:Блокировка таблицы заблокирует таблицу, MySQL использует таблицы блокировки

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

блокировка строки:Блокировка строки заблокирует одну или несколько строк в таблице. MySQL использует команду блокировки в режиме общего доступа, чтобы добавить к строке блокировку чтения, и команду for update, чтобы добавить к строке блокировку записи. Блокировка строки не требуется. для явного освобождения при фиксации транзакции. , блокировки строк, добавленные в транзакцию, будут сняты. Все строки, где k равно 1, могут быть заблокированы командой select k from t, где k = 1 для команды обновления. Кроме того, при использовании команды update для обновления данных таблицы блокировки строк автоматически добавляются к строкам совпадений. Кроме того, когда MySQL добавляет блокировки строк, он не блокирует все строки одновременно. После выполнения команды обновления серверный уровень отправляет команду механизму InnoDB. Механизм InnoDB находит первый фрагмент данных, удовлетворяющий условиям, и возвращается после блокировки Уровень сервера обновляет эти данные, а затем передает их механизму InnoDB. После завершения обновления этого фрагмента данных уровень сервера получает следующий фрагмент данных.

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

mysql-> create table t(id int not null auto_increment, c int not null, primary key(id))ENGINE=InnoDB;
mysql-> insert into t(id, c) values (1, 1), (2, 2), (3, 3);

Транзакция А	Транзакция Б	Транзакция С
begin
select * from t where id = 3 for update;
	update t set c = 0 where id = c;
		set session transaction isolation level READ UNCOMMITTED; select * from t;
commit

Транзакция A Executive SELECT * from T, где id = 3 для обновления Блокирует идентификатор, равный 3, а транзакция B блокируется при выполнении команды Update. В это время включите транзакцию C и измените уровень изоляции транзакции C, чтобы не отправлять чтение.Получается следующая таблица, найдите, что первые две строки были обновлены, последний идентификатор равен 3, что указывает на то, что транзакция B блокирует Здесь.

mysql> select *  from t;
+----+---+
| id | c |
+----+---+
|  1 | 0 |
|  2 | 0 |
|  3 | 3 |
+----+---+

оптимистическая блокировка и пессимистическая блокировка

оптимистическая блокировка

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

пессимистический замок

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

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

Реализация оптимистической блокировки

Есть два способа реализовать оптимистическую блокировку: номер версии и алгоритм CAS.

номер версии

Существуют следующие шаги для реализации оптимистической блокировки по номеру версии:

1 Добавьте поле версии к каждому элементу данных с указанием номера версии

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

3 При последнем обновлении сравните version1 и номер текущей версии в базе данных, чтобы убедиться, что они совпадают. Выраженный в операторе SQL, это update t set version = version + 1, где version = version1. Согласно статье о предыдущей транзакции, мы знаем, что текущее чтение будет выполняться во время операции обновления, поэтому даже при уровне изоляции повторяемого чтения будет получен номер последней версии. Если никакая другая транзакция не обновляла эти данные, тогда версия равна версии1, поэтому обновление выполнено успешно. Если другие транзакции обновили эти данные, то значение поля версии будет увеличено, тогда версия не будет равна версии1, поэтому обновление не вступит в силу.

CAS-алгоритм

CAS — это аббревиатура от слова «сравнение и обмен», что в переводе с китайского означает «сравнить, а затем обменять». Псевдокод для реализации CAS:

<< atomic >>
bool cas(int* p, int old, int new)  
{
    if (*p != old)
    {
        return false
    }
    *p = new
    return true
}

Среди них P — это указатель переменной, которую нужно изменить, Old — это старое значение до изменения, а New — это новое значение, которое будет записано. Этот псевдокод означает, что значение, на которое указывает P, совпадает со старым значением, если другие данные описания были изменены другими потоками, возвращая false. Если новое значение присваивается объекту, указывает на P, возвращается к TRUE. Весь этот процесс реализуется с помощью аппаратной синхронизации, гарантируя атомарность всего процесса.

Большинство языков реализуют функции CAS, например язык C, реализованный в GCC:

bool__sync_bool_compare_and_swap (type *ptr, type oldval type newval, ...)
type __sync_val_compare_and_swap (type *ptr, type oldval type newval, ...)

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

Блокировка MDL и блокировка Gap

блокировка MDL

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

На что MDL должен обратить внимание, так это на то, чтобы блокировки записи MDL не блокировали блокировки чтения MDL.

Транзакция А	Транзакция Б	Транзакция С	Транзакция Д
select * from t
	select * from t
		alter table t add c int
			select * from t

После того, как транзакция A выполнит select, в таблицу t будет добавлена ​​блокировка чтения MDL. После того, как транзакция B выполнит выбор, в таблицу снова добавляется блокировка чтения MDL, и блокировка чтения и блокировка чтения совместимы. Когда транзакция C выполняет команду alter, она блокируется и должна добавить блокировку записи MDL в таблицу t. То, что транзакция C заблокирована, не является большой проблемой, но это приведет к блокировке всех последующих транзакций, таких как транзакция D. Это сделано для того, чтобы избежать голодания блокировок записи. MySQL оптимизирует блокировки. Когда есть ожидающие блокировки записи, новые блокировки чтения должны ждать. Если транзакция C не может получить блокировку в течение длительного времени или если транзакция C выполняется в течение длительного времени, работа базы данных будет заблокирована.

Есть несколько идей по оптимизации, чтобы избежать этого:

1 Избегайте длинных транзакций. Если транзакция A и транзакция B являются длинными транзакциями, это может привести к тому, что транзакция C будет заблокирована блокировкой записи MDL в течение длительного времени.

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

3 Добавьте таймаут ожидания к команде alter

Гэп-блокировка

Гэп-блокировки вводятся движком InnoDB, чтобы избежать фантомных чтений. Как упоминалось в статье о транзакциях MySQL, механизм InnoDB может избегать фантомных чтений при уровне изоляции повторяющегося чтения. Блокировки пробелов блокируют промежутки между строками данных, чтобы предотвратить вставку новых данных. Блокировка пробела будет добавлена ​​только тогда, когда выполняется текущее чтение. О том, что сейчас читается, можно посмотреть в моей прошлой статье»транзакции MySQL".

Практика блокировки

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

карта разума

В конце статьи я поместил ментальную карту MySQL, которую я обобщил, которая является кратким изложением серии статей о MySQL.

Ссылаться на

[1] Концепции системы баз данных (6-е издание)

[2] Курс MySQL по борьбе с 45 лекциями, Линь Сяобинь

[3] Высокопроизводительные MySQL (3-е издание)

[4] Уровень изоляции транзакций и модификация уровня изоляции транзакций mysql

[5] Анализ обработки блокировок MySQL, Хэ Дэнчэн

[6] Оптимистический замок, пессимистический замок, этой статьи достаточно!