Blocco ottimizzato

Si applica a: database SQL di Azuredatabase SQL in Microsoft Fabric

Questo articolo presenta la funzionalità di blocco ottimizzata, una nuova funzionalità del motore di database che offre un meccanismo di blocco delle transazioni migliorato che riduce il consumo di memoria di blocco e il blocco per le transazioni simultanee.

Che cos'è il blocco ottimizzato?

Il blocco ottimizzato consente di ridurre la memoria di blocco poiché vengono mantenuti pochissimi blocchi anche per le transazioni di grandi dimensioni. 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 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 un blocco ottimizzato, l'aggiornamento di 1.000 righe in una tabella potrebbe richiedere 1.000 blocchi di riga esclusivi (X) mantenuti fino alla fine della transazione.
• Con il blocco ottimizzato, l'aggiornamento di 1.000 righe in una tabella potrebbe richiedere 1.000 blocchi di riga X, ma ogni blocco viene rilasciato non appena ogni riga viene aggiornata e viene mantenuto un solo blocco TID fino alla fine della transazione. Poiché i blocchi vengono rilasciati rapidamente, l'utilizzo della memoria di blocco viene ridotto e l'escalation dei blocchi è molto meno probabile, migliorando la concorrenza del carico di lavoro.

Disponibilità

Il blocco ottimizzato è disponibile solo nel database SQL di Azure e database SQL in Microsoft Fabric, in tutti i livelli di servizio e le dimensioni di calcolo.

Il blocco ottimizzato non è attualmente disponibile in Istanza gestita di SQL di Azure o in SQL Server.

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:

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

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

• Il blocco ottimizzato richiede l'abilitazione del ripristino accelerato del database (ADR) nel database.
• Per sfruttare al meglio i vantaggi offerti dal blocco ottimizzato, è necessario abilitare read committed snapshot isolation (RCSI) per il database. Il componente LAQ del blocco ottimizzato è attivo solo se RCSI è abilitato.

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, collegarsi al database e 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

Questo è un breve riepilogo del comportamento quando il blocco ottimizzato non è abilitato. 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 garantire la ACID proprietà delle transazioni.

Quando una transazione deve modificare i dati, richiede 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 tentano di accedere 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 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)

Quando sono in uso livelli di isolamento basati sul versioning delle righe o quando è abilitato l'ADR, ogni riga del database contiene internamente un ID di transazione (TID). 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 S blocco sul TID per attendere il completamento della prima transazione. Con il blocco TID, i blocchi di pagina e di riga continuano a essere acquisiti per le modifiche, 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 singolo sulla risorsa TID, sostituendo i blocchi multipli di pagina e di riga (chiave).

Si consideri l'esempio seguente che mostra i blocchi per la sessione corrente mentre è attiva una transazione di scrittura:

/* Is optimized locking is enabled? */
SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

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

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

BEGIN TRANSACTION;

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 TRANSACTION;
GO

DROP TABLE IF EXISTS t0;

Se il blocco ottimizzato è abilitato, la richiesta contiene solo un singolo X blocco sulla XACT risorsa (di transazione).

Se il blocco ottimizzato non è abilitato, la stessa richiesta contiene quattro blocchi: tre blocchi chiave X per ogni riga e un blocco IX (a intento esclusivo) nella pagina contenente le righe.

La vista di gestione dinamica (DMV) sys.dm_tran_locks è 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 modifica il modo in cui le istruzioni DML, ad esempio INSERT, UPDATE, DELETE, e MERGE acquisiscono blocchi.

Senza il blocco ottimizzato, i predicati delle query vengono controllati riga per riga in uno scan, eseguendo prima un blocco di riga di aggiornamento (U). Se il predicato è soddisfatto, viene eseguito un blocco di riga esclusivo (X) prima di aggiornare la riga e mantenuto fino alla fine della transazione.

Con il blocco ottimizzato e quando è abilitato il READ COMMITTEDlivello di isolamento dello snapshot (RCSI), i predicati vengono applicati alla versione di cui è stato eseguito il commit della riga più recente senza eseguire alcun blocco. Se il predicato non soddisfa, la query passa alla riga successiva nell'analisi. Se il predicato è soddisfatto, viene eseguito un X blocco di riga per aggiornare la riga. Il X blocco di riga 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 bloccano tra loro.

Ad esempio:

CREATE TABLE t1
(
a int NOT NULL,
b int NULL
);

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

Senza il blocco ottimizzato, la sessione 2 viene bloccata perché la sessione 1 contiene un U blocco nella sessione di riga 2 e deve essere aggiornata. Tuttavia, con il blocco ottimizzato, la sessione 2 non viene bloccata perché i blocchi U non vengono eseguiti e poiché nella versione di commit più recente della riga 1, la colonna a è uguale a 1, che non soddisfa la condizione della sessione 2.

Poiché con LAQ Ui blocchi non vengono eseguiti, una transazione simultanea potrebbe modificare la riga dopo la valutazione del predicato. Se il predicato viene soddisfatto e non è presente alcuna altra transazione attiva nella riga (nessun X blocco TID), la riga viene modificata. Se è presente una transazione attiva, il motore di database attende il completamento e rivaluta il predicato al momento della modifica perché l'altra transazione potrebbe aver modificato la riga. Se il predicato è ancora soddisfatto, la riga viene modificata.

Si consideri l'esempio seguente in cui viene ritentata automaticamente la valutazione del predicato perché un'altra transazione ha modificato la riga:

CREATE TABLE t3
(
a int NOT NULL,
b int NULL
);

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

Modifiche al comportamento delle query con blocchi ottimizzati e RCSI

I carichi di lavoro simultanei con isolamento dello snapshot read committed (RCSI) 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 t4 in base alla colonna b aggiornata durante la transazione T1.

CREATE TABLE t4
(
a int NOT NULL,
b int NULL
);

INSERT INTO t4
VALUES (1,1);
GO

Si valuterà ora il risultato dello scenario precedente con e senza blocco dopo la qualificazione (LAQ).

Senza LAQ

Senza LAQ, l’UPDATEistruzione nella transazione T2 verrà bloccata e attenderà il completamento della transazione T1. Al termine di T1, T2 aggiorna la colonna b delle impostazioni di riga in 3 perché il predicato è soddisfatto.

Dopo aver eseguito il commit di entrambe le transazioni, la tabella t4 contiene le righe seguenti:

 a | b
 1 | 3

Con LAQ

Con LAQ, la transazione T2 usa la versione di commit più recente della riga in cui la colonna b è uguale a per 1 valutarne il predicato (b = 2). Il riga non è idoneo; pertanto viene ignorata e l'istruzione viene completata 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 t4 contiene le righe seguenti:

 a | b
 1 | 2

Aggiunte di diagnostica per il blocco ottimizzato

I miglioramenti seguenti consentono di monitorare e risolvere i problemi di blocco e deadlock quando è abilitato il blocco ottimizzato:

• Tipi di attesa per il blocco ottimizzato
  • XACT tipi di attesa per il S blocco sul TID e descrizione 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 del tipo XACTwait_resource, dove non è possibile dedurre l'intento. Questo scenario non è comune.
• Blocco della visibilità delle risorse
  • Risorse bloccate XACT. Per altre informazioni, vedere resource_description in sys.dm_tran_locks (Transact-SQL).
• Attendere la visibilità delle risorse
  • Risorse in attesa XACT. Per altre informazioni, vedere wait_resource in sys.dm_exec_requests (Transact-SQL).
• Grafico del deadlock
  • In ogni risorsa del report deadlock <resource-list>, ogni <xactlock> elemento 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'READ COMMITTEDisolamento come livello di isolamento predefinito. Se non è già abilitato, abilitare RCSI connettendosi al master database ed eseguendo l'istruzione seguente:

ALTER DATABASE [your-database-name] SET READ_COMMITTED_SNAPSHOT ON;

Nel database SQL di Azure, RCSI è abilitato per impostazione predefinita ed READ COMMITTED è il livello di isolamento predefinito. Con RCSI abilitato e quando si usa READ COMMITTED il livello di isolamento, i lettori leggono una versione della riga dallo snapshot acquisito all'inizio dell'istruzione. 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 U blocchi. Con LAQ, una query attende solo se la riga è idonea ed è presente una transazione di scrittura attiva in tale riga. 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, viene 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 vengono mantenuti fino alla fine della transazione. Quando si utilizzano livelli di isolamento più rigorosi, come REPEATABLE READ o SERIALIZABLE, il motore di database mantiene i blocchi di riga e di pagina fino alla fine della transazione anche con il blocco ottimizzato abilitato, sia per i lettori che per gli scrittori, con conseguente aumento del blocco e dell'utilizzo della memoria di blocco.

Evitare di bloccare hint

Anche se gli hint di tabella e query, ad esempio UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCKe così via, vengono rispettati quando è abilitato il blocco ottimizzato, riducono il vantaggio del blocco ottimizzato. Gli hint di blocco costringono il motore di database ad accettare blocchi di riga o di pagina e mantenerli fino alla fine 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 UPDLOCKhint, per aggiornarlo in un secondo momento. È consigliabile usare hint di blocco solo dove richiesto.

Con il blocco ottimizzato, non ci sono restrizioni sulle query esistenti e non è necessario riscriverle. Le query che non usano hint traggono il massimo vantaggio dai blocchi ottimizzati.

Un suggerimento di tabella in una tabella in una query non disabilita 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 DML come INSERT, UPDATE, DELETE, o MERGE.. Ad esempio:

CREATE TABLE t5
(
a int NOT NULL,
b int NOT NULL
);

CREATE TABLE t6
(
a int NOT NULL,
b int NOT NULL
);
GO

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

UPDATE t5 SET t5.b = t6.b
FROM t5
INNER JOIN t6 WITH (UPDLOCK)
ON t5.a = t6.a;

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

UPDATE t5
SET t5.b = t6.b
FROM t5 WITH (REPEATABLEREAD)
INNER JOIN t6
ON t5.a = t6.a;

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

Domande frequenti

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 se 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.

Il blocco ottimizzato viene usato nelle repliche secondarie di sola lettura?

No, perché le istruzioni DML non possono essere eseguite su repliche di sola lettura e i blocchi di riga e di pagina corrispondenti non vengono acquisiti.

Il blocco ottimizzato viene usato quando si modificano i dati in tempdb e nelle tabelle temporanee?

Non al momento.

Contenuto correlato

• Guida per il controllo delle versioni delle righe e il blocco delle transazioni
• Read committed snapshot isolation (RCSI)
• sys.dm_tran_locks (Transact-SQL)
• Ripristino accelerato del database