Blocco ottimizzato

  • Articolo

Si applica a:Database SQL di Azure

Questo articolo presenta il blocco ottimizzato, una nuova funzionalità di motore di database di SQL Server che offre un meccanismo di blocco delle transazioni migliorato per ridurre il consumo di memoria di blocco e il blocco tra le transazioni simultanee.

Che cos'è il blocco ottimizzato?

Il blocco ottimizzato consente di ridurre la memoria di blocco poiché per le transazioni di grandi dimensioni vengono mantenuti pochissimi blocchi. Inoltre, il blocco ottimizzato evita anche l'escalation dei blocchi. In questo modo è possibile accedere simultaneamente alla tabella.

Il blocco ottimizzato è costituito da due componenti principali: blocco ID transazione (TID) e blocco dopo qualificazione (LAQ).

  • Un ID transazione (TID) è un identificatore univoco di una transazione. Ogni riga viene contrassegnata dall'ultimo TID che lo ha modificato. Viene usato un singolo blocco sul TID anziché potenzialmente molti blocchi chiave o identificatore di riga. Per altre informazioni, vedere la sezione relativa al blocco ID transazione (TID).
  • Il blocco dopo qualificazione (LAQ) è un'ottimizzazione che valuta i predicati di una query in base alla versione della riga di cui è stato eseguito il commit più recente senza acquisire un blocco, migliorando così la concorrenza. Per altre informazioni, vedere la sezione relativa al blocco dopo qualificazione (LAQ).

Ad esempio:

  • Senza il blocco ottimizzato, l'aggiornamento di 1 milione di righe in una tabella potrebbe richiedere 1 milione di blocchi di riga esclusivi (X) mantenuti fino alla fine della transazione.
  • Con il blocco ottimizzato, l'aggiornamento di 1 milione di righe in una tabella potrebbe richiedere 1 milione di blocchi di riga (X), ma ogni blocco viene rilasciato al completamento dell’aggiornamento di ogni riga e verrà mantenuto un solo blocco TID fino alla fine della transazione.

Questo articolo illustra in dettaglio questi due concetti base relativi al blocco ottimizzato.

Disponibilità

Attualmente, il blocco ottimizzato è disponibile solo in database SQL di Azure. Per altre informazioni, vedere Dove è attualmente disponibile il blocco ottimizzato?

Il blocco ottimizzato è abilitato?

Il blocco ottimizzato è abilitato per ogni database utente. Connettersi al database e usare la query seguente per verificare se il blocco ottimizzato è abilitato nel database:

SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX('testdb', 'IsOptimizedLockingOn');

Se non si è connessi al database specificato in DATABASEPROPERTYEX, il risultato sarà NULL. Si dovrebbe ricevere 0 (il blocco ottimizzato è disabilitato) o 1 (abilitato).

Il blocco ottimizzato si basa su altre funzionalità del database:

Sia ADR che RCSI sono abilitati per impostazione predefinita in database SQL di Azure. Per verificare che queste opzioni siano abilitate per il database corrente, usare la query T-SQL seguente:

SELECT name
, is_read_committed_snapshot_on
, is_accelerated_database_recovery_on
FROM  sys.databases
WHERE name = db_name();

Panoramica del blocco

Si tratta di un breve riepilogo del comportamento quando non è abilitato il blocco ottimizzato. Per altre informazioni, vedere Guida per il controllo delle versioni delle righe e il blocco della transazione.

Nel motore di database, il blocco è un meccanismo che impedisce a più transazioni di aggiornare contemporaneamente gli stessi dati per proteggere l'integrità e la coerenza dei dati.

Quando una transazione deve modificare i dati, può richiedere un blocco sui dati. Il blocco viene concesso se non vengono mantenuti altri blocchi in conflitto sui dati e la transazione può procedere con la modifica. Se nei dati viene mantenuto un altro blocco in conflitto, la transazione deve attendere il rilascio del blocco prima di poter procedere.

Quando più transazioni hanno accesso contemporaneamente agli stessi dati, il motore di database deve risolvere conflitti potenzialmente complessi con letture e scritture simultanee. Il blocco è uno dei meccanismi in base ai quali il motore di database può fornire la semantica per i livelli di isolamento delle transazioni SQL ANSI. Anche se il blocco nei database è essenziale, la riduzione della concorrenza, i deadlock, la complessità e il sovraccarico del blocco possono influire sulle prestazioni e sulla scalabilità.

Blocco ottimizzato e blocco ID transazione (TID)

Ogni riga nella motore di database contiene internamente un ID transazione (TID) quando è in uso il controllo delle versioni delle righe. Questo TID viene mantenuto su disco. Ogni transazione che modifica un riga imprime il relativo TID.

Con il blocco TID, invece di acquisire il blocco sulla chiave della riga, viene eseguito un blocco sul TID della riga. La transazione di modifica contiene un blocco X sul relativo TID. Altre transazioni acquisiscono un blocco S sul TID per verificare se la prima transazione è ancora attiva. Con il blocco TID, i blocchi di pagina e di riga continuano a essere acquisiti per gli aggiornamenti, ma tutte le pagine e blocchi di riga vengono rilasciati al completamento dell'aggiornamento di ciascuna riga. L'unico blocco mantenuto fino alla fine della transazione è il blocco X sulla risorsa TID, sostituendo i blocchi di pagina e di riga (chiave), come illustrato nella demo successiva. Il blocco ottimizzato non riguarda altri blocchi di database e oggetti standard.

Il blocco ottimizzato consente di ridurre la memoria di blocco poiché per le transazioni di grandi dimensioni vengono mantenuti pochissimi blocchi. Inoltre, il blocco ottimizzato evita anche l'escalation dei blocchi. Ciò consente ad altre transazioni simultanee di accedere alla tabella.

Si consideri lo scenario di esempio T-SQL seguente che cerca i blocchi nella sessione corrente dell'utente:

CREATE TABLE t0
(a int PRIMARY KEY not null
,b int null);

INSERT INTO t0 VALUES (1,10),(2,20),(3,30);
GO

BEGIN TRAN
UPDATE t0
SET b=b+10;

SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
AND resource_type in ('PAGE','RID','KEY','XACT');

COMMIT TRAN
GO
DROP TABLE IF EXISTS t0;

A screenshot of the result set of a query on sys.dm_tran_locks for a single session shows only one lock when optimized locking is enabled.

La stessa query senza il vantaggio del blocco ottimizzato crea quattro blocchi:

A screenshot of the result set of a query on sys.dm_tran_locks for a single session shows three locks when optimized locking is not enabled.

La vista di gestione dinamica (DMV) sys.dm_tran_locks può essere utile per esaminare o risolvere i problemi di blocco, inclusa l'osservazione del blocco ottimizzato in azione.

Blocco ottimizzato e blocco dopo qualificazione (LAQ)

Basandosi sull'infrastruttura TID, il blocco ottimizzato cambia la modalità in cui i predicati di query proteggono i blocchi.

Senza il blocco ottimizzato, i predicati delle query vengono controllati e analizzati riga per riga eseguendo prima un blocco di riga di aggiornamento (U). Se il predicato è soddisfatto, viene eseguito un blocco di riga X prima di aggiornare la riga.

Con il blocco ottimizzato e quando è abilitato il livello di isolamento dello snapshot read committed (RCSI), i predicati vengono applicati alla versione di cui è stato eseguito il commit più recente senza eseguire alcun blocco di riga. Se il predicato non è soddisfatto, la query passa alla riga successiva nell'analisi. Se il predicato è soddisfatto, viene eseguito un blocco di riga X per aggiornare effettivamente la riga. Il blocco di riga X viene rilasciato al completamento dell'aggiornamento di riga, prima della fine della transazione.

Poiché la valutazione del predicato viene eseguita senza acquisire blocchi, le query simultanee che modificano righe diverse non si ostacolano.

Esempio:

CREATE TABLE t1
(a int not null
,b int null);

INSERT INTO t1 VALUES (1,10),(2,20),(3,30);
GO
Sessione 1 Sessione 2
BEGIN TRAN
UPDATE t1
SET b=b+10
WHERE a=1;
BEGIN TRAN
UPDATE t1
SET b=b+10
WHERE a=2;
COMMIT TRAN
COMMIT TRAN

Comportamento delle modifiche del blocco con il blocco ottimizzato nell'esempio precedente. Senza il blocco ottimizzato, la sessione 2 verrà bloccata.

Tuttavia, con il blocco ottimizzato, la sessione 2 non verrà bloccata perché la versione di cui è stato eseguito il commit più recente della riga 1 contiene a=1 che non soddisfa il predicato della sessione 2.

Se il predicato è soddisfatto, attendere il completamento di qualsiasi transazione attiva nella riga. Se fosse necessario attendere il blocco S TID, la riga potrebbe essere stata modificata e la versione in cui è stato eseguito il commit più recente potrebbe essere stata modificata. In tal caso, invece di interrompere la transazione a causa di un conflitto di aggiornamento, il motore di database ritenta la valutazione del predicato nella stessa riga. Se il predicato viene qualificato al nuovo tentativo, la riga verrà aggiornata.

Si consideri l'esempio seguente quando viene ritentata automaticamente una modifica del predicato:

CREATE TABLE t2
(a int not null
,b int null);

INSERT INTO t2 VALUES (1,10),(2,20),(3,30);
GO
Sessione 1 Sessione 2
BEGIN TRAN
UPDATE t2
SET b=b+10
WHERE a=1;
BEGIN TRAN
UPDATE t2
SET b=b+10
WHERE a=1;
COMMIT TRAN
COMMIT TRAN

Modifiche al comportamento delle query con blocchi ottimizzati e RCSI

I sistemi simultanei con livello di isolamento dello snapshot read committed (RCSI) con carichi di lavoro basati su un ordine di esecuzione rigoroso delle transazioni potrebbero riscontrare un comportamento di query diverso quando è abilitato il blocco ottimizzato.

Si consideri l'esempio seguente in cui la transazione T2 aggiorna la tabella t1 in base alla colonna b aggiornata durante la transazione T1.

CREATE TABLE t1 (a int not null, b int null);

INSERT INTO t1 VALUES (1,1);
GO
Sessione 1 Sessione 2
BEGIN TRAN T1
UPDATE t1
SET b=2
WHERE a=1;
BEGIN TRAN T2
UPDATE t1
SET b=3
WHERE b=2;
COMMIT TRAN
COMMIT TRAN

Si valuterà il risultato dello scenario precedente con e senza blocco dopo qualificazione (LAQ), una parte integrante del blocco ottimizzato.

Senza LAQ

Senza LAQ, la transazione T2 verrà bloccata e attenderà il completamento della transazione T1.

Dopo aver eseguito il commit di entrambe le transazioni, la tabella t1 conterrà le righe seguenti:

 a | b
 1 | 3

Con LAQ

Con LAQ, la transazione T2 userà la versione di cui è stato eseguito il commit più recente della riga b (b=1 nell'archivio versioni) per valutarne il predicato (b=2). Questa riga non è idonea per cui viene ignorata e T2 passa alla riga successiva senza essere bloccata dalla transazione T1. In questo esempio LAQ rimuove il blocco ma genera risultati diversi.

Dopo aver eseguito il commit di entrambe le transazioni, la tabella t1 conterrà le righe seguenti:

 a | b
 1 | 2

Importante

Anche senza LAQ, le applicazioni non devono presupporre che SQL Server (con livelli di isolamento del controllo delle versioni) garantirà un ordinamento rigoroso, senza usare hint di blocco. La raccomandazione generale per i clienti sui sistemi simultanei in RCSI con carichi di lavoro che si basano su un ordine di esecuzione rigoroso delle transazioni (come illustrato nell'esercizio precedente), consiste nell'usare livelli di isolamento più rigorosi.

Aggiunte di diagnostica per il blocco ottimizzato

Per supportare il monitoraggio e la risoluzione dei problemi di blocco e deadlock con il blocco ottimizzato, cercare le aggiunte seguenti:

  • Tipi di attesa per il blocco ottimizzato
    • Tipi di attesa XACT e descrizioni delle risorse in sys.dm_os_wait_stats (Transact-SQL):
      • LCK_M_S_XACT_READ - Si verifica quando un'attività è in attesa di un blocco condiviso su un tipo XACTwait_resource, con la finalità di leggere.
      • LCK_M_S_XACT_MODIFY - Si verifica quando un'attività è in attesa di un blocco condiviso su un tipo XACTwait_resource, con la finalità di modificare.
      • LCK_M_S_XACT - Si verifica quando un'attività è in attesa di un blocco condiviso in un tipo XACTwait_resource, in cui non è possibile dedurre la finalità. Rare.
  • Blocco della visibilità delle risorse
  • Attendere la visibilità delle risorse
  • Grafico del deadlock
    • In ogni risorsa del report deadlock <resource-list>, ogni elemento <xactlock> segnala le risorse sottostanti e informazioni specifiche per i blocchi di ogni membro di un deadlock. Per altre informazioni e un esempio, vedere Blocchi ottimizzati e deadlock.

Procedure consigliate con il blocco ottimizzato

Abilitare isolamento dello snapshot commit di lettura (RCSI)

Per ottimizzare i vantaggi del blocco ottimizzato, è consigliabile abilitare read committed snapshot isolation (RCSI) nel database e usare l'isolamento read committed come livello di isolamento predefinito. Se non è abilitata, abilitare RCSI usando l'esempio seguente:

ALTER DATABASE databasename SET READ_COMMITTED_SNAPSHOT ON;

In database SQL di Azure, RCSI è abilitato per impostazione predefinita e read committed è il livello di isolamento predefinito. Con RCSI abilitato e quando si usa il livello di isolamento read committed, scrittura e lettura non si bloccano a vicenda. I lettori leggono una versione della riga dallo snapshot acquisito all'inizio della query. Con LAQ, i writer qualificano le righe per ogni predicato in base alla versione di cui è stato eseguito il commit più recente della riga senza acquisire blocchi U. Con LAQ, una query rimarrà in attesa solo se la riga è idonea e in tale riga o pagina è presente una transazione di scrittura attiva. L'idoneità in base alla versione di cui è stato eseguito il commit più recente e al blocco delle sole righe qualificate riduce il blocco e aumenta la concorrenza.

Oltre a ridurre il blocco, verrà ridotta la memoria di blocco necessaria. Ciò dipende dal fatto che i lettori non accettano blocchi e i writer accettano solo blocchi di durata breve, anziché i blocchi che scadono alla fine della transazione. Quando si usano livelli di isolamento più rigidi come lettura ripetibile o serializzabile, si forza il motore di database a contenere i blocchi di riga e di pagina fino alla fine della transazione, sia per i lettori sia per i writer, con conseguente aumento della memoria di blocco e di bloccaggio.

Evitare di bloccare hint

Anche se gli hint di tabella e query vengono rispettati, riducono il vantaggio del blocco ottimizzato. Gli hint di blocco nelle query, ad esempio UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCK, riducono i vantaggi completi del blocco ottimizzato. La presenza di tali hint di blocco nelle query forza il motore di database ad assumere blocchi di riga/pagina e mantenerli in attesa fino al termine della transazione, per rispettare la finalità degli hint di blocco. La logica di alcune applicazioni prevede dove sono necessari hint di blocco, ad esempio durante la lettura di una riga con select with UPDLOCK, per aggiornarli in un secondo momento. È consigliabile usare hint di blocco solo dove richiesto.

Con il blocco ottimizzato, non è necessario riscrivere le query e le query esistenti. Le query che non usano hint otterranno il massimo vantaggio dal blocco ottimizzato.

Un hint di tabella di una tabella in una query non disabiliterà il blocco ottimizzato per altre tabelle nella stessa query. Inoltre, il blocco ottimizzato influisce solo sul comportamento di blocco delle tabelle aggiornate da un'istruzione UPDATE. Ad esempio:

CREATE TABLE t3
(a int not null
, b int not null);

CREATE TABLE t4
(a int not null
, b int not null);
GO
INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
INSERT INTO t4 VALUES (1,10),(2,20),(3,30);
GO

UPDATE t3 SET t3.b = t4.b
FROM t3
INNER JOIN t4 WITH (UPDLOCK) ON t3.a = t4.a;

Nell'esempio di query precedente, l’hint di blocco interesserà solo la tabella t4, mentre t3 può comunque trarre vantaggio dal blocco ottimizzato.

UPDATE t3 SET t3.b = t4.b
FROM t3 WITH (REPEATABLEREAD)
INNER JOIN t4 ON t3.a = t4.a;

Nell'esempio di query precedente, solo la tabella t3 userà il livello di isolamento di lettura ripetibile e manterrà i blocchi fino alla fine della transazione. Altri aggiornamenti per t3 possono comunque trarre vantaggio dal blocco ottimizzato. Lo stesso vale per l'hint HOLDLOCK.

Domande frequenti

Dove è attualmente disponibile il blocco ottimizzato?

Attualmente, il blocco ottimizzato è disponibile in database SQL di Azure.

Il blocco ottimizzato è disponibile nei livelli di servizio seguenti:

  • tutti i livelli di servizio DTU
  • tutti i livelli di servizio vCore, inclusi provisioning e serverless

Il blocco ottimizzato non è attualmente disponibile in:

  • Istanza gestita di SQL di Azure
  • SQL Server 2022 (16.x)

Il blocco ottimizzato è attivato per impostazione predefinita nei database nuovi ed esistenti?

In database SQL di Azure sì.

Come è possibile rilevare se è abilitato il blocco ottimizzato?

Vedere La funzionalità di blocco ottimizzato è abilitata?

Cosa accade quando il ripristino accelerato del database (ADR) non è abilitato nel database?

Se ADR è disabilitato, anche il blocco ottimizzato viene disabilitato automaticamente.

Cosa accade se si desidera forzare il blocco delle query nonostante il blocco ottimizzato?

Per i clienti che usano RCSI, per forzare il blocco tra due query quando è abilitato il blocco ottimizzato, usare l'hint per la query READCOMMITTEDLOCK.

Contenuto correlato