Transakce prakticky

Tato kapitola navazuje na teoretickou kapitolu o transakcích. Zde ukážu několik praktických příkladů, jak transakce začínat, ukončovat a odvolávat, jak nastavit úroveň izolace transakcí atd. Bez znalosti transakcí se žádný opravdový databázista neobejde, tak dávejte dobrý pozor :-).

BEGIN, COMMIT, ROLLBACK
SAVEPOINT
Nastavení
- Úrovně izolace transakcí
- Autocommit
Problém vejce a slepice
Zamykání tabulek
SELECT FOR UPDATE
MySQL
SQLite
Oracle
Závěr

BEGIN, COMMIT, ROLLBACK

Transakci zahájíte SQL příkazem BEGIN. Za BEGIN může následovat ještě klíčové slovo WORK nebo TRANSACTION, ale ty nemají žádný efekt (jsou tam jen kvůli kompatibilitě s jinými DBMS nebo standardem).

Následují SQL příkazy, které se neuloží, dokud nepotvrdíte transakci příkazem COMMIT (a zase může následovat WORK nebo TRANSACTION).

Pokud si transakci rozmyslíte (třeba proto, že jste udělali nějaký nechtěný UPDATE), můžete celou transakci odvolat pomocí ROLLBACK [WORK|TRANSACTION].

Příklady budu ukazovat na tabulce trest (zkratka transakční test :-). A hned do ní vložím nějaká data.

CREATE TABLE trest (
id SERIAL,
uzivatel VARCHAR(255),
penize NUMERIC(10,0)
);
INSERT INTO trest(uzivatel,penize) VALUES
('Franta', 30000),
('Tonda', 40000),
('Pepa', 11000),
('Marie', 35000);

Fungování transakce si ověříte pomocí dvou spojení do databáze. Abych je od sebe nějak rozlyšil, budou v příkladech tentokrát místo názvu databáze (rimmer1) jména připojení (conn1 a conn2).

A teď se podívejte, co se stane, když se dvě transakce pokusí upravit stejný řádek.

conn1=> BEGIN;
conn1=> UPDATE trest SET penize = penize - 5000 WHERE uzivatel = 'Marie';
UPDATE 1
conn2=> UPDATE trest SET penize = penize - 5000 WHERE uzivatel = 'Marie';
-- prikaz "zamrzne" a ceka na potvrzeni/odvolani transakce conn1
-- pokud conn1 nepotvrdi/neodvola transakci dost rychle, prikaz po nejake dobe selze
conn1=> COMMIT;
-- ted se teprve provede UPDATE z conn2
conn2=> SELECT * FROM trest WHERE uzivatel = 'Marie';
id | uzivatel | penize
----+----------+--------
4 | Marie | 25000
(1 řádka)

Jak vidíte, odečetlo se celkem 10000, žádné „odečtení“ se neztratilo.

Dlouho trvající transakce může způsobit zbytečné selhání jiné transakce. Proto by měli být transakce vždy co nejkratší.

Každá transakce musí počítat s tím, že bude odvolána!

Příklad 3:

conn1=> BEGIN;
conn1=> SELECT * FROM trest WHERE uzivatel = 'Tonda';
id | uzivatel | penize
----+----------+--------
2 | Tonda | 40000
(1 řádka)
conn2=> UPDATE trest SET penize = '35000' WHERE uzivatel = 'Tonda';
UPDATE 1
conn1=> UPDATE trest SET penize = penize - 1000 WHERE uzivatel = 'Tonda';
UPDATE 1
conn1=> SELECT * FROM trest WHERE uzivatel = 'Tonda';
id | uzivatel | penize
----+----------+--------
2 | Tonda | 34000
(1 řádka)
conn1=> COMMIT;
COMMIT
conn1=> SELECT * FROM trest WHERE uzivatel = 'Tonda';
id | uzivatel | penize
----+----------+--------
2 | Tonda | 34000

Postgres databáze má (defaultně) izolační level READ-COMMITED. To znamená, že uvnitř transakce se čtou změny provedené (commitnuté) v jiných transakcích. Proto druhý SELECT vrátil 34000 na místo 39000.

Nezapomeňte, že příkazy na změnu struktury databáze (CREATE TABLE, ALTER TABLE atd.) nemohou být součástí transakce. Pokud je provedete a máte zrovna nějakou transakci započatou, tak se automaticky potvrdí (nebo odvolá, když nejde potvrdit).

SAVEPOINT

Příkaz ROLLBACK zruší celou transakci (od jejího počátku). Naštěstí existuje příkaz SAVEPOINT, kterým si můžete v transakci označit místa, ke kterým se můžete vrátit pomocí ROLLBACK TO savepoint (takže nemusíte rušit celou transakci).

Místo si označíte libovolně zvoleným jménem (které by nemělo začínat číslem, obsahovat mezery atp.).

Není možné zrušit jen příkazy mezi dvěma SAVEPOINTy (například zrušit jen příkaz mezi A a B, ale ponechat příkaz za B (před ROLLBACK TO). Vždycky se ruší všechny příkazy od SAVEPOINTu až po poslední provedený příkaz před ROLLBACK TO příkazem.

Používat SAVEPOINT se vyplatí. Pokud v Postgresu uděláte nějakou chybu (stačí překlep v SQL příkazu), tak Postgres celou transakci zneplatní. Vrácením se k poslednímu SAVEPOINTu si tak může zachránit spoustu práce.

rimmer1=> BEGIN;
BEGIN
rimmer1=> SELECT * FROM trest WHERE id = 2;
id | uzivatel | penize
----+----------+--------
2 | Tonda | 30000
(1 řádka)

rimmer1=> UPDATE trest SET penize = 15000 WHERE id = 2;
UPDATE 1
rimmer1=> SAVEPOINT A;
SAVEPOINT
rimmer1=> UPDATE trest SET penize = 15000 WHERE id 1;
ERROR: syntax error at or near "1"
ŘÁDKA 1: UPDATE trest SET penize = 15000 WHERE id 1;
^
rimmer1=> UPDATE trest SET penize = 15000 WHERE id = 1;
ERROR: current transaction is aborted, commands ignored until end of transaction block
rimmer1=> ROLLBACK TO A;
ROLLBACK
rimmer1=> COMMIT;
COMMIT
rimmer1=> SELECT * FROM trest WHERE id = 2;
id | uzivatel | penize
----+----------+--------
2 | Tonda | 15000
(1 řádka)

Nastavení

Úrovně izolace transakcí

Úroveň izolace aktuální transakce se nastavuje v PostgreSQL pomocí SET TRANSACTION ISOLATION LEVEL. Nastaví se tím úroveň izolace pouze pro začatou transakci. Další transakce bude mít zase nastavenu úroveň podle defaultního natavení, takže, pokud to potřebujete, musíte jí nastavit ISOLATION LEVEL znovu.

SET TRANSACTION ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }

Můžete nastavit jednu ze čtyř nabízených možností, ale READ UNCOMMITTED se v Postgresu chová stejně jako READ COMMITTED. Což znamená, že tak nízkou úroveň izolace jako je READ UNCOMMITTED si v Postgresu nevyzkoušíte.

Před verzí 9.1 byla úroveň SERIALIZABLE synonymum pro REPEATABLE READ.

Aktuálně nastavenou úroveň zobrazíte příkazem SHOW TRANSACTION ISOLATION LEVEL;.

Zkusím znovu příklad 3, tentokrát s ISOLATION LEVEL REPEATABLE READ:

conn1=> UPDATE trest SET penize = 40000 WHERE uzivatel = 'Tonda';
conn1=> BEGIN;
conn1=> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
conn1=> SHOW TRANSACTION ISOLATION LEVEL;
transaction_isolation
-----------------------
repeatable read
(1 řádka)
conn1=> SELECT * FROM trest WHERE uzivatel = 'Tonda';
id | uzivatel | penize
----+----------+--------
2 | Tonda | 40000
(1 řádka)
conn2=> UPDATE trest SET penize = '35000' WHERE uzivatel = 'Tonda';
conn2=> UPDATE trest SET penize = '4444' WHERE uzivatel = 'Marie';
conn1=> SELECT * FROM trest WHERE uzivatel IN ('Tonda', 'Marie');
id | uzivatel | penize
----+----------+--------
2 | Tonda | 40000
4 | Marie | 25000
(2 řádky)
conn1=> UPDATE trest SET penize = penize - 1000 WHERE uzivatel = 'Tonda';
ERROR: could not serialize access due to concurrent update
conn1=> COMMIT;
ROLLBACK

Tentokrát se update nepovedl a celá transakce byla Postgresem odvolána. Kdybych provedl během transakce nějaké změny, všechny by se vrátili zpět – databáze by zůstala ve stavu před začátkem transakce, tedy v konzistentním stavu.

Všiměte si, že Postgres je trochu přísnější v REPEATABLE READ než vyžaduje norma (chová se trochu jako SERIALIZABLE). Norma ale nezakazuje, aby DBMS byli v různých úrovních izolace přísnější. Norma říká, jak „minimálně přísná“ má být úroveň izolace. Takže když se v PostgreSQL READ UNCOMMITTED chová stejně jako READ COMMITTED, normu to taky neporušuje :-).

Úroveň izolace v rámci celého sezení (spojení s databází) se nastavuje příkazem:

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }

Autocommit

Autocommit je nastavení systému, které způsobí, že každý příkaz je defaultně brán jako (potvrzená transakce). Tedy každý SQL příkaz je vlastně BEGIN; SQL příkaz; COMMIT;.

Pokud je autocommit vypnutý (off), pak první příkaz po přihlášení k databázi a každý příkaz po ukončení předchozí transakce začíná novou transakci, tj BEGIN; SQL příkaz; . Transakce se musí ukončit pomoci COMMIT (nebo ROLLBACK).

Navzdory tomu co tvrdí dokumentace, nelze v Postgresu nastavit AUTOCOMMIT na OFF.

rimmer1=> SET AUTOCOMMIT TO OFF;
ERROR: SET AUTOCOMMIT TO OFF is no longer supported
rimmer1=> SHOW AUTOCOMMIT;
autocommit
------------
on
(1 řádka)

Autocommit si můžete vypnout pro klienta psql nastavením autocommit na off v jeho konfiguračním souboru. V Linuxu je to soubor ~/.psqlrc.
Zadejte do něj tuto řádku:

\set AUTOCOMMIT off

Toto nastavení platí samozřejmě jen pro klienta psql. Od této chvíle v něm budete muset každou změnu potvrzovat příkazem COMMIT. Pozor! Bezchybné ukončení psql poslední transakci nepotvrdí (jak by podle standardu mělo).

SERIALIZABLE vs REPEATABLE READ

Jaký že je rozdíl v PostgreSQL (v 9.1 a novějším) mezi SERIALIZABLE a REPEATABLE READ?

Stručně řečeno, pokud je úroveň izolace SERIALIZABLE, musí celá transakce začít a skončit tak, že všechny SQL příkazy v rámci serializované transakce nemohli být ovlivněné příkazy z jiné transakce.

Následující příklad by v úrovni izolace REPEATABLE READ prošla (obě transakce by se podařilo COMMITnout), ale při SERIALIZABLE projde úspěšně jen ta první COMMITnutá.

conn1=> BEGIN;
conn1=> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
conn2=> BEGIN;
conn2=> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
conn2=> SELECT * FROM trest ORDER BY id;
id | uzivatel | penize
----+----------+--------
1 | Franta | 30000
2 | Tonda | 35000
3 | Pepa | 12000
4 | Marie | 4444
(4 řádky)
conn1=> UPDATE trest SET penize = 22000 WHERE id = 1;
conn2=> UPDATE trest SET penize = 32000 WHERE id = 2;
conn1=> COMMIT;
COMMIT
conn2=> 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.
DOPORUČENÍ: The transaction might succeed if retried.

Proč že druhá transakce selhala? Protože conn1 sáhla na tabulku trest a tak conn2 už nemá jistotu, že provedené změny neovlivní aktuální transakci. Konkrétně SELECT v conn2 by dopadl jinak, kdyby transakce conn2 proběhla celá až po conn1.

Platí to i obráceně. Kdybych nejdřív ukončil druhou transakci, commitla by se. Selhal by ale následný commit první transakce – řádek s id 2 by byl během transakce změněn.

Jak už jsem psal, při REPEATABLE READ by se obě transakce odeslali vpořádku.

Chyba by při REPEATABLE READ nastala, pokud by se transakce pokusili přepsat stejný řádek. Při READ COMMITTED by pokus o přepsání stejného řádku v druhé transakci byl zablokován, dokud by neskončila první transakce a pak by proběhl (platil by výsledek po ukončení druhé transakce).

Aby to fungovalo jak jsem pospal, musí mít obě transakce nastaven transaction isolation level na SERIALIZABLE. Je to divný, že level jedné transakce ovlivní funkci jiné transakce, ale je to tak. Pokud by conn1 neměl level SERIALIZABLE, neúčastil by se "serializable" transakcí a neovlivnil by tak conn2.

Problém vejce a slepice

Představte si, že potřebujete vytvořit dvě tabulky (třeba slepice a vejce), které se na sebe navzájem odkazují. Problém nastane už při vytváření první tabulky, která by se měla odkázat na druhou tabulku, která ovšem ještě neexistuje.

To se dá vyřešit přidáním integritního omezení FOREIGN KEY až po vytvoření tabulek.

Pak je tu ale ještě jeden zakopaný pes (nebo slepice?). Když budete chtít vložit do tabulky slepici a odkazovat se na vejce (ze kterého třeba pochází), musí vejce už být uloženo (když je odkaz na vejce NOT NULL). Jenže když chcete uložit vejce, musíte se odkazovat na slepici …

Tento problém lze řešit pomocí odkládání kontroly integritních omezení na konec transakce. Postup je následující: začnete transakci a řeknete, že nechcete kontrolovat integritní omezení. Provedete insert do obou tabulek a transakci ukončíte. Až při jejím ukončení DBMS zkontrolue, zda odložená integritní omezení jsou platná a pokud ne, transakci zruší.

Aby šlo kontrolu integritního omezení odložit, musí se už při vytváření integritního omezení označit za deferrable. Implicitně je totiž každé integritní omezení not deferrable – nejde odložit. Odkládání kontroly na konec transakce totiž stojí nějakou tu námahu pro DBMS navíc.

Pokud označíte IO za deferrable, musíte ještě určit jestli je initially deferred nebo initially immediate (tedy jestli se má implicitně odložit až na konec transakce, nebo se má implicitně kontrolovat hned).

To, jestli mají být IO odloženy na konec transakce nebo ne se dá nastavit explicitně během transakce příkazy:

SET CONSTRAINTS [MODE] ALL DEFERRED;
SET CONSTRAINTS [MODE] ALL IMMEDIATE;

Můžete to nastavit i pro konkrétní IO dle jejich jmen:

SET CONSTRAINTS [MODE] io1, io2,... {DEFERRED|IMMEDIATE};

Nastavení platí pouze pro IO, které byli vytvořeny jako deferrable a pouze do konce aktuální transakce.

Pokud chcete, aby platila změna pro celé sezení (aktuální připojení k databázi), můžete to udělat takto:

ALTER SESSION SET CONSTRAINT {DEFERRED|IMMEDIATE};

Odsunutí nelze aplikovat na CHECK a NOT NULL.

A teď tedy praktický příklad. Nejdřív vytvoření tabulek:

CREATE TABLE slepice (id INT NOT NULL PRIMARY KEY, vejce_id INT NOT NULL);
CREATE TABLE vejce (id INT NOT NULL PRIMARY KEY, slepice_id INT NOT NULL);
ALTER TABLE slepice ADD FOREIGN KEY (vejce_id)
REFERENCES vejce(id) DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE vejce ADD FOREIGN KEY (slepice_id)
REFERENCES slepice(id) DEFERRABLE INITIALLY IMMEDIATE;

Vložení záznamů:

rimmer1=> INSERT INTO vejce VALUES(1,1);
ERROR: insert or update on table "vejce" violates foreign key constraint "vejce_slepice_id_fkey"
DETAIL: Key (slepice_id)=(1) is not present in table "slepice".
rimmer1=> BEGIN;
rimmer1=> SET CONSTRAINTS ALL DEFERRED;
rimmer1=> INSERT INTO vejce VALUES(1,1);
INSERT 0 1
rimmer1=> INSERT INTO slepice VALUES(1,1);
INSERT 0 1
rimmer1=> COMMIT;
COMMIT

Zamykání tabulek

Pokud jste pročetli poctivě kapitolu od zhora až sem, tak už asi tušíte, že to s těma transakcema není zase tak jednoduchý, jak jsem se vám na začátku snažil namluvit :-).

Existuje způsob, jak si nelámat hlavu úrovní izolace transakce. A to je zamykání (LOCK) tabulek.

Můžete si zamknout celou tabulku (nebo i více tabulek) pro zápis, nebo i pro čtení. Do zamknutých taublek pak nikdo nesmí zapisovat nebo znich ani číst. Pokud se o to někdo pokusí, příkaz zamrzne, dokud tabulku neodemknete. Nebo příkaz po nějaké době selže.

Existuje mnoho různých typů zámků, které můžete v Postgresu na tabulku aplikovat. Jejich popis najdete v dokumentaci Explicit Locking. Pokud žádný typ neurčíte, bere se implicintě ACCESS EXCLUSIVE, který ostatním neopovolí vůbec nic (ani zápis do tabulky, ani její čtení).

Představte si situaci, kdy váš program chce vložit do tabulky trest řádek s id 3, uživatelem Tonda a částkou 0 Kč. Pokud ale řádek s tímto ID už existuje, tak ho chce jen updatovat.

ID je primární klič, tak je dobré míst jistotu, že se nikdo jiný nepokusí vložit do tabulky to samé, pokud zjistím, že řádek s daným ID neexistuje. Mohlo by se stát, že dva konkurenční programi si v (téměř) stejný okamžik zjistí že takový řádek v tabulce není a oba se pokusí ho vložit. Když si ale zamknu tabulku, tak se to stát nemůže.

Tabulka se zamyká příkazem LOCK TABLE a jde jí zamknout jen v rámci transakce. Tabulky se uvolní po ukončení transakce (Postgres nemá UNLOCK TABLES).

Postup vypadá tedy nějak následovně:

BEGIN;
LOCK TABLE trest;
SELECT * FROM trest WHERE id = 3;
-- kdyz radek neexistuje
INSERT ...;
-- kdyz radek existuje
UPDATE ... WHERE id = 3;
COMMIT;

Zamykání tabulek má samozřejmě výrazně negativní vliv na efektivitu konkurenční práce. Zamykejte tabulky na co nejkratší dobu, nebo raději vůbec.

SELECT FOR UPDATE

Příkaz SELECT … FOR UPDATE zablokuje (v transakci) všechny řádky, které SELECT vrátí, až do konce transakce. Výhodou oproti LOCK table je, že jsou zablokované jen řádky, které vyhovují podmínce WHERE a ne celá tabulka. Nevýhodou je, že nemůžete zablokovat řádek, který neexistuje. Takže v příkladu z části o Zamykání tabulek by bylo SELECT … FOR UPDATE bezmocné.

Řádky jsou zablokované pouze pro další SELECT … FOR UPDATE, UPDATE, DELETE … „Obyčejný“ SELECT můžete bez problémů provést.

Příklad použití:

SET SESSION TRANSACTION ISOLATION LEVEL ...
-- nejdřív malá inicializace dat v tabulce
conn1=> UPDATE trest SET penize = 10000;
UPDATE 4
conn1=> BEGIN;
BEGIN

conn1=> SELECT * FROM trest WHERE id = 2 FOR UPDATE;
id | uzivatel | penize
----+----------+--------
2 | Tonda | 10000
(1 řádka)

conn2=> SELECT * FROM trest WHERE id = 2;
id | uzivatel | penize
----+----------+--------
2 | Tonda | 10000
(1 řádka)

conn2=> SELECT * FROM trest WHERE id = 2 FOR UPDATE;
-- teď se příkaz zablokoval a čeká na dokončení transakce v conn1
-- buď se dočká, nebo po vypršení nějaké doby umře

SELECT FOR UPDATE druhého spojení se zablokoval, ačkoliv jsem nezačal transakci pomocí BEGIN. Vtip je v tom, že jsem v autocommit režimu, takže je to jako bych BEGIN před příkazem zapsal. Po skončení příkazu se automaticky provede COMMIT, což znamená (a modří už tuší), že se v conn2 žádný řádky nezablokují (resp. se zablokují jen na čas v průběhu SELECTu).

Pozor! V PostgreSQL je bug, který uvolní zámek získaný pomocí SELECT FOR UPDATE, pokud SELECT provedete před SAVEPOINTem, po SAVEPOINTu provedete nějaký UPDATE/DELETE… a pak se k SAVEPOINTu ROLLBACKnete. Viz žlutý rámeček Caution. (Od verze Postgres 9.3 už tato chyba není.)

MySQL

První odlišnost od PostgreSQL je v nastavování úrovně izolace transakcí. MySQL rozlyšuje mezi READ COMMITTED a READ UNCOMMITTED, Defaultní úroveň je (pro tabulky InnoDB) REPEATABLE READ, která není tak přísná jako v Postgresu (kde se víc blíží SERIALIZABLE).

Úroveň transakce se nastavuje před jejím začátekem (a ne až po BEGIN).

K nastavení úrovně pro všechny budoucí transakce v aktuálním sezení stačí přidat kličové slovo SESSION:

SET SESSION TRANSACTION ISOLATION LEVEL ...

Zjištění aktuálního nastavení:

V MySQL lze nastavit autocommit na ON nebo OFF:

mysql> SET AUTOCOMMIT = Off;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+

Řešení problému vejce a slepice

V MySQL nelze odkládat kontrolu integritních omezení (neumí deferrable). Problém s vejcem a slepicí lze vyřešit třeba tak, že se vzájemné odkazy umísti do extra tabulky, třeba se jménem slepice_vejce_spoj. Odkazy v této tabulce se udělají unikátní, aby bylo zajištěno to co v původním zadání – slepice může mít jen jeden odkaz na vejce a opačně.

V MySQL také můžete zamykat tabulky, ale funguje to trochu jinak. Máte na výběr jiné druhy zámků (jen WRITE pro zápis a READ pro čtení). Zamykání se také neprovádí uvnitř transakce a zámky se musí uvolnit příkazem UNLOCK TABLES;. Uvolní se všechny zámky tabulek, nemůžete uvolnit zámek jen na některé tabulce.

LOCK TABLES tabulka1, tabulka2 READ;
-- teď si z tabulek nemůže nikdo nic přečíst, natož zapisovat
... -- nějaká práce
UNLOCK TABLES;

LOCK TABLES se může v MySQL používat namísto transakcí. Jako vždy platí, že byste měli zamykat tabulky na co nejkratší dobu.

SELECT FOR UPDATE funguje obdobně jako v Postgresu (jen v rámci transakce atp.).

SQLite

SQLite zvládá BEGIN [TRANSACTION], SAVEPOINT, ROLLBACK a COMMIT, ale transakce umí jen v režimu SERIALIZABLE.

Pokud se pokusíte upravit tabulku se kterou pracuje jiná transakce, příkaz na nic nečeká a selže. SQLite navíc blokuje (pro UPDATE) celou databázi, nejen tabulku nebo řádek v tabulce.

conn1> BEGIN;
conn1> UPDATE trest SET penize = 15000 WHERE uzivatel = 'Franta';
conn2> UPDATE trest SET penize = 10000 WHERE uzivatel = 'Marie';
Error: database is locked

SQLite neumí LOCK TABLE, SELECT FOR UPDATE ani deferrable integritní omezení.

SQLite prostě není vhodné do prostředí, kde se hodně uživatelů snaží databázi upravovat. Hodí se ale tam, kde se hodně uživatelů snaží z databáze číst (s tím problém není), třeba na webu, nebo pro jednouživatelské aplikace, které chtějí využít sílu SQL.

Oracle

Tabulku pro testování s daty můžete v Oracle vytvořit třeba takto:

CREATE TABLE trest (
id integer primary key,
uzivatel VARCHAR2(255),
penize NUMERIC(10,0)
);
INSERT INTO trest(id, uzivatel, penize) VALUES (1, 'Franta', 30000);
INSERT INTO trest(id, uzivatel, penize) VALUES (2, 'Tonda', 40000);
INSERT INTO trest(id, uzivatel, penize) VALUES (3, 'Pepa', 11000);
INSERT INTO trest(id, uzivatel, penize) VALUES (4, 'Marie', 35000);
COMMIT;

Skrz webové rozhraní APEX si bohužel transakce v Oracle moc nevyzkoušíte. Každý příkaz se totiž posílá z prohlížeče na server k vykonání, každý znamená navázání spojení s databází, provedení příkazu a ukončení spojení. Žádná spojení nepřežije více než jeden SQL dotaz.

Použijte buď spojení přes příkazovou řádku, nebo Oracle SQL Developer, nebo kombinaci obojího.

Oracle má defaultně autocommit vypnutý. Takže první příkaz po připojení k databázi nebo po ukončení transakce začíná transakci novou. Pokud se z databáze normálně odhlásíte, provede se automaticky COMMIT.

Ať vás ani nenapadne začínat transakci příkazem BEGIN. Tak to v Oracle nefunguje. Jak už jsem psal, v Oracle, když si nezapnete autocommit, začne transakce prvním příkazem.

Tímto začnete novou transakci a přiřadíte jí jméno. COMMIT, SAVEPOINT a ROLLBACK už fungují normálně.

Znovu upozorňuji na autocommit :-). Pokud budete zkoušet příklady z této lekce, tak musíte u conn2 zadávat za každým příkazem (kde jsem nezačínal transakci pomocí BEGIN) COMMIT. Nebo si u conn2 zapněte autocommit.

Autocommit se dá nastavit příkazem SET AUTOCOMMIT { ON|OFF };
Aktuální stav se zobrazí příkazem SHOW AUTOCOMMIT;

oracle> SET AUTOCOMMIT ON;
oracle> SHOW AUTOCOMMIT;
autocommit IMMEDIATE

Oracle má jako defaultní úroveň izolace transakcí READ COMMITTED. Nastavení úrovně pro celé sezení (aktuální přihlášení) se provádí příkazy:

ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL = READ COMMITTED;

Je to tak. V Oracle můžete nastavit jen SERIALIZABLE, nebo READ COMMITTED. Tedy můžete nastavit ještě jeden, ale to jde jen pro aktuální transakci. Pro tu se nastavuje úroveň izolace takto:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;

Úroveň izolace pro aktuální transakci musíte nastavit hned na jejím začátku. READ ONLY, jak název napovídá, umožňuje během transakce jen číst (v transakci bude vidět databáze celou dobu v takovém stavu, kdy transakce začala). Inserty, updaty ani dylíty dělat nemůžete.

Bohužel se mi nepodařilo zjistit žádný hezký způsob, jak zjistit aktuální nastavení úrovně izolací. Jediný způsob je pomocí následujícího SELECTu, který ovšem tahá data ze systémových tabulek, ke kterým má přístup jenom administrátorský účet, jako je například SYSTEM, kterému jste zadávali heslo během instalace (já vám říkal, abyste si ho zapamatovali).

Tento SELECT navíc zobrazí úroveň izolace pouze pro aktivní transakce, tedy takové transakce, které provedly nějaký insert, update, delete …

Takže asi takhle:

oracle1> connect system/tajneheslo
oracle2> connect rimmer/tajneheslo
oracle2> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE NAME 'libovolne_jmeno';
oracle2> INSERT INTO trest VALUES (5,'Krasomila',0);
oracle1> SELECT s.sid, s.serial#,t.name,
CASE BITAND(t.flag, POWER(2, 28))
WHEN 0 THEN 'READ COMMITTED' ELSE 'SERIALIZABLE' END
AS isolation_level
FROM v$transaction t, v$session s WHERE t.addr = s.taddr;
SID SERIAL# NAME ISOLATION_LEVE
----------------------------------------------------
32 39 libovolne_jmeno SERIALIZABLE
oracle2> ROLLBACK;

Pokud jde o odkládání IO (deferrable), funguje to jako v Postgresu. Jen příkazy pro nastavení pro SESSION vypadají malilinko jinak:

ALTER SESSION SET CONSTRAINT = DEFERRED;
ALTER SESSION SET CONSTRAINT = IMMEDIATE;

Při zamykání tabulky musíte v Oracle explicitně uvést, v jakém módu (lockmode Clause) ji chcete zamknout.

LOCK TABLE trest IN EXCLUSIVE MODE;

Teď když v jiné transakci provedete SELECT … FOR UPDATE, tak se SELECT zablokuje, dokud neukončíte transakci (a tím neuvolníte zamknutí tabulky). Čímž jsem i prozradil, že v Oracle funguje SELECT … FOR UPDATE :-).

Závěr

Teď už víte, jak šedá je teorie a barevný je strom života. Možná jste trochu frustrovaní z toho, že pořádně nevíte, co vlastně která úroveň izolace v růzdných DBMS dovoluje, co nedovoluje, kdy automaticky stornuje transakci, kdy čeká na dokončení té druhé atd. Vypsat všechny varianty by bylo asi na hodně tlustý sešit. Ale tím se nenechte znechutit, obyčejně si vystačíte s defaultním nastavením izolace a tam, kde potřebujete vědět jak to přesně funguje, si to prostě vyzkoušíte :-).