MySql (3) Механизм блокировки в MySql

задняя часть база данных MySQL SQL
MySql (3) Механизм блокировки в MySql

В предыдущих двух блогах мы кратко говорили об индексах в mysql, а сегодня поговорим о блокировках в mysql (движок InnoDB) и реализации транзакций

MySql (1) Анализ индекса MySql

MySQL (2) Обычно используемая оптимизация MySql

Когда дело доходит до замков, с ними должен быть знаком каждый. Например, в Java распространена оптимистическая блокировка, реализуемая алгоритмом CAS. Типичным примером является атомарный класс. Обновление атомарной операции реализуется через спин CAS. Пессимистическая блокировка обычноSynchronizedиLockвыполнить.

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

  • **Оптимистическая блокировка: **Каждый раз, когда вы читаете данные, вы думаете, что другие люди не будут их изменять, поэтому они не будут заблокированы, но чтобы определить, обновили ли данные другие люди во время обновления, вы можете использовать номер версии механизм. В базе данных это может быть достигнуто путем добавления поля номера версии в таблицу данных. Номер версии считывается вместе при чтении данных, и номер версии увеличивается на единицу при каждом обновлении данных. При обновлении сравниваем текущий номер версии соответствующей записи в таблице БД со значением номера версии, вынутым в первый раз.Если значение равно, обновляем, иначе считается просроченными данными. Оптимистическая блокировка подходит для типов приложений с множественным чтением и может повысить пропускную способность.

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

Общие блокировки и эксклюзивные блокировки

  • **Общие блокировки: **Общие блокировки также называются блокировками чтения или блокировками S. После добавления общей блокировки другие транзакции могут добавлять общие блокировки только до окончания транзакции и могут выполнять только операции чтения, но не операции записи на них. , Никакие другие замки не могут быть добавлены.

    плюс блокировка в режиме общего доступа

    SELECT description FROM book_book lock in share mode;

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

    плюс за обновление

    SELECT description FROM book_book for update;

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

Разница между блокировками строк и блокировками таблиц заключается в степени детализации блокировок. Подсистема Innodb поддерживает как блокировки строк, так и блокировки таблиц (подсистема MyISAM поддерживает только блокировки таблиц). InnoDB использует блокировки на уровне строк, только когда данные извлекаются с помощью условий индекса. В противном случае, InnoDB будет использовать блокировки на уровне строк.

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

Здесь есть небольшая путаница, почему в блокировках таблиц нет взаимоблокировок? существуетMyISAMПоскольку транзакции нет, блокировка снимается после выполнения SQL, и циклического ожидания не будет, поэтому произойдет только блокировка, а взаимоблокировка не возникнет. Но когдаЕсли есть транзакция в InnoDB, это более запутанно.Я надеюсь, что некоторые друзья, которые понимают это, могут дать указатели @-@

Вот два примера, иллюстрирующих вышеперечисленные типы замков:

# 事务1
BEGIN;
SELECT description FROM book_book where name = 'JAVA编程思想' lock in share mode;

# 事务2
BEGIN;
UPDATE book_book SET name = 'new book' WHERE name = 'new';

# 查看事务状态
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

trx_id  trx_state       trx_started           trx_tables_locked    trx_rows_locked
39452	LOCK WAIT	2018-09-08 19:01:39	    1	                1	
282907511143936	RUNNING	2018-09-08 18:58:47	    1	                38

Транзакция 1 добавляет разделяемую блокировку к таблице book, а транзакция 2 пытается изменить таблицу book и блокируется.Глядя на состояние транзакции, вы можете знать, что транзакция 1 использует блокировку таблицы, поскольку она не переходит к индексу.

# 事务1
BEGIN;
SELECT description FROM book_book WHERE id = 2 lock in share mode;

# 事务2
BEGIN;
UPDATE book_book SET name = 'new book' WHERE id = 1; 

# 查看事务状态
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

trx_id          trx_state   trx_started     trx_tables_locked    trx_rows_locked
39454	        RUNNING	2018-09-08 19:10:44	1	                1	
282907511143936	RUNNING	2018-09-08 19:10:35	1	                1

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

Блокировка намерения (специфично для InnoDB)

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

  • **Преднамеренная блокировка.**Преднамеренная блокировка — это блокировка на уровне таблицы, представляющая операцию над строкой записей. Он делится на совместную блокировку намерения (IS) и монопольную блокировку намерения (IX).

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

  • **Ситуация без преднамеренной блокировки: **1.Определить, была ли таблица заблокирована другими транзакциями с блокировкой таблицы. 2. Определите, заблокирована ли каждая строка в таблице блокировкой строки, так что обход всей таблицы очень неэффективен.

  • ** Существуют преднамеренные блокировки: ** 1. Определите, была ли таблица заблокирована другими транзакциями с блокировками таблиц. 2. Определите, есть ли преднамеренная блокировка таблицы

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

  • Будет ли блокировка намерения конфликтовать:Между блокировками намерения не будет конфликта, поскольку блокировка намерения просто представляет собой операцию над рядом записей.

Сосуществование различных замков

       IX     IS       X      S
IX    兼容    兼容    冲突    冲突
IS    兼容    兼容    冲突    兼容
X     冲突    冲突    冲突    冲突
S     冲突    兼容    冲突    兼容

тупик

  • **Концепция: **Явление, при котором две или более транзакций ожидают друг друга из-за конкуренции за ресурсы в процессе выполнения.

  • **Условие существования:**1.Условие взаимного исключения: ресурс может использоваться только одной транзакцией за раз. 2. Условия запроса и удержания: когда транзакция блокируется из-за запроса ресурсов, она будет удерживать полученные ресурсы. 3. Условия недепривации: Приобретенные ресурсы не могут быть насильственно лишены до того, как они будут израсходованы. 4. Условие циклического ожидания: сформируйте отношения циклического ожидания, которые связаны «голова к хвосту».

  • **Выйти из состояния взаимоблокировки:** отменить одну из транзакций.

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

MVCC позволяет InnoDB лучше реализовать ПОВТОРЯЕМОЕ ЧТЕНИЕ на уровне изоляции транзакций.

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

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

  • 1.DB_TRX_ID: 6-байтовый идентификатор, каждый раз, когда обрабатывается транзакция, его значение автоматически равно +1, что можно найти с помощью инструкции «show engine innodb status».

  • 2.DB_ROLL_PTR: размер 7 байт, указывающий на запись журнала отмены, записанную в сегмент отката (сегмент отката).

  • 3.DB_ROW_ID: размер 6 байт, и значение монотонно увеличивается при вставке новых строк.

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

  • ВСТАВКА: InnoDB записывает номер текущей версии системы в качестве номера версии создания для каждой новой строки.

  • УДАЛИТЬ: InnoDB записывает номер текущей версии системы для каждой удаленной строки как номер версии удаления строки.

  • ОБНОВЛЕНИЕ: InnoDB скопировал часть данных. Номер версии этих данных использует номер версии системы. Он также рассматривает номер версии системы как номер удаления старых данных.

  • Примечание. Чтение здесь — это разблокированный выбор и т. д. MVCC реализует повторяемое чтение, читая зафиксированные данные в отмене, которая не блокирует. Во время операции вставки «время создания» = DB_ROW_ID, затем «время удаления» не определено; во время обновления скопируйте новую строку «время создания» = DB_ROW_ID, время удаления не определено, а старая строка данных «время создания» остается неизменной, время удаления = DB_ROW_ID транзакции, операция удаления, "время создания" соответствующей строки данных остается неизменным, время удаления = DB_ROW_ID транзакции;

гэп замок

Gap lock позволяет InnoDB решить проблему фантомного чтения, а MVCC позволяет уровню изоляции InnoDB RR достичь эффекта уровня сериализации и сохранить более высокую производительность параллелизма.

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

Например, в таблице book существуют записи с bookId 1–80 и 90–99. ВЫБЕРИТЕ * ИЗ книги, ГДЕ bookId

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

использованная литература: Dev.MySQL.com/doc/Furious/… woo woo woo.cn blog on.com/Chen Pingzha…

Категории