Databasteknik: Teoriövning 8, prestanda, transaktioner

Välj själv om du vill göra övningen ensam eller i någon typ av grupp. Det gör inget om en del av frågorna skulle vara svåra att svara på, för de är kanske avsedda att inleda en diskussion snarare än att ha ett enda rätt svar. Det gör inte heller något om man inte hinner med alla frågorna på övningstillfället.

Förberedelser

Innan denna övning bör du ha tittat på föreläsning nummer 9, på YouTube (uppdelad i två Youtube-videor). Del 1 handlar om index och prestanda i databasen, och del 2 handlar om transaktioner i databaser. Man kan också läsa i kursboken eller motsvarande.

Scenario till uppgifterna

Vi använder samma exempeldatabas med onda robotar som i teoriövning 7, som handlade om mer avancerad SQL. Här är tabellerna:

Robotar
Nummer	Färg	Vikt
1	Blå	40
2	Blå	40
3	Svart	1000
4	Stålgrå	800

Deltagare
Robot	Strid	Resultat
1	1	Oavgjort
2	1	Oavgjort
2	2	Vinst
3	2	Förlust
1	3	Oavgjort
2	3	Oavgjort
3	3	Oavgjort

Strider
Nummer	Datum
1	2022-11-23
2	2022-11-23
3	2022-11-24

Uppgift 1

Det visar sig att den här frågan körs väldigt ofta, men kanske med andra färger än just blå:

SELECT Nummer FROM Robotar WHERE Färg = 'Blå';

Bör vi skapa index, och i så fall vilka, om:

a) Tabellen innehåller något tiotal rader.
b) Tabellen innehåller många miljoner rader.
c) Tabellen innehåller många miljoner rader, och alla robotarna är antingen blå, svarta eller stålgrå.

Uppgift 2

Det visar sig att den här frågan körs väldigt ofta, men kanske med andra robotnummer än just 1234:

SELECT Färg FROM Robotar WHERE Nummer = 1234;

Nummer är primärnyckel, och vi skrev PRIMARY KEY på den kolumnen när vi skapade tabellen.

Bör vi skapa index? Diskutera!

Uppgift 3

Vi söker ofta efter vikterna på de robotar av en viss färg som deltagit i en strid ett visst datum:

SELECT DISTINCT Robotar.Nummer AS Robot, Robotar.Vikt
FROM Robotar, Deltagare, Strider
WHERE Robotar.Nummer = Deltagare.Robot
AND Deltagare.Strid = Strider.Nummer
AND Robotar.Färg = 'Blå'
AND Strider.Datum = DATE '2022-11-23';

Alla tabellerna innehåller många miljoner rader. Vilka index bör man skapa för att frågan ska gå snabbt att köra?

Uppgift 4

Vi söker fortfarande ofta efter vikterna på de robotar av en viss färg som deltagit i en strid ett visst datum. Samma sökning går ofta att skriva på flera sätt i SQL, antingen med implicita joinar som i uppgiften ovan, eller med explicita joinar, eller som en nästlad fråga. Det provar vi nu:

SELECT DISTINCT Robotar.Nummer AS Robot, Robotar.Vikt
FROM Robotar JOIN Deltagare ON Robotar.Nummer = Deltagare.Robot
JOIN Strider ON Deltagare.Strid = Strider.Nummer
WHERE Robotar.Färg = 'Blå'
AND Strider.Datum = DATE '2022-11-23';

SELECT Nummer AS Robot, Vikt
FROM Robotar
WHERE Färg = 'Blå'
AND Nummer IN (SELECT Robot
               FROM Deltagare
               WHERE Strid IN (SELECT Nummer
                               FROM Strider
                               WHERE Datum = DATE '2022-11-23'));

Vilka index bör man skapa för att dessa två frågor ska gå snabbt att köra?
(Ledtråd: Svaret är enkelt.)

Uppgift 5

Vi vill veta robotarnas sammanlagda vikt, och ställer en SQL-fråga:

SELECT SUM(Vikt) FROM Robotar;

Kan vi skapa index för att frågan ska gå snabbare att köra?

Uppgift 6

I databassammanhang menar med en transaktion en följd av operationer i databasen som hör ihop som en enhet. Om man till exempel flyttar pengar mellan två bankkonton, måste man först dra bort pengarna från det ena kontot, och sen lägga till dem till det andra. En sådan transaktion får inte avbrytas mitt i, och den får inte krocka med andra ändringar i databasen. Men kan man ha transaktioner som bara läser i databasen, och inte gör några ändringar?

Uppgift 7

Man brukar tala om "ACID-transaktioner", där bokstäverna "ACID" står för olika egenskaper som transaktioner bör ha:

A står för "atomicity" ("atomicitet" på svenska)
C för "consistency preserving" ("konsistensbevarande")
I för "isolation" ("isolering")
D för "durability" ("hållbarhet")

Ge för var och en av dessa egenskaper ett exempel på något problem som kan uppstå just i robotdatabasen med en databashanterare som inte har den egenskapen!

Uppgift 8

I Mimer skriver man START TRANSACTION för att påbörja en transaktion och i Microsoft SQL Server skriver man BEGIN TRANSACTION. Varför är det olika? Vilket sätt är rätt?

Uppgift 9

Vad är skillnaden mellan COMMIT och ROLLBACK?

Uppgift 10

Många databashanterare använder lås för att hindra "krockar" mellan samtidiga transaktioner. Då kan man få deadlock. Vad innebär det?

De flesta databashanterare kommer att upptäcka deadlock och hantera det på något sätt, till exempel genom att avbryta en av de inblandade transaktionerna. Betyder det att jag som programmerare, som bara använder databashanteraren för att lagra data och söka i dem, inte behöver bry mig om att det kan uppstå deadlock?

Uppgift 11

Lås kallas ibland för en "pessimistisk" metod, eftersom den redan från början räknar med att det kan bli krockar mellan samtidiga transaktioner, och låser data för att förhindra krockarna.

Vad skulle en optimistisk metod innebära?

Uppgift 12

Vi använder en databashanterare med auto-commit, vilket betyder att varje SQL-kommando räknas som en egen transaktion, så länge man inte uttryckligen startar en transaktion med ett särskilt kommando.

Vi startar två olika klientprogram som loggar in på samma databas, och ger följande SQL-kommandon, i den angivna ordningen, i de två klienterna.

Vad blir resultatet av var och en av de tio select-frågorna?

Klient 1	Klient 2
create table Bananer (nummer integer not null primary key, färg varchar(10));
	insert into Bananer values (1, 'gul');
insert into Bananer values (2, 'grön');
select * from Bananer; -- Fråga 1
	select * from Bananer; -- Fråga 2
	start transaction;
start transaction;
	delete from Bananer where färg = 'gul';
	select * from Bananer; -- Fråga 3
	rollback;
	select * from Bananer; -- Fråga 4
select * from Bananer; -- Fråga 5
insert into Bananer values (3, 'brun');
select * from Bananer; -- Fråga 6
commit;
select * from Bananer; -- Fråga 7
	select * from Bananer; -- Fråga 8
start transaction;
	start transaction;
insert into Bananer values (4, 'grön');
	insert into Bananer values (4, 'brun');
select * from Bananer; -- Fråga 9
	select * from Bananer; -- Fråga 10

Förslag på lösningar

Det finns lösningsförslag till en del av uppgifterna, men försök lösa dem själv först.

Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 24 november 2022