(I. Концепция и характеристики блокировки MySQL)
В системе баз данных MySQL различные механизмы хранения поддерживают различные механизмы блокировки. Например, механизмы хранения MyISAM и памяти используют блокировки на уровне таблиц. База данных BDB использует блокировки страниц и блокировки на уровне таблиц. Механизм хранения InnoDB поддерживает блокировки как на уровне строк, так и на уровне таблиц. По умолчанию используются блокировки на уровне строк.
Особенности этих трех блокировок MySQL можно резюмировать следующим образом:
| Замок стола | Небольшие накладные расходы, быстрая блокировка, отсутствие взаимоблокировки; большая детализация блокировки, самая высокая вероятность конфликта блокировок, самая низкая степень параллелизма. |
| Блокировка уровня строки | Он обладает такими характеристиками, как высокая стоимость, медленная блокировка, взаимоблокировка, минимальная детализация блокировки, наименьшая вероятность конфликта блокировок и самый высокий параллелизм. |
| Блокировка страницы | Стоимость и время блокировки находятся между блокировкой таблицы и блокировкой строки; произойдет взаимоблокировка; детализация блокировки находится между блокировкой таблицы и блокировкой строки, а параллелизм является общим. |
В этом блоге будут записаны блокировки на уровне таблиц в механизме хранения MyISAM
(II) Режим блокировки таблицы MyISAM
1. Блокировка MyISAM прочитана
Совместное чтение: операция чтения таблицы MyISAM не блокирует запросы других пользователей на чтение в ту же таблицу, но блокирует запросы на запись в ту же таблицу
Оперативная команда:
// lock Lock table table name read // unlock unlock tables
Реальный боевой сценарий:
clientA:
Lock table roles read; // read lock
Select * from roles where id = 1; // query succeeded
clientB:
Select * from roles where id = 1; // query succeeded
Update roles set name ='root '; // stuck, waiting for the lock to release
ClientA:
Unlock tables; // unlock
clientB:
Update roles set name ='root2 '; // update succeeded2. Запись блокировки MyISAM
Исключительная запись: операция записи таблицы MyISAM заблокирует операции чтения и записи других пользователей в ту же таблицу.
Оперативная команда:
// lock Lock table table name write // unlock unlock tables
Реальный боевой сценарий:
clientA:
Lock table roles write; // write lock
Select * from roles where id = 1; // query succeeded
Update roles set name ='admin 'where id = 1; // update succeeded
clientB:
Select * from roles where id = 1; // stuck, waiting for the lock to release
ClientA:
Unlock tables; // unlock
clientB:
Select * from roles where id = 1; // query succeeded(III) анализ конфликтов блокировок на уровне таблиц
Конфликт блокировок таблиц в системе анализируется путем проверки переменных состояния ожидающих блокировок таблиц и немедленных блокировок таблиц
show status like '%table_lock%'
Анализ: если ожидаемое значение блокировки таблицы относительно велико, это указывает на серьезные проблемы с блокировкой на уровне таблицы, проблемы с производительностью, высокий уровень параллелизма и необходимость оптимизации
(IV) о планировании блокировки MyISAM
Блокировки чтения и записи механизма хранения MyISAM являются взаимоисключающими, а операции чтения являются последовательными.
Итак, как работать с MySQL, когда один процесс запрашивает блокировку чтения таблицы MyISAM, а другой процесс также запрашивает блокировку записи той же таблицы? Ответ заключается в том, что процесс записи сначала получает блокировку. Мало того, даже если процесс чтения сначала запрашивает очередь ожидания блокировки, а запрос на запись поступает позже, блокировка записи будет вставлена до запроса на чтение! Это связано с тем, что MySQL считает, что запросы на запись, как правило, более важны, чем запросы на чтение. Вот почему таблица MyISAM не подходит для приложений с большим количеством операций обновления и операций запроса, поскольку большое количество операций обновления затруднит операции запроса для получения блокировок чтения, которые могут блокироваться навсегда. Иногда это может быть очень плохо! К счастью, мы можем настроить поведение MyISAM при планировании с помощью некоторых настроек.
- Указав начальный параметр обновления с низким приоритетом, механизм MyISAM по умолчанию предоставляет приоритет запросам на чтение.
- Приоритет запроса на обновление, выданного соединением, уменьшается за счет выполнения команды установить низкий приоритет.
- Уменьшите приоритет инструкции insert, update, delete, указав атрибут с низким приоритетом.
Хотя вышеупомянутые три метода являются либо первыми обновлениями, либо первыми запросами, они все равно могут быть использованы для решения серьезной проблемы ожидания блокировки чтения в относительно важных приложениях (таких как система входа пользователя).
Кроме того, MySQL также предоставляет компромиссный метод для настройки конфликта чтения и записи, то есть для установки правильного значения для системного параметра Max количество блокировок записи. Когда блокировка чтения таблицы достигает этого значения, MySQL временно снижает приоритет запроса на запись, давая процессу чтения определенный шанс получить блокировку.
Ссылка: подробная информация о блокировке таблиц движка MyISAM и блокировке строк InnoDB механизма блокировки базы данных MySQL