-- Transakce je skupina prikazu, ktere se bud vykonaji jako jeden velky
-- celek, nebo se nevykona ani jedna cast transkace.
--
-- Transakce zacina provadenim SQL prikazu. Transakce konci uspesne
-- prikazem COMMIT, kdy jsou provedene zmeny potvrzeny a objevi se v
-- databazi (i pri normalnim odhlaseni je proveden COMMIT).
--
-- Pri chybovem ukonceni transakce je prikazem ROLLBACK proveden navrat
-- k puvodnim hodnotam. Prikazem SAVEPOINT je mozne ulozit stav v
-- rozpracovane transakci a prikazem ROLLBACK to  je mozne
-- obnovit stav v bode SAVEPOINT.
-- Princip mechanismu zamykani tabulky nebo radku v tabulce lze nalezt 
-- na http://www.akadia.com/services/ora_locks_survival_guide.html

000# SQL1> DROP TABLE a CASCADE CONSTRAINTS;
001# SQL1> CREATE TABLE a (a NUMBER);
002#
003# SQL1> SELECT * FROM a;
004#
005# 1> no rows selected
006#
007# SQL1> INSERT INTO a VALUES(1);
008#
009# 1> 1 row created.
010#
011# SQL1> SELECT * FROM a;
012#
013# 1>          A
014# 1> ----------
015# 1>          1
016#
017# SQL1> ROLLBACK;
018#
019# 1> Rollback complete.
020#
021# SQL1> SELECT * FROM a;
022#
023# 1> no rows selected
024#
025# -------------------------------------
026#
027# SQL1> INSERT INTO a VALUES(2);
028#
029# 1> 1 row created.
030#
031# SQL1> COMMIT;
032#
033# 1> Commit complete.
034#
035# SQL1> SELECT * FROM a;
036#
037# 1>          A
038# 1> ----------
039# 1>          2
040#
041# SQL1> ROLLBACK;
042#
043# 1> Rollback complete.
044#
045# SQL1> SELECT * FROM a;
046#
047# 1>          A
048# 1> ----------
049# 1>          2
050#
051# ---------------------------------------
052# SQL1> INSERT INTO a VALUES(3);
053#
054# 1> 1 row created.
055#
056# SQL1> DROP TABLE xyz;
057# SQL1> CREATE TABLE xyz(x NUMBER);
058#
059# 1> Table created.
060#
061# SQL1> ROLLBACK;
062#
063# 1> Rollback complete.
064#
065# SQL1> SELECT * FROM a;
066#
067# 1>          A
068# 1> ----------
069# 1>          2
070# 1>          3
071#
072# SQL1> INSERT INTO a VALUES(9);
073#
074# 1> 1 row created.
075#
076# SQL1> SAVEPOINT s1;
077#
078# 1> Savepoint created.
079#
080# SQL1> INSERT INTO a VALUES(10);
081#
082# 1> 1 row created.
083#
084# SQL1> SAVEPOINT s2;
085#
086# 1> Savepoint created.
087#
088# SQL1> INSERT INTO a VALUES(11);
089#
090# 1> 1 row created.
091#
092# SQL1> SELECT * FROM a;
093#
094# 1>          A
095# 1> ----------
096# 1>          2
097# 1>          3
098# 1>          9
099# 1>         10
100# 1>         11
101#
102# SQL1> ROLLBACK TO s2;
103#
104# 1> Rollback complete.
105#
106# SQL1> SELECT * FROM a;
107#
108# 1>          A
109# 1> ----------
110# 1>          2
111# 1>          3
112# 1>          9
113# 1>         10
114#
115#
116# SQL1> ROLLBACK TO s1;
117#
118# 1> Rollback complete.
119#
120# SQL1> SELECT * FROM a;
121#
122# 1>          A
123# 1> ----------
124# 1>          2
125# 1>          3
126# 1>          9
127#
128# SQL1> ROLLBACK;
129#
130# 1> Rollback complete.
131#
132# SQL1> SELECT * FROM a;
133#
134# 1>          A
135# 1> ----------
136# 1>          2
137# 1>          3
138#
139#
140# -----------------------------------
141# -- READ COMMITED na urovni prikazu funguje vzdy automaticky -- tj.
142# -- libovolny prikaz (vcetne vsech podprikazu, vnorenych selectu apod)
143# -- vidi vzdy konzistentni stav databaze (commitovana data) -- prikaz
144# -- vidi stav tesne pred zacatkem provadeni prikazu
145#
146# -- Defaultne pracuje oracle v transakcnim rezimu read commited
147#
148# -- transakce v rezimu read commited
149#
150# SQL1> DELETE FROM a;
151#
152# 1> 2 rows deleted.
153#
154# SQL1> COMMIT;
155#
156# 1> Commit complete.
157#
158# SQL1> SELECT * FROM a;
159#
160# 1> no rows selected
161#
162# SQL2> INSERT INTO a VALUES(100);
163#
164# 2> 1 row created.
165#
166# SQL1> SELECT * FROM a;
167#
168# 1> no rows selected
169#
170# SQL2> COMMIT;
171#
172# 2> Commit complete.
173#
174# SQL1> SELECT * FROM a;
175# 1>          A
176# 1> ----------
177# 1>        100
178#
179# -- v read committed transaction rezimu jsou zmeny videt,
180# -- jakmile jsou commitovany
181#
182# SQL1> UPDATE lide SET vek=22 WHERE uco=1;
183#
184# 1> 1 row updated.
185#
186# SQL2> SELECT * FROM lide;
187# 2>        UCO       PLAT        VEK
188# 2> ---------- ---------- ----------
189# 2>          1      11000         20
190# 2>          2       8500         25
191#
192# SQL2> UPDATE lide SET plat=11300 WHERE uco=1;
193#
194# -- SQL2 ted ceka na uvolneni zamku pro radek (uco=1)
195#
196# SQL1> COMMIT;
197# 1> Commit complete.
198#
199# 2> 1 row updated.
200#
201# SQL2> COMMIT;
202# 2> Commit complete.
203#
204# SQL1> SELECT * FROM lide;
205# 1>        UCO       PLAT        VEK
206# 1> ---------- ---------- ----------
207# 1>          1      11300         22
208# 1>          2       8500         25
209#
210# -- Chceme-li pracovat nad stejnymi daty po celou dobu transakce
211# -- muzeme pouzit transakcni rezim serializovany
212#
213# SQL1>  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
214#
215# 1> Transaction set.
216#
217# SQL2>  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
218#
219# 2> Transaction set.
220#
221# SQL2> INSERT INTO a VALUES(20);
222#
223# 1> 1 row created.
224#
225# SQL1> SELECT * FROM a;
226#
227# 1>          A
228# 1> ----------
229# 1>        100
230#
231# SQL2> COMMIT;
232#
233# 2> Commit complete.
234#
235# SQL1> SELECT * FROM a;
236# 1>          A
237# 1> ----------
238# 1>        100
239#
240# SQL1> COMMIT;
241#
242# 1> Commit complete.
243#
244# -- SQL1 uvidi zmeny pochazejici od SQL2 az po potvrzeni vlastnich a nasledne (automaticke) synchronizaci tabulky
245# SQL1> SELECT * FROM a;
246#
247# 1>          A
248# 1> ----------
249# 1>         20
250# 1>        100
251#
252#
253# -- Ne vsechny transakce je mozne serializovat
254#
255# SQL1> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
256#
257# 1> Transaction set.
258#
259# SQL2> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
260# 2> Transaction set.
261#
262# SQL1> UPDATE lide SET plat=11300 WHERE uco=1;
263#
264# 1> 1 row updated.
265#
266# SQL2> UPDATE lide SET vek=22 WHERE uco=1;
267#
268# -- SQL2 ted ceka na uvolneni zamku pro na radek (uco=1)
269#
270# SQL1> COMMIT;
271#
272# 1> Commit complete.
273#
274# 2> UPDATE lide SET vek=22 WHERE uco=1;
275# 2>         *
276# 2> ERROR at line ORA-08177: can't serialize access for this transaction
277#
278#
279# -- timto zpusobem muze lehce dojit k deadlocku
280#
281# SQL1> CREATE TABLE lide (uco NUMBER, plat NUMBER, vek NUMBER);
282#
283# 1> Table created.
284#
285# SQL1> INSERT INTO lide VALUES(1,10000,20);
286#
287# 1> 1 row created.
288#
289# SQL1> INSERT INTO lide VALUES(2,8000,25);
290#
291# 1> 1 row created.
292#
293# SQL2> SELECT * FROM lide;
294#
295# 2> no rows selected
296#
297# SQL1> COMMIT;
298#
299# 1> Commit complete.
300#
301# SQL2> SELECT * FROM lide;
302# 2>        UCO       PLAT        VEK
303# 2> ---------- ---------- ----------
304# 2>          1      10000         20
305# 2>          2       8000         25
306#
307# SQL1> UPDATE lide SET plat=8500 WHERE uco=2;
308#
309# 1> 1 row updated.
310#
311# SQL2> UPDATE lide SET vek=21 WHERE uco=1;
312#
313# 2> 1 row updated.
314#
315# -- SQL2 musi nejprve ziskat zamek na zaznam od SQL1 ... 
316# SQL2> UPDATE lide SET vek=26 WHERE uco=2;
317#
318# -- ... a SQL1 musi nejprve ziskat zamek na zaznam od SQL2 => deadlock
319# SQL1> UPDATE lide SET plat=11000 WHERE uco=1;
320#
321# 2> UPDATE lide SET vek=26 WHERE uco=2
322# 2>        *
323# 2> ERROR at line 1:
324# 2> ORA-00060: deadlock detected while waiting for resource
325#
326# SQL2> ROLLBACK;
327#
328# 2> Rollback complete.
329#
330# 1> 1 row updated.
331#
332# SQL1> COMMIT;
333#
334# 1> Commit complete.
335#
336# -- je-li transakce nastavena jen pro cteni,
337# -- nemuze provadet zadne modifikujici operace
338#
339# SQL1> SET TRANSACTION READ ONLY;
340#
341# 1> Transaction set.
342#
343# SQL1> UPDATE lide SET plat=11000 WHERE uco=1;
344#
345# 1> UPDATE lide SET plat=11000 WHERE uco=1;
346# 1>       *
347# 1> ERROR at line 1:
348# 1> ORA-01456: may not perform insert/delete/update operation inside a READ ONLY
349# 1> transaction
350#
351# -- prvni transakce zamkne radky, druha transkce chce zamek na celou tabulku,
352# -- musi pockat az prvni transakce zamek na radcich uvolni.
353#
354# SQL1> SELECT * FROM lide FOR UPDATE;
355# 1>       UCO       PLAT         VEK
356# 1> ---------- ---------- ----------
357# 1>         1      11300          22
358# 1>         2       8500          25
359#
360# SQL2> LOCK TABLE lide IN EXCLUSIVE MODE;
361#
362# SQL1> COMMIT;
363#
364# 1> Commit complete.
365#
366# 2> Table(s) Locked.
367#
368# -- oracle provadi zamykani radku automaticky, pokud napr. vlozime
369# -- v jedne transakci radek a chceme v druhe transkaci chceme vylucny pristup
370# -- k cele tabulce, musime pockat az prvni transakce zamek na radku uvolni
371# -- tj. skonci (at uz commitem nebo rollbackem).
372#
373# SQL1> INSERT INTO lide VALUES(3,1000,19);
374#
375# 1> 1 row created.
376#
377# SQL2> LOCK TABLE lide IN EXCLUSIVE MODE;
378#
379# SQL1> COMMIT;
380#
381# 1> Commit complete.
382#
383# 2> Table(s) Locked.
384#
385# SQL2> COMMIT;
386#
387# 2> Commit complete.
388#
389#
390# -- Pokud specifikujeme nowait parametr pro zamykani tabulek
391# -- tak pokud neni mozne tabulku zamknout hned, dojde k chybe.
392#
393# SQL1> INSERT INTO lide VALUES(4,2000,18); 
394#
395# 1> 1 row created.
396#
397# SQL2> LOCK TABLE lide IN EXCLUSIVE MODE NOWAIT;
398#
399# 2> lock table lide in exclusive mode nowait
400# 2>           *
401# 2> ERROR at line 1:
402# 2> ORA-00054: resource busy and acquire with NOWAIT specified
403#