English version of the materials are work in progress!

Expect bugs, typos, and other issues. The English version is expected to be completed during spring 2026.

Transaction Control (TxCL)

SQL's transaction control (Transaction Control Language, TxCL) contains few commands compared to other areas of SQL, but the theory behind commands is abundant and constantly evolving. Planning transaction control of an application program using relational database is especially important for a database programmer. On the other hand, the generalization of so-called NoSQL database paradigms and their transaction control differing from relational databases makes it especially useful also for a DBA (database administrator) and consultant to know.

Database system is a multitasking environment where even hundreds of thousands of operations can be executed per second. If all operations were executed sequentially, the DBMS would operate too slowly. For this reason, commands targeting the database must be grouped into transactions (transaction) and executed simultaneously, i.e., in parallel. A transaction means a set of (1..n) operations. An operation is an SQL statement or part of an SQL statement, for example a SELECT or UPDATE statement. Operations are divided on a general level into read and write operations. A read operation reads a record from database to main memory, and a write operation writes a variable value to database.

Transaction Control from SQL Perspective

Transactions are controlled according to SQL standard with three commands:

  • START [TRANSACTION] or BEGIN [TRANSACTION] starts a transaction.
  • COMMIT [WORK] confirms write operations to database and ends the transaction.
  • ROLLBACK [WORK] cancels all changes made by transaction operations and ends the transaction.

By default, only changes made by a committed (i.e. received COMMIT command) transaction to database are readable by other transactions.

SQL standard requires that transaction control can be used for all DML operations. Several popular relational database management systems however extend their transaction control to concern also DDL operations. A summary example of transaction control commands is presented in subchapter 4.6.2.2.

ACID

It is typical for relational database management systems that they are required to fulfill and that they fulfill four basic requirements:

  • Atomicity: transaction is atomic. Either all operations contained in the transaction succeed or they all fail. In other words, all operations of the transaction are executed successfully or action is taken as if the transaction had never been started.
  • Consistency: transaction changes the database from one consistent state to another consistent state. Database state is said to be consistent if all data contained in the database follows the internal business logic of the database.
  • Isolation: if multiple transactions are executed simultaneously, results of one transaction are distinguishable from results of another transaction. In other words, the database is finally in the same state regardless of whether transactions are executed in parallel or sequentially.
  • Durability: transaction results are permanent. If a transaction is executed successfully, results do not disappear.

Acronym ACID is used for these four basic requirements. Next, ACID properties are explained in more detail.

A: Atomicity

Operations targeting the database are grouped into transactions. One transaction consists of one or more operations. Success or failure of operations is measured from the perspective of transactions. If all operations of the transaction succeed, the transaction is executed successfully, i.e., committed (commit). If even one operation fails, the transaction is said to be aborted (aborted), and then all already executed operations of the transaction are cancelled (rollback). Transaction cancellation is made possible by transaction log maintained by the database management system. Operation failure can be caused for example by:

  • Interruption made by user.
  • Transaction detects that executing operation would violate database business logic.
  • Some error caused by operating system (e.g. I/O operation).

Atomicity means, as its name suggests, that a transaction is indivisible: either all of its operations are executed successfully or none of them. How operations are grouped into transactions is usually decided by database programmer or other application developer.

C: Consistency

Below is an example of a transaction that changes the database from one consistent state to another. It could be interpreted that in the transaction below there are four operations: SELECT (read), UPDATE (read and write) and SELECT (read). SQL keywords are presented in capital letters. The transaction follows a business rule of an imaginary bank, according to which there must be at least 0 euros of money on an account.

BEGIN TRANSACTION withdraw_money(account_no, amount);
    SELECT saldo 
    FROM tili 
    WHERE tilinro = :account_no;
    
    if (!account_no):
        print("Account not found.");
        ROLLBACK;
    endif;
    
    UPDATE tili
    SET saldo = saldo - :amount
    WHERE tilinro = :account_no;
    
    SELECT saldo
    FROM tili
    WHERE tilinro = :account_no;
    
    if (saldo < 0):
        print("Not enough money on account.");
        ROLLBACK;
    endif;
    
    print("Withdrawal succeeded.");
    
    COMMIT;

END TRANSACTION withdraw_money;

SQL statement BEGIN TRANSACTION starts the transaction. The first operation retrieves the balance of the account according to given parameter to memory. If account number is not found, the whole transaction is cancelled with command ROLLBACK.

The next SQL statement subtracts the value of amount parameter from balance read to memory. If balance is negative after write operation, the whole transaction is cancelled with command ROLLBACK. Otherwise, COMMIT command is executed, which confirms transaction results. After COMMIT command, results are also readable by other transactions.

It is worth noting that the purpose of the example above is to demonstrate especially transaction control commands. Checking the balance could also be implemented on database management system level with a trigger or in host language program code by comparing balance read from database and withdrawal amount.

I: Isolation

It is typical for databases that many users use the database simultaneously, in which case transactions are executed simultaneously, i.e., in parallel. Let's examine the following simple example of two transactions handling the same bank account:

  1. Transaction T1 reads bank account balance (100 euros) to memory.
  2. Transaction T2 reads bank account balance (100 euros) to memory.
  3. Transaction T1 increases balance in memory by 20 euros and writes new balance (120) to database.
  4. Transaction T2 increases balance in memory by 20 euros and writes new balance (120) to database.

When parallel transactions T1 and T2 have been successfully executed, there are 120 euros on the bank account. If transactions had been executed sequentially, there would be 140 euros on the bank account, and thus the parallel case described above does not fulfill the requirement of isolation. The problem described above is called lost update.

D: Durability

ACID durability means durability of data: when a transaction has been executed successfully to the end, data has been written to database. Activity violating durability could be for example reporting success of COMMIT operation although data to be written would in reality be only in internal cache of hard disk. In other words, changes made by transaction are preserved even if the system crashed immediately after success of transaction. Tolerance for serious hardware failures of storage devices is however not required.

# moniacid

Concurrency Control

Schedule (schedule) refers to the order in which operations of multiple parallel transactions are executed and there are several alternative schedules, some of which fulfill ACID isolation requirement and others do not. As mentioned earlier, executing transactions sequentially (so-called serial schedule) is too slow, and effectively all relational database management systems' scheduling is parallel i.e. non-serial. Schedule is called serializable if it is equivalent with some serial schedule.

Figure: Sets of transaction schedules. Parallelism does not guarantee serializability.
Figure: Sets of transaction schedules. Parallelism does not guarantee serializability.

Transaction control that fulfills ACID isolation (isolation) requirement is said to follow serializable schedule. Concurrency control (concurrency control) defines with which methods serializable schedule is achieved. Alternatives are for example timestamps given to transactions (timestamp ordering), timestamps according to commitment time used especially in distributed databases (commitment ordering) and locking (locking) discussed on this course. On a higher level one speaks of optimistic (aiming to prevent problem situations) and pessimistic (aiming to solve problem situations) concurrency control.

Problem situations caused by parallel transactions are solved in database management systems often with locking. Locking refers to restricting operations allowed for database records. Database management system keeps track of record locks in a so-called lock table. Locking is a low-level technique which database programmer or other user usually cannot directly influence. Let's examine next a general implementation way for locking in relational database management systems.

Lock Modes

There are different implementation ways for locking granularity:

In binary locking, a record is either locked (1) or unlocked (0). When a record is locked with binary locking, another transaction cannot use it in any way. A record can be intersection of table column and row, table row, table, table area or whole database depending on database management system implementation way.

In read/write locking, read locks (S i.e. shared lock) and write locks (X i.e. exclusive lock) are in use. When a record is read-locked, all transactions can read it and obtain a read lock to it, but cannot write to it nor obtain a write lock to it. When a record is write-locked, only one transaction can change it, and no other transaction can read it. In some cases so-called lock promotion can be allowed for a transaction, where read lock is changed to write lock.

Nowadays in relational database management systems more fine-grained lock types are in use. Depending on product, there are usually from five to seven different lock types including read and write locks. Different lock types do not however guarantee ACID isolation requirement, but in addition to them a locking protocol is needed, which is usually two-phase locking.

Two-Phase Locking

Let's examine next two-phase locking protocol (two-phase locking, 2PL) with read/write locking. According to two-phase locking protocol, a transaction is divided into two parts:

  1. In the first i.e. expanding phase transaction reserves locks it needs. For records from which is only read, a read lock is reserved. For records to which is written, a write lock is reserved. Locks are reserved just before executing operation targeting the record. Not a single lock is released. If lock promotion is in use, all promotions are done in this phase.
  2. In the latter i.e. shrinking phase locks are released. Lock is released immediately when lock is no longer needed for the record. Not a single new lock is reserved, nor are locks promoted.

There are on a general level four different implementation ways for two-phase locking implementation:

  • Protocol described above (so-called basic protocol).
  • Conservative protocol differs from basic protocol described above so that all locks needed by transaction are reserved in expanding phase before executing a single operation.
  • Strict (strict) protocol (S2PL) differs from basic protocol described above so that all write locks needed by transaction are released in shrinking phase only when transaction is over.
  • Strong strict (strong strict) protocol (SS2PL) differs from basic protocol described above so that all locks needed by transaction are released in shrinking phase only when transaction is over.
Figure: Two-phase locking protocols. Phases are illustrated with a vertical line.
Figure: Two-phase locking protocols. Phases are illustrated with a vertical line.

Deadlock Situations

As potential problems of two-phase locking can be considered so-called deadlock situation, where transactions wait for each other to release locks, and situation does not resolve over time. Let's examine an example situation where simultaneous transactions T1 and T2 targeting the database and following two-phase locking bring database management system to deadlock state:

T1 T2 Result
Write lock to record A. Record A write-locked.
Write lock to record B. Record B write-locked.
Write lock to record B. Waiting for T2 to release lock.
Write lock to record A. Waiting for T1 to release lock.

Although ACID isolation requirement can be considered more important than avoiding deadlock situations depending on target area, deadlock situations can also be handled. There are different approaches to deadlock situations:

  • Prevent deadlock situations with a protocol, i.e. give every transaction a priority based on timestamp:
    • Wait-die protocol: old transactions are allowed to wait for newer transactions to release locks. Transactions are cancelled (ROLLBACK) if they have to wait for transactions older than themselves.
    • Wound-wait protocol: new transactions are allowed to wait for older transactions to release locks. Transactions are cancelled if they have to wait for transactions newer than themselves.
  • Identify deadlock situations based on how long a transaction has had to wait, and cancel transaction after a determined wait time.
# moni2pl

Isolation Levels

SQL standard defines four isolation levels (isolation level). With isolation level one can influence speed and potential problems of parallel transactions: looser isolation level leads in theory to faster transactions, but increases amount of potential problems. Only the strongest isolation level is as its name suggests serializable. Isolation level can be defined per transaction or database server. In some products it is possible to define also table-specific isolation levels.

Following are listed isolation levels according to SQL standard from strongest to loosest:

  1. Serializable (SERIALIZABLE): Strongest isolation level, which results in slowest transactions. Transaction keeps all records it handles locked until transaction is over. Additionally value range handled by transaction is locked.
  2. Repeatable reads (REPEATABLE READ): Records handled by transaction are locked until transaction is over. Value range is not locked.
  3. Read committed (READ COMMITTED): Even if transaction T1 had read value of some record, another transaction T2 can modify this value before transaction T1 is over.
  4. Read uncommitted (READ UNCOMMITTED): Transaction operating on this isolation level can read even uncommitted changes made by other transactions. Uncommitted change means such operation after which transaction has not yet been committed (COMMIT) or cancelled (ROLLBACK).
Figure: Areas and implementations of concurrency control.
Figure: Areas and implementations of concurrency control.

Problem Situations

Let's examine next more closely identified problems i.e. anomalies (anomaly) which violate basic requirement of isolation.

Phantom Read

Similar read operations of same transaction return different set of rows. Problem is caused by some other, simultaneous transaction, which adds a row or rows to the table in the handled value range. Question mark (?) is notation for bound parameter (bound parameter) according to SQL standard. Value of bound parameter is obtained from application program during run time.

T1 T2
BEGIN;
SELECT tilinro
FROM tili
WHERE saldo > 1000000;
BEGIN;
INSERT INTO tili (tilinro, saldo)
VALUES (?, 2000000);
COMMIT;
SELECT tilinro
FROM tili
WHERE saldo > 1000000;

Now two read operations of transaction T1 return different set of rows, which violates basic principle of serializability. If the whole handled value range (saldo > 1000000) were locked for the duration of transaction T1, problem described above could not occur.

Non-repeatable Reads

Read operation executed during transaction T1, targeting one row, returns different row than similar read operation executed later by same transaction. Problem is caused by another, simultaneous transaction T2, which updates said row.

T1 T2
BEGIN;
SELECT saldo
FROM tili
WHERE tilinro = ?;
BEGIN;
UPDATE tili
SET saldo = saldo + 100
WHERE tilinro = ?;
COMMIT;
SELECT saldo
FROM tili
WHERE tilinro = ?;

Now two read operations of transaction T1 (assuming ? gets same value both times) return different row. If handled row (tilinro = ?) were kept locked for the whole duration of transaction T1, problem situation described above could not happen.

Dirty Read

Transaction T2 reads from table such row which operation of another transaction T1 has updated, but transaction T1, which includes write operation, has not yet been committed (commit). Problem in dirty read is the possibility that update is never executed successfully to the end, but it is cancelled (rollback).

T1 T2
BEGIN;
BEGIN;
UPDATE tili
SET saldo = saldo + 100
WHERE tilinro = ?;
SELECT saldo
FROM tili
WHERE tilinro = ?;
ROLLBACK;

Now transaction T1 updates account balance, but write operation is cancelled. Before cancelling write operation transaction T2 has had time to read the (temporarily) changed balance of the account. If reading uncommitted records were not allowed, problem situation described above could not happen.

Below is presented a table of potential problems occurring with standard isolation levels.

Phantom reads Non-repeatable reads Dirty reads
Serializable no no no
Repeatable read yes no no
Read committed yes yes no
Read uncommitted yes yes yes
# monirinn

Tapahtumanhallinta (TxCL)

SQL:n tapahtumanhallinta (Transaction Control Language, TxCL) sisältää muihin SQL:n osa-alueisiin verrattuna vähän komentoja, mutta teoria komentojen takana on runsasta ja jatkuvasti kehittyvää. Relaatiotietokantaa käyttävän sovellusohjelman tapahtumanhallinnan suunnittelu on erityisen tärkeää tietokantaohjelmoijalle. Toisaalta myös ns. NoSQL-tietokantaparadigmojen yleistyminen ja niihin liittyvä, relaatiotietokannoista poikkeava tapahtumanhallinta on erityisen hyödyllistä myös DBA:n (database administrator) ja konsultin tuntea.

Tietokantajärjestelmä on moniajoympäristö, jossa voidaan suorittaa jopa satoja tuhansia operaatioita sekunnissa. Jos kaikki operaatiot suoritettaisiin peräkkäin, DBMS toimisi liian hitaasti. Tästä syystä tietokantaan kohdistuvat käskyt on ryhmiteltävä tapahtumiksi (transaction) ja suoritettava samanaikaisesti eli rinnakkain. Tapahtumalla tarkoitetaan joukkoa (1..n) operaatioita. Operaatio on SQL-lause tai osa SQL-lauseesta, esimerkiksi SELECT- tai UPDATE-lause. Operaatiot jaetaan yleisellä tasolla luku- ja kirjoitusoperaatioihin. Lukuoperaatio lukee tietokannasta tietueen keskusmuistiin, ja kirjoitusoperaatio kirjoittaa muuttujan arvon tietokantaan.

Tapahtumanhallinta SQL:n näkökulmasta

Tapahtumia hallitaan SQL-standardin mukaisesti kolmella komennolla:

  • START [TRANSACTION] tai BEGIN [TRANSACTION] aloittaa tapahtuman.
  • COMMIT [WORK] vahvistaa kirjoitusoperaatiot tietokantaan ja lopettaa tapahtuman.
  • ROLLBACK [WORK] peruuttaa kaikki tapahtuman operaatioiden tekemät muutokset ja lopettaa tapahtuman.

Oletuksena vain vahvistetun (ts. COMMIT-käskyn saaneen) tapahtuman tekemät muutokset tietokantaan ovat muiden tapahtumien luettavissa.

SQL-standardi vaatii, että tapahtumanhallintaa voidaan käyttää kaikkiin DML-operaatioihin. Useat suositut relaatiotietokannanhallintajärjestelmät kuitenkin laajentavat tapahtumanhallintansa koskemaan myös DDL-operaatioita. Kokoava esimerkki tapahtumanhallinnan komennoista on esitetty alaluvussa 4.6.2.2.

ACID

Relaatiotietokannanhallintajärjestelmille on tyypillistä, että niiltä vaaditaan ja että ne täyttävät neljä perusvaatimusta:

  • Atomicity (jakamattomuus): tapahtuma on atominen. Joko kaikki tapahtuman sisältämät operaatiot onnistuvat tai ne kaikki epäonnistuvat. Toisin sanoen tapahtuman kaikki operaatiot suoritetaan onnistuneesti tai toimitaan ikään kuin tapahtumaa ei olisi koskaan aloitettukaan.
  • Consistency (oikeellisuus): tapahtuma muuttaa tietokannan yhdestä oikeellisesta tilasta toiseen oikeelliseen tilaan. Tietokannan tilan sanotaan olevan oikeellinen, jos kaikki tietokannan sisältämä data noudattaa tietokannan sisäistä liiketoimintalogiikkaa.
  • Isolation (eristyvyys): jos useita tapahtumia suoritetaan samanaikaisesti, yhden tapahtuman tulokset ovat erotettavissa toisen tapahtuman tuloksista. Toisin sanoen tietokanta on lopulta samassa tilassa riippumatta siitä, suoritetaanko tapahtumat rinnakkain vai peräkkäin.
  • Durability (pysyvyys): tapahtuman tulokset ovat pysyviä. Jos tapahtuma suoritetaan onnistuneesti, tulokset eivät katoa.

Näistä neljästä perusvaatimuksesta käytetään akronyymiä ACID. Seuraavaksi selitetään tarkemmin ACID-ominaisuuksia.

A: Jakamattomuus

Tietokantaan kohdistuvat operaatiot ryhmitellään tapahtumiksi. Yksi tapahtuma koostuu yhdestä tai useammasta operaatiosta. Operaatioiden onnistumista tai epäonnistumista mitataan tapahtumien näkökulmasta. Jos kaikki tapahtuman operaatiot onnistuvat, tapahtuma on suoritettu onnistuneesti eli vahvistettu (commit). Yhdenkin operaation epäonnistuessa tapahtuman sanotaan keskeytyneen (aborted), ja tällöin kaikki tapahtuman jo suoritetut operaatiot peruutetaan (rollback). Tapahtuman peruuttamisen mahdollistaa tietokannanhallintajärjestelmän ylläpitämä tapahtumaloki. Operaation epäonnistumisen voi aiheuttaa esimerkiksi:

  • Käyttäjän tekemä keskeytys.
  • Tapahtuma havaitsee, että operaation suorittaminen rikkoisi tietokannan liiketoimintalogiikkaa.
  • Jokin käyttöjärjestelmästä (esim. I/O-operaatio) johtuva virhe.

Jakamattomuudella tarkoitetaan nimensä mukaisesti sitä, että tapahtuma on jakamaton: sen sisältämistä operaatioista suoritetaan onnistuneesti kaikki tai ei yhtäkään. Sen, miten operaatiot ryhmitellään tapahtumiin, päättää tavallisesti tietokantaohjelmoija tai muu sovelluskehittäjä.

C: Oikeellisuus

Alla on esimerkki tapahtumasta, joka muuttaa tietokannan yhdestä oikeellisesta tilasta toiseen. Voitaisiin tulkita, että alla olevassa tapahtumassa on neljä operaatiota: SELECT (luku), UPDATE (luku ja kirjoitus) ja SELECT (luku). SQL-avainsanat on esitetty suuraakkosin. Tapahtuma noudattaa kuvitteellisen pankin liiketoimintasääntöä, jonka mukaan tilillä täytyy olla vähintään 0 euroa rahaa.

BEGIN TRANSACTION nosta_rahaa(tilinro, nostomaara);
    SELECT saldo 
    FROM tili 
    WHERE tilinro = :tilinro;
    
    if (!tilinro):
        print("Tiliä ei löytynyt.");
        ROLLBACK;
    endif;
    
    UPDATE tili
    SET saldo = saldo - :nostomaara
    WHERE tilinro = :tilinro;
    
    SELECT saldo
    FROM tili
    WHERE tilinro = :tilinro;
    
    if (saldo < 0):
        print("Tilillä ei ole tarpeeksi rahaa.");
        ROLLBACK;
    endif;
    
    print("Nosto onnistui.");
    
    COMMIT;

END TRANSACTION nosta_rahaa;

SQL-käsky BEGIN TRANSACTION aloittaa tapahtuman. Ensimmäinen operaatio noutaa annetun parametrin mukaisen tilin saldon muistiin. Jos tilinumeroa ei löydy, koko tapahtuma peruutetaan komennolla ROLLBACK.

Seuraava SQL-lause vähentää muistiin luetusta saldosta nostomaara-parametrin arvon. Jos saldo on kirjoitusoperaation jälkeen negatiivinen, koko tapahtuma peruutetaan komennolla ROLLBACK. Muussa tapauksessa suoritetaan COMMIT-käsky, joka vahvistaa tapahtuman tulokset. COMMIT-käskyn jälkeen tulokset ovat myös muiden tapahtumien luettavissa.

On syytä huomata, että yllä olevan esimerkin tarkoitus on demonstroida erityisesti tapahtumanhallinnan komentoja. Saldon tarkastaminen voitaisiin toteuttaa myös tietokannanhallintajärjestelmän tasolla triggerillä tai isäntäkielen ohjelmakoodissa vertaamalla tietokannasta luettua saldoa ja nostomäärää.

I: Eristyvyys

Tietokannoille on tyypillistä, että monet käyttäjät käyttävät tietokantaa samanaikaisesti, jolloin tapahtumia suoritetaan samanaikaisesti eli rinnakkain. Tarkastellaan seuraavaa yksinkertaista esimerkkiä kahdesta tapahtumasta, jotka käsittelevät samaa pankkitiliä:

  1. Tapahtuma T1 lukee pankkitilin saldon (100 euroa) muistiin.
  2. Tapahtuma T2 lukee pankkitilin saldon (100 euroa) muistiin.
  3. Tapahtuma T1 kasvattaa saldoa muistissa 20 eurolla ja kirjoittaa uuden saldon (120) tietokantaan.
  4. Tapahtuma T2 kasvattaa saldoa muistissa 20 eurolla ja kirjoittaa uuden saldon (120) tietokantaan.

Kun rinnakkaiset tapahtumat T1 ja T2 on onnistuneesti suoritettu, pankkitilillä on 120 euroa. Jos tapahtumat olisi suoritettu peräkkäin, pankkitilillä olisi 140 euroa, ja täten yllä kuvattu rinnakkainen tapaus ei täytä eristyvyyden vaatimusta. Yllä kuvattua ongelmaa kutsutaan menetetyksi päivitykseksi (lost update).

D: Pysyvyys

ACID:n pysyvyydellä tarkoitetaan datan pysyvyyttä: kun tapahtuma on suoritettu onnistuneesti loppuun, data on kirjoitettu tietokantaan. Pysyvyyttä rikkovaa toimintaa voisi olla esimerkiksi COMMIT-operaation onnistumisen ilmoittaminen, vaikka kirjoitettava data olisi todellisuudessa vasta kiintolevyn sisäisessä välimuistissa. Toisin sanoen tapahtuman tekemät muutokset säilyvät, vaikka järjestelmä kaatuisi välittömästi tapahtuman onnistumisen jälkeen. Vakavien, tallennuslaitteiden laitevirheiden sietokykyä ei kuitenkaan vaadita.

# moniacid

Rinnakkaisuudenhallinta

Ajoituksella (schedule) tarkoitetaan sitä järjestystä, jossa useamman rinnakkaisen tapahtuman operaatiot suoritetaan ja on olemassa useita vaihtoehtoisia ajoituksia, joista toiset täyttävät ACID:n eristyvyyden vaatimuksen ja toiset eivät. Kuten aiemmin mainittiin, tapahtumien suorittaminen peräkkäin (ns. sarjallinen (serial) ajoitus) on liian hidasta, ja käytännössä kaikkien relaatiotietokannanhallintajärjestelmien ajoitus on rinnakkaista eli ei-sarjallista (non-serial). Sarjallistuvaksi (serializable) ajoitukseksi kutsutaan sellaista rinnakkaista ajoitusta, joka on ekvivalentti eli yhtäpitävä jonkin sarjallisen ajoituksen kanssa.

Kuvio: Tapahtumien ajoitusten joukot. Rinnakkaisuus ei takaa sarjallistuvuutta.
Kuvio: Tapahtumien ajoitusten joukot. Rinnakkaisuus ei takaa sarjallistuvuutta.

Tapahtumanhallinta, joka täyttää ACID:n eristyvyyden (isolation) vaatimuksen, sanotaan noudattavan sarjallistuvaa ajoitusta. Rinnakkaisuudenhallinta (concurrency control) määrittää, millä menetelmillä sarjallistuva ajoitus saavutetaan. Vaihtoehtoja ovat esimerkiksi tapahtumille annettavat aikaleimat (timestamp ordering), erityisesti hajautetuissa tietokannoissa käytetyt vahvistusajan mukaiset aikaleimat (commitment ordering) ja tällä kurssilla käsiteltävä lukitus (locking). Korkeammalla tasolla puhutaan optimistisesta (pyritään ehkäisemään ongelmatilanteet) ja pessimistisestä (pyritään ratkaisemaan ongelmatilanteet) rinnakkaisuudenhallinnasta.

Rinnakkaisista tapahtumista aiheutuvat ongelmatilanteet ratkaistaan tietokannanhallintajärjestelmissä monesti lukituksella. Lukituksella tarkoitetaan tietokannan tietueille sallittujen operaatioiden rajoittamista. Tietokannanhallintajärjestelmä pitää kirjaa tietueiden lukoista ns. lukkotaulussa. Lukitus on matalan tason tekniikka, johon tietokantaohjelmoija tai muukaan käyttäjä ei tavallisesti pääse suoraan vaikuttamaan. Tarkastellaan seuraavaksi yleistä relaatiotietokannanhallintajärjestelmien toteutustapaa lukitukselle.

Lukitustavat

Lukituksen rakeisuudelle on erilaisia toteutustapoja:

Binäärilukituksessa tietue on joko lukittu (1) tai lukitsematon (0). Kun tietue on lukittu binäärilukituksella, toinen tapahtuma ei voi käyttää sitä millään tavalla. Tietue voi olla taulun sarakkeen ja rivin leikkauskohta, taulun rivi, taulu, taulualue tai koko tietokanta riippuen tietokannanhallintajärjestelmän toteutustavasta.

Luku/kirjoituslukituksessa käytössä on luku- (S eli shared lock) ja kirjoituslukkoja (X eli exclusive lock). Kun tietue on lukulukittu, kaikki tapahtumat voivat lukea sitä ja saada siihen lukulukon, mutta eivät voi kirjoittaa siihen eivätkä saada siihen kirjoituslukkoa. Kun tietue on kirjoituslukittu, vain yksi tapahtuma voi muuttaa sitä, eikä yksikään toinen tapahtuma voi lukea sitä. Joissakin tapauksissa tapahtumalle voidaan sallia ns. lukon korotus, jolloin lukulukko muutetaan kirjoituslukoksi.

Nykyään relaatiotietokannanhallintajärjestelmissä on käytössä hienorakenteisempia lukkotyyppejä. Tuotteesta riippuen erilaisia lukkotyyppejä on tavallisesti luku- ja kirjoituslukot mukaan laskettuna viidestä seitsemään. Erilaiset lukkotyypit eivät kuitenkaan takaa ACID-ominaisuuksien eristyvyysvaatimusta, vaan niiden lisäksi tarvitaan lukitusprotokolla, joka on tavallisesti kaksivaiheinen lukitus.

Kaksivaiheinen lukitus

Tarkastellaan seuraavaksi kaksivaiheista lukitusprotokollaa (two-phase locking, 2PL) luku/kirjoituslukituksella. Kaksivaiheisen lukitusprotokollan mukaan tapahtuma jaetaan kahteen osaan:

  1. Ensimmäisessä eli laajentamisvaiheessa tapahtuma varaa käyttöönsä tarvitsemansa lukot. Sellaisiin tietueisiin, joista ainoastaan luetaan, varataan lukulukko. Sellaisiin tietueisiin, joihin kirjoitetaan, varataan kirjoituslukko. Lukot varataan juuri ennen tietueeseen kohdistuvan operaation suorittamista. Yhtään lukkoa ei vapauteta. Jos käytössä on lukkojen korotus, kaikki korotukset tehdään tässä vaiheessa.
  2. Jälkimmäisessä eli supistamisvaiheessa lukot vapautetaan. Lukko vapautetaan heti, kun tietueeseen ei enää tarvita lukkoa. Yhtään uutta lukkoa ei varata, eikä lukkoja koroteta.

Kaksivaiheisen lukituksen toteutukselle on yleisellä tasolla neljä erilaista toteutustapaa:

  • Yllä kuvattu protokolla (ns. perusprotokolla).
  • Konservatiivinen protokolla eroaa yllä kuvatusta perusprotokollasta siten, että kaikki tapahtuman tarvitsemat lukot varataan laajentamisvaiheessa ennen yhdenkään operaation suorittamista.
  • Tiukka (strict) protokolla (S2PL) eroaa yllä kuvatusta perusprotokollasta siten, että kaikki tapahtuman tarvitsemat kirjoituslukot vapautetaan supistamisvaiheessa vasta, kun tapahtuma on ohi.
  • Vahva ja tiukka (strong strict) protokolla (SS2PL) eroaa yllä kuvatusta perusprotokollasta siten, että kaikki tapahtuman tarvitsemat lukot vapautetaan supistamisvaiheessa vasta, kun tapahtuma on ohi.
Kuvio: Kaksivaiheiset lukitusprotokollat. Vaiheet on havainnollistettu pystyviivalla.
Kuvio: Kaksivaiheiset lukitusprotokollat. Vaiheet on havainnollistettu pystyviivalla.

Deadlock-tilanteet

Kaksivaiheisen lukituksen potentiaalisina ongelmina voidaan pitää ns. deadlock-tilannetta, jossa tapahtumat odottavat toisiltaan lukkojen vapauttamista, eikä tilanne ratkea ajan myötä. Tarkastellaan esimerkkitilannetta, jossa tietokantaan kohdistuvat samanaikaiset, kaksivaiheista lukitusta noudattavat tapahtumat T1 ja T2 saattavat tietokannanhallintajärjestelmän deadlock-tilaan:

T1 T2 Lopputulos
Kirjoituslukitus tietueeseen A. Tietue A kirjoituslukittu.
Kirjoituslukitus tietueeseen B. Tietue B kirjoituslukittu.
Kirjoituslukitus tietueeseen B. Odotetaan, että T2 vapauttaa lukon.
Kirjoituslukitus tietueeseen A. Odotetaan, että T1 vapauttaa lukon.

Vaikka ACID-ominaisuuksien eristyvyysvaatimusta voidaan kohdealueesta riippuen pitää tärkeämpänä kuin deadlock-tilanteiden välttämistä, myös deadlock-tilanteita voidaan käsitellä. On olemassa erilaisia lähestymistapoja deadlock-tilanteisiin:

  • Estetään deadlock-tilanteet protokollalla, eli annetaan jokaiselle tapahtumalle prioriteetti aikaleiman perusteella:
    • Wait-die-protokolla: vanhojen tapahtumien annetaan odottaa uudempien tapahtumien vapauttavan lukkoja. Tapahtumat peruutetaan (ROLLBACK), jos ne joutuvat odottamaan itseään vanhempia tapahtumia.
    • Wound-wait-protokolla: uusien tapahtumien annetaan odottaa vanhempien tapahtumien vapauttavan lukkoja. Tapahtumat peruutetaan, jos ne joutuvat odottamaan itseään uudempia tapahtumia.
  • Tunnistetaan deadlock-tilanteet sen perusteella, kuinka kauan tapahtuma on joutunut odottamaan, ja peruutetaan tapahtuma määrätyn odotusajan jälkeen.
# moni2pl

Eristyvyystasot

SQL-standardi määrittää neljä eristyvyystasoa (isolation level). Eristyvyystasolla voidaan vaikuttaa rinnakkaisten tapahtumien nopeuteen ja potentiaalisiin ongelmiin: väljempi eristyvyystaso johtaa teoriassa nopeampiin tapahtumiin, mutta kasvattaa potentiaalisten ongelmien määrää. Vain vahvin eristyvyystaso on nimensä mukaisesti sarjallistuva. Eristyvyystaso voidaan määrittää tapahtuma- tai tietokantapalvelinkohtaisesti. Joissakin tuotteissa on mahdollista määrittää myös taulukohtaisia eristyvyystasoja.

Seuraavassa on lueteltu SQL-standardin mukaiset eristyvyystasot vahvimmasta väljimpään:

  1. Sarjallistuva (SERIALIZABLE): Vahvin eristyvyystaso, jonka seurauksena ovat hitaimmat tapahtumat. Tapahtuma pitää kaikki käsittelemänsä tietueet lukittuina, kunnes tapahtuma on ohi. Lisäksi lukitaan tapahtuman käsittelemä arvoväli.
  2. Toistettavat lukuoperaatiot (REPEATABLE READ): Tapahtuman käsittelemät tietueet ovat lukittuina, kunnes tapahtuma on ohi. Arvoväliä ei lukita.
  3. Vahvistettujen tietueiden luku (READ COMMITTED): Vaikka tapahtuma T1 olisi lukenut jonkin tietueen arvon, toinen tapahtuma T2 voi muokata tätä arvoa ennen kuin tapahtuma T1 on ohi.
  4. Vahvistamattomien tietueiden luku (READ UNCOMMITTED): Tällä eristyvyystasolla toimiva tapahtuma voi lukea muiden tapahtumien tekemät, vahvistamattomatkin muutokset. Vahvistamaton muutos tarkoittaa sellaista operaatiota, jonka jälkeen tapahtumaa ei ole vielä vahvistettu (COMMIT) tai peruutettu (ROLLBACK).
Kuvio: Rinnakkaisuudenhallinnan osa-alueita ja toteutuksia.
Kuvio: Rinnakkaisuudenhallinnan osa-alueita ja toteutuksia.

Ongelmatilanteet

Tarkastellaan seuraavaksi tarkemmin tunnistettuja ongelmia eli poikkeamia (anomaly), jotka rikkovat eristyvyyden perusvaatimusta.

Haamuluku (phantom read)

Saman tapahtuman samanlaiset lukuoperaatiot palauttavat erilaisen joukon rivejä. Ongelman saa aikaan jokin toinen, samanaikainen tapahtuma, joka lisää tauluun rivin tai rivejä käsiteltävälle arvovälille. Kysymysmerkki (?) on SQL-standardin mukainen merkintä sidotulle parametrille (bound parameter). Sidotun parametrin arvo saadaan sovellusohjelmalta ajon aikana.

T1 T2
BEGIN;
SELECT tilinro
FROM tili
WHERE saldo > 1000000;
BEGIN;
INSERT INTO tili (tilinro, saldo)
VALUES (?, 2000000);
COMMIT;
SELECT tilinro
FROM tili
WHERE saldo > 1000000;

Nyt tapahtuman T1 kaksi lukuoperaatiota palauttavat erilaisen joukon rivejä, mikä rikkoo sarjallistuvuuden perusperiaatetta. Mikäli koko käsiteltävä arvoväli (saldo > 1000000) lukittaisiin tapahtuman T1 ajaksi, yllä kuvattua ongelmaa ei voisi esiintyä.

Ei-toistettavat lukuoperaatiot (non-repeatable reads)

Tapahtuman T1 aikana suoritettu lukuoperaatio, joka kohdistuu yhteen riviin, palauttaa erilaisen rivin kuin saman tapahtuman myöhemmin suorittama, samanlainen lukuoperaatio. Ongelman saa aikaan toinen, samanaikainen tapahtuma T2, joka päivittää kyseistä riviä.

T1 T2
BEGIN;
SELECT saldo
FROM tili
WHERE tilinro = ?;
BEGIN;
UPDATE tili
SET saldo = saldo + 100
WHERE tilinro = ?;
COMMIT;
SELECT saldo
FROM tili
WHERE tilinro = ?;

Nyt tapahtuman T1 kaksi lukuoperaatiota (olettaen, että ? saa saman arvon molemmilla kerroilla) palauttavat erilaisen rivin. Mikäli käsiteltävä rivi (tilinro = ?) pidettäisiin lukittuna koko tapahtuman T1 ajan, yllä kuvattua ongelmatilannetta ei voisi tapahtua.

Likainen lukuoperaatio (dirty read)

Tapahtuma T2 lukee taulusta sellaisen rivin, jota toisen tapahtuman T1 operaatio on päivittänyt, mutta tapahtumaa T1, johon kirjoitusoperaatio sisältyy, ei ole vielä vahvistettu (commit). Ongelma likaisessa luvussa on se mahdollisuus, että päivitystä ei koskaan suoriteta onnistuneesti loppuun, vaan se peruutetaan (rollback).

T1 T2
BEGIN;
BEGIN;
UPDATE tili
SET saldo = saldo + 100
WHERE tilinro = ?;
SELECT saldo
FROM tili
WHERE tilinro = ?;
ROLLBACK;

Nyt tapahtuma T1 päivittää tilin saldoa, mutta kirjoitusoperaatio peruutetaan. Ennen kirjoitusoperaation peruuttamista tapahtuma T2 on ehtinyt lukea tilin (väliaikaisesti) muuttuneen saldon. Jos vahvistamattomien tietueiden lukua ei sallittaisi, yllä kuvattua ongelmatilannetta ei voisi tapahtua.

Alla on esitetty taulukko standardin mukaisilla eristyvyystasoilla esiintyvistä potentiaalisista ongelmista.

Haamuluvut Ei-toistettavat luvut Likaiset luvut
Sarjallistuva ei ei ei
Toistettavat lukuoperaatiot kyllä ei ei
Vahvistettujen tietueiden luku kyllä kyllä ei
Vahvistamattomien tietueiden luku kyllä kyllä kyllä
# monirinn

These are the current permissions for this document; please modify if needed. You can always modify these permissions from the manage page.