SQL Server und RCSI

January 16, 2024

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.

Erste Probleme

Blockieren ist straight forward - aber natürlich schlecht für die Performance. Folgendes Beispiel zeigt aber ein neues Problem:

ALTER DATABASE Test SET ALLOW_SNAPSHOT_ISOLATION ON  
GO

USE Test
GO

DROP TABLE IF EXISTS Test
GO

CREATE TABLE Test 
(
  Id int NOT NULL identity(1,1) PRIMARY KEY,
  BusinessDate date NOT NULL,	
  BusinessDateYear AS YEAR(BusinessDate) PERSISTED,
  BusinessKeyId int,
  BusinessKey AS CONCAT(YEAR(BusinessDate), '-', BusinessKeyId) PERSISTED	 
)
GO

CREATE OR ALTER TRIGGER TR_Test_GenerateBusinessKey
ON Test
AFTER INSERT
AS
WITH records AS
(
  SELECT
    COALESCE(BusinessKeyId,
      COUNT(CASE WHEN BusinessKeyId IS NULL THEN 1 END) OVER (PARTITION BY BusinessDateYear ORDER BY Id)
      + COALESCE(MAX(BusinessKeyId) OVER (PARTITION BY BusinessDateYear), 0)) AS GeneratedBusinessKeyId,
    Id,
    BusinessKeyId
  FROM Test 
)
UPDATE records
  SET records.BusinessKeyId = records.GeneratedBusinessKeyId
  FROM records INNER JOIN inserted 
  ON records.Id = inserted.Id;
GO

Ziel war es, dass pro Jahr ein Identifier gemacht wird - beginnend immer bei 1. Also 2023-1 bis 2023-n und dann wieder 2024-1 bis 2024-m.

Transaktion 1:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
  INSERT INTO test (BusinessDate)
    SELECT ('2024-04-21') FROM GENERATE_SERIES(1, 10)

Transaktion 2:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
  INSERT INTO Test (BusinessDate) VALUES
    ('2024-04-25'), ('2024-04-26'), ('2024-04-27');
  COMMIT
 GO

Transaktion 1:

COMMIT
GO

SELECT * FROM Test

Id | BusinessDate | BusinessDateYear | BusinessKeyId |	BusinessKey |
---+--------------+------------------+---------------+--------------|
1  | 2024-04-21	  | 2024             | 1             | 2024-1       |
2  | 2024-04-21	  | 2024             | 2             | 2024-2       |
3  | 2024-04-21	  | 2024             | 3             | 2024-3       |
4  | 2024-04-25	  | 2024             | 1             | 2024-1       |
5  | 2024-04-26	  | 2024             | 2             | 2024-2       |
6  | 2024-04-27	  | 2024             | 3             | 2024-3       |

Das ist natürlich falsch. Beheben kann man das Prolem aber sehr einfach:

WITH records AS
(
  SELECT
    COALESCE(BusinessKeyId, COUNT(CASE WHEN BusinessKeyId IS NULL THEN 1 END) OVER (PARTITION BY BusinessDateYear ORDER BY Id)
    + COALESCE(MAX(BusinessKeyId) OVER (PARTITION BY BusinessDateYear), 0)) AS GeneratedBusinessKeyId,
    Id,
    BusinessKeyId
  FROM Test 
  WITH(TABLOCK)
)

Dann Verhalten sich die Transaktionen so, als würde man sie sequentiell durchführen. Achtung: Das Ganze blockiert auch bei READ COMMITTED. Aber: Wenn man sich die Locking Tabelle anschaut, so wird nur blockiert, weil Transaktion A auf eine Row der Transaktion B einen Update-Lock machen will.

SQL Server uses another lock type, update (U) locks, during data modifications, acquiring them while searching for the rows that need to be updated.

Ich verstehe nicht ganz warum, da ja nur auf die einzufügenden upgedated wird.

Ebenfalls hilft:

CREATE UNIQUE INDEX IX_Test_BusinessDateYear_BusinessKeyId ON Test(BusinessDateYear, BusinessKeyId) WHERE BusinessKeyId IS NOT NULL;

Siehe auch https://sqlperformance.com/2014/04/t-sql-queries/the-serializable-isolation-level

PostgreSQL

Macht man folgendes in T1:

DROP TABLE IF EXISTS Persons CASCADE;

CREATE TABLE Persons (
  id BIGINT PRIMARY KEY,
  LastName varchar(255) NOT NULL  
);

INSERT INTO Persons (Id, LastName) VALUES(1, 'Mustermann');

Dann in T2:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
  SELECT * FROM persons

Sieht man Mustermann.

Macht man in T1:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
  UPDATE Persons SET LastName = 'Lutz' WHERE id = 1;

Kann man in T2 noch immer ein SELECT machen - es blockiert nicht:

SELECT * FROM persons

Man sieht noch immer Mustermann. Erst wenn man T1 commited, sieht man in T2 den neuen Namen (Repeatable Read geht nicht).

Siehe auch https://www.postgresql.org/files/developer/concurrency.pdf

Serializable

Wie man aus der Präsentation von Tom Lane entnehmen kann, funktioniert folgendes Beispiel in PostgreSQL nicht:

DROP TABLE IF EXISTS Foo;

CREATE TABLE Foo 
(
  id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,,
  balance int NOT NULL,
  accountid text NOT NULL
);

INSERT INTO Foo (balance, accountid) VALUES (600, 'checking'), (600, 'savings');

T1:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
  UPDATE Foo SET balance = 400 WHERE accountid = 'checking';
  SELECT SUM(balance) FROM Foo;

Ergibt 1000

T2:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
  UPDATE Foo SET balance = 400 WHERE accountid = 'savings';
  SELECT SUM(balance) FROM Foo;

Ergibt ebenfalls 1000 - also falsch, da inkonsistent.

Wie schaut es jetzt in SQL Server aus?

DROP TABLE IF EXISTS Foo
GO

CREATE TABLE Foo 
(
  Id int NOT NULL identity(1,1) PRIMARY KEY,
  balance int NOT NULL,
  accountid varchar(max) NOT NULL
)
GO

INSERT INTO FOo (balance, accountid) VALUES (600, 'checking'), (600, 'savings')
GO

T1:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION
  UPDATE Foo SET balance = 400 WHERE accountid = 'checking';
  SELECT SUM(balance) FROM Foo;

Ergibt 1000

T2:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION
  UPDATE Foo SET balance = 400 WHERE accountid = 'savings';
  SELECT SUM(balance) FROM Foo;

Blockiert! Nach dem Commit von T1 wird 800 ausgegeben - also korrekt.