СУБД. Лекция 4

Транзакции, триггеры и процедуры

• Транзакции:
  • Обеспечение долговечности. Журнал транзакций;
  • Обеспечение изолированности и атомарности. MVCC;
  • Уровни изолированности транзакций.
• Триггеры:
  • Использование триггеров для поддержания целостности и бизнес-логики;
  • Недостатки триггеров.
• Хранимые процедуры и функции
• Распределенные транзакции (XA) и персистентные очереди.

ACID

ACID описывает требования к транзакционной системе, обеспечивающие наиболее надёжную и предсказуемую её работу. Требования ACID были в основном сформулированы в конце 70-х годов Джимом Греем.

• Atomicity — Атомарность
• Consistency — Согласованность
• Isolation — Изолированность
• Durability — Долговечность

Atomicity — Атомарность

Атомарность гарантирует, что никакая транзакция не будет зафиксирована в системе частично. Будут либо выполнены все её подоперации, либо не выполнено ни одной.

Consistency — Согласованность

Транзакция, достигающая своего нормального завершения и, фиксирующая свои результаты, сохраняет согласованность базы данных.

Другими словами, каждая успешная транзакция по определению фиксирует только допустимые результаты.

Isolation — Изолированность

Во время выполнения транзакции параллельные транзакции не должны оказывать влияние на её результат.

Durability — Долговечность

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

Пример транзакции

BEGIN;

UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';

UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';

COMMIT;

Журнал транзакций

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

Записывается следующая информация:

• порядковый номер, тип и время изменения;
• идентификатор транзакции;
• объект, подвергшийся изменению (номер хранимого файла и номер блока данных в нём, номер строки внутри блока);
• предыдущее состояние объекта и новое состояние объекта.

Журнал содержит отметки начала и завершения транзакции, и отметки принятия контрольной точки.

Журнал транзакций

Общий алгоритм:

• измения пишутся в журнал транзакций (состояние до и после) и изменяются страницы БД в памяти;
• в журнал транзакций сбрасывается маркер успешного завершения транзакции;
• журнал транзакций сбрасывается на диск;
• данные страниц БД пишутся на диск;
• данные страниц БД сбрасываются на диск.

Приблизительные значения IOPS

Групповой коммит

Журнал транзакций. Бонус

На базе журнала транзакций так же реализуется ряд дополнительных возможностей:

• Point in time recovery;
• Репликация.

Изолированность. MVCC

MVCC

MultiVersion Concurrency Control

• Разные пользователи могут одновременно работать с одними и теми же данными;
• Каждый пользователь видит свой изолированный срез данных;
• Изменения, вносимые пользователем, никому не видны до завершения транзации.

UPDATE IN PLACE 1: TABLE

UPDATE IN PLACE 2: SCAN

UPDATE IN PLACE 3: UPDATE

UPDATE IN PLACE 4: UPDATED

UPDATE IN PLACE 5: INSERT

UPDATE IN PLACE 6: DELETE

UPDATE IN PLACE 7

UPDATE IN PLACE 8: REALITY

MVCC 1: TABLE

MVCC 2: UPDATE

MVCC 3: UPDATE IN PROGRESS

MVCC 4: UPDATE IN PROGRESS

MVCC 5: UPDATED

MVCC 6: INSERT

MVCC 7: INSERTED

MVCC 8: DELETE

MVCC 9: DELETED

Проблемы MVCC

• MVCC работает идеально, когда мы пишем номер окончания отранзакции, но мы его заранее не знаем;
• Надо как-то избавляться от "мертвых" кортежей.

Варианты реализации MVCC

Heap (PostgreSQL)

• Все кортежи лежат в общей "куче".
• Мертвые кортежи удаляются в фоне отдельным сборщиком мусора (AUTOVACUUM).
• Фискация и откат транзакции имеют одинаковую стоимость.

Rollback segment (MySQL, Oracle)

• В страницах таблицы лежат только актуальные данные.
• Старые версии хранятся в журнале отката.
• Не требуется сборщик мусора и более эффективное использование дискового пространства.
• Долгий откат транзакции.

Блокировки

test=# BEGIN TRANSACTION
test-# ISOLATION LEVEL REPEATABLE READ;
BEGIN
test=# SELECT balance
test-# FROM accounts WHERE name = 'Alice';
balance
---------
400
(1 row)

test=# UPDATE accounts
test-# SET balance = balance + 100
test-# WHERE name = 'Alice';
UPDATE 1



test=# COMMIT;
COMMIT
            

test=# BEGIN TRANSACTION
test-# ISOLATION LEVEL REPEATABLE READ;
BEGIN
test=# SELECT balance
test-# FROM accounts WHERE name = 'Alice';
balance
---------
400
(1 row)





test=# UPDATE accounts
test-# SET balance = balance + 100
test-# WHERE name = 'Alice';


ERROR:  could not serialize access
        due to concurrent update
            

Блокировки

test=# BEGIN TRANSACTION
test-# ISOLATION LEVEL REPEATABLE READ;
BEGIN
test=# SELECT balance
test-# FROM accounts WHERE name = 'Alice';
balance
---------
500
(1 row)

test=# UPDATE accounts
test-# SET balance = balance + 100
test-# WHERE name = 'Alice';
UPDATE 1



test=# ROLLBACK;
ROLLBACK
            

test=# BEGIN TRANSACTION
test-# ISOLATION LEVEL REPEATABLE READ;
BEGIN
test=# SELECT balance
test-# FROM accounts WHERE name = 'Alice';
balance
---------
500
(1 row)





test=# UPDATE accounts
test-# SET balance = balance + 100
test-# WHERE name = 'Alice';


UPDATE 1
test=# COMMIT;
COMMIT
            

Уровни изолированности транзакций

• Read uncommitted - чтение незафиксированных данных
• Read committed (по-умолчанию) - чтение фиксированных данных
• Repeatable read - повторяемость чтения
• Serializable - упорядочиваемость

Потерянное обновление (Lost Update)

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

UPDATE tbl1 SET f2=f2+20 WHERE f1=1;

UPDATE tbl1 SET f2=f2+25 WHERE f1=1;

«Грязное» чтение (Dirty Read)

Чтение данных, добавленных или изменённых еще не завершенной транзакцией.

SELECT f2 FROM tbl1 WHERE f1=1;
f2
---------
100
(1 row)

UPDATE tbl1 SET f2=200 WHERE f1=1;

SELECT f2 FROM tbl1 WHERE f1=1;
f2
---------
200
(1 row)

ROLLBACK;

Неповторяющееся чтение (Non-Repeatable Read)

При повторном чтении в рамках одной транзакции ранее прочитанные данные оказываются изменёнными.

SELECT f2 FROM tbl1 WHERE f1=1;

UPDATE tbl1 SET f2=f2+1 WHERE f1=1;

COMMIT;

SELECT f2 FROM tbl1 WHERE f1=1;

Чтение "фантомов" (Phantom Reads)

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

От неповторяющегося чтения оно отличается тем, что результат повторного обращения к данным изменился не из-за изменения/удаления самих этих данных, а из-за появления новых (фантомных) данных.

SELECT SUM(f2) FROM tbl1;

INSERT INTO tbl1 (f1,f2) VALUES (15,20);

COMMIT;

SELECT SUM(f2) FROM tbl1;

Аномалии сериализации

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

SELECT SUM(value) FROM mytab
WHERE class = 1;
---------
30
(1 row)

SELECT SUM(value) FROM mytab
WHERE class = 2;
---------
300
(1 row)

INSERT INTO mytab (value, class)
VALUES (30, 2)

INSERT INTO mytab (value, class)
VALUES (300, 1)

COMMIT;

COMMIT;

Уровни изолированности транзакций

READ COMMITED

Вариант A

SELECT id, money FROM account WHERE name = 'Romeo';
SELECT id, money FROM account WHERE name = 'Giulietta';
UPDATE account SET money = 500 WHERE id = 13 AND money = 600;
UPDATE account SET money = 200 WHERE id = 42 AND money = 100;
COMMIT;

Вариант B

SELECT id, money FROM account WHERE name = 'Romeo';
COMMIT;
---
SELECT id, money FROM account WHERE name = 'Giulietta';
COMMIT;
---
UPDATE account SET money = 500 WHERE id = 13 AND money = 600;
UPDATE account SET money = 200 WHERE id = 42 AND money = 100;
COMMIT;

Хранимые процедуры

CREATE TABLE test1 (a int);

CREATE PROCEDURE transaction_test1()
AS $$
BEGIN
	FOR i IN 0..9 LOOP
		INSERT INTO test1 (a) VALUES (i);
		IF i % 2 = 0 THEN
			RAISE NOTICE 'i=%, txid=% will be committed', i, txid_current();
			COMMIT;
		ELSE
			RAISE NOTICE 'i=%, txid=% will be rolledback', i, txid_current();
			ROLLBACK;
		END IF;
	END LOOP;
END
$$
LANGUAGE PLPGSQL;

Хранимые процедуры

В PostgreSQL хранимые процедуры доступны с версии PostgreSQL 11.

В более старых версиях PostgreSQL можно довольствоваться только функциями.

Чем отличаются хранимые процедуры от функций?

• Фукнции можно использовать в запросах
• Хранимые процедуры могут начинать транзакции

Хранимые процедуры

CREATE [ OR REPLACE ] PROCEDURE
    name ( [ [ argmode ] [ argname ]
    argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

Хранимые процедуры (+)

• Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.
• Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц.
• Обеспечивает механизм защиты.
• Улучшение выполнения как следствие сокращения сетевого трафика. С помощью хранимых процедур множество запросов могут быть объединены.

Хранимые процедуры (-)

• Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая - на клиентской.
• Дублирование логики своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
• Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.

Хранимые процедуры: пример

DROP FUNCTION IF EXISTS add(integer, integer);

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION inc(i integer) RETURNS integer AS $$
        BEGIN
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;

SELECT inc(42), add(2, 3);

Хранимые процедуры: пример

CREATE FUNCTION python_max (a integer, b integer)
  RETURNS integer
AS $$
  if a > b:
    return a
  return b
$$ LANGUAGE plpythonu;

CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
    my ($x, $y) = @_;
    if (not defined $x) {
        return undef if not defined $y;
        return $y;
    }
    return $x if not defined $y;
    return $x if $x > $y;
    return $y;
$$ LANGUAGE plperl;

PL/pgSQL: IF

IF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
    ...]]
[ ELSE
    statements ]
END IF;

PL/pgSQL: LOOP

LOOP
    -- some computations
    EXIT WHEN count > 100;
    CONTINUE WHEN count < 50;
    -- some computations for count IN [50 .. 100]
END LOOP;

WHILE amount_owed > 0 LOOP
    -- some computations here
END LOOP;

WHILE NOT done LOOP
    -- some computations here
END LOOP;

PL/pgSQL: FOR

FOR i IN 1..10 LOOP
    -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;

FOR i IN REVERSE 10..1 LOOP
    -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
END LOOP;

FOR i IN REVERSE 10..1 BY 2 LOOP
    -- i will take on the values 10,8,6,4,2 within the loop
END LOOP;

PL/pgSQL: FOR target IN query LOOP

CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
DECLARE
    mviews RECORD;
BEGIN
    RAISE NOTICE 'Refreshing materialized views...';

    FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP

        -- Now "mviews" has one record from cs_materialized_views

        RAISE NOTICE 'Refreshing view %s ...', quote_ident(mviews.mv_name);
        EXECUTE format('TRUNCATE TABLE %I', mviews.mv_name);
        EXECUTE format('INSERT INTO %I %s', mviews.mv_name, mviews.mv_query);
    END LOOP;

    RAISE NOTICE 'Done refreshing materialized views.';
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

PL/pgSQL: FOR target IN query LOOP

CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');

CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS
$BODY$
DECLARE
    r foo%rowtype;
BEGIN
    FOR r IN
        SELECT * FROM foo WHERE fooid > 0
    LOOP
        -- can do some processing here
        RETURN NEXT r; -- return current row of SELECT
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE plpgsql;

SELECT * FROM get_all_foo();

Триггеры

CREATE [ CONSTRAINT ] TRIGGER name
       { BEFORE | AFTER | INSTEAD OF }{ event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ]
        [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE PROCEDURE function_name ( arguments )

where event can be one of:

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE
    TRUNCATE

Триггеры: переменные

NEW

Тип данных RECORD. Переменная содержит новую строку базы данных для команд INSERT/UPDATE.

OLD

Тип данных RECORD. Переменная содержит старую строку базы данных для команд UPDATE/DELETE.

TG_NAME

Тип данных name. Переменная содержит имя сработавшего триггера.

TG_OP

Тип данных text. Строка, содержащая INSERT, UPDATE, DELETE или TRUNCATE, в зависимости от того, для какой операции сработал триггер.

TG_TABLE_NAME

Тип данных name. Имя таблицы, для которой сработал триггер.

Триггеры: пример

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Create a row in emp_audit to reflect the operation performed on emp,
        -- make use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DE­LETE') THEN
            INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UP­DATE') THEN
            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
            RETURN NEW;
        ELSIF (TG_OP = 'IN­SERT') THEN
            INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();

XA-транзакции

1. Приложение начинает транзакцию. Управление передается координатору.
2. Для каждой транзакции координатор вызывает xa_open() для инициализации. Транзакции как таковой ещё нет.
3. Для каждой БД вызывается xa_start() с параметром xid, в котором global_id одно, а branch_id разные. Транзакция началась.
4. Управление возвращается приложению, которое выполняет бизнес логику.
5. Координатор завершает транзакцию, вызывая xa_end().
6. Координатор закрепляет транзакцию, используя протокол двухфазной фиксации (xa_prepare() и xa_commit() или xa_rollback()).

XA-транзакции

BEGIN;

UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';

PREPARE TRANSACTION 'tx-42';

SELECT * FROM pg_prepared_xacts;

ROLLBACK PREPARED 'tx-41';
COMMIT PREPARED 'tx-42';

XA-транзакции

Плюсы

• Простота использования и поддержка из коробки во многих фреймворках.

Минусы

• Более дорогие с точки зрения записи на диск;
• Проблема согласованности резервных копий;
• Координатор - потенциальная точка отказа.

Персистентные очереди

Мы можем "развязать" и клиента и сервер, т.е. у нас выполнение задач не зависит от того, доступен ли в данный момент клиент, или доступен в данный момент сервер.

Клиент ставит задачу в очередь, после этого его роль, в каком-то смысле, заканчивается, сервер в этот момент не обязан быть доступен. Сервер в какой-то момент, когда у него есть возможность, берет задачи из очереди, выполняет ее, изменяет ее статус и т.д.

Клиент всегда может обратиться и проверить статус задачи.

Персистентные очереди

Плюсы

• Клиент и сервер полностью "развязаны";
• Состояние задачи всегда известно;
• Можно поддержать приоритеты.

Минусы

• Дополнительная сущность;
• Больше операций на задачу;
• Очередь должна быть персистентной.