[[oktatas:adatbázis-kezelés|< Adatbázis-kezelés]]

====== Tranzakció ======

  * **Szerző:** Sallai András
  * Copyright (c) Sallai András, 2014, 2021
  * Licenc: GNU Free Documentation License 1.3
  * Web: https://szit.hu
===== Fogalom =====

==== Inkonzisztens ====
Egymásnak ellentmondó adatok
===== Osztott erőforrások =====

Az erőforrásainkat szeretjük megosztani több felhasználó között, a gazdaságosabb, hatékonyabb működés érdekében.
Egy erőforrás használata konfliktushoz vezethet, ha egyszerre akarja használni két vagy több felhasználó.
Az adatbázisainkat általában egyszerre többen használjuk, az egyes adatbázis műveletek szintén konfliktusba kerülhetnek
egymással.


{{:oktatas:adatbázis-kezelés:osztot_eroforrasok.png|}}

===== ACID elvek =====

  * Atomicity - **atomicitás** - [ˌæ.təm.ˈɪ.sɪ.ti]
    * A műveletek egybe tartoznak. Vagy **mindent** végrehajtok **vagy semmit**.
  * Consistency - **konzisztencia** - kən.ˈsɪ.stən.si]
    * Konzisztens állapot - műveletek előtt naplózás.
    * Az adatok nincsenek ellentmondásban egymással.
  * Isolation - **izoláció** - [ˌaɪ.sə.ˈleɪʃ.n̩]
    * Egy művelet nem függ másoktól
  * Durability - **tartósság** - [ˌdjʊə.rə.ˈbɪ.lɪ.ti]
    * Az elvégzett műveletek eredménye legyen tartós.


===== A tranzakcióról =====

Egyben kezelt műveletsor, amelyre teljesülnek az ACID elvek.

Az adatok védelme érdekében a tranzakciókezelés során két feladat van:
  * a tranzakciók, korrekt párhuzamos végrehajtása
  * tevékenységek naplózása


===== Tranzakció példa =====

A tranzakciók tárgyalásánál gyakran használnak egy banki átutalást példaként:

  - Megnézzük létezik-e a célszámla
  - Megnézzük a forrásszámlán van-e elég pénz
  - Csökkentjük a forrásszámla egyenlegét
  - Növeljük a célszámla egyenlegét


===== Dolgozók =====

{{:oktatas:adatbazis-kezeles:dolgozok.png?200|}}


^ folyamat1  ^^
| beolvas Béla fizetése | 5000 |
| Béla fizetését megemeljük 2000-rel | 7000  |
| Kiírjuk a fizetést  | 7000 |

^ folyamat2  ^^
| beolvas Béla fizetése | 7000 |
| Béla fizetését megemeljük 2000-rel | 9000  |
| Kiírjuk a fizetést  | 9000 |

| f1 | beolvas Béla fizetése | 5000 |
| f1 | Béla fizetését megemeljük 2000-rel | 7000  |
| f2 | beolvas Béla fizetése | 5000 |
| f2 | Béla fizetését megemeljük 2000-rel | 7000  |
| f1 | kiír Béla fizetése | 7000 |
| f2 | kiír Béla fizetése | 7000 |

==== Megoldás ====

  * sorosítás
  * zárolás

==== Tranzakció ====

Az utasítások oszthatatlanul hajtódnak végre.

Jellemzők:
  * zárolást alkalmazunk
  * a külső tranzakció vár
  * véglegesítés


===== Tranzakció példa =====

<code mysql>
create database tran
character set utf8
collate utf8_hungarian_ci;


create table dolgozok (
    az int not null primary key auto_increment,
    nev varchar(20),
    fizetes double
);


insert into dolgozok
(nev, fizetes)
values
('Béla', 5000),
('Tibi', 5000);

</code>


==== Visszavont ====

<code mysql>
start transaction;
update dolgozok set fizetes = 16000;
update dolgozok set fizetes = fizetes + 10000 where id = 2;
select * from dolgozok;
rollback
select * from dolgozok;
</code>


==== Konkurálás ====

Két terminálban:

^  terminál1  ^  terminál2  ^
| select * from dolgozok | select * from dolgozok  |


^  terminál1  ^  terminál2  ^
| start transaction | start transaction  |



^  terminál1  ^  terminál2  ^
| update dolgozok \\ set fizetes=11000 \\ az = 2 | update dolgozok \\ set fizetes=12000 \\ az = 1   |
| select * from dolgozok  | select * from dolgozok  |

Mivel a két tranakció el van szigetelve egymástól, a terminál1-ben nem látszik még a terminál2 módosítása, és a terminál2-ben nem látszik még termnál1 módoítása.

^  terminál1  ^  terminál2  ^
| update dolgozok \\ set nev='Emese' \\ az=1 |   |

A terminál1 várakozik, mert az 1-s rekordot a terminál2 már módosította. 


^  terminál1  ^  terminál2  ^
|  |  commit  |

A terminál2 végzett. Nézzük meg baloldalon:
^  terminál1  ^  terminál2  ^
| select * from dolgozok  |   |


Nézzük meg a terminál2-ben:
^  terminál1  ^  terminál2  ^
|  | select * from dolgozok  |


A terminál2-ben még nem látható a terminál1 Emese névváltoztatása.

^  terminál1  ^  terminál2  ^
|  commit |   |
|  select * from dolgozok | select * from dolgozok  |

Most már az állapotok egyeznek.

==== Deadlock ====


^  terminál1  ^  terminál2  ^
| start transaction | start transaction  |


^  terminál1  ^  terminál2  ^
| update dolgozok \\ set nev="Irén" \\ where az=1  | update dolgozok \\ set nev="Kati" \\ where az=2  |


^  terminál1  ^  terminál2  ^
| update dolgozok \\ set fizetes=20000 \\ where az=2  |   |

A terminál1 várakozásra kényszerül. 

Terminál2-ben:
^  terminál1  ^  terminál2  ^
|  | update dolgozok \\ set fizetes=40000 \\ where az=1  |

Most keresztbe várakoznak. 










===== MySQL tranzakció =====

Példa:

Legyen a következő két tábla:

<code mysql>
CREATE TABLE IF NOT EXISTS `Dolgozok` (
`az` int(11) NOT NULL,
  `nev` varchar(28) DEFAULT NULL,
  `anyja_neve` varchar(23) DEFAULT NULL,
  `telepules` varchar(20) DEFAULT NULL,
  `lakcim` varchar(46) DEFAULT NULL,
  `fizetes` double DEFAULT NULL,
  `szuletes` date DEFAULT NULL,
  `jutalom` double DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
</code>

<code mysql>
CREATE TABLE IF NOT EXISTS `Naplo` (
`az` int(11) NOT NULL,
  `telepules` varchar(100) COLLATE utf8_hungarian_ci NOT NULL,
  `osszeg` double NOT NULL,
  `megjegyzes` text COLLATE utf8_hungarian_ci NOT NULL,
  `datum` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci;
</code>


Feltöltjük, a Dolgozok táblát, úgy hogy legyen miskolci dolgozó, majd a következő 
SQL utasítást adjuk ki:

<code mysql>
start transaction;

SELECT @A:=sum(fizetes) 
FROM `Dolgozok`
where telepules="Miskolc";

insert into Naplo (osszeg, telepules, datum)
values (@A, "Miskolc", "2014-09-23");

commit;
</code>


===== Izolációs szintek =====

==== Izolációs szintek a MySQL tranzakciókban ====


=== READ UNCOMMITED ===

Olyan adatokat is **olvashatunk**, amelyek **még nincsenek rögzítve**. Piszkos olvasás.
A tranzakció vége előtt már olvashatók a nem rögzített adatok.


=== READ COMMITED ===

**Olvasáskor** mindig a **már rögzített (commitelt) eredményt kapjuk**.
Ha fut egy tranzakció ami már változtatott az általunk kívánatos sorokon, 
akkor mi a régi eredményeket kapjuk. Ha sokan írják az adatbázist, itt
lassulás következhet be, más folyamatok arra **várnak**, hogy **az egész 
tábla frissítése befejeződjön.**

Előforulhat a **Phantom read** és **Non-repeatable reads** hiba.

A **Phantom read** jelentése a következő: Előfordulhat, hogy két lekérdezés lefut és más eredményt adnak.

A **Non-repeatable reads** azt jelenti, hogy egy tranzakció még nem fejeződött 
be, amikor egy másik már megváltoztatta az olvasott adatokat.

Alapértelmezett szint MS SQL Server esetén.

=== REPEATABLE READ ===

Alapértelmezett szintje az InnoDB tábláknak. 
**Csak rögzített rekordokat olvasunk.** Vagyis **nem várunk
a teljes tábla frissítésére**, ami már rögzített rekord az
olvasható is.

Előfordulhat a **Phantom read** hiba.



=== SERIALIZABLE ===

Más tranzakció nem frissíthet, nem nézhet a táblába, amíg véget nem ér
egy tranzakció. Olyan **mint a zárolt (LOCK) tábla**.
==== InnoDB és más rendszerek ====

Az InnoDB mind a négy szintet támogatja. De vigyázzunk
konvertáláskor, mert más adatbázis-kezelők esetleg nem 
támogatják mind a négy szintet.

Pl.:
  * PostgreSQL
    * alapértelmezett: REPEATABLE READ
    * támogatja: REPEATABLE READ and SERIALIZABLE
  * Oracle
    * alapértelmezett: READ COMMITTED
    * támogatott: READ COMMITTED, SERIALIZABLE és a nem szabványos READ ONLY)
  * MS SQL
    * READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT , SERIALIZABLE
==== Izolációs szint beállítása ====

A my.cnf állományban a [mysqld] szakaszban állíthatjuk:

<code ini>
[mysqld]
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}
</code>

Vagy egy SQL paranccsal is megadhatjuk amit szeretnénk:
<code mysql>
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
                       {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
</code>



A tranzakciós szint nem állítható tranzakción belül.
Ha nem használjuk a session vagy a global kulcsot, a beállítás egy select elvégzéséig él csak.

Lehetséges beállítások:
<code mysql>
set transaction isolation level read uncommitted;
set transaction isolation level read committed;
set transaction isolation level repeatable read;
set transaction isolation level serializable;
</code>

Ellenőrzés:
<code mysql>
select @@global.tx_isolation, @@tx_isolation
</code>

Különbségek a session, global és mindkettő elhagyása között:
  * mindkettő elhagyása:
    * Csak egyetlen select utasításig érvényes.
    * set transaction isolation level read uncommitted;
  * session
    * Csak adott belépő számára érvényes.
    * set session transaction isolation level read uncommitted;
  * global
    * Minden új belépőnek érvényes.
    * set global transaction isolation level read uncommitted;



===== Izolációs szint tesztelése =====
==== Teszttábla létrehozása ====

<code mysql>
create database tran
character set utf8
collate utf8_hungarian_ci;

create table dolgozok (
    az int not null primary key auto_increment,
    nev varchar(20),
    fizetes double
);

insert into dolgozok
(nev, fizetes)
values
('Béla', 5000),
('Tibi', 5000);
</code>


==== Read Uncommitted ====

Indítsunk egy terminálban egy kapcsolatot:
<code mysql>
start transaction;
update dolgozok set fizetes=2000;
</code>


Egy másik terminálban, vizsgáljuk meg milyen izolációs szint van beállítva a munkameneten:
<code mysql>
select @@tx_isolation;
</code>

Kérdezzük le a tran adatbázis dolgozók táblájának tartalmát:
<code mysql>
select * from dolgozok;
</code>


Állítsuk be a **read uncommitted** izolációs szintet a munkamenetre:

<code mysql>
set session transaction isolation level read uncommitted;
</code>

Most kérdezzük le újból:
<code mysql>
select * from dolgozok;
</code>

Az első lekérdezésnél, ha az izolációs szint **repeatable read** volt,
az update eredménye még nem látszik. A második lekérdezésnél, azonban 
meg kell jelenjen az első terminálon beállított új érték. A 
**read uncommitted** izolációs szint működik.