Блокировки MySQL: виды, проблемы и способы обнаружения

Блокировки MySQL: виды, проблемы и способы обнаружения

Рано или поздно любой разработчик или администратор СУБД, имеющий дело с MySQL, сталкивается с проблемой блокировок. Всё дело в природе MySQL как системы с конкурентным доступом на чтение/запись. Я расскажу о видах блокировок в MySQL, их преимуществах и недостатках, о проблемах, которые они вызывают, а также дам полезные советы по обнаружению и способам борьбы с блокировками.

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

Краткая аналогия с блокировками баз данных

Представим, что вы разрабатываете программу для совместного редактирования текстовых документов, наподобие Google Docs. Вы закончили работу над первой версией и обнаружили, что ваши пользователи, работающие одновременно в программе, перетирают изменения друг друга. Вы решаете добавить в базу данных какое-нибудь булево значение is_locked, которое будет принимать значение true, если документ открыт пользователем. И если в этом случае другой пользователь попытается редактировать документ, ему придется подождать, пока первый не закончит свою работу. Такая схема будет работать, но не позволит поддерживать одновременное редактирование документа, поскольку только один пользователь сможет изменять документ в текущий момент времени.

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

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

Конкурентный доступ и виды блокировок

Конкурентный доступ является одной из главных проблем в системах управления базами данных. Что делать, когда несколько клиентов пытаются изменить одни и те же данные? А что если один из клиентов пытается получить данные, изменяемые другим в этот же момент времени? Можно привести много подобных примеров. Для разрешения этой проблемы в MySQL используется два типа блокировок: разделяемые (shared locks) и монолитные (exclusive locks). Их ещё называют блокировками на чтение и на запись. Блокировки на чтение являются разделяемыми, или неблокирующими. Это означает, что множество клиентов может читать запись в одно и то же время. Блокировки на запись являются эксклюзивными, то есть они не дадут другим клиентам захватывать блокировки на чтение/запись, поскольку это единственный безопасный способ гарантировать наличие единственного клиента, записывающего в данный момент времени.

Табличные блокировки

MySQL использует табличные блокировки для подсистем хранения MyISAM, MEMORY, MERGE, но вы можете явно заблокировать таблицу в любой подсистеме хранения командой LOCK TABLES, о которой мы поговорим ниже.

Табличная блокировка аналогична примеру выше. Когда клиент хочет записать что-нибудь в таблицу, он захватывает монолитную блокировку (exclusive lock) ко всей таблице. Остальные клиенты должны дождаться снятия блокировки. Если в текущий момент нет клиентов, которые изменяют данные, то все клиенты, читающие их, захватывают разделяемую блокировку (shared lock), которая не будет конфликтовать с другими блокировками на чтение.

Сервер MySQL содержит две очереди табличных блокировок, называемые read lock queue (очередь блокировок на чтение) и write lock queue (очередь блокировок на запись). Очередь блокировок на запись имеет приоритет перед очередью блокировок на чтение, то есть, если очередь блокировок на запись не пуста, то сервер MySQL будет освобождать её, пока она не опустеет, и только потом приступит к очереди блокировок на чтение. Это поведение можно изменить, запустив сервер MySQL с флагом —low-priority-updates, тогда очередь блокировок на чтение получит более высокий приоритет.