Блокировка таблиц в MySQL

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

Клиентский сеанс может устанавливать или снимать блокировки таблиц только для себя. И клиентский сеанс не может устанавливать или снимать блокировки таблиц для других клиентских сеансов.

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

CREATE TABLE messages ( 
    id INT NOT NULL AUTO_INCREMENT, 
    message VARCHAR(100) NOT NULL, 
    PRIMARY KEY (id) 
);

Инструкция MySQL LOCK TABLES

Следующий LOCK TABLES оператор явно получает блокировку таблицы:

LOCK TABLES table_name [READ | WRITE]

В этом синтаксисе после ключевых слов вы указываете имя таблицы, которую хотите заблокировать LOCK TABLES. Кроме того, вы указываете тип блокировки:  READ или WRITE.

MySQL позволяет вам блокировать несколько таблиц, указав список имен таблиц, разделенных запятыми, с типами блокировки, которые вы хотите заблокировать после ключевых LOCK TABLES слов:

LOCK TABLES table_name1 [READ | WRITE], 
            table_name2 [READ | WRITE],
             ... ;

Инструкция MySQL UNLOCK TABLES

Чтобы снять блокировку таблицы, используйте следующий UNLOCK TABLES оператор:

UNLOCK TABLES;

READ Locks

Lock READ имеет следующие особенности:

• Блокировка READ таблицы может быть получена несколькими сеансами одновременно. Кроме того, другие сеансы могут читать данные из таблицы без установки блокировки.
• Сеанс, который удерживает блокировку, READ может только читать данные из таблицы, но не может записывать. А другие сессии не могут записывать данные в таблицу, пока READ не будет снята блокировка. Операции записи из другого сеанса будут переведены в состояние ожидания до тех пор, пока READблокировка не будет снята.
• Если сеанс завершается нормально или ненормально, MySQL неявно снимет все блокировки. Эта особенность актуальна и для WRITE lock.

Давайте посмотрим, как READ работает блокировка в следующем сценарии.

Сначала подключитесь к базе данных в первом сеансе и используйте функцию CONNECTION_ID() для получения текущего идентификатора соединения следующим образом:

SELECT CONNECTION_ID();

Затем вставьте новую строку в messages таблицу.

INSERT INTO messages(message) 
VALUES('Hello');

Далее запросите данные из messages таблицы.

SELECT * FROM messages;

После этого получите блокировку, используя LOCK TABLE оператор.

LOCK TABLE messages READ;

Наконец, попробуйте вставить в messages таблицу новую строку:

INSERT INTO messages(message) 
VALUES('Hi');

MySQL выдал следующую ошибку:

Error Code: 1099. Table 'messages' was locked with a READ lock and can't be updated.

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

Давайте проверим READ блокировку из другой сессии.

Сначала подключитесь к базе данных и проверьте идентификатор соединения:

SELECT CONNECTION_ID();

Далее запросим данные из messages таблицы:

SELECT * FROM messages;

Затем вставьте новую строку в messages таблицу:

INSERT INTO messages(message) 
VALUES('Bye');

Вот результат:

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

Начиная с первого сеанса, используйте SHOW PROCESSLIST оператор для отображения подробной информации:

SHOW PROCESSLIST;

После этого вернитесь к первому сеансу и снимите блокировку с помощью оператора UNLOCK TABLES. После снятия READ блокировки первого сеанса INSERT операция выполняется во втором сеансе.

Наконец, проверьте данные таблицы messages, чтобы убедиться, что INSERT операция из второго сеанса действительно выполнена.

SELECT * FROM messages;

Запись блокировок

Замок WRITE имеет следующие особенности:

• Единственный сеанс, который удерживает блокировку таблицы, может читать и записывать данные из таблицы.
• Другие сеансы не могут читать данные и записывать данные в таблицу, пока блокировка WRITE не будет снята.

Давайте углубимся в то, как WRITE работает lock.

Сначала получите WRITE блокировку из первого сеанса.

LOCK TABLE messages WRITE;

Затем вставьте новую строку в messages таблицу.

INSERT INTO messages(message) 
VALUES('Good Morning');

Это сработало.

Далее запросите данные из messages таблицы.

SELECT * FROM messages;

Это также работает.

После этого со второго сеанса попытайтесь записать и прочитать данные:

INSERT INTO messages(message) 
VALUES('Bye Bye');

SELECT * FROM messages;

MySQL переводит эти операции в состояние ожидания. Проверить это можно с помощью SHOW PROCESSLIST оператора:

SHOW PROCESSLIST;

Наконец, снимите блокировку первого сеанса.

UNLOCK TABLES;

Вы увидите все выполненные ожидающие операции второго сеанса, а результат иллюстрируется следующим рисунком:

Блокировки чтения и записи

• Блокировки чтения — это «общие» блокировки, которые предотвращают получение блокировки записи, но не других блокировок чтения.
• Блокировки записи — это «эксклюзивные» блокировки, которые предотвращают любые другие блокировки.

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