Condividi tramite

Blocco ottimizzato

  • Articolo

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

Questo articolo presenta il blocco ottimizzato, una nuova funzionalità di motore di database 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é 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 il blocco ottimizzato, l'aggiornamento di mille righe in una tabella potrebbe richiedere mille blocchi di riga esclusivi (X) mantenuti fino alla fine della transazione.
  • Con il blocco ottimizzato, l'aggiornamento di mille righe in una tabella potrebbe richiedere mille 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. 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.

Nota

L'abilitazione del blocco ottimizzato riduce o elimina i blocchi di riga e di pagina acquisiti dalle istruzioni di Data Modification Language (DML) ad esempio INSERT, UPDATE, DELETE. MERGE Non ha alcun effetto su altri tipi di blocchi di database e oggetti, ad esempio blocchi dello schema.

Disponibilità

Il blocco ottimizzato è disponibile solo in database SQL di Azure e nel database SQL dell'infrastruttura, 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');
Risultato Descrizione
0 Il blocco ottimizzato è disabilitato.
1 Il blocco ottimizzato è abilitato.
NULL Il blocco ottimizzato non è disponibile.

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, 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

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 le proprietà ACID 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 cercano 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).

Uno screenshot del set di risultati di una query su sys.dm_tran_locks per una singola sessione che mostra un solo blocco quando è abilitato il blocco ottimizzato.

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

Uno screenshot del set di risultati di una query su sys.dm_tran_locks per una singola sessione che mostra tre blocchi quando il blocco ottimizzato non è abilitato.

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 è soddisfatto, 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 ostacolano a vicenda.

Ad 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 TRANSACTION;
UPDATE t1
SET b = b + 10
WHERE a = 1;
BEGIN TRANSACTION;
UPDATE t1
SET b = b + 10
WHERE a = 2;
COMMIT TRANSACTION;
COMMIT TRANSACTION;

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 è bloccata perchéU i blocchi non vengono eseguiti e perché nell'ultima versione commit della riga 1, la colonna a è uguale a 1, il che non soddisfa il predicato 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
Sessione 1 Sessione 2
BEGIN TRANSACTION;
UPDATE t3
SET b = b + 10
WHERE a = 1;
BEGIN TRANSACTION;
UPDATE t3
SET b = b + 10
WHERE a = 1;
COMMIT TRANSACTION;
COMMIT TRANSACTION;

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
Sessione 1 Sessione 2
BEGIN TRANSACTION T1;
UPDATE t4
SET b = 2
WHERE a = 1;
BEGIN TRANSACTION T2;
UPDATE t4
SET b = 3
WHERE b = 2;
COMMIT TRANSACTION;
COMMIT TRANSACTION;

Si valuterà il risultato dello scenario precedente con e senza blocco dopo 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). Questa riga non è idonea, per cui 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

Importante

Anche senza LAQ, le applicazioni non devono presupporre che il motore di database garantirà un ordinamento rigoroso senza usare hint di blocco quando vengono utilizzati livelli di isolamento basati sul controllo delle versioni delle righe. La raccomandazione generale per i clienti con carichi di lavoro simultanei in RCSI che si basano su un ordine di esecuzione rigoroso delle transazioni (come illustrato nell’esempio precedente) consiste nell'usare livelli di isolamento più rigorosi come REPEATABLE READ e SERIALIZABLE.

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 XACT wait_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 XACT wait_resource, con la finalità di modificare.
      • LCK_M_S_XACT - Si verifica quando un'attività è in attesa di un blocco condiviso in un tipo XACT wait_resource, in cui non è possibile dedurre la finalità. Non è uno scenario comune.
  • Blocco della visibilità delle risorse
  • Attendere la visibilità delle risorse
  • 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 rimarrà in attesa solo se la riga è idonea e in tale riga è 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, 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 usano livelli di isolamento più rigidi 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 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, ad esempio UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCKe così via, vengono rispettati quando è abilitato il blocco ottimizzato, riducono il vantaggio del blocco ottimizzato. Tali hint di blocco forzano 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 UPDLOCKhint, per aggiornarlo 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 saranno quelle a beneficiare maggiormente del blocco ottimizzato.

Un hint di tabella di 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