Блокировка строки MySQL, блокировка таблицы, детали блокировки промежутка

MySQL
Блокировка строки MySQL, блокировка таблицы, детали блокировки промежутка

предисловие

О том, что такое индекс и как его использовать, мы говорили в предыдущих статьях.explainАнализировать использование индексов, способы оптимизации индексов иshow profilesанализироватьSQLОбучение потреблению ресурсов выполнения операторов. Сегодня мы поговоримMySQLРазличные блокировки, здесь механизм хранения, который мы используемInnoDB

Готов к работе

Создайте таблицу tb_innodb_lock 

drop table if exists test_innodb_lock;
CREATE TABLE test_innodb_lock (
    a INT (11),
    b VARCHAR (20)
) ENGINE INNODB DEFAULT charset = utf8;
insert into test_innodb_lock values (1,'a');
insert into test_innodb_lock values (2,'b');
insert into test_innodb_lock values (3,'c');
insert into test_innodb_lock values (4,'d');
insert into test_innodb_lock values (5,'e');

создать индекс 

create index idx_lock_a on test_innodb_lock(a);
create index idx_lock_b on test_innodb_lock(b);

Демонстрация различных блокировок MySQL

  • Во-первых, измените автоматическую транзакцию фиксации на ручную фиксацию:set autocommit=0;
  • Мы запускаем два окна сеанса A и B, имитируя блокировку одного из них и блокировку другого.

блокировка строки (запись и чтение)

  • Оконное исполнение
update test_innodb_lock set b='a1' where a=1;

SELECT * from test_innodb_lock;

Мы видим, что окно A может видеть обновленный результат

  • исполнение окна B
SELECT * from test_innodb_lock;

Мы видим, что окно B не может видеть обновленные результаты, но старые данные все еще видны, потому что строка a = 1 заблокирована оператором SQL, выполняемым окном A, и операция фиксации не выполняется. Итак, окно B по-прежнему видит старые данные. Это «фиксация чтения» на уровне изоляции MySQL.

  • Окно A выполняет операцию фиксации
COMMIT;
  • Запрос окна B
SELECT * from test_innodb_lock;

В это время мы обнаружили, что окно B прочитало последние данные

блокировка строки (запись и запись)

  • Окно A выполняет обновление для записей с a = 1.
update test_innodb_lock set b='a2' where a=1;

В это время фиксации фиксации нет, и блокировка удерживается окном А.

  • Окно B также выполняет обновление записи с a = 1.
update test_innodb_lock set b='a3' where a=1;

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

  • Окно A выполняет операцию фиксации
COMMIT;
  • Окно B меняется

Вы можете видеть, что окно B было успешно выполнено в это время.

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

В случае сбоя индекса блокировка строки будет обновлена ​​до блокировки таблицы.Один из способов сделать индекс недействительным — это автоматически или вручную изменить индекс. Поле a само по себе является целым числом. Когда мы добавляем кавычки, оно становится строкой. В это время индекс будет недействительным.

  • Окно A обновляет записи с a = 1
update test_innodb_lock set b='a4' where a=1 or a=2;
  • Окно B обновляет запись с a = 2
update test_innodb_lock set b='b1' where a=3;

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

  • Окно A выполняет операцию фиксации
COMMIT;
  • Окно B меняется

Вы можете видеть, что окно B было успешно выполнено в это время.

гэп замок

  • Что такое гэп-лок

Когда мы запрашиваем данные, используя условия диапазона, InnoDB блокирует данные в этом диапазоне. Например, есть 4 куска данных с id: 1, 3, 5, 7, ищем данные в диапазоне 1-7. Тогда 1-7 будут заблокированы. 2, 4, 6 также находятся в диапазоне 1-7, но этих записей данных не существует, эти 2, 4, 6 называются пробелами.

  • Опасности щелевых замков

При поиске в диапазоне все данные во всем диапазоне будут заблокированы. Даже некоторые данные, которые не существуют в этом диапазоне, будут безвинно заблокированы. Например, если я хочу вставить 2 в 1, 3, 5 и 7, на этот раз 1-7 все заблокированы, а 2 вообще нельзя вставить. В некоторых сценариях производительность будет сильно снижена.

  • Демонстрация Gap Lock

Сначала мы изменяем значение поля a на 1, 3, 5, 7, 9.

  • Окно A обновляет данные в диапазоне a = 1~7.
update test_innodb_lock set b='b5' where a>1 and a<7;
  • Окно B вставляет данные в a = 2
insert into test_innodb_lock values(2, "b6");

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

анализ блокировки строк

  • Выполнение команд разбора SQL
show status like 'innodb_row_lock%';

  • Имя_переменной Описание

    • Innodb_row_lock_current_waits: количество ожидающих блокировок.

    • Innodb_row_lock_time: время, в течение которого система была заблокирована до настоящего момента.

    • Innodb_row_lock_time_avg: Среднее время ожидания блокировки каждый раз.

    • Innodb_row_lock_time_max: время ожидания самой длинной блокировки с момента запуска системы.

    • Innodb_row_lock_waits: общее количество раз, когда система ожидала блокировки с момента запуска системы.

Эпилог

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

айтишник

一个在大厂做高级Java开发的程序猿

Обратите внимание на публичный аккаунт WeChat: IT-брат

Ответ: полный набор учебных пособий по Java, которые вы можете получить: основы Java, веб-приложение Java, все учебные пособия по JavaEE, включая весеннюю загрузку и т. д.

Re: Шаблон резюме, вы можете получить 100 красивых резюме

Re: Маршрут обучения Java, вы можете получить самую последнюю и самую полную дорожную карту обучения

Re: Java eBook, вы можете получить 13 обязательных к прочтению книг для лучших программистов

Категории