Cambiamento nella priorità degli Sch-S locks in SQL 2012 sotto RUI e RCSI

Oggi continuiamo ad esplorare le novità di SQL 2012 andando a testare una piccola ma importante modifica alla gestione della priorità degli schema lock. Gli schema lock sono quei particolari tipi di lock utilizzati da SQL server per coordinare le operazioni con impatto a livello di schema. Alcuni esempi? Un alter table ovviamente richiede un lock di tipo schema M (modifica). Una select in read uncommitted richiede - sorpresa - un lock di tipo schema S (stabilità). Come potete immaginare (ma come potete confermare leggendo qui: http://msdn.microsoft.com/en-us/library/ms186396(v=sql.105).aspx) i due diversi tipi di lock non sono compatibili fra di loro. In casi in cui non si riesca ad acquisire un lock perché ne esiste uno incompatibile SQL metterà il lock in attesa (o, meglio, il thread che ha richiesto il lock). Appena il lock sarà disponibile SQL risveglierà il thread in attesa e permetterà al lock di acquisire il possesso dell’oggetto.

Questo meccanismo si basa su una coda FIFO che garantisce che tutti i thread avranno la loro occasione di ottenere il possesso dell’oggetto (questo ovviamente a meno di deadlocks ma non ne parleremo ora).

Ad esempio supponendo di avere un lock di tipo S (condiviso) sull’oggetto A avremo una coda di questo tipo:

1: Lock S su A (attivo).

Ora supponiamo che arrivino due richieste X (esclusive) su A. X non è compatibile con S per cui viene messo in attesa.

3: Lock X su A (attesa).

2: Lock X su A (attesa).

1: Lock S su A (attivo).

Appena il lock X su A verrà rilasciato il primo thread in attesa del lock X su A verrà risvegliato per cui avremo:

2: Lock X su A (attesa).

1: Lock X su A (attivo).

Se ora arrivasse una richiesta di lock S su A anche lui andrà in coda:

3: Lock S su A (attesa).

2: Lock X su A (attesa).

1: Lock X su A (attivo).

NB: Questa è una semplificazione estrema, i lock sono molti di più e gli oggetti su cui si pongono sono vari; tuttavia è sufficiente per lo scopo di questo post.

Questo meccanismo aveva per gli schema locks – fino a SQL 2008 R2 incluso – una deroga: gli Sch-S lock posti negli isolation level read uncommitted e read committed snapshot avevano precedenza sugli altri schema lock (cioè sul Sch-M).

Ciò vuol dire che in una situazione come questa:

3: Lock Sch-M su A (attesa).

2: Lock Sch-M su A (attesa).

1: Lock Sch-S su A (attivo).

All’arrivo di un nuovo Sch-S invece che essere messo in fondo alla coda questo verrà messo in testa:

4: Lock Sch-M su A (attesa).

3: Lock Sch-M su A (attesa).

2: Lock Sch-S su A (attesa).

1: Lock Sch-S su A (attivo).

Come potete ben immaginare in determinate condizioni – ad esempio arrivando continui Sch-S in read uncommitted – i thread in attesa del Sch-M non verrebbero mai risvegliati. Questa situazione è chiamata in letteratura thread starvation.

Ora voi sarete tranquilli perchè non utilizzate l’isolation level read uncommitted. Chi lo usa dopotutto? ;). Però se ci pensate ogni volta che usate il query hint WITH(NOLOCK) lo state facendo! Quindi se voi utilizzate WITH(NOLOCK) siete esposti a questo problema.

Vi do un paio di script per provare il tutto (non in prod, mi raccomando!):

 CREATE DATABASE StarvationDemo;
GO
USE StarvationDemo;
GO
CREATE TABLE NoLockSample(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Testo NVARCHAR(255));
GO
INSERT INTO NoLockSample
SELECT name FROM sys.objects;
GO 256

Ora aprite tre o quattro finestre di SSMS e lanciate contemporaneamente questo script

 USE StarvationDemo;
GO

DECLARE @cnt FLOAT

WHILE 1=1
    SELECT @cnt=AVG(CONVERT(FLOAT, ID)) FROM NoLockSample
        WITH(NOLOCK)
        WHERE LEFT(Testo, 1) = 'LL';

Notate che usiamo il famigerato NOLOCK.

Se a questo punto, in un’altra finestra, lanciate un banale alter table:

 USE [StarvationDemo];
GO
ALTER Table [dbo].[NoLockSample] ADD PocaRoba BIT;
GO

Attenderete parecchio :). Se controllate i lock presenti:

 SELECT l.request_mode,r.transaction_id, r.blocking_session_id, r.wait_type, r.start_time, l.request_lifetime--, r.*, l.*
FROM sys.dm_tran_locks l
      inner join sys.dm_exec_requests r
            on l.request_session_id = r.session_id
where request_mode in ('Sch-S', 'Sch-M')
order by start_time desc

Vedrete gli Sch-S locks attivi con il vostro Sch-M lock in attesa (notate come l'id di transazione cambi):

Se avete sottomano un SQL 2012 vedrete che il thread in attesa di Sch-M non verrà “scavalcato”, di fatto risolvendo il thread starvation.

Ecco un altro motivo in più per passare a SQL 2012 :).

Happy coding,

Francesco Cogno