Ошибка table was not locked with lock tables - ErrorsMaster.ru - большая энциклопедия ошибок и их решений

I try and load tables via MySQL and get the following error?

MySQL said: Table ‘cms’ was not locked with LOCK TABLES

Why does the table need to be Locked? I haven’t seen this before? is there any way to unlock? do you even want to?

asked Apr 7, 2016 at 5:19

Kieran HeadleyKieran Headley

9131 gold badge12 silver badges21 bronze badges

If in one session, you locked one table but want to select from another table, you must either lock that table too or unlock all tables.

mysql> LOCK TABLES t1 READ;
mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
mysql> SELECT COUNT(*) FROM t2;
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

Brilliand

13.3k6 gold badges46 silver badges58 bronze badges

answered Apr 10, 2018 at 9:47

The solution for me was to unlock the tables. They had been locked by a previous query which failed before reaching the unlock tables statement.

UNLOCK TABLES
SELECT ...

answered Mar 9, 2017 at 10:44

sashoalmsashoalm

74.2k119 gold badges427 silver badges771 bronze badges

http://dev.mysql.com/doc/refman/5.7/en/lock-tables.html

MySQL enables client sessions to acquire table locks explicitly for
the purpose of cooperating with other sessions for access to tables,
or to prevent other sessions from modifying tables during periods when
a session requires exclusive access to them. A session can acquire or
release locks only for itself. One session cannot acquire locks for
another session or release locks held by another session.

Locks may be used to emulate transactions or to get more speed when
updating tables. This is explained in more detail later in this
section.

LOCK TABLES explicitly acquires table locks for the current client
session. Table locks can be acquired for base tables or views. You
must have the LOCK TABLES privilege, and the SELECT privilege for each
object to be locked.

For view locking, LOCK TABLES adds all base tables used in the view to
the set of tables to be locked and locks them automatically. If you
lock a table explicitly with LOCK TABLES, any tables used in triggers
are also locked implicitly, as described in Section 13.3.5.2, “LOCK
TABLES and Triggers”.

UNLOCK TABLES explicitly releases any table locks held by the current
session. LOCK TABLES implicitly releases any table locks held by the
current session before acquiring new locks.

Another use for UNLOCK TABLES is to release the global read lock
acquired with the FLUSH TABLES WITH READ LOCK statement, which enables
you to lock all tables in all databases. See Section 13.7.6.3, “FLUSH
Syntax”. (This is a very convenient way to get backups if you have a
file system such as Veritas that can take snapshots in time.)

Syntax for LOCK and UNLOCK

 LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE

Eg:-

LOCK TABLE t WRITE, t AS t1 READ;

Unlock tables

 UNLOCK TABLES

answered Apr 7, 2016 at 5:34

NarutoNaruto

4,2111 gold badge21 silver badges32 bronze badges

One of the most important lines in the MySQL docs relating to the «Table ‘my_table’ was not locked with LOCK TABLES» message is as follows:

«While the locks thus obtained are held, the session can access only the locked tables»
https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html

This means that if you are trying to access any other table in the database while the LOCK is in place you will get the error message «Table ‘my_table’ was not locked with LOCK TABLES»

The fix is to apply the lock to all of the tables you want to have access to during the lock like this. «LOCK TABLES table_1 WRITE, table_2 WRITE»

Where table_1 is the one you really want to lock but you also want to access table_2 during the same process.

This was confusing because I was locking only table_1 but the error message was telling me Table ‘table_2’ was not locked with LOCK TABLES

Took me a while to figure out why table_2 was even involved. I hope that this helps someone else with the same issue.

answered Aug 24, 2018 at 18:13

Cosworth66Cosworth66

5775 silver badges13 bronze badges

In my case the problem was the aliases.

From the docs:

If your statements refer to a table by means of an alias, you must lock the table using that same alias. It does not work to lock the table without specifying the alias.

Conversely, if you lock a table using an alias, you must refer to it in your statements using that alias.

Wrong:

LOCK TABLE my_table READ;
SELECT * FROM my_table t;
#ERROR 1100: Table 't' was not locked with LOCK TABLES

Correct:

LOCK TABLE my_table t READ;
SELECT * FROM my_table t;

answered Oct 16, 2020 at 5:36

StalinkoStalinko

3,20926 silver badges31 bronze badges

I encountered this problem:

LOCK TABLE <table_a> READ;
LOCK TABLE <table_b> READ;
LOCK TABLE <table_a> WRITE;
LOCK TABLE <table_b> WRITE;

then I read from , this raises Table 'table_a' was not locked with Lock Tables.

After reading documentation, I fix the lock code to :

LOCK TABLE <table_a> WRITE, <table_b> WRITE

This solve the problem for me.

lock type

READ Read lock, no writes allowed

WRITE Exclusive write lock. No other connections can read or write to this table

answered Apr 25, 2017 at 6:49

CloudCloud

2,8292 gold badges19 silver badges22 bronze badges

In my case, this error occurred because I was attempting to import data from Windows to Linux: Windows is case-insensitive and had all lowercase table names, but Linux is case-sensitive and had the same table names, but with uppercase letters. Once I changed the case of the source table names to match the destination table names, this error no longer occurred. The following SO post explains the case sensitivity issue between Windows and Linux in regards to mysql: Are table names in MySQL case sensitive?

answered Aug 17, 2019 at 5:17

AryaArya

5662 gold badges9 silver badges22 bronze badges

I had what appears a forked process in my stored procedure. It was causing the error:

#ERROR 1100: Table 'cached_sales_data' was not locked with LOCK TABLES

on the following SQL Statements:

LOCK TABLES cached_sales_data WRITE, v_sales_data_2 READ;
call refresh_sales_data_now ();
UNLOCK TABLES;

It was in fact being caused by a where clause sub-query in the refresh_sales_data_now() stored procedure.

...
where
  orderdatetime > (select max(orderdatetime)
from
  cached_sales_data)

According to the docs, I would need to create a second READ lock with an alias during the initial lock, then I can write and use the alias for the secondary select.

LOCK TABLES cached_sales_data WRITE, cached_sales_data as csd READ, v_sales_data_2 READ;

...
where
  orderdatetime > (select max(orderdatetime)
from
  csd)

answered Jul 19, 2022 at 14:59

MrYutzMrYutz

3423 silver badges12 bronze badges

Источник

I’m trying to import a mysqldump backup of a drupal database taken from a server running MySQL 5.0 (which will be upgraded to 5.5) onto my local machine running MySQL 5.5.23. The import fails with the below error:

ERROR 1100 (HY000) at line 11084: Table 'search_index' was not locked with LOCK TABLES

When I open up the backup at line 11084 here is what I get:

LOCK TABLES `sessions` WRITE;
/*!40000 ALTER TABLE `sessions` DISABLE KEYS */;
INSERT INTO `sessions` VALUES ...
-- MySQL dump 10.11
--
-- Host: localhost    Database: drupal
-- ------------------------------------------------------
-- Server version       5.0.77-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `search_index`
--

DROP TABLE IF EXISTS `search_index`;        <=== LINE 11084
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `search_index` (
  `word` varchar(50) NOT NULL default '',
  `sid` int(10) unsigned NOT NULL default '0',
  `type` varchar(16) default NULL,
  `score` float default NULL,
  UNIQUE KEY `word_sid_type` (`word`,`sid`,`type`),
  KEY `sid_type` (`sid`,`type`),
  KEY `word` (`word`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

I checked the other statements and normally INSERT statements are followed by UNLOCK TABLES;. Accordingly if I make the below modifications to the mysqldump file the problem is fixed:

INSERT INTO `sessions` VALUES ...
UNLOCK TABLES;                   <=== ADDED LINE
-- MySQL dump 10.11
[...]
DROP TABLE IF EXISTS `search_index`;

I don’t know what’s wrong there but the whole comment block starting with -- MySQL dump 10.11 is the same as the one at the beginning of the mysqldump file, and it looks like mysqldump started a new dump while writing the INSERT INTO sessions statements which were not closed properly with UNLOCK TABLES;.

Does someone have any idea as to what’s going on here?

Источник

Данная статья посвящена блокировке страниц в MySQL. Она основана на официальной документации.

LOCK TABLES and UNLOCK TABLES

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

LOCK TABLES

tbl_name [[AS] alias] lock_type

[, tbl_name [[AS] alias] lock_type] …

lock_type:

READ [LOCAL]

| [LOW_PRIORITY] WRITE

UNLOCK TABLES

MySQL позволяет сессиям клиентов явно блокировать таблицы. Сессия может блокировать таблицы только для себя. Блокировка может быть использована, чтобы эмулировать транзакции, чтобы получить больше скорости при обновлении таблиц. Для блокировки таблиц должны быть соответствующие права (priveleges), а также SELECT privilege для каждого заблокированного объекта.

Типы блокировок

READ [LOCAL] lock: (блокировка чтения)

Сессия, которая блокирует таблицу может только читать её, но не писать в неё.

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

[LOW_PRIORITY] WRITE lock: (блокировка записи)

Сессия, которая блокирует таблицу может читать и писать в таблицу.

Только сессия, которая держит блокировку имеет доступ к таблице до тех пор пока блокировка не будет снята.

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

Сессия, которая требует блокировки должна требовать блокировку всех таблиц, которые ей нужны в одном утверждении LOCK TABLES. При этом сессия может обратиться только к заблокированным таблицам. В примере ниже происходит ошибка, когда сессия пытается получить доступ к таблице t2, потому как она не была заблокирована в инструкции LOCK TABLES.

mysql> LOCK TABLES t1 READ;

mysql> SELECT COUNT(*) FROM t1;

+———-+

| COUNT(*) |

+———-+

| 3 |

+———-+

mysql> SELECT COUNT(*) FROM t2;

ERROR 1100 (HY000): Table ‘t2’ was not locked with LOCK TABLES

К таблицам из INFORMATION_SCHEMA невозможно обратиться, заблокировав их явно. Вы не можете ссылаться на заблокированные таблицы несколько раз в одном запросе, используя одно имя. Вместо этого используйте псевдонимы (aliases), чтобы получить отдельную блокировку для таблицы в каждом псевдониме (alias):

mysql> LOCK TABLE t WRITE, t AS t1 READ;

mysql> INSERT INTO t SELECT * FROM t;

ERROR 1100: Table ‘t’ was not locked with LOCK TABLES

mysql> INSERT INTO t SELECT * FROM t AS t1;

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

mysql> LOCK TABLE t READ;

mysql> SELECT * FROM t AS myalias;

ERROR 1100: Table ‘myalias’ was not locked with LOCK TABLES

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

mysql> LOCK TABLE t AS myalias READ;

mysql> SELECT * FROM t;

ERROR 1100: Table ‘t’ was not locked with LOCK TABLES

mysql> SELECT * FROM t AS myalias;

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

Это поведение можно переписать, используя параметр LOW_PRIORITY WRITE (более подробно в документации).

Правила UNLOCK

Таблицы могут быть разблокированы явно или неявно при выполнении некоторых условий

-Явная разблокировка при использовании UNLOCK TABLES

-Если сессия пытается заблокировать таблицы повторно, то первая блокировка снимается и ставится вторая

-Если сессия начинает транзакцию (START TRANSACTION) на заблокированных таблицах, то блокировка снимается

Взаимодействие Table Locking и Транзакций

LOCK TABLES и UNLOCK TABLES взаимодействуют следующим образом.

LOCK TABLES это не безопасный с точки зрения транзакций способ. Если была какая-то транзакция и вызван метод LOCK TABLES, то эта транзакция коммитится. Таким образом LOCK TABLES неявно подтверждает любые активные действия до применения блокировки таблиц.

UNLOCK TABLES неявно подтверждает все активные транзакции, но только если была использована инструкция LOCK TABLES. В следующем примере UNLOCK TABLES закрывает все таблицы, но не подтверждает транзакцию, потому что никакая таблица не заблокирована

FLUSH TABLES WITH READ LOCK;

START TRANSACTION;

SELECT … ;

UNLOCK TABLES;

Начало транзакции, например START TRANSACTION, неявно записывает все изменения в базу и снимает блокировку таблиц.

Для справки. Инструкция FLUSH TABLES WITH READ LOCK;

Закрываются все открытые таблицы и блокируется доступ для чтения всех таблиц для всех баз данных, пока не будет запущена команда UNLOCK TABLES. Это очень удобный способ создавать резервные копии, если у вас файловая система наподобие Veritas, которая может обеспечить моментальные снимки данных в режиме реального времени.

FLUSH TABLES WITH READ LOCK это не тоже самое, что LOCK TABLES и UNLOCK TABLES, то есть, например START TRANSACTION не отменит global read lock

Корректный путь для использования LOCK TABLES и UNLOCKTABLES с транзакционными таблицами, такими как InnoDB, это начать с инструкции SET autocommit = 0 (а не с инструкции START TRANSACTION), далее LOCK TABLES, далее что-то делаем с заблокированными таблицами, далее COMMIT и только после этого UNLOCK TABLES, например можно это сделать так…

SET autocommit=0;

LOCK TABLES t1 WRITE, t2 READ, ...;

... do something with tables t1 and t2 here ...

COMMIT;

UNLOCK TABLES;

Это корректный код для взаимодействия сервера MySQL и движка InnoDB. По умолчанию для InnoDB стоит autocommit=1;

ROLLBACK не освобождает транзакции.

Блокировка таблиц и триггеры

Если заблокировать явно с использованием LOCK TABLES, то любые таблицы, используемые в триггерах также неявно блокируются (что логично).

Если в триггере таблица используется только для чтения, то она и блокируется только на чтение.

Аналогично, если в триггере таблица используется на запись, то она и блокируется на запись.

Если таблица заблокирована явно на чтение LOCK TABLES

Предположим, мы заблокировали 2 таблицы

LOCK TABLES t1 WRITE, t2 READ;

Предположим таблица t1 имеет триггер

CREATE TRIGGER t1_a_ins AFTER INSERT ON t1 FOR EACH ROW

BEGIN

UPDATE t4 SET count = count+1

WHERE id = NEW.id AND EXISTS (SELECT a FROM t3);

INSERT INTO t2 VALUES(1, 2);

END;

Результат блокировки таблиц (LOCK TABLES) в том, что t1 и t2 блокируются благодаря первому утверждению, а t3 и t4 блокируются, потому что они в триггере.

t1 блокируется для записи

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

t3 блокируется для чтения, потому что она читается в триггере

t4 блокируется для записи, потому что она может быть обновлена в триггере

Ограничения и условия блокировки таблиц

Можно безопасно уничтожать сессию, которая ожидает блокировку таблиц, используя синтаксис KILL.

Не следует блокировать таблицы, которые Вы используете с INSERT DELAYED, так как это приведет к ошибке.

LOCK TABLES и UNLOCK TABLES не могут быть использованы внутри хранимой процедуры.

Таблице в БД performance_schema не могут быть заблокированы с LOCK TABLES, кроме setup_xxx таблиц.

Источник

Рано или поздно любой разработчик или администратор СУБД, имеющий дело с 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, тогда очередь блокировок на чтение получит более высокий приоритет.

Проанализировать конкуренцию за блокировку таблиц можно командой

SHOW STATUS LIKE ‘Table%’.

Variable_name	Value
Table_locks_immediate	12931
Table_locks_waited	1932

Переменная Table_locks_immediate показывает количество раз, когда запрос на табличную блокировку был удовлетворен моментально. А Table_locks_waited показывает, сколько раз требовалось ожидать применения блокировки. Если это значение велико, то у вас проблемы с производительностью.

Преимущества табличных блокировок:

Требуют относительно небольшого объема памяти.
Быстрая работа при блокировке большого количества таблиц.
Быстрая работа при операциях GROUP BY или при полном сканировании таблиц.
Хорошо подходят для приложений, в которых данные изменяются редко.
Отсутствие взаимоблокировок (deadlocks), к которым мы еще вернемся.

Недостатки табличных блокировок:

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

Учитывая достоинства и недостатки табличных блокировок, я рекомендую использовать их только для таблиц, которые часто запрашиваются и редко изменяются (еще лучше, если никогда не изменяются).

Пользовательские блокировки

Пользовательскую блокировку можно получить с помощью функции GET_LOCK(key, timeout), где key — произвольный строковый ключ длиной до 64 символов, а timeout — таймаут в секундах. Отрицательное значение таймаута означает бесконечное время ожидания.

SELECT GET_LOCK(‘key’, 10);

Пользовательская блокировка применяется не к строке или таблице, это монолитная блокировка произвольного строкового значения. По-сути, именованный мьютекс. Вы задаете строку (ключ), которую хотите заблокировать, и указываете таймаут.

Снять пользовательскую блокировку можно явно командой RELEASE_LOCK(key). Второй случай снятия — неявно при завершении сеанса, в том числе аварийном. И третий случай — по истечении таймаута.

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

Преимущества пользовательских блокировок:

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

Недостатки пользовательских блокировок:

Небезопасно использовать с репликацией на основе SQL-выражений (покомандная репликация).
Из-за возможности вызвать сколько угодно команд GET_LOCK в рамках одного сеанса есть опасность применить слишком много блокировок в цикле вашей программы или, например, командой INSERT INTO … SELECT GET_LOCK(t1.col_name) FROM t1.
Такие блокировки не снимаются при фиксации или откате транзакций.
До версии 5.7 команда GET_LOCK имела очень опасное для ваших данных поведение, если вы применяли её к ключу, который уже был заблокирован этой командой.

Глобальные блокировки

Глобальная блокировка закрывает на чтение все открытые таблицы для всех баз данных. Применить её можно только с помощью команды FLUSH TABLES WITH READ LOCK. Это удобно для создания резервной копии. Однако стоит иметь в виду два момента:

Команда конфликтует с другими табличными блокировками.
Команда вызовет снятие всех предыдущих блокировок, вызванных командой LOCK TABLES, как будто вы выполнили команду UNLOCK TABLES.

Снять глобальную блокировку можно командой UNLOCK TABLES.

Преимущества глобальных блокировок:

Очень удобный способ получения резервной копии.
Эту команду используют как трюк для быстрого alter таблиц через подмену .frm-файлов.
Не так затратна, как полная остановка сервера, так как бОльшая часть информации остается кешированной в памяти и сервер «прогрет».
Используется для восстановления на конкретный момент времени.

Недостатки глобальных блокировок:

Требует более глубоких знаний администрирования MySQL. Не используйте эту команду, если не знаете, что делаете. Это может привести к глобальной блокировке вашей базы данных на очень долгое время.
На получение глобальной блокировки может уйти довольно много времени, поскольку команда ждет завершения всех операторов SELECT. В это время все остальные запросы будут вынуждены простаивать, неизвестно как долго.
В случае с таблицами MyISAM при большом объеме избыточного кеша придется ждать еще больше, так как ему необходимо сбросить блоки ключей на диск.

Построчные блокировки

Блокировки на уровне строк доступны в подсистеме хранения Archive, InnoDB и её форке XtraDB от Percona. Построчные блокировки реализуются только подсистемами хранения, а не сервером MySQL.

Идея проста: для улучшения конкурентного доступа мы не будем блокировать таблицу целиком, вместо этого мы заблокируем только ту часть таблицы, которую намерены изменить. А еще лучше, заблокируем только определенные фрагменты данных, чтобы другие запросы могли читать из этой строки или строк то, что мы не изменяем.

Например, если наш запрос изменяет поле balance в таблице Users, то мы можем заблокировать только это поле, чтобы дать возможность другому запросу читать данные first_name, last_name и age. Однако если другая транзакция захочет прочитать поле balance, ей придется подождать снятия блокировки.

Преимущества построчных блокировок:

Обеспечивают лучшее управление конкурентным доступом.
Позволяют одновременно выполнять несколько изменений одного ресурса (если они не конфликтуют друг с другом).
Позволяют заблокировать одну строку на долгое время.
Меньше конфликтов блокировок при доступе к разным строкам.

Недостатки построчных блокировок:

Влекут максимальные издержки.
Приводят к взаимоблокировкам (deadlocks).
Работают медленней табличных блокировок, если используется большая часть таблицы.
Работают намного медленней при полном сканировании таблицы или для группировок GROUP BY.

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

Табличные блокировки и команда LOCK TABLES

Табличные блокировки бывают явные и неявные. Явная захватывается командой LOCK TABLES. Синтаксис следующий:

LOCK TABLES table_name1 {lock_type}, table_name2 {lock_type}...table_name3 {lock_type};

где lock_type — тип блокировки.

lock_type: {
  READ [LOCAL] | WRITE
}

READ означает применение табличной блокировки на чтение, WRITE — на запись.

Особенности блокировки READ [LOCAL]:

Клиент, удерживающий блокировку, может читать из таблиц (но не писать).
Другие клиенты могут читать из таблиц без явной READ блокировки.
Другие клиенты могут применять READ блокировку одновременно с вами.
Модификатор LOCAL для движка MyISAM разрешает другим клиентам выполнять конкурентные INSERT-вставки во время удержания блокировки. Подробнее можно прочитать здесь.
Для InnoDB READ LOCAL то же самое, что и READ.

Особенности блокировки WRITE:

Клиент, удерживающий блокировку, может читать и писать в таблицу.
Другие клиенты не могут читать или изменять что-то в таблицах, поскольку блокировка монолитная.
Запросы на блокирование таблицы другими клиентами будут отклонены, пока удерживается WRITE-блокировка.
У WRITE есть модификатор LOW_PRIORITY, который с версии 5.6 не имеет никакого эффекта.

Правильный способ сделать LOCK/UNLOCK TABLES при использовании транзакционных движков, таких как InnoDB, — это начинать транзакцию с SET autocommit = 0 (а не со START TRANSACTION), затем применить блокировку LOCK TABLES и не выполнять UNLOCK TABLES, пока транзакция не будет зафиксирована явно.

SET autocommit = 0;
LOCK TABLES drivers WRITE, clients READ;
<--делаем что-нибудь-->
COMMIT;
UNLOCK TABLES;

Почему этот способ правильный? Потому, что команда LOCK TABLES неявно фиксирует транзакцию и если переменная autocommit будет в значении 1/ON, то после вызова LOCK TABLES INNODB неявно зафиксирует транзакцию. Такая ситуация может легко привести к взаимоблокировке (deadlock).

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

Таблицы, которые используются в триггерах, также будут заблокированы.
Все таблицы, связанные ограничениями внешних ключей, будут неявно заблокированы на чтение (LOCK TABLES READ). Для каскадных обновлений таблицы будут заблокированы на запись (LOCK TABLES WRITE).

Чем явные блокировки отличаются от неявных? На внутреннем уровне их структура одинакова, и управляются они одним и тем же кодом. На внешнем уровне разница лишь в том, что явные блокировки управляются с помощью команд LOCK TABLES и UNLOCK TABLES.

Однако всё это актуально для движка MyISAM. Табличные блокировки для других подсистем хранения ведут себя магическим образом. Когда сервер MySQL применяет и снимает неявные блокировки, он сообщает об этом подсистеме хранения, а та конвертирует эти блокировки, исходя из своих собственных потребностей.

Добавлю еще несколько моментов, которые стоит держать в голове при работе с командой LOCK TABLES:

Вы не можете работать с таблицами, которые не перечислены в LOCK TABLES.

LOCK TABLES users WRITE;
INSERT INTO users (name) VALUES (‘Вася’); // успех
INSERT INTO drivers (name) VALUES (‘Петя’); // ошибка!
UNLOCK TABLES;

Error: Table 'drivers' was not locked with LOCK TABLES

Исключение — таблица INFORMATION_SCHEMA.

LOCK TABLES неявно снимает все блокировки таблиц, удерживаемых в текущем сеансе.
Если вы используете псевдоним таблицы, то должны явно обращаться к этому псевдониму после LOCK TABLES.

LOCK TABLES drivers as d WRITE;
SELECT * FROM drivers;
UNLOCK TABLES:

Error: Table 'drivers' was not locked with LOCK TABLES

То же самое правило относится и к псевдониму в выражениях после LOCK TABLES.

LOCK TABLES drivers WRITE;
SELECT * FROM drivers as d;
UNLOCK TABLES:

Error: Table 'd' was not locked with LOCK TABLES

Работа с блокировками на уровне строк в InnoDB и транзакции

Множественная блокировка гранулярности

Блокировки на уровне строк тесно связаны с транзакционным механизмом. Как я уже писал выше, блокировки делятся на разделяемые (shared locks) и монолитные (exclusive locks). Назовем их S и X соответственно.

Если транзакция T1 применила разделяемую блокировку к строке r, то запрос от второй транзакции T2 на блокировку этой строки обрабатывается по правилу:

Запрос T2 на S-блокировку может быть удовлетворен. Обе транзакции удерживают блокировку строки r.
Запрос T2 на X-блокировку не может быть удовлетворен. Транзакция T2 ожидает снятия блокировки T1.

Если транзакция T1 захватила монолитную блокировку к строке r, то запрос от второй транзакции T2 на любую (S или X) блокировку этой строки не может быть удовлетворен. Транзакция T2 ожидает снятия блокировки T1.

Но такой механизм не мог бы удовлетворить потребностям систем с большим конкурентным доступом, поскольку в таких системах также присутствуют табличные блокировки, которые будут конфликтовать с блокировками строк, и это может стоить нам потери параллелизма. Для решения этой проблемы в InnoDB была добавлена поддержка множественной блокировки гранулярности (Multiple granularity locking).

Под гранулярностью лучше всего представить некий элемент данных, который можно заблокировать, а множественную гранулярность стоит понимать как иерархическое разбиение базы данных на блоки, которые можно заблокировать. Можно провести аналогию с деревом, у которого самый верхний узел — это база данных, состоящая из файлов, которые, в свою очередь, состоят из записей. Каждый файл состоит из тех записей, которые являются его дочерними узлами, и никакая запись не может присутствовать более чем в одном файле.

Каждый узел в таком дереве может быть заблокирован индивидуально. Когда транзакция блокирует узел дерева в разделяемом или монопольном режиме, все остальные узлы этого дерева будут неявно заблокированы в том же самом режиме. Например, если транзакция блокировала файл F, то она неявно монопольно блокирует все записи, принадлежащие этому файлу. В этом случае нет необходимости явно блокировать каждую отдельную запись файла F.

Теперь, когда стало проще блокировать файлы и записи, как InnoDB определяет, можно ли заблокировать корневой узел? Как вариант — поиск по дереву, но это сводит на нет саму идею блокировки с множественной гранулярностью. Более эффективный способ получить эту информацию — ввести новый режим: блокировку с намерением.

Блокировки с намерением (Intention locks)

Блокировки с намерением, или преднамеренные блокировки — это блокировки на уровне таблицы, которые сообщают подсистеме хранения о том, какой тип блокировки (shared или exclusive) позже потребуется транзакции для строки в таблице. По-сути, это указание движку на тот факт, что где-то есть обычные блокировки.

Так же как обычные блокировки делятся на shared и exclusive, преднамеренные делятся на разделяемые преднамеренные (intention shared locks) и монолитные преднамеренные блокировки (intention exclusive locks). Назовем их IS и IX соответственно.

В документации MySQL приводится следующая таблица совместимости блокировок:

	X	IX	S	IS
X	Конфликт	Конфликт	Конфликт	Конфликт
IX	Конфликт	Совместимы	Конфликт	Совместимы
S	Конфликт	Конфликт	Совместимы	Совместимы
IS	Конфликт	Совместимы	Совместимы	Совместимы

Общие правило преднамеренных блокировок звучит так:

Прежде чем получить S-блокировку для строки r в таблице t, транзакция сначала должна получить IS или более сильную блокировку для таблицы t.

Прежде чем получить X-блокировку для строки r в таблице t, транзакция сначала должна получить IX-блокировку для таблицы t.

Если какой-то поток уже удерживает монопольную блокировку таблицы, то достаточно будет проверить только совместимость между монолитной и преднамеренной, а не проверять совместимость блокировки строки и таблицы. Это ускоряет проверку конфликтов.

Блокировки намерений не конфликтуют друг с другом, но блокировки таблиц конфликтуют как с IS, так и с IX, и наоборот. Не существует простого способа обнаружить конфликты блокировок на уровне строк и таблиц, но легко обнаружить конфликт между блокировками таблиц и преднамеренными блокировками.

Синтаксис блокировок с намерением чрезвычайно простой:

SELECT … FOR SHARE [NOWAIT | SKIP LOCKED] — намерение установить IS-блокировку.

SELECT … FOR UPDATE [NOWAIT | SKIP LOCKED] — намерение установить IX-блокировку.

Пара примеров, демонстрирующих совместимость на практике:

Пример 1:

# Транзакция 1 (IS)
SELECT id, name FROM drivers FOR SHARE;

# Транзакция 2 (S)
LOCK TABLES drivers READ;
SELECT * FROM drivers;
UNLOCK TABLES;

В этом случае транзакция 2 ничего не ждет, поскольку IS- и S-блокировки совместимы.

Пример 2:

# Транзакция 1 (IX)
SELECT id, name FROM drivers FOR UPDATE;

# Транзакция 2 (S)
LOCK TABLES drivers READ;
SELECT * FROM drivers;
UNLOCK TABLES;

Здесь транзакция 2 будет дожидаться окончания работы транзакции 1, поскольку IX- и S-блокировки конфликтуют между собой.

Оба типа преднамеренных блокировок имеют необязательные флаги NOWAIT и SKIP LOCKED. Обычно, если ваша транзакция хочет заблокировать строку, то она дожидается окончания текущих блокировок. Но если в запросе имеются один из этих флагов, то транзакция не будет дожидаться снятия блокировки и сразу же попробует захватить её сама. С флагом NOWAIT вы получите ошибку, если строка уже будет заблокирована. А в случае SKIP LOCKED заблокированная строка будет удалена из выборки.

Работа флагов на примере:

Пример с NOWAIT.

# Транзакция 1
START TRANSACTION;  
SELECT id, name FROM drivers WHERE id = 1 FOR UPDATE;
# Транзакция 2
START TRANSACTION;
SELECT id, name FROM drivers WHERE id = 1 FOR UPDATE NOWAIT;

Error: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

Пример со SKIP LOCKED.

# Транзакция 1
START TRANSACTION;  
SELECT id, name FROM drivers WHERE id = 1 FOR UPDATE;

# Транзакция 2
START TRANSACTION;
SELECT * FROM drivers FOR UPDATE SKIP LOCKED;

Примечания по работе с блокировками намерений:

При блокировке строки (строк) SELECT … FOR SHARE, если эта строка (строки) были изменены транзакцией, которая еще не зафиксирована, ваш запрос будет ожидать фиксации транзакции, чтобы получить актуальные данные.
До MySQL 8.0.22 SELECT … FOR SHARE требует SELECT-привилегий и, по крайней мере, одну привилегию из DELETE, LOCK TABLES, UPDATE. Начиная с 8.0.22 достаточно только привилегии SELECT.
Во время блокировки SELECT … FOR UPDATE для записей индекса, обнаруженных при поиске, блокируются строки и все связанные с индексом записи, как если бы вы выполняли UPDATE этих строк. В этот момент все транзакции, которые пытаются изменить эти строки или выполнить SELECT … FOR SHARE этих записей, будут ждать снятия блокировки. Также эти записи будут заблокированы для SELECT уровне изоляции SERIALIZABLE, поскольку на этом уровне все выражения SELECT неявно приводятся к SELECT … FOR SHARE.

Пример 1:

# Транзакция 1
START TRANSACTION;  
SELECT id, name FROM drivers WHERE id > 5 FOR UPDATE;
COMMIT;

# Транзакция 2
START TRANSACTION;
UPDATE drivers SET balance = balance + 50 WHERE id > 5;
COMMIT;

Пример 2:

# Транзакция 1
START TRANSACTION;  
SELECT id, name FROM drivers WHERE id > 5 FOR UPDATE;
COMMIT;

# Транзакция 2
START TRANSACTION;
SELECT id, name FROM drivers WHERE id > 5 FOR SHARE;
COMMIT;

Пример 3:

# Транзакция 1
START TRANSACTION;  
SELECT id, name FROM drivers WHERE id > 5 FOR UPDATE;
COMMIT;
# Транзакция 2
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
START TRANSACTION; 
SELECT id, name FROM drivers WHERE id > 5;
COMMIT;

Во всех примерах транзакция 2 ожидает снятия блокировки всех записей индекса, которые попадают под условие id > 5.

SELECT … FOR UPDATE требует SELECT-привилегий и, по крайней мере, одну привилегию из DELETE, LOCK TABLES, UPDATE.
Фиксация или откат транзакции снимает все блокировки SELECT … FOR SHARE и SELECT … FOR UPDATE.
SELECT … FOR SHARE является заменой устаревшему выражению LOCK IN SHARE MODE (хотя последнее еще присутствует в MySQL для обратной совместимости).
SELECT … FOR SHARE и SELECT … FOR UPDATE не блокируют таблицы в подзапросах.

# В этом примере строки из таблицы blocked_drivers не блокируются

SELECT id, name 
FROM drivers 
WHERE id = (SELECT driver_id FROM blocked_drivers) 
FOR UPDATE;

# А в этом блокируются
SELECT id, name 
FROM drivers 
WHERE id = (SELECT driver_id FROM blocked_drivers FOR UPDATE) 
FOR UPDATE;

Хочу еще раз обратить ваше внимание, что преднамеренные блокировки НЕ являются реальными блокировками строк, а являются лишь блокировками таблиц с УКАЗАНИЕМ того, какой тип блокировки позже потребуется для строки. Конкретные же стратегии блокировок строк мы рассмотрим прямо сейчас.

Блокировки записей (Record Locks)

Record lock есть ни что иное, как блокировка записи индекса. Тема индексов выходит за рамки этой статьи, напомню только, что в InnoDB существуют кластерные индексы. Обычно это Primary Key-таблицы, но, если он не определен, то используется unique index. Если нет и его, то InnoDB сам создаст скрытый кластерный индекс. Также в InnoDB есть вторичные (secondary) индексы, которые хранят, помимо всего прочего, значение кластерного индекса. Во время блокировки записи блокируется запись первичного и вторичного индекса.

Добавим индекс по колонке car_id в нашей таблице Drivers, и теперь запрос

SELECT id, name 
FROM drivers 
WHERE car_id = 5
FOR UPDATE;

заблокирует индекс и не позволит другим транзакциям, пытающимся вставить/изменить/удалить записи в индексе, подходящим под условие car_id = 5.

Команда SHOW ENGINE INNODB STATUS, о которой мы поговорим позже, покажет нам две блокировки:

RECORD LOCKS index drivers_car_id_index of table drivers trx id 2653 lock_mode X

RECORD LOCKS index PRIMARY of table locks.drivers trx id 2653 lock_mode X locks rec but not gap

Первая — блокировка записей во вторичном индексе, вторая — в кластерном.

Блокировки промежутка (Gap Locks)

Этот вид строковой блокировки блокирует записи:

A. Между записями индекса:

SELECT id, name, car_id 
FROM drivers 
WHERE car_id BETWEEN 3 AND 7
FOR UPDATE;

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

SELECT id, name, car_id 
FROM drivers 
WHERE car_id = 4
FOR UPDATE;

SHOW ENGINE INNODB STATUS;

TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 2 page no 5 n bits 80 index drivers_car_id_index of table drivers trx id 3151 lock_mode X waiting

B. До записи индекса:

SELECT id, name, car_id 
FROM drivers 
WHERE car_id < 5
FOR UPDATE;

Промежуток индексов, подходящих под условие car_id < 5, будет также заблокирован.

C. После записи индекса:

SELECT id, name, car_id 
FROM drivers 
WHERE car_id > 5
FOR UPDATE;

Пример аналогичен предыдущему, только условие меняется на car_id > 5.

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

Блокировки промежутков не конфликтуют между собой, то есть один и тот же промежуток может быть заблокирован двумя разными транзакциями, причем они могут применять к одному промежутку одновременно S- и X-блокировку. Для этого вида блокировки нет разницы между разделяемыми и монолитными, поскольку они решают одну и ту же задачу: предотвращают потенциальные нарушения целостности из-за одновременных вставок.

Если вы понизите уровень изоляции транзакции до READ COMMITED, то блокировки промежутков больше не будут блокировать индексные записи, потому что на таком уровне изоляции допускается существование фантомов. В этом случае блокировки промежутков будут использованы только для проверки ограничений внешнего ключа и проверки дубликатов ключа.

Блокировки следующего ключа (Next-Key Locks)

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

Предположим, у нас есть три записи в индексе (10, 20, 30), тогда мы имеем четыре интервала возможных значений (-inf…10], (10…20], (20…30], (30…+inf), где круглая скобка означает исключение точки из интервала, а квадратная — включение. Для последнего интервала блокируется промежуток над наибольшим значением в индексе до записи supremum, который является псевдо-записью, имеющим значение больше любого другого в индексе. Например, блокируя ключ 30, мы блокируем и диапазон (20…30], чем добиваемся консистентности.

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

Блокировки намерений вставок* (Insert intention locks)

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

Этот тип блокировки является разновидностью блокировки промежутка, но для операций вставки. Промежуток будет заблокирован до вставки новой записи в индекс, это сигнализирует о намерении вставить запись таким образом, что другие операции вставки в этот промежуток не конфликтуют друг с другом (если, конечно, вы не вставляете запись в одну и ту же позицию).

Для примера давайте добавим пару новых записей в нашу таблицу:

INSERT INTO drivers (name, car_id, balance) VALUES (‘new1’, 14, 100);
INSERT INTO drivers (name, car_id, balance) VALUES (‘new2’, 17, 100);

Теперь в первой транзакции сделаем запрос:

START TRANSACTION;
SELECT * FROM drivers WHERE car_id > 13 FOR UPDATE;

Вторая же транзакция пытается вставить новую запись в наш промежуток car_id > 13:

INSERT INTO drivers (name, car_id, balance) VALUES (‘new2’, 15, 100);

Команда SHOW ENGINE INNODB STATUS покажет нам следующую информацию:

RECORD LOCKS space id 2 page no 5 n bits 80 index drivers_car_id_index of table drivers trx id 3165 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 80000011; asc ;;

1: len 4; hex 8000000b; asc ;;

Однако вторая транзакция не будет блокировать другие намерения вставок в этот промежуток и они могут сосуществовать бесконфликтно.

Блокировки AUTO-INC (AUTO-INC Locks)

AUTO-INC является блокировкой на уровне таблицы для инкрементирования первичного ключа во время вставок новых записей в таблицу. Есть три алгоритма увеличения первичного ключа, которые контролируются переменной innodb_autoinc_lock_mode. Значения могут быть 0, 1 или 2 (по-умолчанию в MySQL 8). Подробнее об алгоритмах можно почитать здесь.

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

Проблемы блокировок и способы решения

Существует три вида проблем с которыми вы можете столкнуться: конфликт за блокировку, долгосрочные блокировки и взаимоблокировки (deadlocks). Я постараюсь привести хоть и тривиальные, но понятные примеры.

Конфликт за блокировку

Конфликт возникает тогда, когда множеству сеансов постоянно требуется доступ к одной и той же блокировке. Такую ситуацию еще называют горячей блокировкой (Hot Locks). Каждый сеанс удерживает блокировку небольшое количество времени и снимает её. Это создает ситуацию наподобие выезда с парковки, когда множеству водителей для выезда нужно отсканировать чек об оплате, чтобы шлагбаум поднялся. Причём шлагбаум только один. Проблема незаметна при низком трафике (или при низком параллелизме), но по мере увеличения трафика возникает узкое место.

Конфликты за блокировку ограничивают масштабируемость. По мере увеличения параллелизма пропускная способность системы не увеличивается, а может даже и снижаться. Также такая ситуация приводит к увеличению нагрузки на ЦП.

Пример 1.

На сайте требуется записывать все хиты для каждой страницы (даже от одного и того же пользователя), и вы решаете хранить это значение в таблице базы данных. Каждый раз, когда делается запрос к странице, это значение увеличивается на единицу. Пока что ваш сайт не особо популярен и вы не чувствуете проблем, но когда им стали пользоваться миллионы людей, страницы стали загружаться медленно, а то и вообще падать с таймаутом.

Пример 2.

Для бухгалтерской отчетности нужно вычислять идентификатор следующего потенциального заказа. Вы храните это значение в отдельной таблице и после каждого заказа вычисляете его с помощью формулы last_order_id + 1. По мере роста нагрузки вам приходится слишком часто блокировать как таблицу заказов, так и таблицу, хранящую следующий идентификатор, пока он вычисляется.

Способы решения.

Как бы банально это ни звучало, но для решения проблемы горячих блокировок стоит просто не допускать горячих блокировок. Старайтесь не использовать критически важную функциональность совместно с механизмом постоянного блокирования строки/таблицы.

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

Используйте более быстрое хранилище, например, Redis.
Для различных счетчиков, где допустима потеря части данных, может хорошо помочь использование систем очередей, чтобы вести подсчет асинхронно.
Подумайте над возможностью собирать по несколько инструкций INSERT/UPDATE в одном операторе. Например, для частых обновлений можно добавить промежуточную таблицу, в которую вставляются новые записи с помощью оператора INSERT, а фоновая задача время от времени агрегирует эти данные и делает нечастый UPDATE основной таблицы.
Не держите другие данные в таблице, которую вы используете для частых блокировок. В идеале, у вас должна быть одна строка в одной таблице.
Уменьшайте время удержания такой блокировки с помощью оптимизации алгоритма программного кода или обнаружения активности, которая без надобности защищена блокировкой.
Вместо табличной блокировки используйте InnoDB и детализированную блокировку на уровне строк.

В целом, проблемы с конфликтом блокировок имеют относительно небольшое влияние. С ростом мощности аппаратного обеспечения они могут оставаться незаметными очень долго и проявлять себя, когда ситуация стала совсем плачевной.

Долгосрочные блокировки

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

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

Пример.

Рассмотрим тот же пример с заказами, описанный выше. Добавим к нему условие, что мы имеем запускаемую по расписанию задачу, которая берет следующий ID заказа и составляет большой и сложный отчет в течение 15 минут. Никакие другие заказы не могут быть обработаны, пока отчет не будет составлен. Ситуацию усугубляет тот факт, что пользователи не могут оформить заказ и думают, что система вышла из строя. Они пытаются создать новый заказ, отправляя новые запросы в базу данных. В результате пользователи исчерпывают все потоки соединения с базой данных, из-за чего система не может отвечать на запросы, даже не связанные с заказами.

Способы решения.

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

Исключите взаимодействие с пользователем во время блокировки.
Понизьте уровень изоляции транзакции.
Проверьте, что у вас нет «спящих» потоков: это подключения к базе, которые ничего не делают. Например, PHP-скрипт, подключился, начал выполнять транзакцию, а затем продолжил работу, и в этот момент ваш поток MySQL находится в состоянии SLEEP. Еще хуже, если ваш код инициализирует постоянное соединение с базой, которое будет активно даже после завершения работы скрипта.
Проверьте, что запросы, удерживающие блокировку, используют оптимальные индексы. Для этого подойдет команда EXPLAIN, о которой я рассказывал в другой своей статье.
Попробуйте использовать несколько коротких транзакций вместо одной долгой.
Убедитесь, что вы закрываете все соединения, а также что все транзакции, начатые со START TRANSACTION, завершаются COMMIT/ROLLBACK.

Долгосрочные блокировки, как правило, представляют более значительную угрозу, чем конфликт блокировок. Они могут остановить работу большой части системы или даже всю её целиком. Блокировки, задействованные в этом сценарии, могут быть недостаточно «горячими», но если они становятся еще и «горячими», катастрофы не избежать.

Взаимоблокировки (Deadlocks)

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

Пример 1.

Самый простой случай взаимоблокировки:

Время	Сессия 1	Сессия 2
1	Начало транзакции.
2		Начало транзакции.
3	Обновляет строку с id =10 в таблице А.
4		Обновляет строку с id = 15 в таблице B.
5	Попытка обновить строку с id = 15 в таблице B.
	Сессия 1 заблокирована сессией 2.
6		Попытка обновить строку с id = 10 в таблице A.
	Сессия 1 заблокирована сессией 2.	Сессия 2 заблокирована сессией 1.
	Deadlock.	Deadlock.

Пример 2.

Взаимоблокировка из трех транзакций:

Время	Сессия 1	Сессия 2	Сессия 3
1	Начало транзакции.
2		Начало транзакции.
3			Начало транзакции.
4	Обновляет строку с id =10 в таблице А.
5		Обновляет строку с id = 15 в таблице B.
6			Обновляет строку с id = 20 в таблице C.
7	Попытка обновить строку с id = 20 в таблице C.
	Сессия 1 заблокирована сессией 3.
8		Попытка обновить строку с id = 10 в таблице A.
		Сессия 2 заблокирована сессией 1.
9			Попытка обновить строку с id = 15 в таблице B.
			Сессия 3 заблокирована сессией 2.
	Сессия 1 заблокирована сессией 3.	Сессия 2 заблокирована сессией 1.	Сессия 3 заблокирована сессией 2.
	Deadlock.	Deadlock.	Deadlock.

Способы решения.

Хорошей новостью является то, что InnoDB умеет самостоятельно обнаруживать взаимоблокировки и выбирает транзакцию-«жертву», которую откатит. Учитывайте этот момент в своих приложениях и будьте готовы выполнить транзакцию еще раз, если она откатывается из-за взаимоблокировки. Но если это стало частой проблемой, то вот список того, что стоит предпринять:

Определите причину последней взаимоблокировки с помощью SHOW ENGINE INNODB STATUS.
Активируйте флаг отладки взаимоблокировок innodb_print_all_deadlocks. Когда он включен, MySQL будет записывать информацию обо всех взаимоблокировках в журнал ошибок. Не забудьте отключить флаг после отладки, чтобы не создавать лишних накладных расходов.
Не забывайте повторно выполнять транзакцию, которая была выбрана «жертвой».
Минимизируйте свои транзакции, сделайте их максимально короткими. Избегайте взаимодействия с пользователями и обращений к внешним сервисам во время транзакции.
Для транзакций SELECT … FOR UPDATE/SHARED попробуйте понизить уровень изоляции.
Всегда выполняйте одни и те же наборы операций в одном и том же порядке.
Подберите оптимальные индексы для своих таблиц, чтобы запросы сканировали как можно меньше индексных записей.
Убедитесь, нужны ли вам вообще блокировки в запросах, которые блокируют строки. Часто бывает так, что для обычных операций SELECT достаточно выбрать данные из старого снимка состояния без использования FOR UPDATE/SHARE.
Старайтесь избегать блокировок таблиц с помощью LOCK TABLES, но если совсем ничего не помогает, то LOCK TABLES становится одним из вариантов сериализации транзакций. Только не забудьте установить флаг autocommit в значение 0 по причине, о которой я писал выше. Цена, которую вам придется заплатить в данном случае, — это уменьшение скорости отклика базы данных.
Еще одним способом сериализации транзакций является создание искусственного семафора в виде таблицы с одной единственной строкой. Каждая транзакция должна будет получить блокировку этой строки перед выполнением своих основных функций.

В целом, взаимоблокировки не представляют большую опасность, и это вполне нормальная ситуация для MySQL, если только они не настолько частые, что транзакции не могут выполняться вообще.

Как видите, множество советов по решению всех видов проблем, связанных с блокировками, повторяются. Всё дело в том, что блокировки являются проблемами не базы данных, а организации вашего приложения.

Стоит добавить, что если ваши транзакции начали периодически отваливаться с ошибкой “Lock wait timeout exceeded”, значит ваше приложение столкнулось с проблемой конфликта за блокировку и/или долгосрочными блокировками. В этом случае может помочь увеличение времени ожидания снятия блокировки транзакцией с помощью изменения переменной innodb_lock_wait_timeout (по умолчанию 50 секунд).

SET innodb_lock_wait_timeout = {количество_секунд}

Но это попытка устранить следствие, а не причину. Поиск же конкретных проблемных, блокирующих запросов рассмотрим ниже.

Отладка блокировок

Для определения, какие запросы сейчас заблокированы и почему, можно использовать следующий запрос:

SELECT 
	r.trx_id waiting_trx_id,
	r.trx_mysql_thread_id waiting_thread,
	r.trx_query waiting_query,
	b.trx_id blocking_trx_id,
	b.trx_mysql_thread_id blocking_thread,
	b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b 
ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
	ON r.trx_id = w.requesting_engine_transaction_id;

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

# блокирующий запрос
SELECT id, SLEEP(30) FROM drivers FOR UPDATE;

# эти два запроса ждут снятия блокировки 
SELECT name FROM drivers FOR UPDATE;

SELECT car_id FROM drivers FOR UPDATE;

Результат отладочного запроса будет следующий

waiting_trx_id	waiting_thread	waiting_query	blocking_trx_id	blocking_thread	blocking_query
3199	49	select car_id from drivers for update	3197	47	select id, SLEEP(30) FROM drivers FOR UPDATE;
3198	48	select name from drivers for update	3197	47	select id, SLEEP(30) FROM drivers FOR UPDATE;

где:

waiting_trx_id — ID ожидающей транзакции;

waiting_thread — ID ожидающего потока MySQL;

waiting_query — запрос, ожидающий снятия блокировки;

blocking_trx_id — ID блокирующей транзакции;

blocking_thread — ID блокирующего потока MySQL;

blocking_query — блокирующий запрос.

Если всё плохо и вам нужно срочно прибить блокирующий запрос, то можно воспользоваться командой KILL {blocking_thread}. Для данного примера это будет выглядеть так:

KILL 47;

Чуть более подробный, но примерно такой же результат можно увидеть просто выполнив команду:

SELECT * FROM sys.innodb_lock_waits;

Например:

# блокирующий запрос
START TRANSACTION;
SELECT * FROM drivers FOR UPDATE;

# этот запрос ждет снятия блокировки 
SELECT name FROM drivers FOR UPDATE;

# отладочный запрос
SELECT * FROM sys.innodb_lock_waits;

****** 1.row ******

— wait_started: 2021-12-03 09:50:18

— wait_age: 00:00:34

— wait_age_secs: 34

— locked_table: ‘locks’.drivers

— locked_table_schema: locks

— locked_table_name: drivers

— locked_table_partition: NULL

— locked_table_subpartition: NULL

— locked_index: PRIMARY

— locked_type: RECORD

— waiting_trx_id: 3253

— waiting_trx_started: 2021-12-03 09:50:18

— waiting_trx_age: 00:00:34

— waiting_trx_rows_locked: 1

— waiting_trx_rows_modified: 0

— waiting_pid: 53

— waiting_query: SELECT name FROM drivers FOR UPDATE;

— waiting_lock_id: 140500149771672:2:4:25:140500057267936

— waiting_lock_mode: X

— blocking_trx_id: 3252

— blocking_pid: 52

— blocking_query: NULL

— blocking_lock_id: 140500149772528:2:4:25:140500057274160

— blocking_lock_mode: X

— blocking_trx_started: 2021-12-03 09:50:16

— blocking_trx_age: 00:00:36

— blocking_trx_rows_locked: 11

— blocking_trx_rows_modified: 0

— sql_kill_blocking_query: KILL QUERY 52

— sql_kill_blocking_connection: KILL 52

Однако в ряде случаев в колонке blocking_query будет значение NULL. Такое происходит, когда сеанс переходит в неактивное состояние, то есть не делает ничего. Для примера я начну транзакцию и не буду выполнять COMMIT:

# блокирующий запрос
START TRANSACTION;
SELECT * FROM drivers FOR UPDATE;

# эти два запроса ждут снятия блокировки 
SELECT name FROM drivers FOR UPDATE;

SELECT car_id FROM drivers FOR UPDATE;

Результат отладочного запроса

waiting_trx_id	waiting_thread	waiting_query	blocking_trx_id	blocking_thread	blocking_query
3232	49	SELECT car_id FROM drivers FOR UPDATE	3230	50	null
3232	49	SELECT car_id FROM drivers FOR UPDATE	3231	48	SELECT name FROM drivers FOR UPDATE
3231	48	SELECT name FROM drivers FOR UPDATE	3230	50	null

Запрос SELECT car_id FROM drivers FOR UPDATE; блокирует сразу два запроса. Один из них мы видим, но второй в значении null. То же самое мы видим в колонке blocking_query и для запроса SELECT name FROM drivers FOR UPDATE;. Это происходит потому, что блокирующий запрос после начала транзакции и выборки SELECT не делает ничего. Чтобы узнать, что это за блокирующий запрос, стоит проделать следующее:

С помощью нужного blocking_thread получить идентификатор процесса блокирующей транзакции:

SELECT blocking_pid 
FROM sys.innodb_lock_waits 
WHERE blocking_trx_id = {blocking_thread}

С помощью полученного blocking_pid получить идентификатор потока:

SELECT THREAD_ID 
FROM performance_schema.threads 
WHERE PROCESSLIST_ID = {blocking_pid}

С помощью полученного THREAD_ID получить текст запроса:

SELECT THREAD_ID, SQL_TEXT 
FROM performance_schema.events_statements_current 
WHERE THREAD_ID = {THREAD_ID}

Если запрос не предоставил достаточную информацию и в SQL_TEXT вы получили что-то типа SELECT @@session.transaction_isolation, то можно запросить историю, чтобы посмотреть последние 10 команд:

SELECT THREAD_ID, SQL_TEXT 
FROM performance_schema.events_statements_history 
WHERE THREAD_ID = {THREAD_ID} 
ORDER BY EVENT_ID;

Один запрос для всего вышеперечисленного:

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history
WHERE THREAD_ID = (
   SELECT THREAD_ID
   FROM performance_schema.threads
   WHERE PROCESSLIST_ID = (
       SELECT blocking_pid
       FROM sys.innodb_lock_waits
       WHERE blocking_trx_id = {blocking_thread} LIMIT 1
   )
)
ORDER BY EVENT_ID;

THREAD_ID	SQL_TEXT
89	SELECT @@session.transaction_read_only
89	SHOW WARNINGS
89	select database()
89	SHOW WARNINGS
89	SHOW WARNINGS
89	SET net_write_timeout=600
89	SET SQL_SELECT_LIMIT=501
89	*SELECT FROM drivers FOR UPDATE**
89	SHOW WARNINGS
89	SELECT @@session.transaction_isolation

Еще один способ узнать о блокировках в системе — это воспользоваться командой SHOW ENGINE INNODB STATUS;. Но предварительно нужно установить переменную innodb_status_output_locks в значение ON.

SET GLOBAL innodb_status_output_locks = ON;
SHOW ENGINE INNODB STATUS;

Не забудьте отключить эту переменную после отладки, так как InnoDB создает еще один поток для записи это информации.

После выполнения команды можно прочитать информацию о блокировках в разделе TRANSACTIONS. Стандартный вывод включает список активной и ожидающих транзакций и информацию о заблокированных таблицах и строках, а также о режиме блокировки.

Заключение

Блокировки являются обычным явлением при работе с базами данных. Не стоит воспринимать их как что-то плохое. Напротив, блокировки — это необходимый компромисс для обеспечения целостности ваших данных в системах с конкурентным доступом.

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

Литература и источники

High Performance MySQL (by Baron Schwartz, Peter Zaitsev, Vadim Tkachenko)
https://dev.mysql.com/
https://stackoverflow.com/
https://www.percona.com/
https://www.methodsandtools.com/archive/archive.php?id=83

Источник

Official website address:https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html

A session that requires locks must acquire all the locks that it needs in a single LOCK TABLES statement. While the locks thus obtained are held, the session can access only the locked tables. For example, in the following sequence of statements, an error occurs for the attempt to access t2 because it was not locked in the LOCK TABLES statement:

mysql> LOCK TABLES t1 READ;
mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
mysql> SELECT COUNT(*) FROM t2;
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

Tables in the INFORMATION_SCHEMA database are an exception. They can be accessed without being locked explicitly even while a session holds table locks obtained with LOCK TABLES.

You cannot refer to a locked table multiple times in a single query using the same name. Use aliases instead, and obtain a separate lock for the table and each alias:

mysql> LOCK TABLE t WRITE, t AS t1 READ;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;

The error occurs for the first INSERT because there are two references to the same name for a locked table. The second INSERT succeeds because the references to the table use different names.

If your statements refer to a table by means of an alias, you must lock the table using that same alias. It does not work to lock the table without specifying the alias:

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

Conversely, if you lock a table using an alias, you must refer to it in your statements using that alias:

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;

Problem analysis: If you lock the table in a session, you can only access the table you locked; if you access other tables, you will report this exception. For example, if you lock table ‘t’ read, and then you select t2, t2 will report this exception.

Solution:

Get the table lock you need to access at one time: A session that requires locks must acquire all the locks that it needs in a singleLOCK TABLES statement.
Unlock tables release the table locks already held by the session, and then access other tables.

Источник