Статья
Версия для печати
Обсудить на форуме
MySQL. Генерация числовых последовательностей. Эмуляция SEQUENCE.
Часть 2.


Автор: RXL.
Дата написания: 28.11.2009.
Права на статью принадлежат автору и Клубу программистов «Весельчак У».

Содержание.


Вступление ко второй части.

Примечательно, что за прошедшую неделю тема про реализацию SEQUENCE-подобных алгоритмов опять попадалась мне на глаза. Значит, кому-то данный материал может понадобиться.
В этой части рассмотрим реализацию, достоинства и недостатки вариантов реализации подобия SEQUNCE на базе команд UPDATE и SELECT.

На базе MYISAM и UPDATE/SELECT.

Многие, кто пытаются реализовать в MySQL механизм, похожий на оракловый SEQUNCE, почему-то обязательно хотят, чтобы приращение было регулируемым — не строго равным единице. Что ж, давайте с этого и начнем.
Сперва сделаем реализацию на таблицах формата MYISAM. На ум сразу приходит что-то подобное:

Код: (SQL)
CREATE TABLE sequences
(
  name VARCHAR(16) NOT NULL,
  curr_value BIGINT UNSIGNED NOT NULL,
  inc_value BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (name)
) ENGINE=MYISAM;

В принципе, можно добавить больше условий — скажем, ограничить конечное значение, управлять переполнением (переполнить BIGINT — непростая задача) и т.п. Но мы не будем усложнять задачу.
Напишем пару процедур для создания и удаления записей.

Код: (SQL)
CREATE PROCEDURE sequence_create(
  p_name VARCHAR(16),
  p_start_value BIGINT UNSIGNED,
  p_inc_value BIGINT UNSIGNED
)
  MODIFIES SQL DATA
BEGIN
  INSERT IGNORE INTO sequences
  VALUES (p_name, p_start_value, p_inc_value);
END;

CREATE PROCEDURE sequence_drop(
  p_name VARCHAR(16)
)
  MODIFIES SQL DATA
BEGIN
  DELETE FROM sequences
  WHERE name = p_name;
END;

А теперь попробуем сделать функцию nextval.

Код: (SQL)
CREATE FUNCTION NEXTVAL(p_name VARCHAR(16))
  RETURNS BIGINT UNSIGNED
  NOT DETERMINISTIC
  READS SQL DATA
BEGIN
  DECLARE curr_value BIGINT UNSIGNED;

  SELECT s.curr_value
  INTO curr_value
  FROM sequences s
  WHERE s.name = p_name;

  UPDATE sequences s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = p_name;

  RETURN curr_value;
END;

Вроде бы, все просто и даже работает, но есть тут существенный недостаток: если два одновременно выполняющихся в разных сессиях запроса вызовут nextval, то у них может получиться одинаковый результат. Т.е., такая реализация имеет право на существование исключительно при условии, что вызываться nextval будет только одним потоком.
С currval еще сложнее: нам где-то надо сохранить последнее значение, выданное nextval. При этом, значение не должно изменяться никем другим, пока мы не закроем сессию к базе данных.
На ум приходят переменные, типа @varname — они локальны для сессии и самоуничтожаются при отключении. Но опять ограничение: мы не можем задавать имя переменной динамически, чтобы можно было работать с несколькими последовательностями в одной сессии.
Функции nextval и currval для такого решения:

Код: (SQL)
CREATE FUNCTION NEXTVAL(p_name VARCHAR(16))
  RETURNS BIGINT UNSIGNED
  NOT DETERMINISTIC
  READS SQL DATA
BEGIN
  DECLARE curr_value BIGINT UNSIGNED;

  SELECT s.curr_value
  INTO curr_value
  FROM sequences s
  WHERE s.name = p_name;

  UPDATE sequences s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = p_name;

  SET @sequence_value = curr_value;

  RETURN curr_value;
END;

CREATE FUNCTION currval(p_name VARCHAR(16))
  RETURNS BIGINT UNSIGNED
  NOT DETERMINISTIC
  CONTAINS SQL
BEGIN
  RETURN @sequence_value;
END;

Другой вариант — хранить текущее значение в какой-нибудь таблице. В качестве идентификатора возьмем внутренний для MySQL номер текущей сессии.

Код: (SQL)
CREATE TABLE sequence_tmp_values
(
  conn_id INT UNSIGNED NOT NULLб
  name VARCHAR(16) NOT NULL,
  curr_value BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (conn_id, name)
) ENGINE=MYISAM;

Подправим процедуры создания и удаления записей под изменившиеся условия.

Код: (SQL)
CREATE PROCEDURE sequence_create(
  p_name VARCHAR(16),
  p_start_value BIGINT UNSIGNED,
  p_inc_value BIGINT UNSIGNED
)
  MODIFIES SQL DATA
BEGIN
  INSERT IGNORE INTO sequences
  VALUES (p_name, p_start_value, p_inc_value);

  IF ROW_COUNT() > 0 THEN
    DELETE sequence_tmp_values
    WHERE name = p_name;
  END IF;
END;

CREATE PROCEDURE sequence_drop(
  p_name VARCHAR(16)
)
  MODIFIES SQL DATA
BEGIN
  DELETE FROM sequences
  WHERE name = p_name;

  DELETE sequence_tmp_values
  WHERE name = p_name;
END;

Изменившиеся nextval и currval:

Код: (SQL)
CREATE FUNCTION NEXTVAL(p_name VARCHAR(16))
  RETURNS BIGINT UNSIGNED
  NOT DETERMINISTIC
  READS SQL DATA
BEGIN
  DECLARE curr_value BIGINT UNSIGNED;

  SELECT s.curr_value
  INTO curr_value
  FROM sequences s
  WHERE s.name = p_name;

  UPDATE sequences s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = p_name;

  INSERT INTO sequence_tmp_values
  VALUES (CONNECTION_ID(), p_name, curr_value);

  RETURN curr_value;
END;

CREATE FUNCTION currval(p_name VARCHAR(16))
  RETURNS BIGINT UNSIGNED
  NOT DETERMINISTIC
  READS SQL DATA
BEGIN
  DECLARE curr_value BIGINT UNSIGNED;

  SELECT st.curr_value
  INTO curr_value
  FROM sequence_tmp_values st
  WHERE st.conn_id = CONNECTION_ID()
    AND st.name = p_name;

  RETURN curr_value;
END;

Так уже лучше. Но и на этом не стоит успокаиваться: кто ж будет удалять ненужные записи из sequence_tmp_values? Нет на свете совершенства...
Особенно неприятно ограничение на работу в один поток.

На базе INNODB и транзакций.

Адепты транзакций, наверняка, скажут: мол, проблемы ваши все от нетранзакционных таблиц, а использовали бы транзакции, и была бы надежность и защищенность. Что ж, давайте проверим, что можно сделать с транзакциями.
Напомню, что по базовым условиям, описанным в первой части статьи, мы не можем создавать собственных транзакций и не можем закрывать существующие, чтобы не мешать работе пользовательских программ. Кстати, в функциях мы это сделать и не сможем. Так что считаем, что транзакцию открыл пользователь до вызова наших процедур и функций — пусть он ее и закрывает.
Таблицу sequences и процедуры создания и удаления используем из первого варианта (конечно, тип таблицы меняем на ENGINE=INNODB). Заново напишем только функции nextval и currval.
Напомню, что алгоритм механизма транзакций зависит от уровня изолированности транзакции, устанавливаемого оператором SET TRANSACTION ISOLATION LEVEL.
Нам доступны следующие уровни:
  • READ UNCOMMITTED — неблокируемое чтение самых последних данных, включая данные еще не подтвержденных транзакций.
  • READ COMMITTED — неблокируемое чтение данных всех закрытых транзакций. В Oracle это уровень изоляции по умолчанию.
  • REPEATABLE READ — чтение и модификация данных с блокировкой по зависимым данным. Если запрос затрагивает строки, заблокированные другой сессией при чтении с использованием SELECT FOR UPDATE либо измененные посредством INSERT/REPLACE/UPDATE/DELETE, то он будет ожидать завершения либо отката блокирующей его транзакции. Это режим по умолчанию для MySQL.
  • SERIALIZABLE — подобен REPEATABLE READ, но все операторы SELECT без FOR UPDATE автоматически блокируют выбранные строки с LOCK IN SHARE MODE.

Код: (SQL)
CREATE FUNCTION NEXTVAL(p_name VARCHAR(16))
  RETURNS BIGINT UNSIGNED
  NOT DETERMINISTIC
  READS SQL DATA
BEGIN
  DECLARE curr_value BIGINT UNSIGNED;

  SELECT s.curr_value
  INTO curr_value
  FROM sequences s
  WHERE s.name = p_name
  FOR UPDATE;

  UPDATE sequences s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = p_name;

  RETURN curr_value;
END;

CREATE FUNCTION currval(p_name VARCHAR(16))
  RETURNS BIGINT UNSIGNED
  NOT DETERMINISTIC
  READ SQL DATA
BEGIN
  DECLARE curr_value BIGINT UNSIGNED;

  SELECT st.curr_value - s.inc_value
  INTO curr_value
  FROM sequence_tmp_values st
  WHERE st.name = p_name;

  RETURN curr_value;
END;

Если мы используем режим изоляции REPEATABLE READ, то хранить текущее значение отдельно не нужно, т.к. строка, выбираемая по name, блокируется до завершения транзакции.
Недостатки:
  • Другие сессии, которые будут работать с этой строкой, заблокируются в вызове nextval.
  • Другие сессии, использующие режим изоляции READ COMMITTED, не будут видеть наших изменений до завершения нашей транзакции, но и не будут блокироваться, и вызов nextval вернет то же самое значение, что и в нашей сессии.
Т.е., все пользователи нашего генератора последовательностей должны работать в режиме REPEATABLE READ. В режиме READ COMMITTED наша конструкция неработоспособна совсем.

Заключение ко второй части.

Надеюсь, не надо дополнительных доказательств, что показанная в этой части статьи модель — плохая. Она либо совсем неработоспособна, либо работает, но в жестко определенных рамках. В следующей части мы рассмотрим варианты с классическим для MySQL последовательным генератором - AUTO_INCREMENT.

(Ожидается продолжение.)
Версия для печати
Обсудить на форуме