Трансакции, конкурентно извршување и заклучување на ресурсите

Трансакции

Трансакцијата претставува логичка единица на работа составена од една или повеќе SQL операции кои мора да се извршат како целина. Тоа значи дека или сите операции во трансакцијата ќе се извршат успешно, или во случај на грешка сите направени промени ќе бидат поништени. На овој начин се избегнуваат ситуации во кои базата на податоци би останала во неконзистентна состојба. Во PostgreSQL, трансакцијата се отвора со BEGIN и се затвора со COMMIT или ROLLBACK. Покрај тоа, PostgreSQL поддржува и SAVEPOINT точка внатре во трансакцијата до која може да се врати со ROLLBACK TO SAVEPOINT, без да се откаже целата трансакција. Во практични системи, трансакциите се користат секогаш кога повеќе операции се логички поврзани и треба да се третираат како една целина.

Во системот Film Rental, на пример, процесот на изнајмување филм од страна на клиент не се состои од само една операција. При изнајмување потребно е да се креира нов запис во табелата rental, да се евидентира плаќањето во табелата payment, како и да се ажурира состојбата на копијата на филмот во табелата inventory. Доколку некоја од овие операции не успее, целиот процес треба да се поништи за да се избегнат неконзистентни податоци, како на пример да постои плаќање без регистрирано изнајмување. Поради ова, сите овие операции се извршуваат во рамките на една трансакција.

BEGIN;
INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id)
VALUES (now(), 42, 101, 1);
SAVEPOINT after_rental;
INSERT INTO payment (customer_id, staff_id, rental_id, amount, payment_date)
VALUES (101, 1, (SELECT rental_id FROM rental WHERE customer_id = 101 ORDER BY rental_date DESC LIMIT 1), 4.99, now());
-- Ако уплатата не е точна се враќаме само до savepoint-от
-- изнајмувањето останува само уплатата се откажува
ROLLBACK TO SAVEPOINT after_rental;
-- Се внесува исправна уплата
INSERT INTO payment (customer_id, staff_id, rental_id, amount, payment_date)
VALUES (101, 1, (SELECT rental_id FROM rental WHERE customer_id = 101 ORDER BY rental_date DESC LIMIT 1), 2.99, now());
COMMIT;

Системите за управување со бази на податоци како PostgreSQL обезбедуваат сигурно извршување на трансакциите преку таканаречените ACID својства атомичност, конзистентност, изолација и трајност. Овие својства гарантираат дека трансакциите се извршуваат безбедно, дури и во услови кога повеќе корисници истовремено работат со истите податоци или кога се појавуваат системски грешки.

Аномалии при конкурентно извршување

Кога повеќе трансакции се извршуваат истовремено врз истите податоци, може да се појават различни проблеми кои доведуваат до неконзистентни или неочекувани резултати. Овие проблеми се познати како аномалии при конкурентно извршување. Аномалиите се јавуваат кога трансакциите читаат или модифицираат податоци кои во исто време се менуваат од други трансакции.

Dirty Read

Dirty Read се појавува кога една трансакција чита податоци кои се изменети од друга трансакција, но тие промени сè уште не се потврдени (COMMIT). Доколку втората трансакција подоцна изврши ROLLBACK, првата трансакција ќе има прочитано податоци кои всушност никогаш не требало да постојат во базата.

Non-repeatable Read

Non-repeatable Read се појавува кога една трансакција чита одреден ред, а друга трансакција го изменува истиот ред и ја потврдува промената. Ако првата трансакција повторно го прочита истиот ред, ќе добие различна вредност, иако самата не направила никаква промена.

Phantom Read

Phantom Read се појавува кога една трансакција извршува пребарување кое враќа одреден број редови, а друга трансакција во меѓувреме додава нов ред кој ги исполнува условите на пребарувањето.

Serialization Anomaly

Serialization anomaly се појавува кога повеќе трансакции се извршуваат паралелно и нивниот комбиниран ефект доведува до резултат кој не би бил можен доколку трансакциите се извршеле една по една (сериски).

За да се контролира ова однесување, системите за управување со бази на податоци користат различни изолациски нивоа кои одредуваат колку трансакциите се изолирани една од друга.

Изолациски нивоа

Изолациските нивоа одредуваат како една трансакција ги гледа промените направени од други трансакции кои се извршуваат паралелно. Со користење на различни изолациски нивоа се контролира кои аномалии можат да се појават при конкурентно извршување. PostgreSQL ги поддржува следните изолациски нивоа:

• Read Committed
• Repeatable Read
• Serializable

Секое од овие нивоа обезбедува различен степен на изолација помеѓу трансакциите.

Read Committed Read Committed е стандардното изолациско ниво во PostgreSQL. Кај ова ниво, една трансакција може да чита само податоци кои се веќе потврдени (COMMIT) од други трансакции. Секоја SELECT наредба гледа најнова верзија на податоците во моментот кога се извршува таа наредба. Поради ова, во Read Committed може да се појави Non-repeatable Read.

Сценарио: Вработен ја проверува уплатата за одредено изнајмување, но во меѓувреме друг вработен ја коригира вредноста.

-- Трансакција 1 (Вработен 1 проверува уплата)
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT amount
FROM payment
WHERE payment_id = 1;
-- Враќа 4.99

-- Трансакција 2 (Вработен 2 ја коригира уплатата)
BEGIN;
UPDATE payment
SET amount = 9.99
WHERE payment_id = 1;
COMMIT;


-- Трансакција 1 повторно го чита истиот ред
SELECT amount
FROM payment
WHERE payment_id = 1;
-- Сега враќа 9.99 -> Non-repeatable Read
COMMIT; 

Repeatable Read

Кај Repeatable Read, трансакцијата работи со snapshot на базата од моментот кога започнала. Тоа значи дека сите SELECT операции ќе ги гледаат истите вредности, дури и ако други трансакции направат промени и COMMIT.

Сценарио: Се генерира месечен извештај за сите изнајмувања на клиент каде што е потребен конзистентен поглед и да не се вклучат нови изнајмувања додека извештајот се пишува.

-- Трансакција 1
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM rental WHERE customer_id = 101;
-- Враќа 5

-- Трансакција 2 (додава ново изнајмување)
BEGIN;
INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id)
VALUES (now(), 55, 101, 2);
COMMIT;
--Трансакција 1 повторно брои
SELECT COUNT(*) FROM rental WHERE customer_id = 101;
-- Сè уште враќа 5 снимката е заштитена
COMMIT;

Serializable

Serializable е највисокото изолациско ниво. Кај ова ниво PostgreSQL гарантира дека резултатот од паралелното извршување на трансакциите ќе биде ист како тие да се извршени една по една (сериски).

Сценарио: Системот автоматски доделува бесплатно изнајмување на секој клиент кој ќе достигне 10 изнајмувања во месецот. Двајца вработени истовремено внесуваат изнајмување за ист клиент кој има 9 и двајца гледаат 9, и двајца одлучуваат да додадат бонус.

-- Трансакција 1 (Вработен 1)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM rental
WHERE customer_id = 101
AND rental_date >= date_trunc('month', now());

-- Враќа 9 го внесува изнајмувањето и додава бесплатен бонус

WITH new_rental AS (
    INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id)
    VALUES (now(), 42, 101, 1)
    RETURNING rental_id
)
INSERT INTO payment (customer_id, staff_id, rental_id, amount, payment_date)
SELECT 101, 1, rental_id, 0.00, now()
FROM new_rental;


-- Трансакција 2 (Вработен 2 истовремено)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM rental
WHERE customer_id = 101
AND rental_date >= date_trunc('month', now());

-- Исто така враќа 9 исто одлучува да додаде бесплатен бонус

WITH new_rental AS (
    INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id)
    VALUES (now(), 55, 101, 2)
    RETURNING rental_id
)
INSERT INTO payment (customer_id, staff_id, rental_id, amount, payment_date)
SELECT 101, 2, rental_id, 0.00, now()
FROM new_rental;

-- Трансакција 2 се потврдува
COMMIT;


-- Трансакција 1 се обидува да потврди
COMMIT;
-- ERROR:  could not serialize access due to read/write dependencies among transactions
-- DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
-- HINT:  The transaction might succeed if retried.
-- PostgreSQL ја прекинува Трансакција 1 за да спречи двоен бесплатен бонус

Заклучување на ресурси

При конкурентно извршување на повеќе трансакции, може да се случи повеќе корисници истовремено да се обидат да ги изменат истите податоци. За да се зачува конзистентноста на базата и да се спречат конфликти помеѓу трансакциите, PostgreSQL користи механизам на заклучување на ресурси (locking). Заклучувањето може да биде на ниво на табела (Tabel level locks) или на ниво на ред (Row level locks).

Table-Level Locks

• ACCESS SHARE (AccessShareLock) е најслабото заклучување на табела. Се добива при извршување на SELECT операции кога трансакцијата само чита податоци. Овој lock дозволува повеќе трансакции истовремено да читаат од истата табела и не ги блокира операциите за измена на податоците. Единствено конфликтува со ACCESS EXCLUSIVE заклучување.

• ROW SHARE (RowShareLock) се користи кога трансакцијата избира редови со намера подоцна да ги измени, најчесто при SELECT ... FOR UPDATE или SELECT ... FOR SHARE. Овој тип на заклучување означува дека одредени редови се резервирани за понатамошна обработка и спречува некои посилни операции над табелата.

• ROW EXCLUSIVE (RowExclusiveLock) се добива при операции кои ги менуваат податоците во табелата, како INSERT, UPDATE или DELETE. Овој lock дозволува повеќе трансакции истовремено да менуваат различни редови, но блокира одредени структурни операции врз табелата.

• SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock) се користи при операции за одржување на базата на податоци, како VACUUM, ANALYZE или CREATE INDEX CONCURRENTLY. Овој тип на заклучување овозможува читање на табелата, но спречува други операции кои би можеле да влијаат врз структурата или интегритетот на податоците додека се извршува одржувањето.

• SHARE (ShareLock) се користи кога е потребно табелата да може да се чита, но не и да се менуваат податоците. Овој lock најчесто се добива при извршување на CREATE INDEX. Во овој случај SELECT операциите се дозволени, но INSERT, UPDATE и DELETE се блокирани.

• SHARE ROW EXCLUSIVE (ShareRowExclusiveLock) е посилно заклучување кое се користи при одредени структурни операции како CREATE TRIGGER. Овој lock спречува повеќе вакви операции да се извршуваат истовремено и обезбедува конзистентност на структурата на табелата.

• EXCLUSIVE (ExclusiveLock) дозволува само операции за читање (SELECT), но блокира повеќето други операции кои би можеле да ја изменат табелата. Овој тип на заклучување се користи релативно ретко и служи за операции кои бараат висок степен на изолација.

• ACCESS EXCLUSIVE (AccessExclusiveLock) е најсилното заклучување на табела. Се добива при операции како ALTER TABLE, DROP TABLE или TRUNCATE. Додека овој lock е активен, сите други операции врз табелата се блокирани, вклучувајќи и читање и измена на податоците.

Row-Level Locks

• FOR KEY SHARE Најслабиот row-level заклучок. Дозволува паралелно читање и ажурирање на редот, но блокира бришење и промена на клучните колони. Се користи при проверка на надворешни клучеви кога друга табела го референцира овој ред и сака да се осигура дека нема да биде избришан.

• FOR SHARE Дозволува паралелно читање, но блокира било какво ажурирање или бришење на редот. Корисно кога сакаме да читаме ред и да бидеме сигурни дека нема да се промени додека трае трансакцијата.

• FOR NO KEY UPDATE Слично на FOR UPDATE но послаб ги заклучува редовите за ажурирање, но дозволува паралелен FOR KEY SHARE. Се применува автоматски при UPDATE кој не ги менува клучните колони.

• FOR UPDATE Најстрогиот row-level заклучок. Целосно го заклучува редот и ги блокира сите останати нивоа на заклучување. Се користи кога сакаме да читаме ред и потоа да го ажурираме, а во меѓувреме никој друг не смее да го допре.

Во продолжение се прикажани неколку практични сценарија во кои се користат механизми за заклучување на ресурси со цел да се обезбеди конзистентност на податоците при истовремено извршување на повеќе трансакции.

Сценарио: Продавницата одлучува да ги зголеми цените на сите филмови од категоријата "Action" за 20%. За тоа време не сакаме друг вработен да внесува нови изнајмувања или уплати бидејќи цените се уште не се финализирани.

BEGIN;
LOCK TABLE film IN SHARE MODE;
UPDATE film
SET rental_rate = rental_rate * 1.20
WHERE film_id IN (
    SELECT fc.film_id FROM film_category fc
    JOIN category c ON fc.category_id = c.category_id
    WHERE c.name = 'Action'
);
COMMIT;

Сценарио: На крај на месецот се пресметува вкупниот приход по продавница. Сакаме да читаме конзистентни податоци но да не блокираме обични SELECT операции.

BEGIN;
LOCK TABLE payment IN EXCLUSIVE MODE;
LOCK TABLE rental IN EXCLUSIVE MODE;

SELECT
    s.store_id,
    date_trunc('month', p.payment_date),
    SUM(p.amount),
    COUNT(r.rental_id)
FROM payment p
JOIN rental r ON p.rental_id = r.rental_id
JOIN staff s ON r.staff_id = s.staff_id
WHERE date_trunc('month', p.payment_date) = date_trunc('month', now() - INTERVAL '1 month')
GROUP BY s.store_id, date_trunc('month', p.payment_date);

COMMIT;

Сценарио: Корисник се обидува да изнајми филм, системот прво ја проверува достапноста на копијата и го заклучува редот за да спречи други трансакции да ја изменат истата копија во меѓувреме.

BEGIN;

SELECT i.inventory_id
FROM inventory i
LEFT JOIN rental r
ON i.inventory_id = r.inventory_id
AND r.return_date IS NULL
WHERE i.inventory_id = 42
FOR UPDATE;

INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id)
VALUES (NOW(), 42, 15, 1);

COMMIT;

DEADLOCKS

Deadlock претставува ситуација во која две или повеќе трансакции меѓусебно се блокираат бидејќи секоја од нив држи заклучување врз ресурс што е потребен на другата трансакција. Во таква ситуација ниту една од трансакциите не може да продолжи со извршување. Во PostgreSQL deadlock може да настане и при table-level locks и при row-level locks. На пример, ако една трансакција прво заклучи табела A, а потоа се обиде да ја заклучи табела B, додека друга трансакција веќе ја има заклучено табела B и се обидува да ја заклучи табела A, двете трансакции ќе чекаат една на друга и ќе настане deadlock. PostgreSQL автоматски ги детектира ваквите ситуации. Кога системот ќе открие deadlock, една од трансакциите се прекинува со грешка, со што се ослободуваат заклучувањата и другата трансакција може да продолжи. Кoja трансакција ќе биде прекината не може однапред да се предвиди.

Сценарио: Двајца вработени истовремено ажурираат цени на два филма. Вработен 1 прво го ажурира филм 100 па потоа филм 200, а Вработен 2 почнува со филм 200 па потоа го ажурира филм 100.

Трансакција 1
BEGIN;

UPDATE film
SET rental_rate = rental_rate * 1.10
WHERE film_id = 100;

Трансакција 2
BEGIN;
UPDATE film
SET rental_rate = rental_rate  * 1.10
WHERE film_id = 200;


Трансакција 1
UPDATE film
SET rental_rate = rental_rate * 1.10
WHERE film_id = 200;

Трансакција 2
UPDATE film
SET rental_rate = rental_rate * 1.10
WHERE film_id = 100;

Во овој момент трансакција 2 се обидува да го заклучи редот film_id = 100, кој веќе е заклучен од трансакција 1. Така што трансакција 1 чека ред што го држи трансакција 2 а трансакција 2 чека ред што го држи трансакција 1.

Поради ова PostgreSQL детектира deadlock и прекинува една од трансакциите со грешка:

ERROR:  deadlock detected
DETAIL:  Process 20003 waits for ShareLock on transaction 840; blocked by process 19996.
Process 19996 waits for ShareLock on transaction 841; blocked by process 20003.

Се препорачува трансакциите кои работат со повеќе ресурси секогаш да ги заклучуваат тие ресурси во ист редослед. Исто така, првото заклучување во трансакцијата треба да биде во најрестриктивниот режим што ќе биде потребен за тој ресурс. Доколку не е можно однапред да се гарантира ова, апликацијата треба да биде подготвена да ја повтори трансакцијата доколку таа биде прекината поради deadlock.