Commits, rollbacks och isolation levels

Transactions

Allt vi har gjort hittills har förutsatt att man kör ett kommando i taget, eller det bara är en person som interagerar med databasen. I praktiken är detta sällan fallet, ibland behöver flera kommandon följa varandra i en viss ordning, och ofta används en databas av flera personer samtidigt. Detta medför logistiska problem som kan ha förödande konsekvenser om exempelvis inte alla kommandon kan genomföras, eller någon annan ändrar på datan mellan våra kommandon.

Ta exemplet av en bank som använder en databas med tabellen accounts(account_id, money) som representerar alla deras användares bankkonto och hur mycket pengar de har på det. Nu vill en morförälder skicka sitt barnbarn 1 000 kronor. Det skulle kanske se ut så här:

UPDATE accounts
SET money = money - 1000     -- Ta bort 1 000 :-
WHERE account_id = 24423;     -- Från morföräldrens konto.

UPDATE accounts
SET money = money + 1000     -- Lägg till 1 000 :-
WHERE account_id = 43030;     -- Till barnbarnets konto.

Säg att något händer mellan första kommandot och andra (datorn dog, kortet spärras, etc). Då kommer första kommandot att genomföras medan andra misslyckas, vilket betyder att morföräldern just förlorade 1 000 kronor. Inte bra.

Lösning på problemet är att använda sig av transactions. En transaction är en samling av kommandon där antingen alla kommandon genomförs eller inga. För att skapa en transaction använder vi oss av nyckelorden START TRANSACTION följt av en serie kommandon som avslutar med antingen COMMIT eller ROLLBACK.

Commit

COMMIT får vår transaction att genomföras och alla våra ändringar att sparas.

Rollback

ROLLBACK gör att vår databas går tillbaka till det stadiet som det var innan vår transaction påbörjade. Med andra ord, den gör alla våra ändringar ogjorda. Vi kan kontrollera att detta stämmer genom att köra dessa kommandon en efter en.

START TRANSACTION;

-- Visar alla följare användaren med ID 1.
SELECT *
FROM   followers
WHERE  user_id = 1;

-- Tar bort alla användarens följare.
DELETE FROM followers
WHERE user_id = 1;

-- Visar att de verkligen är borta.
SELECT *
FROM   followers
WHERE  user_id = 1;

-- Fast nää, vi ångra oss.
-- Gå tillbaka så som det var innan vi startade vår transaction.
ROLLBACK;

-- De är kvar.
SELECT *
FROM   followers
WHERE  user_id = 1;

I applikationen

COMMIT och ROLLBACK hanteras enkelt i vår applikation med hjälp av våran connection-instans. I Python använder vi rollback ifall vår query inte kan genomföras p.g.a. ett felmeddelande, annars gör vi en commit.

try:
    self.cursor.execute(query, arguments)
except Error as error:
    print(error.pgerror)
    self.connection.rollback()
else:
    self.connection.commit()

Transactions metoder

Det finns två olika typer av transactions: READ ONLY och READ WRITE.

Ifall vi inte specificerar vilken av dessa två vi vill använda så kommer Postgres att anta att transaction är READ WRITE. Det betyder att vår query kommer att hämta data från databasen och eventuellt uppdatera eller sätta in data. Vad det har för betydelse är att om någon annan gör en query (eller ditt program gör flera queries) samtidigt på samma tabell så måste deras query vänta på att våran query är klar, eftersom vår query kanske ändrar på data de behöver.

READ ONLY säger till SQL att vi bara tänker hämta data från databasen, d.v.s. att vi inte tänker uppdatera eller sätta in värden. Detta är bra för att då vet Postgres att om vår query och någon annans query (som också bara ska hämta data) båda använder sig av samma tabell så kan vi köra dem parallellt, eftersom de inte påverkar varandra.

Vi sätter en transaction som READ ONLY på följande vis:

START TRANSACTION READ ONLY;

Isolation level

Vi kan specificera ännu mer beteenden hos våran transaction, i form av ISOLATION LEVEL. Det finns 4 typer.

READ UNCOMMITTED är den minst stränga begränsningen vi kan sätta. Det den säger är att om vi ska hämta data som någon annan query har eller eventuellt kommer ändra, så hämta datan som den är. Vi väntar alltså inte på att den andra queryn ska bli klar.

READ COMMITTED säger att om vi ska hämta data så måste det vara data som är COMMITTED ("sparad"), d.v.s. att det inte finns några nuvarande query som skriver eller eventuellt kommer skriva till datan.

REPEATABLE READ säger att om vi hämtar data flera gånger i vår transaction, så kommer den datan att vara detsamma alla gånger. För READ COMMITTED kan det exempelvis hända att vi hämtar data, någon annan query uppdaterar datan och committar, och sedan hämtar vi den nya datan utan att veta att den har ändrats.

START TRANSACTION READ ONLY ISOLATION LEVEL REPEATABLE READ;

SELECT user_id FROM users;

-- Do other stuff.

SELECT user_id FROM users;  -- All read data from previous query will still be available here.

COMMIT;

SERIALIZABLE är den strängaste begränsningen (och vad som används om ingen annan isolation level explicit nämns). Denna säger att ingen ny data kan tillkomma under våran transaction. I READ REPEATABLE försäkrar vi oss om att ingen data har tagits bort eller ändrats, men data kan fortfarande läggas till.