Transaktiokäsittely
sivu 1
Johdanto SQL:n
transaktiokäsittelyyn
HAAGA-HELIA / Martti Laiho
Transaktiokäsittely
sivu 2
SQL-transaktio
liiketransaktio
päätetransaktio
LUW
Begin work
tietokantakäsittely ..
...
Commit / Rollback
Tietokantaympäristö Tietokantaympäristö
yhdenmukaisessa yhdenmukaisessa
(consistent) tilassa LUW = Logical Unit of Work
(consistent) tilassa
siirtää tietokantaympäristön ehyestä,
yhdenmukaisesta tilasta toiseen
periaatteena
Vertaa
ACID-periaate!
- käsittelyn jakamattomuus (atomicity)
- sarjallistuvuus
- tietojen säilyvyys (durability)
Martti Laiho 1998
Transaktiokäsittely
sivu 3
Transaktiokäsittelyn tavoitteet ja keinot
- käsittelyn jakamattomuus
määritetään transaktiorajat:
Begin Work transaktion alku (implisiittinen tai eksplisiittinen)
Commit Work => “kaikki”, koko transaktio valmis, sitoudutaan
Rollback Work => “ei mitään”, kaikki käsittely perutaan
- sarjallistuvuus (serializability)
samanaikaiset transaktiot kirjautuvat tietokantaan
kuin jokainen olisi suoritettu yksinään jossakin järjetyksessä
- keinoja: lukitukset, aikaleimat/versiointi, optimistiset menetelmät
- eristyvyystaso (isolation level)
- tietojen säilyvyys
- keino: transaktioloki => toipuminen (rollback recovery)
varmistukset + transaktioloki => elvytys (rollforward r.)
Martti Laiho 1998
Transaktiokäsittely
sivu 4
Samanaikaisuuden ongelmia
ks Date
Tyypilliset ongelmatilanteet (anomaliat)
1 Hukatun päivityksen (lost update) ongelma
- päällekirjoitus
2 Keskeneräisen käsittelyn (uncommitted dependency) ongelmia
(Dirty Read)
3 "Ristiriitaisen analyysin" (inconsistent analysis) ongelmia
a) - alkuperäisen lukujoukon supistuminen (nonrepeatable read)
b) - uusien rivien ongelma (phantoms) (Milton, SQL-92 )
(mahdollisen lukujoukon kasvaminen)
Martti Laiho 1998
Transaktiokäsittely
sivu 5
1. Hukatun päivityksen ongelma
C. J. Date: Lost Update
Esim. pankkiautomaateilla
transaktio A transaktio B
tili x:
saldo 1000 mk
“Nostan 200 mk” “Nostan 500 mk”
lue tili x
lue tili x
saldo = saldo -200
saldo = saldo -500
kirjoita tili x
kirjoita tili x
aika
(Huom. Tämä ongelma on ratkaistu
ks notes)
Martti Laiho 1998
Transaktiokäsittely
sivu 6
2 Keskeneräisen luku (dirty read)
C.J. Date
transaktio A transaktio B
tili x:
saldo 1000 mk
“Katson saldon” “Nostan 500 mk”
lue tili x
saldo = saldo - 500
kirjoita tili x
lue tili x
ROLLBACK
aika
Martti Laiho 1998
Transaktiokäsittely
sivu 7
3a Toistamaton luku (nonrepeatable read)
Date
transaktio A transaktio B
tili 1: 1000 mk
tili 2: 2000 mk
tili 3: 5000 mk
“Tilien 1, 2 ja 3 “Siirrän 5000 mk
saldojen summa” tililtä 3 tilille 1”
lue tili 1
summa = saldo
lue tili 2
summa = summa+saldo lue tili 3
saldo = saldo -5000
- kirjoita tili 3
lue tili 1
- saldo = saldo +5000
kirjoita tili 1
COMMIT
lue tili 3
summa = summa+saldo
aika
Miten RR-taso tämän ratkaisee? Martti Laiho 1998
Transaktiokäsittely
sivu 8
3b Phantom (lukujoukon laajentuminen)
transaktio A tili 100: 1000 mk transaktio B
...
tili 400: 2000 mk
...
tili 500: 5000 mk
“Asiakkaan X tilien X: “Avaan tilin 200
saldojen summa” ja laitan tilille 1000”
lue tili 100
summa = saldo
lue tili 400
summa = summa+saldo alusta tili 200
saldo = 1000
- kirjoita tili 200
COMMIT
-
lue tili 500
summa = summa + saldo Vrt eri järjestelmät!
aika
Martti Laiho 1998
Transaktiokäsittely
sivu 9
Samanaikaisuuden hallinta
Transaktioiden samanaikaisuuden hallinta toteutetaan
järjestelmästä riippuen tyypillisesti käyttäen
• lukituksia (esim DB2, Allbase, SQL Server, ...),
• riviversioita ja aikaleimoja (esim Solid) tai
• lukituksia ja riviversioita (esim Oracle)
Lukitukset ratkaisee optimoija suoritussuunnitelman perusteella.
SQL-standardi ei ota kantaa samanaikaisuuden hallintamenetelmään.
Sovellusohjelma tilaa DBMS:ltä sopivan eristyvyystasopalvelun
ISOLATION LEVEL -asetuksella. Jos palvelutasoa ei voida toteuttaa
DBMS antaa tästä virheilmoituksen ja mahdollisesti katkaisee
transaktion suorituksen.
Martti Laiho 2007
Transaktiokäsittely
sivu 10
ACID SQL-transaction
[BEGIN TRANSACTION
if ...
]
SET TRANSACTION [READ ONLY | READ WRITE ]
ISOLATION LEVEL {READ UNCOMMITTED |
Isolation
READ COMMITTED |
REPEATABLE READ |
SERIALIZABLE }
Consistency SET {UNIQUE | REFERENCIAL} CONSTRAINTS
{DEFERRED | IMMEDIATE }
[ LOCK TABLE … ]
SELECT …
if ..
INSERT …
if ...
UPDATE … Database
Atomicity if ...
DELETE …
if … Transaction log
Durability
COMMIT | ROLLBACK
if ...
Martti Laiho 1998
Transaktiokäsittely
sivu 11
SQL-92: Isolation Levels
Phenomena: Dirty Read Nonrepeatable Phantoms
Isolation Read
Level:
READ UNCOMMITTED possible possible possible
READ COMMITTED NOT possible possible possible
REPEATABLE READ NOT possible NOT possible possible
SERIALIZABLE NOT possible NOT possible NOT possible
Martti Laiho 1998
Transaktiokäsittely
sivu 12
Lukitusprotokollan perusmalli
S- ja X-lukkojen yhteensopivuus
Lukituskohteen yksikkö
(granularity) voi olla
transaktiolla A lukko kohteeseen r
taulu
Shared eXclusive
Samanaikaisen sivu
transaktion B S OK Wait
rivi ...
lukitusyritys
kohteeseen r X Wait Wait
- S-lukko oikeuttaa lukemiseen
- X-lukko tarvitaan aina kirjoittamista varten
- Jos lukkoa ei saada, jäädään odottamaan
- Jos S-lukon saanut haluaa kirjoittaa,
on S-lukko nostettava X-lukoksi (lock promotion)
Martti Laiho 1998
Transaktiokäsittely
sivu 13
Isolation Levels
SERIALIZABLE (SQL-92) Toteutus lukulukkojen avulla
- taattu toistettavuus
lukemisen osalta, ei uusiakaan Automaattinen lukitus
- esim. S-lukko kaikkiin käsiteltäviin tauluihin
S- lock l. lukulukko
S
RR = Repeatable Read S->X X -lock l. kirjoituslukko
- 2PL protokollan mukainen S
- taattu toistettavuus
luettujen osalta
S-lukot transaktion loppuun asti
CS = Cursor Stability (SQL-92 ei tunne)
- nyky-yksikön taattu lukon pito
S-lukon vapautus siirryttäessä seuraavaan
RC = Read Committed
Lock escalation? - nyky-yksikön taattu S-lukittavuus
luvun alussa
RU = Read Uncommitted
- luku ilman S-lukkoa
Martti Laiho 1998
Transaktiokäsittely
sivu 14
.. Lukitukset
Järjestelmä lukitsee automaattisesti käsittelytavan ja kohteen perusteella:
Lukitusyksikkö (granularity)
- rivi / sivu / taulu / taulutila
Lukitustyypit
S = shared lock, “lukulukko”
X = exclusice lock, “kirjoituslukko”
laajennus “aielukot”:
SIX = share and intention exclusive
IS = intention share
IX = intention exclusive
Lukituksen kesto
- X-lukko pidetään aina transaktion loppuun
- muut riippuen eristyvyystasosta (isolation level)
- 2PL-teoria: 1. lukkojen hankintavaihe -> 2. vapautusvaihe (ei uusia!)
Martti Laiho 1998
Transaktiokäsittely
sivu 15
Lukituksen valinta
Hakutapa?
Index scan Table scan
Päivitys? Päivitys?
ei kyllä ei kyllä
IS-lukko IX-lukko S-lukko X-lukko
taululle ja taululle ja taululle 1) taululle
ko.sivuille ko.sivuille
S-lukko S-lukko luettaville Lähde:
luettaville riveille 1) IBM, Developerworks, Sanders
Riveille 1) X-lukko päivitettäville DB2 9 – Data concurrency
riveille
1) Riippuen isolation level -asetuksesta
Martti Laiho 2007
Transaktiokäsittely
sivu 16
Aielukot (Intent Locking)
Lukko virittää vastaavat aielukot
kaikille karkeammille Aielukko kertoo aikomuksesta
lukitusyksikkötasoille jatkaa virittäneen tason
• riviltä sivulle lukituksia jatkossakin.
• sivulta taululle
taulu
aielukko
sivu aielukot
rivi ... ...
rivilukko sivulukko
Martti Laiho 1998
Transaktiokäsittely
sivu 17
Lukkojen yhteensopivuus
Transaktion (B) lukkotarvetta tiettyyn kohteeseen (tietty rivi, sivu tai taulu)
verrataan samaan kohteeseen muille myönnettyihin lukkoihin:
transaktiolla A lukko kohteeseen r
IS IX S SIX X
Samanaikaisen IS Y Y Y Y
transaktion B
lukitusyritys
kohteeseen r IX Y Y
S Y Y
SIX Y
X
Joissakin järjestelmissä Y = lukitusyritys onnistuu
käytössä myös U-lock
"yhden paikan jono X-lukolle" Martti Laiho 1998
Transaktiokäsittely
sivu 18
Lukkiuma (deadlock)
Tilannetta missä lukko-odotukset menevät ristiin
kutsutaan lukkiumaksi (deadlock)
DBMS-järjestelmä tunnistaa tilanteen (deadlock detection)
ja purkaa sen
- uhrin (victim) valinta
- tälle automaattinen Rollback (ei Oracle)
- ilmoitus sarjallistuvuuskonfliktista sovellukselle
=> DBMS:n palveluja käyttävän sovellusohjelman
on varauduttava tähän !
Martti Laiho 1998
Transaktiokäsittely
sivu 19
Optimistinen samanaikaisuuden hallinta
Tästä on useita erilaisia “toteutuksia”
- versiointi
- myöhäinen lukitus, jne
1. Transaktion aikana kirjoitukset uusille versioille tai työkopioille
Transaktio tehdään loppuun
2. Commit-vaiheessa tarkastetaan tuliko konflikteja
(eri tapahtumien kirjoitusjoukoissa samoja rivejä)
(vrt Khoshafianin määrittely)
Jos konflikteja niin
hylätään työrivit ja aloitetaan transaktion alusta (restart)
muuten kirjoitukset vahvistetaan.
Martti Laiho 1998
Transaktiokäsittely
sivu 20
.. Optimistinen samanaikaisuuden hallinta
Kirjallisuudessa käytetään termiä myös tarkoittamaan aikaisemmassa
transaktiossa (T1) luetun tiedon päivitystä saman prosessin toisessa
transaktiossa (T2) siten että T1:n kannasta näkemä riviversio on ohjelmalla
tallessa ja ennen rivin päivitystä luetaan rivi uudelleen kannasta, verrataan
alkuperäiseen riviversioon ja jos nämä täsmäävät, niin tehdään päivitys,
muussa tapauksessa joku on ehtinyt päivittämään riviä ja transaktiolla T2
ei ole enää oikeutta tehdä päivitystä.
Tätä tekniikkaa käyttää esim Microsoftin ADO. Tätä tukee erityisesti
SQL Serverin tietotyyppi Rowversion, jota järjestelmä
päivittää automaattisesti kaikissa päivitystilanteissa.
Martti Laiho 2005
Transaktiokäsittely
sivu 21
Sovelluksen toimintasarja
sovellus
”kilpaileva”
sovellus
Hae asiakas
T1 Lukeva transaktio
Asiakastaulu lue asiakas
Tx
päivitä asiakas
Korjaa osoite
Talleta
Tx:n päivitys voi ”hukkua”
T.2 päivitävä transaktio
päivitä asiakas
Martti Laiho 2005
Transaktiokäsittely
sivu 22
Version verifioiva toimintasarja
sovellus
”kilpaileva”
sovellus
Käyttöliittymä:
Hae asiakas
T1
lue asiakas Lukeva transaktio
Asiakastaulu ja riviversio
Tx
päivitä asiakas Käyttöliittymä:
Korjaa osoite
Talleta
Päivittävä transaktio:
T.2 Lue riviversio
päivitä asiakas Jos sama versio kuin
T1:ssä, niin päivitä
Martti Laiho 1998
Transaktiokäsittely
sivu 23
Transaktiokäsittelyn ohjelmointi
käyttöliittymä
vuorov. sovellusohjelma
SQL-transaktion aikana
ei vuorovaikutteista käsittelyä!
tk-käsittelyn
kohta tr-ohjaus
vuorov. SQL- - atominen komentosarja
trans- - restartable, idempotent eli toistettava
aktio
execute
DBMS talletetut
säännöt ja
proseduurit
- komentojen atomisuus
DB
Martti Laiho 1998
Transaktiokäsittely
sivu 24
(Moni)Versiointi
aikaleima
transaktio A
transaktio B
päivitys kopioon
lue rivi
lue rivi käsittele
(vanha versio, päivitä rivi
jos uutta versiota (uusi versio)
ei ole committoitu
ennen transaktion …
alkuperäinen rivi
alkua) Commit;
Sarjallistuvuuskonflikti syntyy
jos kirjoitettavalla versiolla
on ennestään kilpaileva
versio tietokannassa!
Martti Laiho 1998
Transaktiokäsittely
sivu 25
Oraclen versiointi
Taulu Rollback segment
2. kopio talteen
1. Rivi lukkoon
3. Rivin päivitys ... Ennen Commit-
… komentoa
alkaneet
Commit muut transaktiot
näkevät
vain kopion
Martti Laiho 1998
Transaktiokäsittely
Fault-Tolerant Transaction Programming sivu 26
[reconnect?]
Restart?
Start Exceptions Errors Restart?
multiprogramming
[Set Transaction ...] YES
-limit, timeout
deadlock? [Begin Work]
timeout? if .. syntax error in
dynamic SQL
livelock?
.. Select ... not found ! reoptimized plan
if .. -objects/privileges
integrity !
Update - Uniqueness serializability YES
if .. - Referential - conflict
- Check - deadlock
- timeout
services of
- DBMS
Commit Rollback - buffers, etc
if .. if .. - OpSys
- data communication
- HW
Martti Laiho 1998-2007