Luku 6

Rinnakkaiset transaktiot

Lisämaustetta transaktioiden käsittelyyn tuo se, että tietokannalla voi olla useita käyttäjiä, joilla on meneillään samanaikaisia transaktioita. Missä määrin eri käyttäjien transaktiot tulisi eristää toisistaan?

Tämä on kysymys, johon ei ole yhtä oikeaa vastausta, vaan vastaus riippuu käyttötilanteesta ja myös tietokannan ominaisuuksista. Tavallaan paras ratkaisu olisi eristää transaktiot täydellisesti toisistaan, mutta toisaalta tämä voi haitata tietokannan käyttämistä.

Transaktiotasot

SQL-standardi määrittelee transaktioiden eristystasot seuraavasti:

Taso 1 (read uncommitted)

On sallittua, että transaktio pystyy näkemään toisen transaktion tekemän muutoksen, vaikka toista transaktiota ei ole viety loppuun.

Taso 2 (read committed)

Toisin kuin tasolla 1, transaktio saa nähdä toisen transaktion tekemän muutoksen vain, jos toinen transaktio on viety loppuun.

Taso 3 (repeatable read)

Tason 2 vaatimus ja lisäksi jos transaktion aikana luetaan saman rivin sisältö useita kertoja, joka kerralla saadaan sama sisältö.

Taso 4 (serializable)

Transaktiot ovat täysin eristettyjä ja komennot käyttäytyvät samoin kuin jos transaktiot olisi suoritettu peräkkäin yksi kerrallaan jossain järjestyksessä.

Esimerkki

Tarkastellaan tilannetta, jossa tuotteen 1 hinta on aluksi 8 ja kaksi käyttäjää suorittaa samaan aikaan komentoja transaktioiden sisällä (käyttäjän 1 komennot ovat vasemmalla ja käyttäjän 2 komennot ovat oikealla):

BEGIN TRANSACTION;
                                                BEGIN TRANSACTION
                                                UPDATE Tuotteet SET hinta=5 WHERE id=1;
SELECT hinta FROM Tuotteet WHERE id=1;
                                                UPDATE Tuotteet SET hinta=7 WHERE id=1;
                                                COMMIT;
SELECT hinta FROM Tuotteet WHERE id=1;
COMMIT;

Tasolla 1 käyttäjä 1 voi saada kyselyistä tulokset 5 ja 7, koska käyttäjän 2 tekemät muutokset voivat tulla näkyviin heti, vaikka käyttäjän 2 transaktioita ei ole viety loppuun.

Tasolla 2 käyttäjä 1 voi saada kyselyistä tulokset 8 ja 7, koska ensimmäisen kyselyn kohdalla toista transaktiota ei ole viety loppuun, kun taas toisen kyselyn kohdalla se on viety loppuun.

Tasoilla 3 ja 4 käyttäjä 1 saa kyselyistä tulokset 8 ja 8, koska tämä on tilanne ennen transaktion alkua eikä välissä loppuun viety transaktio saa muuttaa luettua rivin sisältöä.

Transaktiot käytännössä

Transaktioiden toteutustavat ja saatavilla olevat eristystasot riippuvat käytetystä tietokantajärjestelmästä. Esimerkiksi SQLitessä ainoa mahdollinen taso on 4, kun taas PostgreSQL toteuttaa tasot 2–4 ja oletuksena käytössä on taso 2.

Hyvää tietoa transaktioiden toiminnasta saa perehtymällä käytetyn tietokannan dokumentaatioon sekä testailemalla asioita itse käytännössä. Esimerkiksi voimme käynnistää itse kaksi SQLite-tulkkia, avata niillä saman tietokannan ja sen jälkeen kirjoittaa transaktioita sisältäviä komentoja ja tehdä havaintoja.

Seuraava keskustelu näyttää edellisen esimerkin tuloksen kahdessa rinnakkain käynnissä olevassa SQLite-tulkissa:

sqlite> BEGIN TRANSACTION;
                                                  sqlite> BEGIN TRANSACTION;
                                                  sqlite> UPDATE Tuotteet SET hinta=5 WHERE id=1;
sqlite> SELECT hinta FROM Tuotteet WHERE id=1;
8
                                                  sqlite> UPDATE Tuotteet SET hinta=7 WHERE id=1;
                                                  sqlite> COMMIT;
                                                  Error: database is locked
sqlite> SELECT hinta FROM Tuotteet WHERE id=1;
8
sqlite> COMMIT;

Tästä näkee, että ensimmäinen transaktio tosiaan saa kummastakin kyselystä tuloksen 8. Toista transaktiota ei sen sijaan saada vietyä loppuun, vaan tulee virheviesti database is locked, koska tietokanta on lukittuna samanaikaisen transaktion takia. Eristys toimii siis hyvin, mutta toista transaktiota pitäisi yrittää viedä loppuun uudestaan.

Vertailun vuoksi tässä on vastaava keskustelu PostgreSQL-tulkeissa (tasolla 2):

user=> BEGIN TRANSACTION;
                                                  user=> BEGIN TRANSACTION;
                                                  user=> UPDATE Tuotteet SET hinta=5 WHERE id=1;
user=> SELECT hinta FROM Tuotteet WHERE id=1;
8
                                                  user=> UPDATE Tuotteet SET hinta=7 WHERE id=1;
                                                  user=> COMMIT;
user=> SELECT hinta FROM Tuotteet WHERE id=1;
7
user=> COMMIT;

Nyt toisen transaktion muuttama arvo 7 ilmestyy ensimmäiseen transaktioon, mutta toisaalta molemmat transaktiot saadaan vietyä loppuun ongelmitta.