Триггеры PostgreSQL и уровни изоляции

Вступление

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

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

Транзакции базы данных

В системе реляционных баз данных транзакции обладают свойствами ACID, что означает , что они являются атомарными, Согласованными, Изолированными и Долговечными.

Транзакции позволяют базе данных переходить из одного согласованного состояния в другое. Таким образом, все операторы, выполняемые в рамках данной транзакции, должны пройти все проверки ограничений (например, NULL, Внешний ключ, Уникальный ключ, пользовательские ограничения ПРОВЕРКИ), чтобы транзакция была успешно зафиксирована.

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

2PL и MVCC

В системах реляционных баз данных используются два механизма управления параллелизмом:

2PL (Двухфазная блокировка)
MVCC (Управление параллелизмом нескольких версий)

Механизм 2PL был первым, который был использован, и SQL Server по-прежнему использует его по умолчанию (хотя он также может использовать MVCC). Механизм 2PL очень прост для понимания. Чтение получает блокировки общего доступа, в то время как запись получает эксклюзивные блокировки. Блокировки снимаются только в конце транзакций базы данных, будь то фиксация или откат. Таким образом, 2PL-это пессимистичный механизм управления параллелизмом блокировки, поскольку он предотвращает конфликты путем блокировки записей базы данных.

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

Модель базы данных

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

В таблице отдел есть одна запись:

| id | budget | name |
|----|--------|------|
| 1  | 100000 | IT   |

И, в настоящее время в ИТ-отделе работают три строки сотрудник :

| id | name  | salary | department_id |
|----|-------|--------|---------------|
| 1  | Alice | 40000  | 1             |
| 2  | Bob   | 30000  | 1             |
| 3  | Carol | 20000  | 1             |

Предотвращение превышения бюджета

Теперь давайте рассмотрим, что у нас есть два пользователя, Алиса и Боб, которые оба хотят изменить сумму заработной платы следующим образом:

Алиса хочет повысить зарплату на 10% в конце года всем сотрудникам ИТ-отдела, что должно увеличить бюджет с 90000 до 99000
Боб хочет нанять Дейва с зарплатой в 9000 , что также должно увеличить бюджет с 90000 до 99000

Если и Алисе, и Бобу разрешат взять на себя обязательства, то мы рискнем превысить бюджет. Итак, нам нужно определить функцию check_department_budget на основе триггера, которая гарантирует, что сумма зарплат в данном отделе не превысит заранее определенный бюджет:

CREATE OR REPLACE FUNCTION check_department_budget()
  RETURNS TRIGGER AS $$
DECLARE
  allowed_budget BIGINT;
  new_budget     BIGINT;
BEGIN
  SELECT INTO allowed_budget budget
  FROM department
  WHERE id = NEW.department_id;
 
  SELECT INTO new_budget SUM(salary)
  FROM employee
  WHERE department_id = NEW.department_id;
 
  IF new_budget > allowed_budget
  THEN
    RAISE EXCEPTION 'Overbudget department [id:%] by [%]',
    NEW.department_id,
    (new_budget - allowed_budget);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Функция check_department_budget выполняется для каждой ВСТАВКИ и ОБНОВЛЕНИЯ в таблице сотрудник с помощью триггера check_department_budget_trigger PostgreSQL.

CREATE TRIGGER check_department_budget_trigger
AFTER INSERT OR UPDATE ON employee
FOR EACH ROW EXECUTE PROCEDURE check_department_budget();

СЕРИАЛИЗУЕМЫЙ уровень изоляции

Использование СЕРИАЛИЗУЕМОГО уровня изоляции является самым безопасным выбором, потому что это единственный уровень изоляции, который гарантирует стабильность как чтения, так и записи. При запуске нашего примера с использованием СЕРИАЛИЗУЕМОГО мы видим, что транзакция Боба продолжается, в то время как транзакция Алисы откатывается.

При использовании СЕРИАЛИЗУЕМОГО уровня изоляции запрос будет видеть базу данных с начала транзакции, и во время фиксации строки, которые мы ранее прочитали, проверяются, не были ли они изменены в то же время какой-либо параллельной транзакцией.

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

Будучи первым, кто совершит сделку, транзакция Боба завершится успешно. С другой стороны, транзакция Алисы завершается неудачно, поскольку Алиса принимает состояние базы данных, которое устарело в конце ее транзакции. Уровень СЕРИАЛИЗУЕМОЙ изоляции в PostgreSQL использует улучшенную версию стандартного алгоритма изоляции моментальных снимков, который может обнаруживать аномалии перекоса записи.

Этот усовершенствованный механизм изоляции моментальных снимков MVCC называется Сериализуемая изоляция моментальных снимков , и он основан на Докторской диссертации Майкла Джеймса Кэхилла .

ПОВТОРЯЕМЫЙ уровень изоляции СЧИТЫВАНИЯ

При переключении на ПОВТОРЯЕМОЕ ЧТЕНИЕ и повторном запуске нашего предыдущего примера мы видим, что check_department_budget_trigger_ |/больше не может предотвратить аномалию Перекос записи :

Точно так же, как СЕРИАЛИЗУЕМЫЙ, при использовании ПОВТОРЯЕМОГО ЧТЕНИЯ запрос будет видеть базу данных с начала транзакции. Таким образом, когда check_department_budget_trigger будет выполнен из-за заявления Алисы об ОБНОВЛЕНИИ, сумма заработной платы составит 90 000 как это было в начале сделки Алисы.

Но, в отличие от СЕРИАЛИЗУЕМОГО, ПОВТОРЯЕМОЕ ЧТЕНИЕ не отменяет транзакцию из – за аномалий перекоса записи. Таким образом, и Бобу, и Алисе разрешается брать на себя обязательства, и сумма заработной платы выходит за рамки бюджета.

ПОВТОРЯЕМЫЙ уровень изоляции ЧТЕНИЯ в PostgreSQL на самом деле является моделью Изоляции моментальных снимков согласованности. Хотя изоляция моментальных снимков может предотвратить аномалию Фантомного чтения , она не может предотвратить явление перекоса записи.

ПРОЧИТАЙТЕ уровень изоляции С ФИКСАЦИЕЙ

При использовании уровня изоляции С ФИКСАЦИЕЙ ЧТЕНИЯ по умолчанию мы видим, что функция check_department_budget базы данных предотвращает аномалию Перекос записи , которая в противном случае вызвала бы проблему с чрезмерным бюджетом:

В режиме ЧТЕНИЯ С ФИКСАЦИЕЙ компонент MVCC database engine позволяет транзакциям базы данных считывать последние зафиксированные состояния записей. Таким образом, даже если наша текущая транзакция ранее считывала версию N данной записи, если текущая версия этой записи теперь N+1, потому что другая параллельная транзакция только что изменила ее и зафиксировала, наша транзакция будет считывать версию N+1 с помощью последующего оператора SELECT.

В отличие от ПОВТОРЯЕМОГО ЧТЕНИЯ, при использовании READ COMMITTED запрос будет видеть базу данных с начала запроса.

По этой причине инструкция UPDATE завершится ошибкой, поскольку check_department_budget_trigger_ обнаружил, что ОБНОВЛЕНИЕ превысит бюджет. Даже если Алиса прочитает сумму заработной платы в начале своей транзакции, второй ВЫБОР, выполняемый функцией check_department_budget , прочитает последнюю сумму заработной платы сотрудника, что означает, что при этом будет учтена ВСТАВКА Боба.

Но в данном конкретном случае перекос записи может быть предотвращен только в том случае, если транзакция Боба зафиксирована до того, как транзакция Алисы вызовет функцию check_department_budget . Если изменение Боба еще не внесено, Алиса не сможет прочитать изменение, и обе транзакции будут разрешены для фиксации. Чтобы устранить эту проблему, мы можем использовать либо пессимистическую, либо оптимистическую блокировку.

При использовании пессимистической блокировки обе транзакции должны будут заблокировать соответствующую строку department в функции check_department_budget . Таким образом, как только строка department заблокирована, другая параллельная транзакция, пытающаяся получить ту же блокировку, будет заблокирована и будет ждать, пока блокировка будет снята. Таким образом, транзакция Алисы будет ждать фиксации Боба, и аномалия перекоса записи будет предотвращена. Для получения более подробной информации о том, как вы можете получить пессимистическую блокировку, ознакомьтесь с этой статьей .

Другой вариант-использовать оптимистическую блокировку и принудительно увеличивать версию в соответствующей строке department , как описано в этой статье . Таким образом, каждое изменение сотрудника вызовет увеличение версии в соответствующей строке отдела . В конце транзакции Алисы инструкция UPDATE, пытающаяся увеличить отдел , завершится ошибкой, если значение столбца версия было изменено транзакцией Боба, и аномалия перекоса записи будет предотвращена.

Вывод

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

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