SNAPSHOT vs READ COMMITTED

We wrześniu prowadziłem krótki warsztat SQL. Podczas poruszania tematu poziomów izolacji transakcji padło pytanie, na które nie potrafiłem udzielić odpowiedzi. Chodziło o interakcję dwóch poziomów izolacji: SNAPSHOT i READ COMMITTED, a ja z trybem SNAPSHOT miałem do czynienia głównie w książkach i niestety nie pamiętałem dokładnie co się stanie.

„Załóżmy, że mamy dwie sesje o poziomach izolacji SNAPSHOT i READ COMMITTED, które operują na tym samym zbiorze danych. Sesja nr 1 (SNAPSHOT) odczytuje dane, w tym czasie sesja nr 2 (READ COMMITTED) aktualizuje dane – np jeden rekord. Po tej aktualizacji sesja nr 1 chce usunąć rekord zaktualizowany w sesji nr 2. Co się stanie? Jaki będzie wynik?”

Przeprowadźmy test. Zakładam nową bazę, w której włączam obsługę trybu SNAPSHOT oraz tabelę, w której będę modyfikował jeden rekord.
Okno, w którym wprowadzę polecenia tworzenia bazy i tabeli nazywam „sesja 0”

-- sesja 0
CREATE DATABASE [TransakcjeTest]
ON PRIMARY (
	NAME		= N'TransakcjeTest',
	FILENAME	= N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TransakcjeTest.mdf',
	SIZE		= 25600KB,
	FILEGROWTH	= 10240KB
)
LOG ON (
	NAME		= N'TransakcjeTest_log',
	FILENAME	= N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TransakcjeTest_log.ldf',
	SIZE		= 10240KB,
	FILEGROWTH	= 10240KB
)
GO

USE [TransakcjeTest]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY')
	ALTER DATABASE [TransakcjeTest] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

-- ustaw Snapshot Isolation (SI)
ALTER DATABASE [TransakcjeTest] SET ALLOW_SNAPSHOT_ISOLATION ON
GO

-- przygotowanie tabeli
USE [TransakcjeTest];
GO

CREATE TABLE Test (id INT, info VARCHAR(10));
INSERT INTO Test(id, info) VALUES
(1, 'Info 1'),
(2, 'Info 2'),
(3, 'Info 3'),
(4, 'Info 4'),
(5, 'Info 5'),
(6, 'Info 6'),
(7, 'Info 7'),
(8, 'Info 8'),
(9, 'Info 9'),
(10, 'Info 10')

Po przygotowaniu środowiska otwieram nowe okno zapytania (niech się nazywa „sesja 1”) i rozpoczynam transakcję w trybie SNAPSHOT:

-- sesja 1
USE TransakcjeTest;
GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
-- zobacz dane
SELECT *
FROM dbo.Test

Pojawia się 10 rekordów, żadne zaskoczenie. Otwieram kolejne okno (niech się nazywa „sesja 2”) i rozpoczynam transakcję w trybie READ COMMITTED:

-- sesja 2
USE TransakcjeTest;
GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION

-- zobacz dane
SELECT *
FROM dbo.Test

Też nic nadzwyczajnego – dane jakie są, każdy widzi. Usuwam teraz jeden z rekordów (sesja 2), niech będzie ten z id = 6, a potem powrót do sesji nr 1

-- sesja 2, usuń rekord id = 6
DELETE dbo.Test
WHERE id = 6

Kasowanie rekordu odbyło się bez żadnych problemów. To ciekawe czy uda się zmienić skasowany rekord (sesja 1):

-- sesja 1, spróbuj zaktualizować dane id = 6
UPDATE dbo.Test SET
    info = 'Info 6 X'
WHERE
    1 = 1
    AND id = 6

Okazuje się, że sesja wisi – czeka na zwolnienie zasobów. W „sesji 0” sprawdzam na co czeka:

-- sesja 0
--SELECT * FROM sys.dm_tran_locks
SELECT
	[type]		= resource_type,
	[mode]		= request_mode,
	[desc]		= resource_description,
	[status]	= request_status,
	[sess_id]	= request_session_id
FROM sys.dm_tran_locks
WHERE
	1 = 1
	AND resource_type  'DATABASE'
ORDER BY
	request_session_id,
	request_status

-- wynik
type    mode  desc     status  sess_id
PAGE    IX    1:290    GRANT   53
OBJECT  IX             GRANT   53
RID      X    1:290:5  WAIT    53
OBJECT  IX             GRANT   54
RID      X    1:290:5  GRANT   54
PAGE    IX    1:290    GRANT   54

request_session_id == 53 to sesja 1, request_session_id == 54 to sesja 2. Sesja 2 wciąż trwa i trzyma exclusive lock na obiekcie 1:290:5, natomiast sesja 1 czeka, aż będzie mogła dostać się do tego samego obiektu. Powrót zatem do sesji 2 i zatwierdzenie transakcji

-- sesja 2, zatwierdź transakcję
COMMIT TRANSACTION

Sesja 2 zakończona, sesja 1 również kończy się od razu – znika napis ‚Executing…’ z tytułu okna zapytania. Ale jednocześnie w sesji 1 jest komunikat:
-- sesja 1
Msg 3960, Level 16, State 6, Line 18
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Test' directly or indirectly in database 'TransakcjeTest' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

Transakcja sesji 1 została zakończona, a zmiany wstrzymane. Widać zatem, że oprócz tego, że tryb snapshot dostarcza taki sam zestaw danych przez czas trwania całej transakcji, to pilnuje, żeby nie stracić modyfikacji wykonywanych na tych danych.

Krótko odpowiadając na pytanie: sesja z trybem SNAPSHOT nie pozwoli wykonać zmiany, ponieważ stwierdzi, że dane źródłowe się zmieniły.

• sql
• sql server