Mysql error 1100

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

LOCK TABLES and UNLOCK TABLES

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 закрывает все таблицы, но не подтверждает транзакцию, потому что никакая таблица не заблокирована

Начало транзакции, например 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, например можно это сделать так…

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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?