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

СУБД
Навроцкий Артем
Лекция 4

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

ACID

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

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

Тип Устройство IOPS Интерфейс
HDD 7,200 об/мин SATA-диски ~75-100 SATA 3 Гбит/с
HDD 10,000 об/мин SATA-диски ~125-150 SATA 3 Гбит/с
HDD 10,000 об/мин SAS-диски ~140 SAS
HDD 15,000 об/мин SAS-диски ~175-210 SAS
SSD Intel X25-M G2 MLC ~8 600 SATA 3 Гбит/с
SSD OCZ Vertex 3 ~60 000 (4K) SATA 6 Гбит/с
SSD OCZ Vertex 3 MAX IOPS ~75 000 (4K) SATA 6 Гбит/с
SSD OCZ Vertex 4 ~120 000 IOPS (4K) SATA 6 Гбит/с
SSD OCZ RevoDrive 3 X2 ~200 000 IOPS (4K) PCIe
SSD OCZ Z-Drive R4 CloudServ ~500 000 IOPS PCIe

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

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

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

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

MVCC
MultiVersion Concurrency Control

UPDATE IN PLACE 1: TABLE

id name notes
1 Alice Great at programming
2 Bob Always talking to alice
3 Eve Listens to everyone's conversations

UPDATE IN PLACE 2: SCAN

id name notes
1 Alice
read
Great at programming
2 Bob Always talking to alice
3 Eve Listens to everyone's conversations

UPDATE IN PLACE 3: UPDATE

id name notes
1 Alice Great at programming
~ update
2
Bob Always talking to alice
3 Eve Listens to everyone's conversations

UPDATE IN PLACE 4: UPDATED

id name notes
1 Alice Great at programming
~ update
2
Bob Working very hard
3 Eve Listens to everyone's conversations

UPDATE IN PLACE 5: INSERT

id name notes
1 Alice Great at programming
2 Bob Working very hard
3 Eve Listens to everyone's conversations
+ insert
4
Dave Very promising new-hire

UPDATE IN PLACE 6: DELETE

id name notes
1 Alice Great at programming
2 Bob Working very hard
- delete
3
Eve Listens to everyone's conversations
4 Dave Very promising new-hire

UPDATE IN PLACE 7

id name notes
1 Alice Great at programming
2 Bob Working very hard
4 Dave Very promising new-hire

UPDATE IN PLACE 8: REALITY

id name notes
1 Alice
read
Great at programming
+ update
2
Bob Working very hard
- delete
3
Eve Listens to everyone's conversations
+ insert
4
Dave Very promising new-hire

MVCC 1: TABLE

TXID: 103
xmin
xmax id name notes
100 0 1 Alice Great at programming
101 0 2 Bob Always talk to alice
102 0 3 Eve Listens to everyone's conversations

MVCC 2: UPDATE

TXID: 103
xmin
xmax id name notes
100 0 1 Alice Great at programming
- update
101
0 2 Bob Always talk to alice
102 0 3 Eve Listens to everyone's conversations

MVCC 3: UPDATE IN PROGRESS

TXID: 103
xmin
xmax id name notes
100 0 1 Alice Great at programming
- update
101
103 2 Bob Always talk to alice
102 0 3 Eve Listens to everyone's conversations

MVCC 4: UPDATE IN PROGRESS

TXID: 103
xmin
xmax id name notes
100 0 1 Alice Great at programming
- update
101
103 2 Bob Always talk to alice
102 0 3 Eve Listens to everyone's conversations
+ update
103
0 2 Bob Working very hard

MVCC 5: UPDATED

TXID: 104
xmin
xmax id name notes
100 0 1 Alice Great at programming
101 103 2 Bob Always talk to alice
102 0 3 Eve Listens to everyone's conversations
103 0 2 Bob Working very hard

MVCC 6: INSERT

TXID: 104
xmin
xmax id name notes
100 0 1 Alice Great at programming
101 103 2 Bob Always talk to alice
102 0 3 Eve Listens to everyone's conversations
103 0 2 Bob Working very hard
+ insert
104
0 4 Dave Very promising new-hire

MVCC 7: INSERTED

TXID: 105
xmin
xmax id name notes
100 0 1 Alice Great at programming
101 103 2 Bob Always talk to alice
102 0 3 Eve Listens to everyone's conversations
103 0 2 Bob Working very hard
104 0 4 Dave Very promising new-hire

MVCC 8: DELETE

TXID: 105
xmin
xmax id name notes
100 0 1 Alice Great at programming
101 103 2 Bob Always talk to alice
- delete
102
105 3 Eve Listens to everyone's conversations
103 0 2 Bob Working very hard
104 0 4 Dave Very promising new-hire

MVCC 9: DELETED

TXID: 106
xmin
xmax id name notes
100 0 1 Alice Great at programming
101 103 2 Bob Always talk to alice
102 105 3 Eve Listens to everyone's conversations
103 0 2 Bob Working very hard
104 0 4 Dave Very promising new-hire

Проблемы MVCC

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

Heap (PostgreSQL)

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 uncommited не возможно допускается возможно возможно возможно
Read commited не возможно не возможно возможно возможно возможно
Repeatable read не возможно не возможно не возможно допускается возможно
Serializable не возможно не возможно не возможно не возможно не возможно

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

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

Транзакция 1 Транзакция 2
UPDATE tbl1 SET f2=f2+20 WHERE f1=1;
 
 
UPDATE tbl1 SET f2=f2+25 WHERE f1=1;

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

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

Транзакция 1 Транзакция 2
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)

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

Транзакция 1 Транзакция 2
 
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)

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

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

Транзакция 1 Транзакция 2
 
SELECT SUM(f2) FROM tbl1;
INSERT INTO tbl1 (f1,f2) VALUES (15,20);
 
COMMIT;
 
 
SELECT SUM(f2) FROM tbl1;

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

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

Транзакция 1 Транзакция 2
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 uncommited не возможно допускается возможно возможно возможно
Read commited не возможно не возможно возможно возможно возможно
Repeatable read не возможно не возможно не возможно допускается возможно
Serializable не возможно не возможно не возможно не возможно не возможно

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'
  } ...

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

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

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

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;
transaction gid prepared owner database
48892 tx-41 2017-03-06 22:17:58.231435+03 postgres test
48893 tx-42 2017-03-06 22:18:00.294129+03 postgres test
ROLLBACK PREPARED 'tx-41';
COMMIT PREPARED 'tx-42';

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

Плюсы

Минусы

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

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

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

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

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

Плюсы

Минусы

Навроцкий Артем
E-mail: bozaro@yandex.ru
Спасибо за внимание!