Při transakčním zpracování dat provádí PgSQL server zamykání řádků a tabulek automaticky. Nastartovaná transakce zamkne řádky tabulek, na nichž probíhají aktivní operace, jako například UPDATE a DELETE, po dobu svého trvání, aby dvě se konkurenční operace nepokoušely měnit data v tabulce, přičemž jedna z nich by aktualizovala data, která již nejsou platná. Pokud je nastartována jedna transakce, která provádí aktivní operace a za jejího běhu je spuštěna transakce druhá, tak ta bude čekat, dokud nebudou z první transakce uvolněny řádky, na kterých se při aktivní operaci "potkaly", ať už potvrzením (COMMIT), nebo zamítnutím (ROLLBACK). Výjimka z tohoto chování je úroveň SERIALIZABLE, která by ukončení druhé transakce vrátilo jako nepotvrzené v momentě potvrzení transakce první.

Zamykání řádek se týká pouze aktivních operací. Aplikace/vlákno téže aplikace na SQL příkaz SELECT dostane požadovaná data, podle zapnutého transakčního módu probíhající transakce, buď ta potvrzená, nebo i ta, která ještě nebyla potvrzena.

V případě, že je třeba společně se SELECTem řádek zamknout proti změnám, například je nutné vybrat aktuální data zaměstnance, ta upravit a poslat zpět na server, přičemž je nežádoucí, aby někdo jiný mezitím mohl tato data modifikovat (tento případ může nastat, když má personalistiku ve firmě na starosti několik pracovníků), je možné použít modifikaci příkazu SELECT doplněnou o klauzuli FOR UPDATE. Postup by tedy mohl být následující:

BEGIN
SELECT * FROM employees WHERE pid = 562 FOR UPDATE;
-- v programu jsou zmenena data, ostatni je 
-- mohou poze cist, nikoliv menit, nebo mazat
UPDATE employees SET (... hodnoty ...) WHERE pid = 562;
COMMIT;
-- v pripade chyby ROLLBACK

Pokud by nastala chyba, například by spadl databázový server, nebo bylo přerušené spojení mezi databází a programem, je transakce automaticky zrušena.

Dosud bylo psáno o zamykání jen na úrovni řádek, tak jak je řeší automaticky PostgreSQL server. Samozřejmě, že server může v případě potřeby zamknout i celou tabulku, například při příkazu pro změnu tabulky ALTER TABLE (příkaz ALTER bude probírán až malinko později).

Implicitní zamykání, o kterém byla tato kapitola není pro programátora, pracujícího s PostgreSQL serverem až tak moc zajímavá, protože jí řeší víceméně server sám. Mnohem  zajímavější je explicitní zamykání, kdy vývojář sám určuje, které tabulky/řádky a jak budou zamčeny.

Explicitní zamykání

Podobně, jako u transakcí, je i u zamykání několik módů, které se liší tím, co dovolují a co nikoliv. Dříve, než bude probrán příkaz pro zamykání tabulek, který se jmenuje zcela intuitivně LOCK, je vhodné seznámit se s těmito módu.

Klíčovými slovy pro jednotlivé módy jsou:

• EXCLUSIVE - Výlučný zámek, který nedovoluje zamknout kterékoliv jiné transakci, jinému příkazu, tabulku, nebo řádek. Tento mód zámku je dafaultní, tzn. není li uvedeno EXCLUSIVE, nebo SHARE, je použit tento druh zámku.
• SHARE - Další  příkazy/transakce mohou sdílet tento zámek. Je-li tabulka/řádek zamknuta tímto zámkem, není možné jej "přebít" pomocí zámku EXCLUSIVE.
• ROW - Uzamčení řádk(u/ů) tabulky.
• TABLE - Uzamčení celé tabulky, nejvíce restriktivní, pokud jej PostgreSQL serveru nepřikážeme explicitně, tak ta jej použije jen velmi vyjímečně.
• ACCESS - Zamčení schématu tabulky, tzn. že není možné měnit její strukturu

Jejich logické kombinace, respektive módy, které umí PgSQL a používá implicitně, jsou uvedeny v následujícím výčtu, včetně možných konfliktů:

• ACCES SHARE - Mód pro příkazy, které nepracují aktivně s daty, mohou jej získat například příkazy SELECT a ANALYZE (ten bude probrán později). Nejméně omezující mód, zabraňuje změně tabulky příkazy ALTER TABLE, DROP TABLE a VACUUM
• Tento mód se vyločuje se souběhem ACCESS
• ROW SHARE - Mód, ve kterém si zamyká řádky příkaz SELECT ... FOR UPDATE. Pokud jde o nějaký kombinovaný SELECT, tak všechny, které nejsou označeny jako FOR UPDATE si PgSQL server zamkne v ACCESS SHARE módu. Tento mód lze příkazem povýšit na ROW EXCLUSIVE
• Vylučuje se s módy EXCLUSIVE a ACCESS EXCLUSIVE
• ROW EXCLUSIVE - V tomto módu si zamykají řádky příkazy, které provádí aktivní činnost nad daty, tj. UPDATE, DELETE a INSERT. Změna struktury rad je mnohem více restriktivní. Pokud je s těmito příkazy spjat nějaký SELECT jsou řádky tohoto SELECTU uzamčeny jen jako ACCESS SHARE.
• Konflikt s SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE a ACCESS EXCLUSIVE.
• SHARE UPDATE EXCLUSIVE - Používá jej příkaz VACUUM (opět bude probrán později)
• Konflikt s módy SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE a ACCESS EXCLUSIVE
• SHARE - Data lze číst i zapisovat (jen je tím značně ztížena operace, která si tento zámek vytváří), tento zámek si dělá PgSQL při vytváření indexů. Jedná se o sdílené uzamčení celé tabulky, čiže opět jsou blokováný příkazy ALTER TABLE, DROP TABLE a VACUUM.
• Konflikty: ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE a ACCESS EXCLUSIVE.
• SHARE ROW EXCLUSIVE - Server tento mód nepoužívá automaticky. Blokuje souběžné dotazy SELECT ... FOR UPDATE, tj. zámek ROW SHARE.
• Konflikty: ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE a ACCESS EXCLUSIVE
• ACCESS EXCLUSIVE - Je automaticky použit při ALTER TABLE, DROP TABLE a VACUUM. Nejvíce omezující režim, který nedovolí jakékoliv souběžné operace na tabulce
• Vylučuje se se všemi ostatními módy.
• EXCLUSIVE - Blokuje na celé tabulce stejné dotazy, jako SHARE ROW EXCLUSIVE. Na tabulce je povolené pouze čtení.
• Vylučuje se s  ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE a ACCESS EXCLUSIVE.

Zámek se na tabulce uplatňuje pomocí příkazu: LOCK TABLE jmeno_tabulky [, dalsi_tabulky] [IN mod_zamceni MODE] [NOWAIT], kde mod_zamceni je jeden z výše jmenovaných a klauzule NOWAIT oznamuje, že pokus o uzamčení tabulky a tím i transakce budou ukončeny neúspěšně (ROLLBACK), když nebude možné okamžité požadované uzamčení tabulky (tabulek). Zámky tabulek se uvolňují ukončením transakcí, ať již úspěšným, nebo neúspěšným.

Pokud nebude explicitně vyjmenován mód uzamčení tabulky, server automaticky použije to nejpřísnější zamčení, tj. LOCK TABLE tabulka IN ACCESS EXCLUSIVE MODE NOWAIT.

Automatické zamykání tabulek lze většinou ponechat bez toho, že bude z programu, který PgSQL používá, jakkoliv měněno. Může být ale velikou pomocí, když je třeba tato pravidla pro určité operace zpřísnit, například při postupném čtení velkého množství dat, kdy je nežádoucí, aby někdo změnil data, která již byla načtena. Přesně k tomuto slouží příkaz LOCK.

Deadlock

V případě velkého provozu na databázi (například při souběžném připojení velikého počtu klientských aplikací) lze narazit na stav, kdy dvě transakce budou vzájemně čekat na výsledek té druhé (1. transakce ke svému dokončení potřebuje výsledek z první a naopak), který se označuje jako Deadlock. Systém automaticky jednu z těchto transakcí vrátí (ROLLBACK), protože tyto by jinak mohli čekat do nekonečna. Tomuto stavu lze předejít díky zámkům. PgSQL server má většinu zámků u konkurenčních databází konfliktních, což znamená, že zamkne-li se z aplikace tabulka, nebo řádek, a z jiného klienta je použit zámek, který je konfliktní s tím současným, je tato transakce odmítnuta. Pouhým pohledem do tabulky módů zámků lze najít autokonfliktní zámky (tj. jsou v konfliktu sami se sebou), což znamená, že je výhodné je použít. Pokud není možné použít zamčení na stejný typ zámků, bylo by výhodné zajistit, aby nejdříve byly uplatněny ty nejpřísnější zámky z požadovaných.

V praxi k deadlocku téměř nemůže dojít, protože PgSQL server nejméně jednu transakci zruší, kdyby se do tohoto stavu dostal, aby těm ostatním umožnil doběhnout.

Tip

Pro otestování toho, jak fungují transakce a zámky není třeba psát složitou aplikaci, stačí se přes několik terminálů přihlásit k PgSQL, na nich nastartovat transakce a v nich zkoušet konfliktní chování příkazů, tj. včetně toho, co Vám server dovolí a co nikolivěk.

Závěrem

Cílem tohoto dílu bylo představit zamykání tabulek a řádků, které v těsném závěsu za transakcemi "zaručuje" konzistenci vkládaných dat. Bohužel ani tento mechanismus není dokonalý a pokud se vyskytne uživatel, který bude mít snahu, byť nezáměrnou, vložit do databáze nesmysly a bude tato snaha kombinována s nedostatečně předvídavým programátorem, bude v datech chaos plný nesmyslů, díky transakcím, alespoň konzistentní.

Mechanismus transakcí a zámků umí většina databázových serverů, včetně, především mezi webaři, široce používaného MySQL, byť to neumí transakce na svých přirozených tabulkách, ale o tomto serveru je na portále LinuxSoft.cz "sesterský" seriál, kde tato problematika též bude probrána. Zámky MySQL také umí, ale nemají tak široké možnosti a není zase tak obtížné zamknout tabulku mimo transakci a tato tabulka pak může zůstat zamčená téměř donekonečna, tj. nejméně do doby, než ji administrátor odemkne.

Tímto dílem seriálu je ukončena první logická část seriálu, ve které bylo především cílem naučit čtenáře, jak vytvořit databázi, vložit do ní data, ta umět vybírat, měnit, eventuálně mazat, zpracovat je jednoduše pomocí vestavěných funkcí, databázi urychlit pomocí indexů a naučit se pomocí transakcí udržet konzistentní data, kdy při aktivních operacích se provádí několik souvisejících příkazů. Od dalšího dílu budou již probírána malinko pokročilejší témata, která již nemusí být potřebná pro základní a jednoduché používání PostgreSQL serveru.