SQL Server und RCSI
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.