SNAPSHOT

Transaktioner och felhantering i SQL Server

Miriam Antona

Software Engineer

SNAPSHOT

Varje ändring lagras i databasen tempDB
Ser bara incheckade ändringar som gjordes före SNAPSHOT-transaktionens start, samt egna ändringar
Kan inte se ändringar som andra transaktioner gjort efter transaktionens start
Läsningar blockerar inte skrivningar och skrivningar blockerar inte läsningar
Kan ha uppdateringskonflikter

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SNAPSHOT – jämförelse av isoleringsnivåer

	Smutsiga läsningar	Icke-repeterbara läsningar	Fantomläsningar
READ UNCOMMITTED	ja	ja	ja
READ COMMITTED	nej	ja	ja
REPEATABLE READ	nej	nej	ja
SERIALIZABLE	nej	nej	nej
SNAPSHOT	nej	nej	nej

SNAPSHOT – exempel

Transaction1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN
    SELECT * FROM accounts;

|account_id|account_number      |...| current_balance|
|----------|--------------------|---|----------------|
|1         |55555555551234567890|...| 25000,00       |
|2         |55555555559876543210|...| 200,00         |
|...       |...                 |...| ...            |
|15        |55555555551234567890|...| 25000,00       |

Transaction2

...

BEGIN TRAN
    INSERT INTO accounts
    VALUES (11111111111111111111, 1, 25000);

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

Den blockeras inte!

SNAPSHOT – exempel

Transaction1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN
    SELECT * FROM accounts;

    SELECT * FROM accounts;

|account_id|account_number      |...| current_balance|
|----------|--------------------|---|----------------|
|1         |55555555551234567890|...| 25000,00       |
|2         |55555555559876543210|...| 200,00         |
|...       |...                 |...| ...            |
|15        |55555555551234567890|...| 25000,00       |

Transaction2

...

BEGIN TRAN
    INSERT INTO accounts
    VALUES (11111111111111111111, 1, 25000);

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

Den blockeras inte!

SNAPSHOT – sammanfattning

Fördelar:

God datakonsistens: förhindrar smutsiga, icke-repeterbara och fantomläsningar utan blockering

Nackdelar:

tempDB ökar i storlek

När ska det användas?:

När datakonsistens krävs och blockeringar ska undvikas

READ COMMITTED SNAPSHOT

Ändrar beteendet för READ COMMITTED

ALTER DATABASE myDatabaseName SET READ_COMMITTED_SNAPSHOT {ON|OFF};

OFF som standard
För att använda ON:

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

Med ON kan varje READ COMMITTED-sats bara se incheckade ändringar som gjordes före satsens start
Kan inte ha uppdateringskonflikter

READ COMMITTED SNAPSHOT – exempel

Transaction1

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 BEGIN TRAN 
   UPDATE accounts
   SET current_balance = 30000
   WHERE account_id = 1;

COMMIT TRAN

Transaction2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

| current_balance |
|-----------------|
| 35000,00        |

READ COMMITTED SNAPSHOT – exempel

Transaction1

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 BEGIN TRAN 
   UPDATE accounts
   SET current_balance = 30000
   WHERE account_id = 1;

COMMIT TRAN

Transaction2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

| current_balance |
|-----------------|
| 30000,00        |

WITH (NOLOCK)

Används för att läsa oincheckad data
READ UNCOMMITTED gäller hela anslutningen / WITH (NOLOCK) gäller en specifik tabell
Används under valfri isoleringsnivå när du bara vill läsa oincheckad data från specifika tabeller

WITH (NOLOCK) – exempel

Ursprungligt saldo konto 5 = 35 000 $

Transaction1

BEGIN TRAN
  UPDATE accounts
  SET current_balance = 30000
  WHERE account_id = 5;

Transaction2

...

SELECT current_balance
FROM accounts WITH (NOLOCK) 
WHERE account_id = 5;

| current_balance |
|-----------------|
| 30000,00        |

Nu kör vi en övning!

Transaktioner och felhantering i SQL Server