== Трансакции 

Во системи со повеќе корисници како што е Transfermarkt базата на податоци, истовременото извршување на операции може да доведе до неконзистентни податоци и грешки. Затоа се користат трансакции, механизми за заклучување и Multi-Version Concurrency Control (MVCC), кои обезбедуваат сигурност, изолација и висока конкурентност. Во овој дел се анализираат овие концепти преку практични и реални сценарија. 

Трансакција е логичка целина од повеќе операции над базата на податоци. Тие располагаат со повеќе својства (ACID својства): 

* атомичност: или сите операции ќе се извршат(commit) или ниедна нема да се зачува(rollback). Пример ако внесеме нов играч и во истата трансакција внесуваме статистики за тој играч, не сакаме да имаме статистики без играч 

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

* изолација: трансакциите се изолирани една од друга. Така, во PostgreSQL нивото на изолација најчесто е READ COMMITTED, што значи дека една трансакција гледа само податоци кои се веќе извршени. Пример: додека една трансакција ажурира вредност, друга не може да види полу-променети податоци.

* трајност: најважно од се е промените да се запишат и зачуваат по извршувањето. За тоа PostgreSQL користи WAL (ќе го споменеме понатаму). Ако серверот се рестартира, податоците нема да бидат изгубени по извршена трансакција.

Сценарио: трансфер на играч
При трансфер на играч, потребно е да се ажурираат повеќе табели истовремено: буџетот на клубот, информациите за играчот и историјата на трансфери. Овие операции мора да се извршат атомично за да се избегне неконзистентност. 
{{{
BEGIN; 
 
SELECT budget  
FROM clubs  
WHERE club_id = 10  
FOR UPDATE; 
 
UPDATE clubs 
SET budget = budget - 5000000 
WHERE club_id = 10; 
 
UPDATE players 
SET club_id = 10 
WHERE player_id = 65; 
 
INSERT INTO transfers(player_id, from_club, to_club, amount) 
VALUES (65, 5, 10, 5000000); 
 
COMMIT; 
}}}
Прво се заклучува редот за клубот за да се спречи истовремено менување, буџетот се намалува, играчот се префрла во нов клуб, се запишува трансфер. Доколку било која операција не успее, трансакцијата ќе се поништи (ROLLBACK) 
{{{
BEGIN; 
 
UPDATE clubs 
SET budget = budget - 100000000 
WHERE club_id = 10; 
 
ROLLBACK; 
}}}
Ова спречува неконзистентност на базата.

=== Конкурентност

Lost update проблемот се јавува кога две трансакции истовремено читаат и ажурираат ист податок. 
{{{
Session 1: 

BEGIN; 
SELECT budget FROM clubs WHERE club_id = 10; 
}}}
{{{
BEGIN; 
SELECT budget FROM clubs WHERE club_id = 10; 
UPDATE clubs SET budget = 5000000 WHERE club_id = 10; 
COMMIT; 
}}}
{{{
UPDATE clubs SET budget = 7000000 WHERE club_id = 10; 
COMMIT; 
}}}
Вредноста од Session 2 се губи → lost update. Како да се спречи ова? Со Row-Level Locking, поточно со FOR UPDATE кој го заклучува редот.
{{{
BEGIN; 
 
SELECT budget  
FROM clubs  
WHERE club_id = 10  
FOR UPDATE; 
 
UPDATE clubs 
SET budget = budget - 5000000 
WHERE club_id = 10; 
 
COMMIT; 
}}}

=== Изолациони нивоа

Изолационите нивоа дефинираат колку една трансакција ќе биде изолирана од другите. Во PostgreSQL постојат:

* READ UNCOMMITTED: трансакцијата  може да чите и непотврдени промени (dirty reads)

* READ COMMITTED: трансакцијата чита само комитирани промени

* REPEATABLE READ: сите SELECT операции во една трансакција гледаат исти податоци

* SERIALIZABLE: највисоко ниво на изолација - трансакциите се случуваат една по друга

READ COMMITTED vs SERIALIZABLE 

Доколку имаме сценарио дека еден клуб не смее да има повеќе од 5 играчи кои имаат под 18 години(младинци) и ја имаме следната трансакција:
{{{
-- Transaction 1
BEGIN;

SELECT COUNT(*) 
FROM players 
WHERE club_id = 10 AND age < 18;

-- резултат = 4

INSERT INTO players(name, age, club_id)
VALUES ('Young Player 1', 17, 10);

COMMIT;
}}} 
{{{
-- Transaction 2
BEGIN;

SELECT COUNT(*) 
FROM players 
WHERE club_id = 10 AND age < 18;

-- резултат = 4

INSERT INTO players(name, age, club_id)
VALUES ('Young Player 2', 17, 10);

COMMIT;
}}}
На крај ќе имаме 6 играчи со што constraint-от е прекршен. Ова се нарекува write skew проблем, кој не може да се спречи со READ COMMITTED изолација. Двете трансакции читаат валидна состојба, но поради паралелно извршување, системот завршува во неконзистентна состојба. Решение за оваа ситуација е :
{{{
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
}}}
SERIALIZABLE изолацијата гарантира дека ваквите аномалии нема да се појават, но по цена на намалени перформанси.

=== Deadlocks

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

{{{
BEGIN;  
SELECT * FROM players WHERE player_id = 65 FOR UPDATE;  
SELECT * FROM players WHERE player_id = 80 FOR UPDATE;  
COMMIT; 

BEGIN; 
SELECT * FROM players WHERE player_id = 80 FOR UPDATE; 
SELECT * FROM players WHERE player_id = 65 FOR UPDATE; 
COMMIT; 
}}}

Овие две трансакции ажурираат два играчи, но во различен редослед. PostgreSQL детектира deadlock, но една многу добра карактеристика на PostgreSQL e тоа што има deadlock detector при што автоматски прекинува една трансакција.  

Како да избегнеме deadlock? 

{{{
SELECT * FROM players 

WHERE player_id IN (65, 80) 

ORDER BY player_id 

FOR UPDATE; 
}}}

Deadlock состојбите претставуваат сериозен предизвик во системи со висока конкурентност. PostgreSQL користи автоматска детекција на deadlock, при што една од трансакциите се прекинува за да се избегне застој на системот. Примената на конзистентен редослед на заклучување и краткотрајни трансакции значително го намалува ризикот од вакви ситуации. 

=== MVCC
Во системи со повеќе истовремени корисници, класичните механизми за заклучување (locking) можат да доведат до блокирање и намалени перформанси. PostgreSQL го решава овој проблем преку Multi-Version Concurrency Control (MVCC), кој овозможува читање на податоци без блокирање, преку одржување на повеќе верзии на редиците. Наместо да ги заклучува податоците, системот создава нови верзии при секоја промена, додека старите верзии остануваат достапни за други трансакции.

Сценарио 1: Read without blocking - Во класичен locking систем, кога една трансакција ажурира податок, другите трансакции мора да чекаат. Со MVCC, читањето не се блокира, што овозможува висока конкурентност.
{{{
-- Session 1
BEGIN;

UPDATE players
SET market_value_in_eur = 12000000
WHERE player_id = 65;
}}}
{{{
-- Session 2
SELECT market_value_in_eur
FROM players
WHERE player_id = 65;
}}}
Иако Session 1 ја менува вредноста, Session 2 ја гледа старата вредност. Причината е што промената сè уште не е commit-ирана, па MVCC обезбедува читање од претходната верзија на редицата. На овој начин се избегнува блокирање и се овозможува паралелно извршување на операции.

Сценарио 2: Concurrent updates без блокирање - MVCC овозможува повеќе трансакции да работат паралелно, дури и кога ажурираат различни редови.
{{{
-- Session 1
BEGIN;
UPDATE players
SET market_value_in_eur = market_value_in_eur + 2000000
WHERE player_id = 65;
}}}
{{{
-- Session 2
BEGIN;
UPDATE players
SET market_value_in_eur = market_value_in_eur + 3000000
WHERE player_id = 80;
}}}
Бидејќи трансакциите работат со различни редови, тие не се блокираат меѓусебно. MVCC овозможува паралелно извршување, што значително ги подобрува перформансите во системи со голем број корисници.

Сценарио 3: Update conflict - Иако MVCC дозволува паралелно читање, конфликт може да настане кога две трансакции се обидуваат да ажурираат ист ред.
{{{
-- Session 1
BEGIN;
UPDATE players
SET market_value_in_eur = 15000000
WHERE player_id = 65;
}}}
{{{
-- Session 2
BEGIN;
UPDATE players
SET market_value_in_eur = 20000000
WHERE player_id = 65;
}}}
Во овој случај, една од трансакциите ќе мора да чека додека другата не заврши. MVCC не ги елиминира сите конфликти, туку овозможува баланс помеѓу конкурентност и конзистентност.

MVCC vs Locking: Главната разлика помеѓу MVCC и класичното заклучување е во тоа што MVCC дозволува читање без блокирање, додека locking пристапот блокира операции. MVCC обезбедува подобра конкурентност, но бара дополнителна меморија за чување на повеќе верзии.