Блокировка следующего ключа: устранение проблемы с фантомом

 

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

Блокировка, которая в InnoDB устанавливается на индексные записи, влияет также на интервал перед этой индексной записью. Если у пользователя имеется совместная или эксклюзивная блокировка записи R в индексе, то другой пользователь не может вставить новую индексную запись перед R в порядке следования индексов. Такая блокировка интервалов производится для предотвращения так называемой проблемы с фантомом. Предположим, что необходимо прочитать и заблокировать все дочерние записи с идентификатором, превышающим 100, из таблицы CHILD, и обновить некоторые поля в выбранных строках.

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

Допустим, что создан индекс таблицы CHILD по столбцу ID. Наш запрос произведет сканирование, начиная с первой записи, в которой ID больше 100. Теперь, если установленная на записи индекса блокировка не заблокирует вставки в интервалы, за это время в таблицу может быть вставлена новая дочерняя запись. Если теперь в транзакции запустить

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

еще раз, то в результате запроса будет выдана новая дочерняя запись. Это противоречит принципу изоляции транзакции: транзакция должна запускаться таким образом, чтобы считываемые ею данные не изменялись на протяжении выполнения транзакции. Если мы рассматриваем набор строк как элемент данных, то новая дочерняя ``запись-фантом'' нарушит этот принцип изоляции.

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

Блокировку следующего ключа можно использовать для того, чтобы провести проверку уникальности значений в своей программе. Если данные считываются в режиме совместного доступа и отсутствует дубликат строки, которую необходимо вставить, то можно безопасно вставлять свою строку и быть уверенным, что благодаря блокировке следующего ключа, установленной на предшествующей строке во время чтения, будет предотвращена вставка дублирующейся строки. Таким образом, блокировка следующего ключа позволяет ``заблокировать'' отсутствие чего-либо в таблице.

 

Блокировки, осуществляемые различными операторами SQL в InnoDB.

 

· SELECT... FROM...: согласованное чтение, которое производится из образа базы данных без блокировки.

· SELECT... FROM... LOCK IN SHARE MODE: устанавливает совместно используемую блокировку следующего ключа на все считываемые индексные записи.

· SELECT... FROM... FOR UPDATE: устанавливает эксклюзивную блокировку следующего ключа на все считываемые индексные записи.

· INSERT INTO... VALUES (...): устанавливает эксклюзивную блокировку на вставленную строку. Обратите внимание, что эта блокировка не является блокировкой следующего ключа и не предотвращает вставку другими пользователями записей в интервал перед вставленной строкой. Если произойдет ошибка дублирующегося ключа, оператор устанавливает блокировку совместного доступа на запись дублирующегося индекса.

· INSERT INTO T SELECT... FROM S WHERE... устанавливает эксклюзивную (не следующего ключа) блокировку на каждую вставляемую в T строку. Осуществляет поиск по S как согласованное чтение, но устанавливает блокировки совместного доступа к следующему ключу на S, если включено ведение журнала MySQL. InnoDB в последнем случае должен устанавливать блокировки, так как при восстановлении работоспособности системы с повтором всех завершенных транзакций из резервной копии все операторы SQL должны запускаться точно таким же образом, как и изначально.

· CREATE TABLE... SELECT... выполняет операцию SELECT как согласованное чтение или совместную блокировку, как и в предыдущем пункте.

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

· UPDATE... SET... WHERE...: устанавливает эксклюзивную блокировку следующего ключа для каждой записи, по которой производится поиск.

· DELETE FROM... WHERE...: устанавливает эксклюзивную блокировку следующего ключа для каждой записи, по которой производится поиск.

· Если для таблицы определены ограничения FOREIGN KEY, для любой вставки, обновления или удаления, для которых требуется проверка условий ограничения, устанавливается совместная блокировка на уровне записей, которые просматриваются для проверки ограничения. В InnoDB эти блокировки устанавливаются также в случае нарушения ограничения.

· LOCK TABLES...: устанавливает блокировку таблицы. Эта блокировка производится кодом уровня MySQL. Механизм автоматического обнаружения взаимоблокировок (deadlock) InnoDB не может детектировать взаимоблокировки, в которых участвуют такие блокировки таблиц (см. следующий раздел). Кроме того, поскольку MySQL ``знает'' о блокировке на уровне строки, возможно установление блокировки таблицы, в которой другой пользователь заблокировал строки. Но это не опасно для целостности транзакции.

 

Обнаружение и откат взаимоблокировки (DEADLOCK )

 

InnoDB автоматически обнаруживает взаимоблокировку транзакций и производит откат транзакции или транзакций для предотвращения взаимоблокировок. Начиная с версии 4.0.5 InnoDB будет пытаться выбрать меньшую транзакцию для отката. Размер транзакции определяется количеством строк, которые должны быть добавлены, обновлены или удалены. До версии 4.0.5 InnoDB всегда откатывал транзакцию, запрос на блокировку которой вызвал возникновение взаимоблокировки, то есть замкнутого цикла в графике ожиданий транзакций.

InnoDB не может обнаружить взаимоблокировку, установленную оператором MySQL LOCK TABLES, или блокировку, установленную отличным от InnoDB обработчиком таблиц. Такие ситуации необходимо исправлять при помощи параметра innodb_lock_wait_timeout, который задается в my.cnf.

Когда InnoDB выполняет полный откат транзакции, все блокировки, установленные транзакцией, снимаются. Тем не менее, если в результате ошибки производится откат только одного оператора SQL, некоторые блокировки, установленные оператором, могут остаться в силе. Это происходит потому, что InnoDB хранит блокировку строк в формате, по которому впоследствии нельзя определить, каким оператором SQL была установлена блокировка.

 

Пример работы согласованного чтения в INNODB

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

Чтобы такой момент времени ``передвинуть вперед'', нужно зафиксировать транзакцию, а затем выполнить новую команду SELECT.

Это называется многовариантным контролем совпадений.

Пользователь A Пользователь B SET AUTOCOMMIT=0; SET AUTOCOMMIT=0;время| SELECT * FROM t;| пустой набор данных| INSERT INTO t VALUES (1, 2);|v SELECT * FROM t; пустой набор данных COMMIT; SELECT * FROM t; пустой набор данных; COMMIT; SELECT * FROM t; --------------------- | 1 | 2 |

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

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

SELECT * FROM t LOCK IN SHARE MODE;

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

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

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

· Используйте SHOW INNODB STATUS в MySQL начиная с 3.23.52 и 4.0.3 для определения причины последней взаимоблокировки. Это поможет вам настроить ваше приложение, что бы избежать взаимоблокировок.

· Всегда подготавливайте перезапуск транзакции, если произошел откат из-за взаимоблокировки. Взаимоблокировка не опасна: всего лишь попробуйте еще раз.

· Чаще фиксируйте свои транзакии. Маленькие транзакции меньше склонны к противоречиям.

· Если вы используете чтение с блокировкой SELECT... FOR UPDATE или... LOCK IN SHARE MODE, попробуйте использовать более низкий уровень изоляции READ COMMITTED.

· Производите операции с вашими таблицам и строками в фиксированном порядке. Тогда транзакции будут формировать очередь и не будет происходить взаимоблокировка.

· Добавьте хорошие индексы на ваши таблицы. Тогда ваши запросы будут сканировать меньше индексных записей и, соответственно, будут устанавливать меньше блокировок. Используйте EXPLAIN SELECT для того, чтобы узнать, выбирает ли MySQL соответствующий индекс для ваших запросов.

· Используйте меньше блокировок: если вы можете допустить, чтобы SELECT возвращал данные из старого снимка, не добавляйте к выражению FOR UPDATE или LOCK IN SHARE MODE. Используйте уровень изоляции READ COMMITTED, который больше всего подходит для данной ситуации, так как каждое согласованное чтение внутри одной и той же транзакции читает свой собственный свежий снимок.

· Если ничего не помогло, сериализируйте свои транзакции с блокировкой уровня таблиц: LOCK TABLES t1 WRITE, t2 READ,...; [здесь можете развлекаться с таблицами t1 и t2]; UNLOCK TABLES. Блокировка на уровне таблиц выстраивает ваши транзакции в очередь, и позволяет избежать взаимоблокировки. Заметьте, что LOCK TABLES неявным образом начинает транзакцию наподобие BEGIN, и UNLOCK TABLES неявным образом завершает ее в COMMIT.

· Другое решение для сериализации транзакций - это создание вспомагательного "семафора" таблицы, где есть всего лишь одна строка. Каждая транзакция обновляет эту строку перед доступом к другой таблице. В этом случае все транзакции выполняются в виде очереди. Отметим, что таким же образом в настоящий момент работает и алгоритм определения взаимоблокировок в InnoDB, так как блокировка сериализации - это блокировка уровня строки. При блокировке на уровне таблицы в MySQL мы используем метод таймаута для разрешения взаимоблокировки.

 

Задание на лабораторную работу

1. Изучить предлагаемый теоретический материал.

2. Написать пример работы согласованного чтения.

3. Написать пример работы транзакции на основании выданного варианта.

4. Написать пример работы взаимоблокировки в соответствии с выданным вариантом.

5. Оформить отчет.

6. Защитить лабораторную работу.

 

Контрольные вопросы.

1. Расскажите про типы таблиц.

2. Опишите принципиальные отличия между типами MYISAM и InnoDB.

3. Понятие взаимоблокировки.

4. Понятие согласованного чтения.

5. Опишите принцип действия согласованного чтения.

6. Назовите типы блокировок в InnoDB.

7. Понятие транзакции.

8. Как устраняется проблема с фантомом при блокировке следующего ключа?

Список рекомендуемой литературы

1. MySQL. Справочник по языку.: Пер. с англ. — М.: Издательский дом "Вильямс",2005. — 432 с.

2. MySQL. Библиотека профессионала.: Пер. с англ. — М.: Издательский дом "Вильямс", 2002. — 624 с.: ил. — Парал. тит. англ.

3. Мартин Грубер. Понимание SQL. Москва, 1993

4. Джеймс Р. Грофф, Пол Н. Вайнберг - SQL. Полное руководство. Москва, 2001

5. Mysql.ru

 

 

ПРИЛОЖЕНИЕ А

Таблица А.1 Типы данных полей MySQL

BIGINT [(length)] [UNSIGNED] [ZEROFILL]	8 байт целое (если компилятор поддерживает такой тип)
BLOB	Двоичный объект (максимальная длина 65535 байт)
CHAR(NUM) С	трока фиксированной длины (1 <= NUM <= 255)
DATE	Сохраняет информацию о дате. Использует формат "YYYY-MM-DD". Может модифицироваться как строка или число, хотя Вы, вероятно, используете контекст строки для времени и даты. MySQL тип DATE понимает по крайней мере следующие синтаксис. · YYYY-MM-DD (Обратите внимание что '- ' может фактически быть ЛЮБОЙ не цифрой) · YY-MM-DD (Обратите внимание что '- ' может фактически быть ЛЮБОЙ не цифрой) · YYMMDD · YYMM Диапазон для этого типа данных от 0000-00-00 до 9999-12-31. Так что "проблема 2000" здесь не стоит. В отличие от TIMESTAMP, DATE принимает годы и в виде двух цифр от 0000 до 0099. Это не очень полезно в большинстве случаев. Используйте задание лет четырьмя цифрами в полях типа DATE. Тип DATE имеет длину 4 байта.
DATETIME	Объединение типов DATE и TIME. Тип DATETIME идентичен типу TIMESTAMP со следующими исключениями: · Когда запись вставляется в таблицу, содержащую поля типа DATETIME, поле DATETIME не изменяется. · Диапазон для поля типа DATETIME: '0000-01-01 00:00:00' - '9999-12-31 23:59:59' при использовании в контексте строки, и '00000000000000' - '99991231235959' при использовании в контексте числа. Тип DATETIME имеет длину 8 байт.
DECIMAL (length,dec)	Десятичное число с плавающей запятой.
DOUBLE [(length,dec)]	Число (4 или 8 байт) двойной точности с максимальной длиной и фиксированном числом десятичных чисел.
FLOAT [(precision)]	Номер с плавающей запятой. FLOAT(4) и FLOAT одиночная точность. FLOAT(8) обеспечивает двойную точность.
FLOAT [(length,decimals)]	Число одиночной точности с максимальной длиной и фиксированном числом десятичных чисел (4 байта).
INT [(length)] [UNSIGNED] [ZEROFILL]	Целое (4 байта).
INTEGER [(length)] [UNSIGNED] [ZEROFILL]	Целое число 4 байта
LONGBLOB	Двоичный объект с максимальной длиной 2**32 байт.
MEDIUMBLOB	Двоичный объект с максимальной длиной 16777216 байт.
MEDIUMINT [(length)] [UNSIGNED] [ZEROFILL]	Целое (3 байта).
REAL [(length,dec)]	Идентично DOUBLE (8 байт).
SMALLINT [(length)] [UNSIGNED] [ZEROFILL]	Целое (2 байта).
TINYBLOB	Двоичный объект с максимальной длиной 255 байт.
TINYINT[(length)] [UNSIGNED] [ZEROFILL]	Целое число (1 байт).
VARCHAR(NUM)	Строка переменной длины (1 <= NUM <= 255)
TIME	Хранит информацию о времени. Использует формат "HH:MM:SS". Может использоваться как строка или число. MySQL тип TIME понимает следующий синтаксис. · HH:MM:DD · HHMMDD · HHMM · HH Данные типа TIME имеют длину 3 байта.
TIMESTAMP(NUM)	Автоматически изменяется при вставке/обновлении. Имеет формат YYMMDDHHMMSS или YYYYMMDDHHMMSS. Вы можете модифицировать поле TIMESTAMP при выполнении INSERT. Это полезно, когда Вы хотите установить произвольную дату/время для записи. В течение модификаций Вы не должны определять значение для вашего поля TIMESTAMP, или определять NULL как значение, для вставки. Иначе вы получите недопустимое значение для этого поля. Когда используете mysql с ODBC и Access Вы должны использовать значение 14 для NUM, поскольку это заставляет MySQL всегда использовать в годах четыре цифры. Значение 12 заставит MySQL использовать в году две цифры. Значение по умолчанию - 14. Обратите внимание, что в случае таблиц с несколькими полями TIMESTAMP только первое такое поле будет модифицироваться автоматически.