Автор:
RXL.
Дата написания: 28.11.2009.
Права на статью принадлежат автору и
Клубу программистов «Весельчак У».
- Часть 1.
- Часть 2.
- Часть 3. (Выйдет позже)
- Часть 4. (Выйдет позже)
Примечательно, что за прошедшую неделю тема про реализацию SEQUENCE-подобных алгоритмов опять попадалась мне на глаза. Значит, кому-то данный материал может понадобиться.
В этой части рассмотрим реализацию, достоинства и недостатки вариантов реализации подобия SEQUNCE на базе команд UPDATE и SELECT.
Многие, кто пытаются реализовать в MySQL механизм, похожий на оракловый SEQUNCE, почему-то обязательно хотят, чтобы приращение было регулируемым — не строго равным единице. Что ж, давайте с этого и начнем.
Сперва сделаем реализацию на таблицах формата MYISAM. На ум сразу приходит что-то подобное:
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 — непростая задача) и т.п. Но мы не будем усложнять задачу.
Напишем пару процедур для создания и удаления записей.
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.
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 для такого решения:
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 номер текущей сессии.
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;
Подправим процедуры создания и удаления записей под изменившиеся условия.
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:
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? Нет на свете совершенства...
Особенно неприятно ограничение на работу в один поток.
Адепты транзакций, наверняка, скажут: мол, проблемы ваши все от нетранзакционных таблиц, а использовали бы транзакции, и была бы надежность и защищенность. Что ж, давайте проверим, что можно сделать с транзакциями.
Напомню, что по базовым условиям, описанным в первой части статьи, мы не можем создавать собственных транзакций и не можем закрывать существующие, чтобы не мешать работе пользовательских программ. Кстати, в функциях мы это сделать и не сможем. Так что считаем, что транзакцию открыл пользователь до вызова наших процедур и функций — пусть он ее и закрывает.
Таблицу 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.
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.
(Ожидается продолжение.)