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

Статья

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)

Вроде бы, все просто и даже работает, но есть тут существенный недостаток: если два одновременно выполняющихся в разных сессиях запроса вызовут 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.

(Ожидается продолжение.)

Версия для печати

Обсудить на форуме