SQL Server

Als erstes möchte ich das ganze bei SQL Server ausprobieren. Dazu starten wir eine Instanz in Docker:

version: "3.8"

services:
  sqldata:
    image: mcr.microsoft.com/mssql/server:2017-latest
    environment:
      - SA_PASSWORD=Pass@word
      - ACCEPT_EULA=Y
    ports:
      - "5434:1433"

Danach erstellen wir eine Tabelle:

CREATE TABLE master.dbo.Persons (
  PersonID int NOT NULL PRIMARY KEY IDENTITY,
  LastName varchar(255) NOT NULL  
);

Anschließend fügen wir einen Datenatz ein:

INSERT INTO master.dbo.Persons (LastName) OUTPUT INSERTED.PersonID VALUES('Mustermann');

Macht man nun in Transaktion 1:

BEGIN TRANSACTION;

UPDATE master.dbo.Persons SET LastName = 'Neu' WHERE PersonId = 1;

Und in Transaktion 2:

SELECT * FROM master.dbo.Persons

so, wird Transaktion 2 blockieren.

Eine ausführliche Erklärung findet man hier: https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

Kontrollieren kann man es mit:

SELECT 
    tl.request_session_id,
    wt.blocking_session_id,
    OBJECT_NAME(p.object_id) AS TableName,
    resource_type,
    request_mode,
    request_type
FROM 
    sys.dm_tran_locks tl
JOIN 
    sys.partitions p ON tl.resource_associated_entity_id = p.hobt_id
LEFT JOIN 
    sys.dm_os_waiting_tasks wt ON tl.lock_owner_address = wt.resource_address

Was folgendes ausgibt:

request_session_id|blocking_session_id|TableName|resource_type|request_mode|request_type|
------------------+-------------------+---------+-------------+------------+------------+
                53|                   |Persons  |PAGE         |IX          |LOCK        |
                55|                   |Persons  |PAGE         |IS          |LOCK        |
                53|                   |Persons  |KEY          |X           |LOCK        |
                55|                 53|Persons  |KEY          |S           |LOCK        |

Die Lock compatibility sieht man hier. D.h. Exklsuive (X) blockiert alles.

DBCC USEROPTIONS;

Set Option             |Value         |
-----------------------+--------------+
...
isolation level        |read committed|

Snapshot Isolation in SQL Server

Dies erlaubt es, dass ein SELECT nicht blockiert und den Wert bekommt, der zum Beginn der Transaktion gültig war. Gut erklärt wird es in SQL Server Quickie #30 - Snapshot Isolation.

Transaction 1:

BEGIN TRANSACTION;

UPDATE master.dbo.Persons SET LastName = 'Neu' WHERE PersonId = 1;

SELECT * FROM master.dbo.Persons

PersonID|LastName  |
--------+----------+
       1|Neu       |

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;

SELECT * FROM master.dbo.Persons

PersonID|LastName  |
--------+----------+
       1|Mustermann|

Der alte Wert in den Versionstore in tempdb gespeichert.

Würde man nun Transaktion 1 commiten, so bleibt der Wert in Transaktion 2 gleich - Readstability.

Was ist dann aber RCSI?

Auch hier ist das Ziel, dass der Reader nicht blockiert wird.

• Read Commited Snapshot Isolation (RCSI)

  • Ändert das default Isolation Level der ganzen Datenbank
  • Statement Level Consistency

• Snapshot

  • Transaction level consistency

RCSI wird wie folgt eingeschaltet:

ALTER DATABASE my_database SET READ_COMMITTED_SNAPSHOT ON

Der Unterschied ist also, dass man gleich wie beim Snapshot keine Dirty-Reads hat - aber: wenn die 1. Transaktion commited, dann sieht man den neuen Wert plötzlich in Transaktion 2 - also keine Readstability. Eine genau Erklärung gibt es hier.