Transakcje od Karoliny :D
--LISTA 8 - Transakcje
--1. Utworzyć dwie sesje użytkownika.

--2 polaczenia

--2. W sesji pierwszej spróbować wykonać UPDATE na konkretnym wierszu,w jednej z własnych tabel z PS 5,
--nie zatwierdzając transakcji, a w drugiej spróbować usunąć ten sam wiersz (wiersze najlepiej identyfikować po kluczu głównym).
--Co się dzieje w drugiej transakcji?

--skrypt do zadań
DROP TABLE TABELA1;
DROP TABLE TABELA2;
CREATE TABLE TABELA1(
ID NUMBER PRIMARY KEY,
POLE1 VARCHAR(30),
POLE2 VARCHAR(30));
CREATE TABLE TABELA2(
ID NUMBER PRIMARY KEY,
POLE1 VARCHAR(30),
POLE2 VARCHAR(30));

DECLARE
l_loop NUMBER(6) :=100;
BEGIN
FOR i IN 1 .. l_loop LOOP
  INSERT INTO TABELA1(ID, POLE1, POLE2)
  VALUES(i,'POLE1 '||i, 'POLE2 '||i);
END LOOP;
END;

DECLARE
l_loop NUMBER(6) :=100;
BEGIN
FOR i IN 1 .. l_loop LOOP
  INSERT INTO TABELA2(ID, POLE1, POLE2)
  VALUES(i,'POLE1 '||i, 'POLE2 '||i);
END LOOP;
END;

COMMIT;

--sesja 1
UPDATE TABELA1
SET POLE1 = 'EDYCJA'
WHERE ID=12;

SELECT *
FROM TABELA1
WHERE ID=12; --dostajemy wynik

--sesja 2
DELETE TABELA1
WHERE ID=12;

SELECT *
FROM TABELA1
WHERE ID=12; --zawiesza się, oczekiwanie 2 transakcji na zatwierdzenie 1

--Chodzi o to że operacje na bazie danych wykonywane są w obrębie danej sesji, jezeli w pierwszej updateujemy coś a w drugiej usuwamy
--ten sam wiersz to ta operacja zostanie wykonana ale tylko w obrębie danej sesji,
--w powyższym przypadku następuje zawieszenie transakcji 2 i oczekiwanie na zatwierdzenie zmian wprowadzanych w transakcji 1
--dopiero po zatwierdzeniu zmian (COMMIT) zmiany są tak naprawdę wprowadzane


--3. W sesji pierwszej zatwierdzić transakcję czyli operację UPDATE. Co się stało w drugiej sesji?

--sesja1
COMMIT;

--sesja2
SELECT *
FROM REZERWACJA
WHERE nr_rezerwacji=12; --brak wyników

-- w sesji drugiej zostal usunięty dany wiersz, transakcja 2 oczekiwaa na wykonanie 1 po czym zostaa wykonana

--4. Powtórzyć zadanie 2 tyle, że w drugiej sesji wykonać operację dodania nowej kolumny do tabeli.
--Czy dodanie nowej kolumny do tabeli jest blokowane przez UPDATE pojedynczego wiersza?

--sesja1
UPDATE TABELA1
SET POLE1 = 'EDYCJA - 2'
WHERE ID = 1;

--sesja2
ALTER TABLE TABELA1
ADD POLE3 NUMBER(6);

--Tak, dodanie nowej kolumny do tabeli jest blokowane przez UPDATE pojedynczego wiersza. Transakcja 2 byla zablokowana
--do momentu w którym zatwierdzono zmiany w sesji 1

--5. Powtórzyć zadanie 2. Zalogować się do bazy jako SYSUSER hasło SYSUSER.
--Sprawdzić blokujące transakcje i zabić tę sesję która blokuje. Co się stanie w sesji oczekującej?

CONNECT SYSUSER/SYSUSER AS sysdba;
DISCONNECT;

SELECT SID, SERIAL#, USERNAME
FROM V$SESSION
WHERE SID IN (SELECT BLOCKING_SESSION
              FROM V$SESSION);

ALTER SYSTEM KILL SESSION 'sid,serial';

--Nie wiem czy dobrze, brak uprawnień do polaczenia.
--Najprawdopodobniej sesja oczekująca zostanie wykonana

--6. W jednej sesji wybrać rekordy spełniające narzucony warunek.
--W drugiej sesji dodać nowy rekord spełniający również narzucony warunek w sesji pierwszej.
--W sesji pierwszej dla wybranych rekordów wykonać np. zliczanie rekordów.
--Czy liczba rekordów zgadza się z liczbą pobranych w poprzednim zapytaniu?

--sesja 1
SELECT COUNT(1)
FROM Adres
WHERE kod_pocztowy = '18-200'; --1 rekord

--sesja2
INSERT INTO Adres(id_adresu,ulica,numer_budynku, kod_pocztowy, miasto)
VALUES(100100, 'Ulica',101,'18-200','Biaystok');

SELECT COUNT(1)
FROM Adres
WHERE kod_pocztowy = '18-200'; --2 rekordy

--sesja1
SELECT COUNT(1)
FROM Adres
WHERE kod_pocztowy = '18-200'; --1 rekord

--Chodzi o to że operacje na bazie danych wykonywane są w obrębie danej sesji
--dopiero po zatwierdzeniu zmian (COMMIT) zmiany są tak naprawdę wprowadzane
--dlatego liczby rekordów nie zgodzily się.
--Dopiero po zatwierdzeniu transakcji 2 (dodanie rekordu) liczba wybranych rekordów w obu selectach byla taka sama

--7. W dwóch sesjach doprowadzić do zakleszczenia (deadlock) transakcji.
--Czy baza sama umie poradzić sobie z taką sytuacją?

--PRZYKAD Z WYKLADU
--sesja 1
UPDATE EMP
SET sal = 2000
WHERE empno = 7934;

--sesja 2
UPDATE DEPT
SET loc = ‘BERLIN‘
WHERE dname = ‘OPERATION‘;

--sesja 1
UPDATE DEPT
SET loc = ‘LONDON‘
WHERE dname = ‘OPERATION‘;

--sesja 2
DELETE EMP
WHERE empno = 7934;


--WLASNY PRZYKLAD
--sesja 1
--transakcja 1
UPDATE TABELA1
SET POLE1 = 'ZAKLESZCZENIE'
WHERE ID = 5;

--sesja 2
--transakcja 2
UPDATE TABELA2
SET POLE1 = 'ZAKLESZCZENIE'
WHERE POLE2 = 'POLE2 2';

--sesja 1
--transakcja 3
UPDATE TABELA2
SET POLE1 = 'ZAKLESZCZENIE'
WHERE POLE2 = 'POLE2 2';

--sesja 2
--transakcja 4
DELETE TABELA1
WHERE ID = 5;

-- w pierwszej sesji:
--Error report -ORA-00060: deadlock detected while waiting for resource
-- doszlo do zakleszczenia, baza danych chyba nie rozwiąże tego problemu sama

--w drugiej sesji:
--transakcja oczekuje na wykonanie


--Kolejny przyklad
--Dwie transakje dokonują modyfikacji tych samych danych
--tylko w odwrotnej kolejności.

--transakcja 1
UPDATE EMP
SET sal = 2000
WHERE empno = 7934;

UPDATE EMP
SET sal = 4000
WHERE empno = 7933;

--transakcja 2
UPDATE EMP
SET sal = 4000
WHERE empno = 7933;

UPDATE EMP
SET sal = 2000
WHERE empno = 7934;

--WLASNY PRZYKLAD
--sesja 1
--transakcja 1
UPDATE TABELA1
SET POLE1 = 'ZAKLESZCZENIE2'
WHERE ID = 20;

--transakcja 2
UPDATE TABELA1
SET POLE1 = 'ZAKLESZCZENIE2'
WHERE ID = 21;

--sesja 1
--transakcja 3
UPDATE TABELA1
SET POLE1 = 'ZAKLESZCZENIE2'
WHERE ID = 21;

--transakcja 4
UPDATE TABELA1
SET POLE1 = 'ZAKLESZCZENIE2'
WHERE ID = 20;

-- 2 sesja ciągle oczekuje na wykonanie