Постоянно обновляйте несколько строк в таблице, изолируя от одновременных помех
У меня есть задача синхронизировать две таблицы, живущие в разных базах данных. Таким образом, для каждой вставки, обновления и удаления, которые происходят в исходной таблице, эти изменения должны быть реплицированы в целевой таблице. Таблица назначения будет клоном исходной таблицы. Чтобы реализовать это, я решил установить триггеры в исходной таблице.
Но я глубоко обеспокоен параллельными аспектами этих обновлений, так как несколько пользователей используют таблицы одновременно, а иногда триггерам приходится обновлять несколько строк в целевой таблице. С точки зрения изменений, которые должны быть выполнены в триггерах, я очень убежден, что логика верна, но не об уровнях изоляции, потому что я не эксперт в этом.
Итак, я собираюсь показать вам триггер, отвечающий за вставки и обновления таблицы назначения, и попросить вас выяснить, есть ли какие-либо проблемы, связанные с параллельным аспектом. Но перед этим позвольте мне показать вам таблицу и некоторые варианты использования:
Это исходная таблица (для простоты предположим, что таблица назначения имеет ту же структуру):
CREATE TABLE SRC_DEPARTMENTS
(
ID_DEPARTMENT INT NOT NULL PRIMARY KEY,
NAME VARCHAR(80) NOT NULL,
ID_PARENT_DEPARTMENT INT,
HIERARCHY VARCHAR(50) NOT NULL,
ACTIVE BOOLEAN NOT NULL DEFAULT TRUE,
FOREIGN KEY (ID_PARENT_DEPARTMENT) REFERENCES SRC_DEPARTMENTS (ID_DEPARTMENT) ON DELETE CASCADE
);
Теперь предположим, что у меня есть эти строки в таблице назначения:
ID_DEPARTMENT | ID_PARENT_DEPARTMENT | HIERARCHY
--------------+----------------------+----------
1 | | 1
2 | 1 | 1.2
3 | 2 | 1.2.3
4 | 3 | 1.2.3.4
5 | | 5
6 | 5 | 5.6
и я хочу изменить родительский отдел идентификатора 6, чтобы указать на идентификатор 4. После изменения строки должны быть:
ID_DEPARTMENT | ID_PARENT_DEPARTMENT | HIERARCHY
--------------+----------------------+----------
1 | | 1
2 | 1 | 1.2
3 | 2 | 1.2.3
4 | 3 | 1.2.3.4
6 | 4 | 1.2.3.4.6
5 | | 5
Итак, как видите, обновление затронуло только одну строку. Теперь предположим, что я хочу изменить родительский идентификатор идентификатора 1 (то есть NULL), чтобы он указывал на идентификатор 6 (в исходном наборе строк). Итак, после изменения у вас будет:
ID_DEPARTMENT | ID_PARENT_DEPARTMENT | HIERARCHY
--------------+----------------------+----------
5 | | 5
6 | 5 | 5.6
1 | 6 | 5.6.1
2 | 1 | 5.6.1.2
3 | 2 | 5.6.1.2.3
4 | 3 | 5.6.1.2.3.4
Таким образом, в этом случае мне пришлось обновить несколько строк, чтобы исправить иерархию.
Итак, я хочу, чтобы изменение нескольких строк выполнялось последовательно, и я полагаю, что мой триггер не принимает это во внимание. Это триггер:
CREATE OR REPLACE FUNCTION insert_update_department() RETURNS trigger AS $$
DECLARE
_id_parent_department INT;
_id_parent_department_changed BOOLEAN := FALSE;
_hierarchy VARCHAR(50);
_current_hierarchy VARCHAR(50);
BEGIN
IF TG_OP = 'UPDATE' AND (
NEW.NAME IS NOT DISTINCT FROM OLD.NAME AND
NEW.ID_PARENT_DEPARTMENT IS NOT DISTINCT FROM OLD.ID_PARENT_DEPARTMENT AND
NEW.ACTIVE IS NOT DISTINCT FROM OLD.ACTIVE) THEN
RETURN NULL;
END IF;
IF TG_OP = 'INSERT' OR NEW.ID_PARENT_DEPARTMENT IS DISTINCT FROM OLD.ID_PARENT_DEPARTMENT THEN
IF NEW.ID_PARENT_DEPARTMENT IS NULL OR NEW.ID_PARENT_DEPARTMENT = NEW.ID_PARENT_DEPARTMENT THEN
_id_parent_department := NULL;
ELSE
_id_parent_department := NEW.ID_PARENT_DEPARTMENT;
END IF;
IF _id_parent_department IS NULL THEN
_hierarchy := '';
ELSE
SELECT HIERARCHY || '.'
INTO _hierarchy
FROM DST_DEPARTMENTS
WHERE ID_DEPARTMENT = _id_parent_department;
END IF;
_hierarchy := _hierarchy || cast(NEW.ID_DEPARTMENT AS TEXT);
IF TG_OP = 'UPDATE' THEN
SELECT HIERARCHY || '.'
INTO _current_hierarchy
FROM DST_DEPARTMENTS
WHERE ID_DEPARTMENT = NEW.ID_DEPARTMENT;
UPDATE DST_DEPARTMENTS SET
HIERARCHY = _hierarchy || '.' || substr(HIERARCHY, length(_current_hierarchy) + 1)
WHERE HIERARCHY LIKE _current_hierarchy || '%';
END IF;
_id_parent_department_changed := TRUE;
END IF;
IF TG_OP = 'INSERT' THEN
INSERT INTO DST_DEPARTMENTS VALUES (
NEW.ID_DEPARTMENT,
_name,
_id_parent_department,
_hierarchy,
NEW.ACTIVE
);
ELSE
UPDATE DST_DEPARTMENTS SET
NAME = _name,
ID_PARENT_DEPARTMENT = CASE WHEN _id_parent_department_changed THEN _id_parent_department ELSE ID_PARENT_DEPARTMENT END,
HIERARCHY = CASE WHEN _id_parent_department_changed THEN _hierarchy ELSE HIERARCHY END,
ACTIVE = NEW.ACTIVE
WHERE ID_DEPARTMENT = NEW.ID_DEPARTMENT;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER z_insert_update_department
AFTER INSERT OR UPDATE ON SRC_DEPARTMENTS
FOR EACH ROW
EXECUTE PROCEDURE insert_update_department();
Может быть, изменив эти строки из этого:
SELECT HIERARCHY || '.'
INTO _current_hierarchy
FROM DST_DEPARTMENTS
WHERE ID_DEPARTMENT = NEW.ID_DEPARTMENT;
к этому:
SELECT HIERARCHY || '.'
INTO _current_hierarchy
FROM DST_DEPARTMENTS
WHERE ID_DEPARTMENT = NEW.ID_DEPARTMENT
FOR UPDATE;
решит проблему для текущей строки, но не для других строк, которые должны быть обновлены.
Я буду очень рад, если кто-нибудь скажет мне, что нужно сделать, чтобы исправить курок для правильной работы одновременно.
Заранее спасибо.
Marcos
1 ответ
Может быть, использование транзакций может быть полезным... насколько я знаю, они блокируют все изменения в базе данных, пока все действия не будут завершены, поэтому они фиксируют изменения сразу, избегая противоречивых обновлений. Смотрите транзакции, это может быть то, что вы ищете