Tietokannat: Transaktiot

ACID

ACID (engl. Atomicity, Consistency, Isolation, Durability, suom. Atomisuus, eheys, eristyneisyys, pysyvyys) tarkoittaa tietokantajärjestelmien periaatetta, jonka avulla turvataan järjestelmän tietojen eheys kaikissa tilanteissa.

• Atomisuus tarkoittaa, että transaktio suoritetaan joko kokonaan tai ei lainkaan.
• Eheys takaa, että tietokanta transaktioiden myötä siirtyy yhdestä eheästä tilasta toiseen eheään tilaan.
• Eristyneisyys takaa, että transaktiot eivät vaikuta toisiinsa ja toimivat kuin yksin järjestelmässä.
• Pysyvyys määrää, että transaktion jälkeen muutokset eivät enää voi kadota järjestelmästä

Lähde: http://fi.wikipedia.org/wiki/ACID

Tapahtumien eli transaktioiden hallinta

• Tapahtuma on yhden tai useamman käskyn kokonaisuus, joka suoritetaan joko kokonaan tai ei ollenkaan
• BEGIN aloittaa tapahtuman (MySQL, monissa tuotteissa BEGIN  TRANSACTION)
• COMMIT hyväksyy tapahtuman
  
• ROLLBACK peruuttaa tapahtuman

• Esim. maalitilaston päivitys (joukkueID:t ja maalien määrä välitetään parametreina):

BEGIN;

INSERT INTO ottelu (joukkue1ID, joukkue2ID, kotimaalit,
                    vierasmaalit) VALUES (:j1id, :j2id, :kotimaalit, :vierasmaalit);

UPDATE joukkue
    SET maalisaldo = maalisaldo + :kotimaalit - :vierasmaalit
    WHERE joukkueID = :j1id;

UPDATE joukkue
    SET maalisaldo = maalisaldo + :vierasmaalit - :kotimaalit
    WHERE joukkueID = :j2id;

COMMIT;

Perinteinen tilisiirtoesimerkki

Tuija Tuhlaaja        >---(200 eur)--->  Kaija Kosmetologi
Tili: A                  Tilisiirto      Tili: B
Alkusaldo: 500eur                        Alkusaldo: 700eur

Aika	Tapahtumat	Tili A Saldo	Tili B Saldo
1	SET AUTOCOMMIT=1	500	700
2	Lue tili A	500	700
3	SaldoA= SaldoA-200	500	700
4	Kirjoita tili A	300	700
5	Lue tili B	300	700
6	VIRTAKATKO yms.	???	???
7	LOPPUTILANNE	300	700

Lopputulos:

• Tilillä A: 300eur
• Tilillä B: 700eur
• Tuija on menettänyt rahansa, mutta Kaija EI ole silti saanut rahojansa.

Transaktio sovelluksessa

• Transaktion alku on joko
• eksplisiittinen: komento begin transaction
• implisiittinen: ei komentoa, vaan
• ohjelman(osan) käynnistys tai
• edellisen transaktion päättyminen
• Transaktio päättyy joko
• vahvistukseen: komento commit
• peruutukseen: komentoon rollback
• tai DBMSn suorittamaan automaattiseen peruutukseen, jos ei vahvistusta tule

Autocommit usein oletusarvona

• Mm. ODBC, JDBC, Accessin Jet Engine ja MySQL käyttävät autocommit -tilamuuttujaa, jonka arvot ovat ON tai OFF. Oletusarvo on ON.
  
• Autocommit ei sovi kaikkiin sovelluksiin
  
• Yleensä on annettava aluksi komento set autocommit=off tai set autocommit=0

MySQL

mysql> SELECT @@autocommit;
  +--------------+
  | @@autocommit |
  +--------------+
  |            1 |
  +--------------+

mysql> SET AUTOCOMMIT=0;
mysql> SELECT @@autocommit;
  +--------------+
  | @@autocommit |
  +--------------+
  |            0 |
  +--------------+

mysql> SET AUTOCOMMIT=on;

Atomisuus

    * Transaktio suoritetaan aina jakamattomana eli atomisena (atomic)
    * KAIKKI TAI EI MITÄÄN!

SQL-92 Eristyvyystasot / samanaikaisuuden ongelmat

• Eristyvyystaso ilmaisee transaktion eristyvyyttä eli riippumattomuutta muista samanaikaisista transaktioista
• Tavoitteena ehkäistä samanaikaisuuden ongelmia.

Ongelmatilanteet:

1. Hukatun päivityksen ongelma (lost update): päällekirjoitus
2. Keskeneräisen käsittelyn ongelma (dirty read)
   a)    luetaan peruutettava
   b)    päivitetään peruutettava
3. Ristiriitaisen tulkinnan ongelma (inconsistent analysis)
   a)    eritahtinen päivitys (nonrepeatable read)
   b)    uusien rivien ongelma (phantom rows)

1) Hukatun päivityksen ongelma

• AINA ESTETTÄVÄ
  
• Henkilö T1 ja T2 käyttävät samaa tiliä
• Tilille pitäisi jäädä 100eur, mutta sinne jää 300eur
  

Aika	Transaktio T1 nostan 200eur	Tili A Saldo	Transaktio T2 nostan 300eur
1		600
2	Lue tili A	600
3		600	Lue tili A
4	Saldo = Saldo - 200	600
5		600	Saldo = Saldo - 300
6	Kirjoita tili A	400
7		300	Kirjoita tili A

2a) Keskeneräisen luku

• "DIRTY READ"
• Transaktiota ei vielä komittoitu
• T1 luki tilin saldon keskeneräisestä tilanteesta, joka ei jäänyt voimaan.
  

Aika	Transaktio T1 katson saldon	Tili A Saldo	Transaktio T2 nostan 300eur
1		600
2		600	Lue tili A
3		600	Saldo = Saldo - 300
4		300	Kirjoita tili A
5	Lue tili A	300
6		600	ROLLBACK

2b) Keskeneräisen päivitys

• AINA ESTETTÄVÄ
  
• T1 lukee tilin saldon keskeneräisestä tilanteesta, jossa transaktioita T2 ei ole vielä hyväksytty
  

Aika	Transaktio T1 nostan 200eur	Tili A Saldo	Transaktio T2 nostan 300eur
1		600
2		600	Lue tili A
3		600	Saldo = Saldo - 300
4		300	Kirjoita tili A
5	Lue tili A	300
6		600	ROLLBACK
7	Saldo = Saldo - 200	600
8	Kirjoita tili A	100

3a) Eritahtinen päivitys

• Nonrepeatable read
• Transaktio T1 luki tilin saldoksi 4300eur (600+1000+2700), vaikka se oli viimeisen tilin lukuhetkellä 4600eur.
  

Aika	Transaktio T1 Tilien A, B ja C summa	Tili A Saldo	Tili B Saldo	Tili C Saldo	Transaktio T2 siirrän 300eur tilitä C tilille A
1		600	1000	3000	BEGIN
2	Lue tili A summa = saldo	600	1000	3000
3	Lue tili B summa = summa + saldo	600	1000	3000
4		600	1000	3000	Lue tili C SaldoC = SaldoC - 300 Kirjoita tili C
5		600	1000	3000	Lue tili A SaldoA = SaldoA + 300 Kirjoita tili A
6		900	1000	2700	COMMIT
7	Lue tili C summa = summa + saldo	900	1000	2700

3b) Uusien rivien ongelma

• Phantom rows
  
• Transaktio T1 luki 4600eur, vaikka viimeisen tilin lukuhetkellä tilien yhteissumma oli 5100eur.
  

Aika	Transaktio T1 Tilien 1-N summa	Tili A Saldo	Tili B Saldo	Tili M Saldo	Tili N Saldo	Transaktio T2 avaan tilin B ja talletan 500eur
1		600		1000	3000
2	Lue tili A summa = saldo	600		1000	3000
3	Lue tili M summa = summa + saldo	600		1000	3000
4		600		1000	3000	Avaa tili B Saldo =  500 Kirjoita tili B
5		600	500	1000	3000	COMMIT
6	Lue tili N summa = summa + saldo	600	500	1000	3000
7		600	500	1000	3000

Eristyvyystasojen asettaminen

• Samanaikaisuuden ongelmien käsittelemiseksi tietokantatuotteissa voidaan asettaa haluttu eristyvyystaso
• Eristyvyystasot ovat

	Dirty Read (2A)	Nonrepeatable Read (3A)	Phantoms (3B)
READ UNCOMMITTED	mahdollinen	mahdollinen	mahdollinen
READ COMMITTED	Ei mahdollinen	mahdollinen	mahdollinen
REPEATABLE READ	Ei mahdollinen	Ei mahdollinen	mahdollinen
SERIALIZABLE	Ei mahdollinen	Ei mahdollinen	Ei mahdollinen

Asettaminen käytännössä: MySQL

• tx_isolation-tilamuuttujan avulla seuraavasti
  

mysql> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| REPEATABLE-READ|
+----------------+
1 row in set (0.00 sec)

mysql> SET tx_isolation = 'READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> SET tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> SET tx_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

mysql> SET tx_isolation = 'SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec)

Asettaminen käytännössä: Useat muut tuotteet

• SET TRANSACTION ISOLATION LEVEL -komento
  
• Esimerkki:  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  

Transaktio-esimerkkejä / MySQL

• http://www.databasejournal.com/features/mysql/article.php/3382171/Transactions-in-MySQL.htm

-- ###############################################
-- Esimerkki 1

CREATE TABLE t (f INT) TYPE=InnoDB;
BEGIN;
INSERT INTO t(f) VALUES (1);
SELECT * FROM t;
   [1]
ROLLBACK;
SELECT * FROM t;
Empty set (0.00 sec)

-- ###############################################
-- Esimerkki 2

Connection 1:
  CREATE TABLE t (f INT) TYPE=InnoDB;
  BEGIN;
  INSERT INTO t(f) VALUES (1);
  SELECT * FROM t;
    [1]

Connection 2:
  SELECT * FROM t;
  Empty set (0.00 sec)

Connection 1:
  COMMIT;
  Empty set (0.00 sec)

Connection 2:
  SELECT * FROM t;
    [1]

-- ###############################################
-- Esimerkki 3

Connection 1:
SELECT @@autocommit;
  +--------------+
  | @@autocommit |
  +--------------+
  |            1 |
  +--------------+

SET AUTOCOMMIT=0;
SELECT @@autocommit;
  +--------------+
  | @@autocommit |
  +--------------+
  |            0 |
  +--------------+

INSERT INTO t (f) VALUES (2);
  SELECT * FROM t;
     [1]
     [2]

Connection 2:
  SELECT * FROM t;
    [1]

Connection 1:
  COMMIT;
  SET AUTOCOMMIT=1;
  INSERT INTO t (f) VALUES (3);

Connection 2:
  SELECT * FROM t;
    [1]
    [2]
    [3]

Lukulukot päivityksessä

-- ###############################################
-- Esimerkki 4
-- Halutaan lisätä MAX-arvoa pykälän suurempi luku
-- kahdella eri yhteydellä:

Connection 1:
  BEGIN;
  SELECT MAX(f) FROM t;
    [3]
  INSERT INTO t(f) VALUES (4);

Connection 2:
  BEGIN;
  SELECT MAX(f) FROM t;
    [3]
  INSERT INTO t(f) VALUES(4);
  COMMIT;

Connection 1:
  COMMIT;
  SELECT * FROM t;
    [1]
    [2]
    [3]
    [4]
    [4]

-- Haluttiin luvut 4 ja 5, mutta on kaksi nelosta 

-- ###############################################
-- Esimerkki 5 (Uusi parempi yritys)
-- Halutaan lisätä MAX-arvoa pykälän suurempi luku
-- kahdella eri yhteydellä:

Connection 1:
  DELETE FROM t WHERE f=4;
  SELECT * FROM t;
    [1]
    [2]
    [3]
BEGIN;
SELECT MAX(f) FROM t FOR UPDATE;
INSERT INTO t(f) VALUES (4);

Connection 2:
  SELECT MAX(f) FROM t FOR UPDATE;
   --> Ei tapahdu mitään, koska C1 lukinnut päivitystä varten

Connection 1:
  COMMIT; 

Connection 2:
  +--------+
  | MAX(f) |
  +--------+
  |      4 |
  +--------+
INSERT INTO t(f) VALUES(5);
COMMIT;
  SELECT * FROM t;
    [1]
    [2]
    [3]
    [4]
    [5]

--> Nyt saatiin se lopputulos, mikä halutiin.

Repeatable Reads

-- ###############################################
-- Esimerkki 6
-- Repeatable Reads

Connection 1:
SELECT @@tx_isolation;
  +-----------------+
  | @@tx_isolation  |
  +-----------------+
  | REPEATABLE-READ |
  +-----------------+

BEGIN; 
SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|   55 |
+------+


Connection 2:
BEGIN;
INSERT INTO t VALUES(6);
COMMIT;
SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|   55 |
|    6 |
+------+

Connection 1:
SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|   55 |
+------+

COMMIT;
SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|   55 |
|    6 |
+------+

--> Kummankin yhteyden pitää COMMIT, että näkyy

Jos mysql-rootin oikeudet, voi kokeilla vielä:

• http://www.databasejournal.com/features/mysql/article.php/10897_3393161_2/MySQL-Transactions-Part-II---Transaction-Isolation-Levels.htm

Samanaikaisuuden hallinnan toteutustekniikoita (EI sisälly Tietokannat-opintojakson sisältöön!)

DBMSien tarjoamat toteutustavat:

• lukitus
• versiointi
  

Tekniikkana lukitus

• DBMS voi hallita samanaikaisuutta esim. lukitsemalla tietoja (nk. pessimistinen menetelmä).
• lukulukko on jaettu (S shared), muut saavat lukea,
• kirjoituslukko yksityinen (X exclusive), poissulkeva.
• Käytännössä vanhin ja yleisin tekniikka
• Tuotteiden toteutuksissa merkittäviä eroja mm. tehokkuudessa ja muistin käytössä
  

Tekniikkana versiointi

• Jokainen transaktio käsittelee omaa versiotaan (eli omaa kopiota) datasta.
• Vasta commit-hetkellä DBMS ilmoittaa mahdollisen konfliktin: että joku muu on ehtinyt muuttaa "originaali"dataa
• Nopein voittakoon, muut aloittakoot alusta!
• Kyky uudelleenaloitukseen konfliktin jälkeen on toteutettava ohjelman rakenteeseen.
• Versiointi on optimistinen menetelmä "eihän nyt tule konfliktia", lukinta pessimistinen "kumminkin nyt tulee konflikti"

---

Osa materiaalista on muokattu Ari Rantalan materiaalista.

---