Guida per il controllo delle versioni delle righe e il blocco delle transazioni

Si applica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSistema di Piattaforma Analitica (PDW)Database SQL in Microsoft Fabric

Nei sistemi con numerosi utenti, la gestione delle transazioni nei database spesso comporta problemi di prestazioni e contesa delle risorse. Con l'aumento progressivo del numero di utenti che accedono ai dati, diventa importante disporre di applicazioni che utilizzino le transazioni in modo efficiente. In questa guida vengono descritti i meccanismi di blocco e di controllo delle versioni delle righe usati dal motore di database per assicurare l'integrità di ogni transazione e presenta informazioni su come le applicazioni possono controllare le transazioni in modo efficiente.

Nozioni di base sulle transazioni

Una transazione è una sequenza di operazioni eseguite in un'unica unità logica di lavoro. Per essere considerata una transazione, un'unità logica di lavoro deve includere quattro proprietà, dette ACID (atomicità, consistenza, isolamento e durata):

Atomicity
Una transazione deve essere un'unità di lavoro atomica, ovvero devono essere eseguite tutte le modifiche dei dati oppure non ne deve essere eseguita nessuna.

Consistency
Al termine, una transazione deve lasciare tutti i dati in uno stato consistente. Per salvaguardare l'integrità dei dati in un database relazionale, è necessario che alle modifiche eseguite dalla transazione vengano applicate tutte le regole. Al termine della transazione tutte le strutture dei dati interne, ad esempio gli indici ad albero B o gli elenchi collegati doppiamente, devono risultare corrette.

Isolation
Le modifiche eseguite da transazioni simultanee devono essere isolate dalle modifiche eseguite da qualsiasi altra transazione simultanea. Una transazione riconosce lo stato dei dati precedente alla modifica eseguita da una transazione simultanea oppure lo stato successivo al completamento della seconda transazione, ma non è in grado di riconoscere gli stati intermedi. Questa proprietà viene detta serializzabilità in quanto consente di ricaricare i dati iniziali e di eseguire nuovamente una serie di transazioni in modo da ripristinare lo stato dei dati successivo all'esecuzione delle transazioni originali.

Durability
Gli effetti di una transazione completamente durevole completata sono permanenti all'interno del sistema. Le modifiche eseguite rimangono valide anche in caso di errore del sistema. SQL Server 2014 (12.x) e le versioni successive consentono transazioni con durabilità ritardata. Le transazioni durevoli ritardate vengono sottoposte a commit prima che il record del log delle transazioni diventi permanente su disco. Per altre informazioni sulla durabilità ritardata delle transazioni, consultare l'articolo Controllo della durabilità delle transazioni.

Le applicazioni devono avviare e terminare le transazioni in modo da garantire la consistenza logica dei dati. In particolare, l’applicazione deve definire la sequenza delle modifiche dei dati in modo che lo stato dei dati risulti coerente rispetto alle regole business dell'organizzazione. L’applicazione esegue queste modifiche in un'unica transazione in modo che il motore di database possa tutelare l'integrità della transazione.

È responsabilità di un sistema di database aziendale, ad esempio un'istanza del motore di database, fornire meccanismi che garantiscono l'integrità di ogni transazione. Il motore di database fornisce:

• Meccanismi di blocco che salvaguardano l'isolamento delle transazioni.

• Meccanismi di registrazione per garantire la durabilità delle transazioni. Per le transazioni completamente durevoli, il record del log viene scritto su disco prima che la transazione venga finalizzata. Pertanto, anche in caso di errore dell'hardware del server, del sistema operativo o della stessa istanza del motore di database, l'istanza usa i log delle transazioni al riavvio per eseguire automaticamente il rollback delle eventuali transazioni incomplete fino al punto in cui si è verificato l'errore di sistema. Le transazioni durature e ritardate vengono committate prima che il record del log delle transazioni venga salvato su disco. È possibile che tali transazioni vadano perse se si verifica un errore di sistema prima che il record di log venga sottoposto a protezione avanzata su disco. Per altre informazioni sulla durabilità ritardata delle transazioni, consultare l'articolo Controllo della durabilità delle transazioni.

• Caratteristiche di gestione delle transazioni che ne garantiscono l'atomicità e la consistenza. Una transazione avviata deve essere completata (sottoposta a commit). In caso contrario tutte le modifiche ai dati dall'inizio della transazione verranno annullate dal motore di database. Questa operazione viene definita come rollback di una transazione perché ripristina lo stato dei dati precedente alle modifiche.

Controllare le transazioni

Nelle applicazioni le transazioni vengono controllate principalmente specificandone l'inizio e la fine. Per specificare tali informazioni, è possibile utilizzare le istruzioni di Transact-SQL o le funzioni API (Application Programming Interface) del database. Il sistema deve essere in grado di gestire correttamente gli errori che comportano l'interruzione di una transazione prima che questa venga completata. Per ulteriori informazioni, consultare Transazioni, Eseguire transazioni in ODBC e Transazioni in SQL Server Native Client.

Per impostazione predefinita, le transazioni vengono gestite a livello della connessione. Quando viene avviata una transazione su una connessione, tutte le istruzioni di Transact-SQL eseguite su tale connessione fanno parte della transazione fino a quando questa non viene completata. Tuttavia, nell'ambito di una sessione MARS (Multiple Active Result Set) una transazione di Transact-SQL esplicita o implicita diventa una transazione con ambito batch gestita a livello di batch. Al termine del batch, se la transazione con ambito batch non viene eseguita il commit o annullata, viene eseguito automaticamente il rollback dal Motore di database. Per altre informazioni, vedere Uso di MARS (Multiple Active Result Set).

Avviare le transazioni

Utilizzando le funzioni API e le istruzioni Transact-SQL è possibile avviare transazioni come transazioni esplicite, con autocommit o implicite.

Transazioni esplicite

Per transazione esplicita si intende una transazione di cui vengono definiti in modo esplicito l'inizio e la fine tramite una funzione dell'API o l'emissione delle istruzioni di Transact-SQL BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, o ROLLBACK WORK istruzioni Transact-SQL. Al termine della transazione, viene ripristinata la connessione precedente all'avvio della transazione esplicita, ovvero la modalità transazione implicita o autocommit.

In una transazione esplicita è possibile utilizzare tutte le istruzioni di Transact-SQL, ad eccezione delle seguenti:

• CREATE DATABASE
• ALTER DATABASE
• DROP DATABASE
• CREATE FULLTEXT CATALOG
• ALTER FULLTEXT CATALOG
• DROP FULLTEXT CATALOG
• DROP FULLTEXT INDEX
• ALTER FULLTEXT INDEX
• CREATE FULLTEXT INDEX
• BACKUP
• RESTORE
• RECONFIGURE
• Procedure di sistema a testo completo
• sp_dboption per l'impostazione delle opzioni di database o delle procedure di sistema che modificano il database master all'interno di transazioni implicite o esplicite.

Transazioni autocommit

La modalità autocommit è la modalità predefinita per la gestione delle transazioni nel motore di database. Viene eseguito il commit o il rollback di ogni istruzione Transact-SQL completata. Se un'istruzione viene completata correttamente, viene effettuato il commit; se si verifica un errore, viene effettuato il rollback. In una connessione a un'istanza del motore di database è sempre attivata la modalità autocommit, a meno che tale modalità predefinita non sia stata annullata da transazioni implicite o esplicite. La modalità autocommit è l'impostazione predefinita anche in SqlClient, ADO, OLE DB e ODBC.

Transazioni implicite

Se per una connessione è attivata la modalità di esecuzione implicita delle transazioni, dopo che è stato eseguito il commit o il rollback della transazione corrente, nell'istanza del motore di database viene avviata automaticamente una nuova transazione. Non è necessario specificare l'inizio di una transazione. È sufficiente eseguirne il commit o il rollback. La modalità implicita delle transazioni genera una catena continua di transazioni. La modalità di esecuzione implicita delle transazioni viene impostata tramite una funzione API o l'istruzione Transact-SQL SET IMPLICIT_TRANSACTIONS ON. Questa modalità è anche nota come Autocommit OFF, consultare Metodo setAutoCommit (SQLServerConnection).

Dopo l'abilitazione della modalità di transazione implicita per una connessione, l'istanza del motore di database avvia automaticamente una transazione quando esegue per la prima volta una di queste istruzioni:

• ALTER TABLE
• CREATE
• DELETE
• DENY
• DROP
• FETCH
• GRANT
• INSERT
• OPEN
• REVOKE
• SELECT
• TRUNCATE
• UPDATE

Transazioni con ambito batch

Applicabile solo ai set di risultati multipli attivi (MARS), una transazione Transact-SQL esplicita o implicita che inizia durante una sessione MARS diventa una transazione con ambito di batch. Se per una transazione ad ambito Batch non è stato eseguito il commit o il rollback al completamento del Batch, il rollback viene eseguito automaticamente dal Motore di Database.

Transazioni distribuite

Le transazioni distribuite sono estese a due o più server noti come strumenti di gestione delle risorse. La gestione della transazione deve essere coordinata tra i vari strumenti di gestione delle risorse tramite un componente server denominato gestore delle transazioni. Ogni istanza del motore di database di SQL Server può fungere da Resource Manager per le transazioni distribuite coordinate da gestori di transazioni come Microsoft Distributed Transaction Coordinator (MS DTC) o altri gestori di transazioni che supportano la specifica Open Group XA per l'elaborazione di transazioni distribuite. Per ulteriori informazioni, vedere la documentazione di MS DTC.

Una transazione eseguita in una singola istanza del motore di database che si estende a due o più database è in effetti una transazione distribuita. Nell'istanza le transazioni distribuite vengono gestite internamente e appaiono all'utente come transazioni locali.

A livello dell'applicazione, una transazione distribuita viene gestita in modo simile ad una transazione locale. Al termine della transazione, l'applicazione richiede di effettuare il commit o il rollback. Il commit di transazioni distribuite deve essere gestito dal gestore delle transazioni in modo diverso per evitare che, in seguito a un errore della rete, alcuni strumenti di gestione delle risorse eseguano il commit correttamente mentre altri eseguano il rollback della transazione. Questo risultato si ottiene gestendo il processo di commit in due fasi (la fase di preparazione e la fase di commit), noto come commit a due fasi o protocollo 2PC.

• Fase di preparazione

  Quando il gestore delle transazioni riceve una richiesta di commit, invia un comando di preparazione a tutti gli strumenti di gestione delle risorse coinvolti nella transazione. Ogni gestore delle risorse esegue quindi tutte le operazioni necessarie per rendere la transazione durevole. Tutti i buffer di log delle transazioni vengono inoltre trasferiti su disco. Quando ogni gestore risorse completa la fase di preparazione, restituisce l'esito positivo o negativo della fase al gestore transazioni. SQL Server 2014 (12.x) ha introdotto la durabilità ritardata delle transazioni. Le transazioni durevoli ritardate vengono sottoposte a commit prima che i buffer di log delle transazioni vengano svuotati su disco. Per altre informazioni sulla durabilità ritardata delle transazioni, consultare l'articolo Controllo della durabilità delle transazioni.

• Fase di commit

  Se il gestore delle transazioni riceve conferme positive da tutti i gestori delle risorse, invia comandi di conferma a ciascun gestore delle risorse. I manager delle risorse possono quindi completare il commit. Se il commit viene eseguito correttamente da parte di tutti gli strumenti di gestione delle risorse, il gestore delle transazioni invia all'applicazione una notifica di operazione riuscita. Se viene segnalato un tentativo di preparazione non riuscito in uno strumento di gestione delle risorse, il gestore delle transazioni invia un comando di rollback a tutti gli strumenti di gestione e segnala all'applicazione che il commit ha avuto esito negativo.

  Le applicazioni del motore di database di possono gestire le transazioni distribuite attraverso Transact-SQL o le API di database. Per altre informazioni, vedere BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

Transazioni finali

Per interrompere una transazione, è possibile utilizzare l'istruzione COMMIT o ROLLBACK oppure una funzione API corrispondente.

• Commit

  Se una transazione ha esito positivo, commettila. L'istruzione COMMIT consente di integrare in modo permanente nel database tutte le modifiche apportate dalla transazione. Un commit consente inoltre di liberare risorse, come i blocchi, utilizzate dalla transazione.

• Rollback

  Se in una transazione si verifica un errore oppure l'utente decide di annullare la transazione, è necessario eseguire il rollback della transazione. L'istruzione ROLLBACK consente di annullare tutte le modifiche apportate alla transazione, ripristinando i dati allo stato dell'inizio della transazione. Il rollback consente inoltre di liberare le risorse utilizzate dalla transazione.

Errori durante l'elaborazione delle transazioni

Se una transazione non viene completata correttamente a causa di un errore, il motore di database esegue automaticamente il rollback della transazione e tutte le risorse utilizzate dalla transazione vengono liberate. Se viene interrotta la connessione di rete tra il client e un’istanza del motore di database, quando la rete notifica l'istanza dell'interruzione viene eseguito il rollback delle transazioni in sospeso per tale connessione. La connessione viene interrotta anche se si verifica un errore nell'applicazione client oppure se il computer client viene spento o riavviato. Anche in questi casi, quando la rete notifica l'interruzione, nell'istanza del motore di database viene eseguito il rollback delle connessioni in sospeso. Se il client si disconnette dal motore di database, viene eseguito il rollback delle transazioni in sospeso.

Se in un batch si verifica un errore di un'istruzione in fase di esecuzione, ad esempio la violazione di un vincolo, per impostazione predefinita nel motore di database viene eseguito solo il rollback dell'istruzione che ha generato l'errore. È possibile modificare questo comportamento usando l'istruzione SET XACT_ABORT ON. Dopo l'esecuzione di SET XACT_ABORT ON, qualsiasi errore di esecuzione di un'istruzione comporta il rollback automatico della transazione corrente. Gli errori di compilazione, ad esempio gli errori di sintassi, non sono interessati da SET XACT_ABORT. Per altre informazioni, vedere SET XACT_ABORT (Transact-SQL).

Quando si verificano errori, è necessario includere un'azione appropriata (COMMIT o ROLLBACK) nel codice dell'applicazione. Uno strumento efficace per la gestione degli errori, inclusi quelli nelle transazioni, è rappresentato dal costrutto TRY...CATCH di Transact-SQL. Per altre informazioni ed esempi che includono le transazioni, vedere TRY...CATCH (Transact-SQL). A partire da SQL Server 2012 (11.x), è possibile usare l'istruzione THROW per generare un'eccezione e trasferire l'esecuzione in un blocco CATCH di un costrutto TRY...CATCH. Per altre informazioni, vedere THROW (Transact-SQL).

Errori di compilazione e di run-time in modalità autocommit

In modalità autocommit, a volte potrebbe sembrare che in un'istanza del motore di database venga eseguito il rollback di un intero batch anziché di una sola istruzione SQL. Ciò si verifica solo in caso di errori di compilazione, non di errori di run-time. Un errore di compilazione impedisce infatti al motore di database di compilare un piano di esecuzione e non viene pertanto eseguita alcuna parte del batch. Anche se può sembrare che sia stato eseguito il rollback di tutte le istruzioni precedenti a quella che ha generato l'errore, in realtà l'errore impedisce l'esecuzione di qualsiasi elemento del batch. Nell'esempio seguente, a causa di un errore di compilazione non vengono eseguite le istruzioni INSERT del terzo batch. Sembra che le prime due istruzioni INSERT vengano annullate anche se non vengono mai eseguite.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUSE (3, 'ccc');  -- Syntax error.
GO
SELECT * FROM TestBatch;  -- Returns no rows.
GO

Nell'esempio seguente, la terza istruzione INSERT genera un errore di esecuzione di chiave primaria duplicata. Poiché le prime due istruzioni INSERT hanno avuto esito positivo e ne è stato pertanto eseguito il commit, tali istruzioni rimangono valide anche dopo l'errore di run-time.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUES (1, 'ccc');  -- Duplicate key error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

Nel motore di database viene utilizzata la risoluzione dei nomi posticipata, in base alla quale i nomi degli oggetti vengono risolti solo in fase di esecuzione, non nella fase di compilazione. Nell'esempio seguente vengono eseguite le prime due istruzioni INSERT e ne viene quindi eseguito il commit. Le due righe inserite rimangono nella tabella TestBatch dopo l'errore di run-time generato dalla terza istruzione INSERT a causa di un riferimento a una tabella non esistente.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBch VALUES (3, 'ccc');  -- Table name error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

Nozioni di base sul blocco e sul versionamento delle righe

Per garantire l'integrità delle transazioni e mantenere la coerenza dei database quando più utenti accedono simultaneamente ai dati, nel motore di database vengono utilizzati i meccanismi seguenti:

• Locking

  Ogni transazione richiede blocchi di diversi tipi sulle risorse, ad esempio sulle righe, le pagine o le tabelle dalle quali è dipendente. I blocchi impediscono alle altre transazioni di modificare le risorse in modo tale da creare problemi alla transazione che richiede il blocco. Ogni transazione libera i relativi blocchi quando non è più dipendente dalle risorse bloccate.

• Controllo delle versioni delle righe

  Quando viene attivato un livello di isolamento basato sul controllo delle versioni delle righe, nel motore di database vengono mantenute le versioni di ogni riga modificata. Anziché proteggere tutte le operazioni di lettura con blocchi, nelle applicazioni è possibile specificare che una transazione utilizza le versioni di riga per visualizzare la versione dei dati esistente all'inizio della transazione o dell’istruzioni, invece di proteggere tutto con blocchi. Usando il controllo delle versioni delle righe, la possibilità che un'operazione di lettura blocchi altre transazioni è notevolmente ridotta.

L'utilizzo di blocchi e il controllo delle versioni delle righe impediscono agli utenti di leggere dati di cui non è stato eseguito il commit e di modificare simultaneamente gli stessi dati. Senza il blocco o il controllo delle versioni delle righe, le query eseguite sui dati potrebbero generare risultati imprevisti, restituendo dati di cui non è ancora stato eseguito il commit nel database.

Nelle applicazioni è possibile scegliere il livello di isolamento delle transazioni, ovvero il livello di protezione delle transazioni dalle modifiche eseguite da altre transazioni. Per personalizzare ulteriormente il comportamento in base ai requisiti specifici di dell’applicazione, è possibile specificare hint a livello di tabella per le singole istruzioni di Transact-SQL.

Gestire l'accesso ai dati simultaneo

Quando più utenti accedono a una risorsa contemporaneamente si parla di accesso simultaneo alla risorsa. L'accesso ai dati simultaneo richiede meccanismi per evitare gli effetti negativi derivanti dal tentativo, da parte di più utenti, di tentare di modificare le risorse che altri utenti stanno utilizzando in modo attivo.

Effetti di concorrenza

Le operazioni di modifica dei dati eseguite da un utente possono influire sulle operazioni di lettura o modifica degli stessi dati eseguite contemporaneamente da altri utenti. Tale fenomeno è denominato accesso simultaneo ai dati. Se il database non dispone di controllo della concorrenza, gli utenti potrebbero riscontrare i seguenti effetti collaterali:

• Aggiornamenti persi

  Il problema della perdita degli aggiornamenti si verifica quando due o più transazioni selezionano la stessa riga, la quale viene quindi aggiornata in base al valore selezionato inizialmente. Ogni transazione non è a conoscenza delle altre transazioni. L'ultimo aggiornamento pertanto sovrascrive quelli eseguiti in precedenza dalle altre transazioni, con conseguente perdita di dati.

  Si supponga, ad esempio, che due revisori creino una copia in formato elettronico del medesimo documento Ogni editor modifica la propria copia indipendentemente e quindi salva la copia modificata, sovrascrivendo così il documento originale. L'editor che esegue il salvataggio per ultimo sovrascrive le modifiche apportate dall'altro editor. Questo problema potrebbe essere evitato se un editor non potesse accedere al file finché l'altro editor non avesse finito ed effettuato il commit della transazione.

• Dipendenza non sottoposta a commit (lettura sporca)

  La dipendenza non confermata si verifica quando una seconda transazione legge una riga che viene aggiornata da un'altra transazione. La seconda transazione sta leggendo dati che non sono ancora stati confermati e potrebbero essere modificati dalla transazione che aggiorna la riga.

  Si supponga, ad esempio, che un revisore stia modificando un documento in formato elettronico Un secondo revisore prende una copia del documento contenente tutte le modifiche apportate fino a quel momento e la distribuisce ai destinatari. Si supponga inoltre che il primo revisore decida di eliminare le modifiche apportate e quindi salvi il documento. In questo caso, il documento distribuito contiene modifiche non più esistenti e quindi da considerare come non più valide. Questo problema potrebbe essere evitato impedendo a tutti gli utenti di leggere il documento modificato fino a quando il primo revisore non salva definitivamente le modifiche ed esegue il commit della transazione.

• Analisi inconsistente (lettura non ripetibile)

  L'analisi inconsistente si verifica quando una transazione accede più volte alla stessa riga e legge ogni volta dati diversi. Un'analisi incoerente è simile alla dipendenza non confermata, in quanto un'altra transazione sta modificando i dati che una seconda transazione sta leggendo. Tuttavia, nell'analisi inconsistente, i dati letti dalla seconda transazione sono stati già confermati dalla transazione che ha effettuato la modifica. Con l'analisi inconsistente vengono, inoltre, eseguite due o più operazioni di lettura della stessa riga e a ogni lettura le informazioni vengono modificate da un'altra transazione. Da ciò deriva il termine lettura non ripetibile.

  Si supponga, ad esempio, che un revisore legga lo stesso documento due volte e che nell'intervallo tra la prima e la seconda lettura l'autore del documento apporti alcune modifiche. Quando il revisore legge il documento la seconda volta, il testo risulta diverso, La lettura originale non è ripetibile. Questo problema può essere evitato se il writer non è riuscito a modificare il documento finché l'editor non ha terminato di leggerlo per l'ultima volta.

• Letture fantasma

  Una lettura fantasma è una situazione che si verifica quando vengono eseguite due query identiche e la raccolta di righe restituite dalla seconda query risulta diversa. Nell'esempio seguente viene illustrato come può verificarsi questa situazione. Si supponga che le due transazioni vengano eseguite contemporaneamente. Le due istruzioni SELECT nella prima transazione possono restituire risultati diversi perché l'istruzione INSERT nella seconda transazione modifica i dati utilizzati da entrambe.

  --Transaction 1
  BEGIN TRAN;
  
  SELECT ID
  FROM dbo.employee
  WHERE ID > 5 AND ID < 10;
  
  --The INSERT statement from the second transaction occurs here.
  
  SELECT ID
  FROM dbo.employee
  WHERE ID > 5 and ID < 10;
  
  COMMIT;
  

  --Transaction 2
  BEGIN TRAN;
  INSERT INTO dbo.employee (Id, Name)
  VALUES(6 ,'New');
  
  COMMIT;
  

• Letture mancanti e doppie causate dagli aggiornamenti delle righe

  • Riga aggiornata mancante o più visualizzazioni di una riga aggiornata

    Le transazioni in esecuzione al livello READ UNCOMMITTED (o le istruzioni che utilizzano l'hint di tabella NOLOCK) non emettono blocchi condivisi per impedire che altre transazioni modifichino i dati letti dalla transazione corrente. Le transazioni in esecuzione al livello READ COMMITTED generano blocchi condivisi, ma i blocchi di riga o pagina vengono rilasciati dopo la lettura della riga. In entrambi i casi, quando si esegue l'analisi di un indice, se un altro utente modifica la colonna chiave di indice della riga durante la lettura, la riga potrebbe essere visualizzata di nuovo se la modifica della chiave ha spostato la riga in una posizione prima dell'analisi. Analogamente, la riga potrebbe non essere letta se la modifica della chiave ha spostato la riga in una posizione già letta nell'indice. Per evitare questo problema, usare l'hint SERIALIZABLE o HOLDLOCK oppure il versionamento delle righe. Per ulteriori informazioni, vedere Hint di tabella (Transact-SQL).

  • Manca una o più righe che non erano la destinazione dell'aggiornamento

    Quando si usa READ UNCOMMITTED, se la query legge righe usando un'analisi degli ordini di allocazione (usando le pagine IAM), è possibile che le righe vengano perse se un'altra transazione causa una suddivisione della pagina. Questo non si verifica quando si usa il READ COMMITTED livello di isolamento.

Tipi di concorrenza

Quando più transazioni tentano di modificare i dati in un database contemporaneamente, è necessario implementare un sistema di controlli in modo che le modifiche apportate da una transazione non influiscano negativamente su quelle di un'altra transazione. Questa funzionalità è denominata controllo della concorrenza.

I metodi per l'applicazione del controllo della concorrenza rientrano in due diverse categorie:

• Controllo della concorrenza pessimistica

  Un sistema di blocchi impedisce alle transazioni di modificare i dati con effetto su altre transazioni. Dopo che una transazione esegue un'azione che causa l'applicazione di un blocco, altre transazioni non possono eseguire azioni in conflitto con il blocco finché il proprietario non lo rilascia. Si tratta di un controllo pessimistico perché viene in genere usato nei sistemi in cui si verifica un elevato conflitto per i dati, in cui il costo di protezione dei dati con blocchi è inferiore al costo del rollback delle transazioni in caso di conflitti di concorrenza.

• Controllo della concorrenza ottimistica

  Nel controllo della concorrenza ottimistica, le transazioni non bloccano i dati durante la lettura di dati. Quando una transazione esegue l'aggiornamento dei dati, il sistema verifica se i dati sono stati modificati da un altro utente dopo la lettura. Se un’altra transazione aggiorna i dati, si verifica un errore. In genere, la transazione per cui viene visualizzato l'errore esegue il rollback e ricomincia dall'inizio. Questa operazione viene chiamata ottimistica perché in genere viene usata nei sistemi in cui è presente una contesa ridotta per i dati e in cui il costo del rollback occasionale di una transazione è inferiore al costo di blocco dei dati durante la lettura.

Il motore di database supporta entrambi i metodi di controllo della concorrenza. Gli utenti specificano il tipo di controllo della concorrenza selezionando i livelli di isolamento delle transazioni per le connessioni oppure le opzioni di concorrenza nei cursori. È possibile definire questi attributi usando le istruzioni di Transact-SQL o le proprietà e gli attributi delle API (Application Programming Interface) di database quali ADO, ADO.NET, OLE DB e ODBC.

Livelli di isolamento nel motore di database

Le transazioni specificano un livello di isolamento che definisce il grado di isolamento di una transazione dalle modifiche alle risorse o ai dati apportate da altre transazioni. I livelli di isolamento sono descritti in termini di quali effetti secondari sulla concorrenza, come letture sporche o letture fantasma, sono consentiti.

I livelli di isolamento delle transazioni controllano gli elementi seguenti:

• Se i blocchi vengono acquisiti alla lettura dei dati e quali tipi di blocchi vengono richiesti.
• Per quanto tempo vengono mantenuti i blocchi di lettura.
• Se un'operazione di lettura fa riferimento a righe modificate da un'altra transazione.
  • Si blocca fino al rilascio del blocco esclusivo sulla riga.
  • Recupera la versione della riga su cui è stato eseguito il commit al momento in cui l'istruzione o la transazione ha avuto inizio.
  • Legge la modifica dei dati di cui non è stato eseguito il commit.

Un livello di isolamento inferiore aumenta la possibilità per un maggior numero di transazioni di accedere ai dati contemporaneamente, ma anche la quantità di effetti di concorrenza (ad esempio letture sbagliate o perdita di aggiornamenti) potenzialmente verificabili. Viceversa, un livello di isolamento superiore riduce i tipi di effetti di concorrenza che le transazioni potrebbero riscontrare, ma richiede più risorse di sistema e aumenta le probabilità che una transazione blocchi un'altra. La scelta del livello di isolamento corretto dipende dal giusto equilibrio tra requisiti relativi all'integrità dei dati per l'applicazione e overhead di ogni livello di isolamento. Il livello di isolamento più elevato, SERIALIZABLE, garantisce che una transazione recuperi esattamente gli stessi dati ogni volta che ripete un'operazione di lettura, ma esegue questa operazione eseguendo un livello di blocco che potrebbe influire sulle altre transazioni nei sistemi multiutente. Il livello di isolamento più basso, READ UNCOMMITTED, potrebbe recuperare i dati modificati ma non sottoposti a commit da altre transazioni. Tutti gli effetti collaterali della concorrenza possono verificarsi in READ UNCOMMITTED, ma non esiste alcun blocco di lettura o controllo delle versioni, quindi il sovraccarico è ridotto al minimo.

Livelli di isolamento del motore di database

Lo standard ISO definisce i livelli di isolamento seguenti, tutti supportati dal motore di database:

Il motore di database supporta anche due livelli di isolamento delle transazioni aggiuntivi che usano il controllo delle versioni delle righe. Uno è un'implementazione del livello di isolamento READ COMMITTED e uno è il livello di isolamento della transazione SNAPSHOT.

Nella tabella seguente vengono illustrati gli effetti secondari della concorrenza attivati dai diversi livelli di isolamento.

Per altre informazioni sui tipi specifici di blocco o di controllo delle versioni delle righe controllati da ogni livello di isolamento della transazione, vedere SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Per impostare i livelli di isolamento delle transazioni, è possibile utilizzare Transact-SQL o un'API di database.

Transact-SQL
Gli script di Transact-SQL usano l'istruzione SET TRANSACTION ISOLATION LEVEL.

ADO
La proprietà IsolationLevel dell'oggetto Connection viene impostata su adXactReadUncommitted, adXactReadCommitted, adXactRepeatableRead o adXactReadSerializable dalle applicazioni ADO.

ADO.NET
ADO.NET applicazioni che usano lo Microsoft.Data.SqlClient spazio dei nomi gestito o System.Data.SqlClient possono chiamare il SqlConnection.BeginTransaction metodo e impostare l'opzione IsolationLevel su Unspecified, ChaosReadUncommitted, ReadCommittedRepeatableRead, , Serializable, o Snapshot.

OLE DB
Quando viene avviata una transazione, le applicazioni che utilizzano OLE DB chiamano ITransactionLocal::StartTransaction con isoLevel impostato su ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_SNAPSHOT o ISOLATIONLEVEL_SERIALIZABLE.

Quando si specifica il livello di isolamento della transazione in modalità autocommit, è possibile che le applicazioni OLE DB impostino DBPROPSET_SESSION della proprietà DBPROP_SESS_AUTOCOMMITISOLEVELS su DBPROPVAL_TI_CHAOS, DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_BROWSE, DBPROPVAL_TI_CURSORSTABILITY, DBPROPVAL_TI_READCOMMITTED, DBPROPVAL_TI_REPEATABLEREAD, DBPROPVAL_TI_SERIALIZABLE, DBPROPVAL_TI_ISOLATED o DBPROPVAL_TI_SNAPSHOT.

ODBC
Le applicazioni ODBC chiamano SQLSetConnectAttr con Attribute impostato su SQL_ATTR_TXN_ISOLATION e con ValuePtr impostato su SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ o SQL_TXN_SERIALIZABLE.

Per le transazioni snapshot, le applicazioni chiamano SQLSetConnectAttr con Attribute impostato su SQL_COPT_SS_TXN_ISOLATION e ValuePtr impostato su SQL_TXN_SS_SNAPSHOT. È possibile recuperare una transazione snapshot usando SQL_COPT_SS_TXN_ISOLATION o SQL_ATTR_TXN_ISOLATION.

Blocchi nel motore di database

I blocchi costituiscono un meccanismo utilizzato nel motore di database per sincronizzare l'accesso allo stesso dato eseguito contemporaneamente da più utenti.

Prima che una transazione acquisisca una dipendenza sullo stato corrente del dato, ad esempio mediante un'operazione di lettura o modifica dei dati, deve proteggersi dagli effetti di un'altra transazione che esegue operazioni di modifica sugli stessi dati. A tale scopo, la transazione richiede il blocco del dato. I blocchi sono caratterizzati da diverse modalità e possono ad esempio essere condivisi (S) o esclusivi (X). La modalità di blocco consente di definire il livello di dipendenza della transazione sui dati. Non è possibile concedere a una transazione un blocco che determina un conflitto con la modalità di blocco già concessa a un'altra transazione. Se una transazione richiede una modalità di blocco in conflitto con un blocco già concesso sugli stessi dati, il motore di database metterà in pausa la transazione richiedente fino al rilascio del primo blocco.

Quando una transazione modifica un dato, alcuni blocchi di protezione della modifica vengono mantenuti fino alla fine della transazione. La durata dei blocchi acquisiti da una transazione per proteggere le operazioni di lettura dipende dall'impostazione del livello di isolamento della transazione e dall'abilitazione o disabilitazione del blocco ottimizzato.

• Quando il blocco ottimizzato non è abilitato, i blocchi di riga e di pagina necessari per le scritture sono mantenuti fino alla fine della transazione.

• Quando il blocco ottimizzato è abilitato, viene mantenuto solo un blocco di ID transazione (TID) fino al termine della transazione. Con il livello di isolamento predefinito READ COMMITTED, le transazioni non manterranno i blocchi di riga e di pagina necessari per le scritture sino al termine della transazione. In questo modo si riduce la memoria di blocco necessaria e si riduce il bisogno di escalation dei blocchi. Inoltre, quando è abilitato il blocco ottimizzato, l'ottimizzazione del blocco dopo la qualificazione (LAQ) 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 la concorrenza.

I blocchi acquisiti da una transazione vengono rilasciati al completamento della transazione, sia nel caso di commit sia nel caso di rollback.

Le applicazioni in genere non richiedono blocchi direttamente. I blocchi sono gestiti internamente da un componente del motore di database denominato Gestione blocchi. Quando un'istanza del motore di database elabora un'istruzione di Transact-SQL, il processore di query del motore di database determina le risorse a cui accedere. Il processore di query determina quali tipi di blocchi sono necessari per proteggere le singole risorse in base al tipo di accesso e all'impostazione del livello di isolamento della transazione. Il processore di interrogazione richiede quindi i blocchi appropriati al gestore dei blocchi. Il gestore dei blocchi concede i blocchi se non ci sono blocchi in conflitto detenuti da altre transazioni.

Granularità dei blocchi e gerarchie

Il motore di database supporta il blocco con livelli di granularità diversi, che consente a una transazione di bloccare diversi tipi di risorse. Per ridurre al minimo il costo associato ai blocchi, il motore di database blocca automaticamente le risorse in base al livello più adatto all'attività. Bloccare a un livello di granularità inferiore, come le righe, aumenta la concorrenza ma implica un overhead maggiore, poiché è necessario mantenere più lock se molte righe sono bloccate. L'applicazione di un blocco con un livello di granularità superiore, ad esempio a livello di tabella, comporta invece un aumento dei costi in termini di concorrenza, in quanto bloccando un'intera tabella viene impedito ad altre transazioni di accedere a qualsiasi parte della tabella. L'overhead tuttavia risulta inferiore perché il numero di blocchi da gestire è minore.

Il motore di database deve spesso acquisire blocchi a più livelli di granularità per proteggere completamente una risorsa. Un gruppo di blocchi a più livelli di granularità viene denominato gerarchia di blocchi. Ad esempio, per proteggere completamente la lettura di un indice, un'istanza del motore di database potrebbe dover acquisire blocchi condivisi sulle righe e blocchi di intenti condivisi sulle pagine e nelle tabelle.

La tabella seguente mostra le risorse che possono essere bloccate nel motore di database.

¹ i blocchi HoBT e TABLE possono essere interessati dall'opzione LOCK_ESCALATION di ALTER TABLE.

² Sono disponibili risorse di blocco aggiuntive per XACT le risorse di blocco. Per altre informazioni, vedere Aggiunte di diagnostica per il blocco ottimizzato.

Modalità di blocco

Nel motore di database le risorse vengono bloccate in base a modalità di blocco diverse, che determinano il modo in cui le transazioni simultanee possono accedere alle risorse.

Nella tabella seguente sono illustrate le modalità di blocco delle risorse utilizzate nel motore di database.

Blocchi condivisi

I blocchi condivisi (S), consentono alle transazioni simultanee di leggere una risorsa nell'ambito del controllo della concorrenza pessimistica. senza che altre transazioni possano modificare i dati mentre il blocco condiviso (S) è applicato alla risorsa. I blocchi condivisi (S) applicati a una risorsa vengono rilasciati non appena viene completata l'operazione di lettura, a meno che il livello di isolamento della transazione non sia stato impostato su REPEATABLE READ o superiore oppure non sia in uso l'hint di blocco per mantenere attivo il blocco condiviso (S) per l'intera durata della transazione.

Aggiornare i meccanismi di blocco

Il motore di database inserisce blocchi di aggiornamento (U) durante la preparazione all'esecuzione di un aggiornamento. I blocchi U sono compatibili con i blocchi S, ma su una determinata risorsa solo una transazione può contenere un blocco U alla volta. Un'informazione da tenere a mente è che molte transazioni simultanee possono contenere blocchi S, ma solo una transazione può contenere un blocco U su una risorsa. Per aggiornare una riga, i blocchi di aggiornamento (U) vengono infine aggiornati a blocchi esclusivi (X).

I blocchi di aggiornamento (U) possono essere acquisiti anche da istruzioni diverse da UPDATE, quando viene specificato il suggerimento di tabella UPDLOCK nell'istruzione.

• Alcune applicazioni usano il criterio "seleziona una riga, quindi aggiorna la riga" che prevede che all'interno della transazione lettura e scrittura siano separate in modo esplicito. In questo caso, se il livello di isolamento è REPEATABLE READ o SERIALIZABLE, gli aggiornamenti simultanei possono causare un deadlock, come indicato di seguito:

  Una transazione legge i dati, acquisisce un blocco condiviso (S) sulla risorsa, quindi modifica i dati. Quest'ultima operazione comporta la conversione del blocco in un blocco esclusivo (X). Se due transazioni acquisiscono blocchi in modalità condivisa (S) su una risorsa e quindi eseguono un tentativo simultaneo di aggiornamento dei dati, una delle due transazioni eseguirà il tentativo di conversione del blocco in un blocco esclusivo (X). La conversione di un blocco da condiviso a esclusivo non può essere eseguita immediatamente, in quanto il blocco esclusivo (X) relativo a una transazione non è compatibile con il blocco condiviso (S) relativo all'altra transazione. Si verifica pertanto un'attesa di blocco. La seconda transazione tenta di acquisire un blocco esclusivo (X) per la propria operazione di aggiornamento. Poiché entrambe le transazioni vengono convertite in blocchi esclusivi (X) e sono ognuna in attesa che l'altra transazione rilasci il blocco condiviso (S), si verifica un deadlock.

  Nel livello di isolamento predefinito READ COMMITTED, S i lock sono di breve durata, rilasciati non appena vengono usati. Anche se il deadlock descritto in precedenza è ancora possibile, è molto meno probabile con blocchi di durata breve.

  Per evitare questo tipo di deadlock, le applicazioni possono seguire un criterio "seleziona una riga con hint UPDLOCK, quindi aggiorna la riga".

• Se l'hint UPDLOCK viene usato in una scrittura quando si sta utilizzando l’isolamento SNAPSHOT, la transazione deve avere accesso alla versione più recente della riga. Se la versione più recente non è più visibile, è possibile ricevere Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict. Per un esempio, vedere Uso dell'isolamento snapshot.

Blocchi esclusivi

I blocchi esclusivi (X) impediscono l'accesso a una risorsa da parte di transazioni simultanee. Con un blocco esclusivo (X), nessun'altra transazione può modificare i dati protetti dal blocco. Le operazioni di lettura possono essere eseguite solo utilizzando l'hint NOLOCK o il livello di isolamento READ UNCOMMITTED.

Le istruzioni di modifica dei dati, ad esempio INSERT, UPDATE e DELETE, combinano entrambe le operazioni di lettura e di modifica. L'istruzione esegue innanzitutto le operazioni di lettura per acquisire dati prima di eseguire le operazioni di modifica necessarie. Le istruzioni di modifica dei dati, pertanto, richiedono in genere blocchi sia condivisi che esclusivi. Un'istruzione UPDATE, ad esempio, potrebbe comportare la modifica di righe in una tabella basata su un join con un'altra tabella. In questo caso, l'istruzione UPDATE richiede blocchi condivisi sulle righe lette nella tabella di join, nonché blocchi esclusivi sulle righe aggiornate.

Blocchi di intento

Nel motore di database vengono utilizzati blocchi preventivi per proteggere l'applicazione di un blocco condiviso (S) o esclusivo (X) su una risorsa di livello inferiore nella gerarchia di blocchi. I blocchi preventivi, in inglese "intent lock", sono così denominati in quanto vengono acquisiti prima di un blocco a un livello inferiore e, pertanto, indicano l'intenzione di applicare blocchi a livelli inferiori.

I blocchi preventivi rispondono ai due obiettivi seguenti:

• Impedire alle altre transazioni di modificare la risorsa di livello superiore in un modo che annullerebbe la validità del blocco applicato al livello inferiore.
• Aumentare l'efficacia del motore di database nel rilevare i conflitti tra blocchi a un livello di granularità superiore.

Un blocco preventivo condiviso, ad esempio, viene richiesto a livello di tabella prima che nelle pagine o nelle righe all'interno della tabella vengano richiesti blocchi condivisi (S). L'impostazione di un blocco preventivo a livello di tabella consente di impedire l'acquisizione successiva da parte delle altre transazioni di un blocco esclusivo (X) sulla tabella contenente la pagina specifica. I blocchi di intento migliorano le prestazioni perché il motore di database esamina i blocchi di intento solo a livello di tabella per determinare se una transazione può acquisire un blocco su quella tabella. In questo modo, non è più necessario esaminare ogni blocco di riga o di pagina sulla tabella per determinare se una transazione può bloccare l'intera tabella.

I blocchi intent includono i blocchi intent condivisi (IS), i blocchi intent esclusivi (IX) e i blocchi condivisi con intent esclusivi (SIX).

Blocchi dello schema

Nel motore di database di i blocchi di modifica dello schema (Sch-M) vengono utilizzati durante un'operazione DDL (Data Definition Language) su una tabella, ad esempio l'aggiunta di una colonna o l'eliminazione di una tabella. Durante il periodo in cui è attivo, il blocco Sch-M impedisce l'accesso simultaneo alla tabella. Questo significa che il blocco Sch-M blocca tutte le operazioni esterne finché non viene rilasciato.

Alcune operazioni DML (Data Manipulation Language), ad esempio il troncamento delle tabelle, utilizzano i blocchi Sch-M per impedire l'accesso alle tabelle interessate da parte di operazioni simultanee.

Nel motore di database vengono utilizzati i blocchi di stabilità dello schema (Sch-S) durante la compilazione e l'esecuzione delle query. Sch-S i blocchi non bloccano alcun blocco transazionale, inclusi i blocchi esclusivi (X). Durante la compilazione di una query, è possibile continuare l'esecuzione di altre transazioni, comprese quelle che hanno blocchi X su una tabella. Tuttavia, le operazioni DDL simultanee e le operazioni DML simultanee che acquisiscono i blocchi Sch-M vengono bloccate dai blocchi Sch-S.

Blocchi di aggiornamento in bulk

I blocchi di aggiornamento bulk (BU) consentono a più thread di caricare in blocco i dati contemporaneamente nella stessa tabella, impedendo allo stesso tempo altri processi che non eseguono il caricamento bulk dei dati dall'accesso alla tabella. Nel motore di database vengono utilizzati i blocchi di aggiornamento in bulk (BU) quando vengono soddisfatte entrambe le condizioni seguenti.

• Si utilizza l'istruzione Transact-SQL BULK INSERT o la funzione OPENROWSET(BULK), oppure si utilizza uno dei comandi Bulk Insert dell'API, ad esempio .NET SqlBulkCopy, le API Fast Load di OLEDB o le API Bulk Copy di ODBC per eseguire la copia bulk dei dati in una tabella.
• L'hint TABLOCK è specificato o l'opzione della tabella table lock on bulk load è impostata tramite sp_tableoption.

Blocchi dell'intervallo di chiavi

I blocchi di intervalli di chiavi consentono di proteggere un intervallo di righe incluse in modo implicito in un set di record letto da un'istruzione di Transact-SQL quando si utilizza il livello di isolamento della transazione SERIALIZABLE. Il blocco degli intervalli di chiavi impedisce le letture fantasma. Proteggendo gli intervalli di chiavi tra le righe, previene anche inserimenti o eliminazioni fantasma in un insieme di record a cui accede una transazione.

Compatibilità delle serrature

La compatibilità tra blocchi consente di stabilire se più transazioni possono acquisire blocchi sulla stessa risorsa contemporaneamente. Se una risorsa è già bloccata da un'altra transazione, è possibile autorizzare una nuova richiesta di blocco solo se la modalità di blocco richiesta è compatibile con quella esistente. Se la modalità del blocco richiesto non è compatibile con il blocco esistente, la transazione che richiede il nuovo blocco deve attendere il rilascio del blocco esistente oppure la scadenza dell'intervallo del timeout blocco. Non vi sono, ad esempio, modalità di blocco compatibili con i blocchi esclusivi. In caso di presenza di un blocco esclusivo (X), nessun'altra transazione può acquisire altri blocchi di qualsiasi tipo, ovvero condivisi, di aggiornamento o esclusivi, sulla stessa risorsa fino a quando il blocco esclusivo (X) non viene rilasciato. Se invece è stato applicato a una risorsa un blocco condiviso (S), le altre transazioni possono acquisire un blocco condiviso o un blocco di aggiornamento (U) sulla stessa risorsa anche prima del completamento della prima transazione. Tuttavia, altre transazioni non possono ottenere un blocco esclusivo fino a quando il blocco condiviso non viene rilasciato.

La tabella seguente illustra la compatibilità delle modalità di blocco più comuni.

Usare la tabella seguente per determinare la compatibilità di tutte le modalità di blocco disponibili nel motore di database.

Blocco dell'intervallo di chiavi

I blocchi di intervalli di chiavi consentono di proteggere un intervallo di righe incluse in modo implicito in un set di record letto da un'istruzione di Transact-SQL quando si utilizza il livello di isolamento della transazione SERIALIZABLE. Il SERIALIZABLE livello di isolamento richiede che qualsiasi query eseguita durante una transazione debba ottenere lo stesso set di righe ogni volta che viene eseguito durante la transazione. Il blocco di intervalli di chiavi soddisfa questo requisito impedendo ad altre transazioni di inserire nuove righe le cui chiavi rientrerebbero nell'intervallo di chiavi letto dalla transazioneSERIALIZABLE .

Il blocco di intervalli di chiavi impedisce le letture fantasma. Proteggendo gli intervalli di chiavi tra le righe, impedisce anche gli inserimenti fantasma in un insieme di record a cui accede una transazione.

Il blocco di intervallo di chiavi viene applicato a un indice, specificando i valori di chiave iniziale e finale. Questo blocco impedisce ogni tentativo di inserimento, aggiornamento o eliminazione di una riga con un valore di chiave che rientra nell'intervallo, perché tali operazioni dovrebbero prima acquisire un blocco sull'indice. Ad esempio, una transazione SERIALIZABLE può eseguire un'istruzione SELECT che legge tutte le righe i cui valori di chiave corrispondono alla condizione BETWEEN 'AAA' AND 'CZZ'. Un blocco di intervalli di chiavi applicato ai valori di chiave compresi tra 'AAA' e 'CZZ' impedisce ad altre transazioni di inserire righe con valori di chiave all'interno di tale intervallo, ad esempio 'ADG', 'BBD' o 'CAL'.

Modalità di blocco degli intervalli di chiavi

I blocchi di intervallo di chiavi includono sia componenti di intervallo che di riga nel formato intervallo-riga.

• Intervallo rappresenta la modalità di blocco che protegge l'intervallo tra due voci di indice consecutive.
• La riga indica la modalità di blocco che protegge l'elemento dell'indice.
• Modalità indica la modalità di blocco combinato utilizzata. Le modalità di blocco di intervalli di chiavi sono composte da due parti: La prima parte rappresenta il tipo di blocco usato per bloccare l'intervallo di indici (RangeT), mentre la seconda rappresenta il tipo di blocco usato per bloccare una chiave specifica (K). Le due parti sono unite da un segno meno (-), ad esempio RangeT-K.

Le modalità di blocco degli intervalli di chiavi hanno una matrice di compatibilità che mostra quali blocchi sono compatibili con altri blocchi ottenuti sulle chiavi e intervalli che si sovrappongono.

Blocchi di conversione

I blocchi di conversione vengono creati quando un blocco di intervalli di chiavi è sovrapposto a un altro blocco.

I blocchi di conversione possono essere osservati per un breve periodo di tempo in diverse circostanze complesse, in alcuni casi durante l'esecuzione di processi simultanei.

Analisi di intervallo serializzabile, recupero di singolo elemento, eliminazione e inserimento

Il blocco di intervalli di chiavi garantisce la serializzabilità quando si eseguono le operazioni seguenti:

• Query di analisi intervallo
• Recupero di singola istanza di riga inesistente
• Operazione di eliminazione
• Operazione di inserimento

Prima di eseguire un blocco di intervalli di chiavi, è necessario che siano soddisfatte le condizioni seguenti:

• Il livello di isolamento della transazione deve essere impostato su SERIALIZABLE.
• Query Processor deve utilizzare un indice per implementare il predicato di filtro dell'intervallo. La clausola WHERE in un'istruzione SELECT potrebbe, ad esempio, stabilire una condizione di intervallo con il predicato seguente: ColumnX BETWEEN N'AAA' AND N'CZZ'. Un blocco di intervalli di chiavi può essere acquisito solo se ColumnX è coperto da una chiave di indice.

Examples

La tabella e l'indice seguenti sono usati come base per gli esempi di blocco di intervalli di chiavi che seguono.

Query di scansione dell'intervallo

Per garantire che una query di analisi dell'intervallo sia serializzabile, la stessa query deve restituire gli stessi risultati ogni volta che viene eseguita all'interno della stessa transazione. Altre transazioni non devono inserire nuove righe nella query per l'analisi di intervalli in quanto le righe potrebbero diventare inserimenti fantasma. Nella query seguente vengono ad esempio utilizzati la tabella e l'indice illustrati nella figura precedente:

SELECT name
FROM mytable
WHERE name BETWEEN 'A' AND 'C';

I blocchi di intervalli di chiavi vengono applicati alle voci di indice che corrispondono all'intervallo delle righe in cui il nome è compreso tra i valori Adam e Dale, in modo da impedire l'aggiunta o l'eliminazione di nuove righe qualificate nella query precedente. Sebbene il primo nome dell'intervallo sia Adam, il blocco di intervalli di chiavi in modalità RangeS-S applicato a questa voce di indice impedisce l'aggiunta prima del A di nuovi nomi che iniziano con la lettera Adam, ad esempio Abigail. In modo analogo, il blocco di intervalli di chiavi RangeS-S applicato alla voce di indice per Dale impedisce l'aggiunta dopo C di nuovi nomi che iniziano con la lettera Carlos, ad esempio Clive.

Estrazione singleton di dati inesistenti

Se una query di una transazione tenta di selezionare una riga inesistente, la successiva esecuzione della query all'interno della stessa transazione deve restituire il medesimo risultato. A nessun'altra transazione è consentito inserire la riga inesistente. Si consideri ad esempio la query seguente:

SELECT name
FROM mytable
WHERE name = 'Bill';

Un blocco di intervallo di chiave viene applicato alla voce di indice corrispondente all'intervallo di nomi compreso tra Ben e Bing poiché il nome Bill verrebbe inserito tra queste due voci di indice adiacenti. Il blocco di intervalli di chiavi in modalità RangeS-S viene applicato alla voce di indice Bing, impedendo così ad altre transazioni di inserire valori, ad esempio Bill, tra le voci di indice Ben e Bing.

Operazione di eliminazione senza il blocco ottimizzato

Quando viene eliminata una riga in una transazione, l'intervallo a cui appartiene tale riga non deve necessariamente rimanere bloccato per l'intera durata della transazione che esegue l'operazione di eliminazione. Per mantenere la serializzabilità è infatti sufficiente bloccare il valore della chiave eliminata fino al termine della transazione. Si consideri ad esempio l'istruzione DELETE seguente:

DELETE mytable
WHERE name = 'Bob';

Alla voce di indice corrispondente al nome Bob viene applicato un blocco esclusivo (X). Altre transazioni possono inserire o eliminare valori prima o dopo la riga con il valore Bob che viene eliminato. Tuttavia, qualsiasi transazione che tenta di leggere, inserire o eliminare righe corrispondenti al valore Bob viene bloccata fino al commit o al rollback della transazione di eliminazione. (L'opzione di database READ_COMMITTED_SNAPSHOT e il livello di isolamento SNAPSHOT consentono anche da un controllo delle versioni delle righe le letture dello stato di cui è stato eseguito il commit in precedenza.)

È possibile eliminare intervalli usando tre modalità di blocco di base, ovvero il blocco di riga, il blocco di pagina o il blocco di tabella. La strategia di blocco, ovvero il blocco a livello di pagina, di tabella o di riga, viene scelta da Query Optimizer o specificata dall'utente tramite hint di Query Optimizer, ad esempio ROWLOCK, PAGLOCK, o TABLOCK. Quando si utilizza PAGLOCK o TABLOCK, il motore del database rilascia immediatamente una pagina di indice se tutte le righe vengono eliminate da questa pagina. Al contrario, quando ROWLOCK viene usato, tutte le righe eliminate vengono contrassegnate solo come eliminate, ma vengono rimosse dalla pagina di indice in un secondo momento usando un'attività in background.

Operazione di eliminazione con il blocco ottimizzato

Quando si elimina una riga all'interno di una transazione, i blocchi di riga e di pagina vengono acquisiti e rilasciati in modo incrementale e non vengono mantenuti per la durata della transazione. Si consideri ad esempio l'istruzione DELETE seguente:

DELETE mytable
WHERE name = 'Bob';

Un TID lock viene applicato a tutte le righe modificate per tutta la durata della transazione. Un blocco viene acquisito sul TID delle righe di indice corrispondenti al valore Bob. Con il blocco ottimizzato, i blocchi di pagina e di riga continuano a essere acquisiti per gli aggiornamenti, ma ogni blocco di pagina e di riga viene rilasciato non appena ciascuna riga viene aggiornata. Il blocco TID impedisce che le righe siano aggiornate fino al completamento della transazione. Qualsiasi transazione che tenta di leggere, inserire o eliminare righe con il valore Bob viene bloccata fino al momento in cui la transazione che elimina effettua il commit o il rollback. (L'opzione di database READ_COMMITTED_SNAPSHOT e il livello di isolamento SNAPSHOT consentono anche le letture da una versione di riga dello stato precedentemente confermato.)

In caso contrario, i meccanismi di blocco di un'operazione di eliminazione sono uguali a quelli privi di blocchi ottimizzati.

Operazione di inserimento senza il blocco ottimizzato

Quando si inserisce una riga in una transazione, l'intervallo in cui è compresa non deve essere bloccato per l'intera durata della transazione che esegue l'operazione di inserimento. Per mantenere la serializzabilità è infatti sufficiente bloccare il valore della chiave inserita fino al termine della transazione. Si consideri ad esempio l'istruzione INSERT seguente:

INSERT mytable VALUES ('Dan');

Il blocco di intervalli di chiavi in modalità RangeI-N viene inserito nella voce di indice corrispondente al nome David per verificare l'intervallo. Se il blocco viene concesso, viene inserita una riga dal valore Dan e viene impostato un blocco esclusivo (X) su di essa. Il blocco dell'intervallo di chiavi in modalità RangeI-N è necessario solo per verificare l'intervallo e non viene mantenuto per la durata della transazione che esegue l'operazione di inserimento. Altre transazioni possono inserire o eliminare valori prima o dopo il aver inserito la riga col valore Dan. Tuttavia, qualsiasi transazione che tenta di leggere, inserire o eliminare la riga con il valore Dan viene bloccata fino a quando la transazione di inserimento non esegue il commit o il rollback.

Operazione di inserimento con blocco ottimizzato

Quando si inserisce una riga in una transazione, l'intervallo in cui è compresa non deve essere bloccato per l'intera durata della transazione che esegue l'operazione di inserimento. I blocchi di riga e di pagina vengono raramente acquisiti, solo quando è in corso una ricompilazione dell'indice online o quando sono presenti transazioni simultanee SERIALIZABLE . Se vengono acquisiti blocchi di riga e di pagina, vengono rilasciati rapidamente e non mantenuti per la durata della transazione. Per mantenere la serializzabilità è infatti sufficiente applicare il blocco TID esclusivo al valore della chiave inserita fino al termine della transazione. Si consideri ad esempio l'istruzione INSERT seguente:

INSERT mytable VALUES ('Dan');

Con il blocco ottimizzato, un RangeI-N blocco viene acquisito solo se nell'istanza viene usata almeno una transazione che usa il SERIALIZABLE livello di isolamento. Il blocco in modalità RangeI-N dell'intervallo di chiavi viene inserito nella riga di indice corrispondente al nome David per verificare l'intervallo. Se il blocco viene concesso, viene inserita una riga dal valore Dan e viene impostato un blocco esclusivo (X) su di essa. Il blocco di intervalli di chiavi in modalità RangeI-N è necessario solo per verificare l'intervallo e non viene mantenuto attivo per l'intera durata della transazione che esegue l'operazione di inserimento. Altre transazioni possono inserire o eliminare valori prima o dopo il aver inserito la riga col valore Dan. Tuttavia, qualsiasi transazione che tenta di leggere, inserire o eliminare la riga con il valore Dan viene bloccata fino a quando la transazione di inserimento non esegue il commit o il rollback.

Escalation dei blocchi

Per escalation di blocchi si intende il processo di conversione di molti blocchi con granularità fine in blocchi con granularità grossolana, un processo che riduce i costi generali del sistema e aumenta la probabilità di contesa di concorrenza.

L'escalation dei blocchi si comporta in modo diverso a seconda se è abilitato il blocco ottimizzato.

Escalation del blocco senza ottimizzazione del blocco

Man mano che acquisisce blocchi di basso livello, il motore di database inserisce anche blocchi preventivi negli oggetti contenenti gli oggetti di basso livello:

• Quando si bloccano righe o intervalli di chiavi di indice, il motore di database inserisce un blocco intenzionale nelle pagine contenenti le righe o le chiavi.
• Quando si bloccano pagine, il motore di database inserisce un blocco preventivo negli oggetti di livello superiore contenenti le pagine. Oltre al blocco preventivo nell'oggetto, i blocchi di pagina preventivi sono richiesti negli oggetti seguenti:
  • Pagine a livello foglia degli indici non clusterizzati
  • Pagine di dati di indici cluster
  • Pagine di dati heap

Il motore di database potrebbe eseguire il blocco di righe e pagine per la stessa istruzione per ridurre al minimo il numero di blocchi e ridurre la probabilità che sia necessaria l'escalation dei blocchi. Il motore di database potrebbe, ad esempio, applicare blocchi di pagina su un indice non clusterizzato (se viene selezionato un numero sufficiente di chiavi contigue nel nodo dell'indice per soddisfare la query) e blocchi di riga sull’indice clusterizzato o sull'heap.

Per eseguire l'escalation dei blocchi, il motore del database tenta di cambiare il blocco di intenti sulla tabella nel corrispondente blocco completo, ad esempio, cambiando un blocco di intenti esclusivo (IX) in un blocco esclusivo (X) o un blocco di intenti condiviso (IS) in un blocco condiviso (S). Se il tentativo di escalation dei blocchi ha esito positivo e viene acquisito il blocco completo della tabella, tutti i blocchi HoBT, di pagina (PAGE) o a livello di riga (RID, KEY) mantenuti dalla transazione nell'heap o nell'indice vengono rilasciati. Se non è possibile acquisire il blocco completo, non viene eseguita alcuna escalation dei blocchi in quel momento e il motore di database continua ad acquisire blocchi di riga, chiave o pagina.

Il motore di database non esegue l'escalation dei blocchi a livello di riga o di intervallo di chiavi in blocchi a livello di pagina, ma direttamente in blocchi a livello di tabella. Analogamente, i blocchi a livello di pagina vengono sempre promossi ai blocchi di tabella. Il blocco delle tabelle partizionate può essere alzato al livello HoBT per la partizione associata anziché al blocco della tabella. Un blocco a livello di HoBT non blocca necessariamente gli HoBT allineati della partizione.

Se un tentativo di escalation dei blocchi ha esito negativo a causa di blocchi in conflitto mantenuti da transazioni simultanee, il Motore di database ritenta l'escalation dei blocchi per ogni ulteriori 1.250 blocchi acquisiti dalla transazione.

Ogni evento di escalation si verifica principalmente a livello di una singola istruzione di Transact-SQL. All'avvio dell'evento, il motore di database tenta di eseguire l'escalation di tutti i blocchi di proprietà della transazione corrente in tutte le tabelle a cui fa riferimento l'istruzione attiva, a condizione che vengano soddisfatti i requisiti di soglia di escalation. Se l'evento di escalation inizia prima che l'affermazione abbia avuto accesso a una tabella, non viene fatto alcun tentativo di escalation dei blocchi su quella tabella. Se l'escalation dei blocchi ha esito positivo, tutti i blocchi acquisiti dalla transazione in un'istruzione precedente e ancora detenuti al momento dell'avvio dell'evento vengono elevati se la tabella è riferita dall'istruzione corrente ed è inclusa nell'evento di escalation.

Si supponga, ad esempio, che una sessione esegua queste operazioni:

• Avvia una transazione.
• Aggiorna TableA. In questo modo vengono generati blocchi di riga esclusivi in TableA che sono mantenuti fino al completamento della transazione.
• Aggiorna TableB. In questo modo vengono generati blocchi di riga esclusivi in TableB che sono mantenuti fino al completamento della transazione.
• Esegue un'operazione SELECT per unire TableA e TableC. Il piano di esecuzione della query richiede il recupero delle righe da TableA prima che le righe vengano recuperate da TableC.
• L'istruzione SELECT attiva l'escalation dei blocchi durante il recupero delle righe da TableA e prima di accedere a TableC.

Se l'escalation del blocco viene eseguita correttamente, verrà eseguita l'escalation solo dei blocchi detenuti dalla sessione su TableA. Sono inclusi sia i blocchi condivisi dall'istruzione SELECT sia i blocchi esclusivi dall'istruzione UPDATE precedente. Mentre, per determinare se è necessario effettuare l'escalation dei blocchi, vengono conteggiati solo i blocchi acquisiti dalla sessione in TableA per l'istruzione SELECT, una volta che l'escalation ha avuto esito positivo, tutti i blocchi detenuti dalla sessione in TableA vengono trasformati a un blocco esclusivo sulla tabella, e tutti gli altri blocchi con minore granularità, inclusi i blocchi preventivi, in TableA vengono rilasciati.

Non viene eseguito alcun tentativo di escalation dei blocchi su TableB poiché nell'istruzione SELECT non vi erano riferimenti attivi a TableB. Analogamente, non viene effettuato alcun tentativo di escalation dei blocchi in TableC, che non sono stati oggetto di escalation poiché non era ancora stato eseguito l'accesso al momento dell'escalation.

Escalation dei blocchi con gestione ottimizzata dei blocchi

Il blocco ottimizzato contribuisce a ridurre la memoria di blocco perché per la durata della transazione vengono mantenuti pochissimi blocchi. Man mano che il motore di database acquisisce blocchi di riga e di pagina, l'escalation dei blocchi può verificarsi in modo simile, ma molto meno frequentemente. In genere il blocco ottimizzato riesce a evitare l'escalation dei blocchi, poiché riduce il numero di blocchi e la quantità di memoria per i blocchi necessaria.

Quando il blocco ottimizzato è abilitato e, al livello di isolamento predefinito READ COMMITTED, il motore di database rilascia blocchi di riga e di pagina non appena la riga viene modificata. Nessun blocco di riga e di pagina è trattenuto per la durata della transazione, ad eccezione di un singolo blocco ID di transazione (TID). In questo modo si riduce la probabilità dell'escalation dei blocchi.

Soglie di escalation dei blocchi

Si verifica un'escalation dei blocchi quando l'escalazione non viene disabilitata nella tabella utilizzando l'opzione ALTER TABLE SET LOCK_ESCALATION e quando si verifica una delle seguenti condizioni:

• Una singola istruzione di Transact-SQL acquisisce almeno 5.000 blocchi su una singola tabella non partizionata o indice.
• Una singola istruzione di Transact-SQL acquisisce almeno 5.000 blocchi in una singola partizione di una tabella partizionata e l'opzione ALTER TABLE SET LOCK_ESCALATION è impostata su AUTO.
• Il numero di blocchi in un'istanza del motore di database supera la soglia di memoria o di configurazione.

Se i blocchi non possono essere escalati a causa di conflitti di blocco, il motore del database attiva periodicamente l'escalation dei blocchi ogni 1.250 nuovi blocchi che vengono acquisiti.

Soglia di escalation per un'istruzione di Transact-SQL

Quando il motore del database controlla la presenza di possibili escalation ogni 1.250 nuovi blocchi acquisiti, viene eseguita un'escalation dei blocchi solo se un'istruzione Transact-SQL ha acquisito almeno 5.000 blocchi su un singolo riferimento a una tabella. L'escalation lock viene attivata quando un'istruzione di Transact-SQL acquisisce almeno 5.000 lock su un singolo riferimento di tabella. Ad esempio, l'escalation blocchi non viene attivata se un'istruzione acquisisce 3.000 blocchi in un indice e 3.000 blocchi in un altro indice della stessa tabella. Analogamente, l'escalation dei lock non viene attivata se un'istruzione ha un self-join su una tabella e ogni riferimento alla tabella acquisisce solo 3.000 lock.

L'escalation dei blocchi si verifica solo per le tabelle a cui si è avuto accesso nel momento in cui l'escalation viene attivata. Si supponga che un'unica istruzione SELECT corrisponda a un join che accede a tre tabelle in questo ordine: TableA, TableB e TableC. L'istruzione acquisisce 3.000 blocchi di riga nell'indice cluster per TableA e almeno 5.000 blocchi di riga nell'indice cluster per TableB, tuttavia non ha ancora eseguito l'accesso a TableC. Il motore di database, quando rileva che l'istruzione ha ottenuto almeno 5.000 lock di riga in TableB, tenta di eseguire l'escalation di tutti i lock mantenuti dalla transazione corrente su TableB. Tenta anche di elevare tutte le chiusure mantenute dalla transazione corrente su TableA, ma poiché il numero di chiusure su TableA è inferiore a 5.000, l'escalation non riuscirà. Non viene tentata l'escalation dei blocchi perché TableC non è ancora stato eseguito l'accesso quando si è verificata l'escalation.

Soglia di escalation per un'istanza del Motore di Database

Ogni volta che il numero di lock supera la soglia di memoria per l'escalation dei lock, il Motore di Database avvia l'escalation dei lock. La soglia di memoria dipende dall'impostazione dell'opzione di configurazione locks:

• Se l'opzione locks è impostata sul valore predefinito di 0, la soglia di escalation dei blocchi viene raggiunta quando la memoria usata dagli oggetti blocco è pari al 24% della memoria usata dal Motore di Database, esclusa la memoria AWE. La struttura dati utilizzata per rappresentare un lock è lunga circa 100 byte. Si tratta di una soglia dinamica, in quanto il motore di database acquisisce e libera la memoria dinamicamente per adattarsi ai diversi carichi di lavoro.

• Se l'opzione locks è un valore diverso da 0, la soglia di incremento dei blocchi è equivalente al 40% (o minore se è presente una pressione di memoria) del valore dell'opzione blocchi.

Il motore di database può scegliere qualsiasi istruzione attiva da qualsiasi sessione per l'escalation e ogni 1.250 nuovi blocchi sceglierà istruzioni per l'escalation a condizione che la memoria dei blocchi usata nell'istanza rimanga al di sopra della soglia.

Escalation blocchi con tipi di blocco misti

Quando l'escalation dei blocchi si verifica, il blocco selezionato per l'heap o l'indice è sufficientemente forte da soddisfare i requisiti del blocco di livello inferiore più restrittivo.

Si supponga, ad esempio, che una sessione:

• Avvia una transazione.
• Aggiorni una tabella contenente un indice cluster.
• Esegua un'istruzione SELECT che fa riferimento alla stessa tabella.

L'istruzione UPDATE acquisisce questi lock:

• Blocchi esclusivi (X) sulle righe di dati aggiornate.
• Blocchi intenzionali esclusivi (IX) sulle pagine di indice clusterizzato contenenti tali righe.
• Un blocco IX sull'indice raggruppato e un altro sulla tabella.

L'istruzione SELECT acquisisce questi blocchi:

• Blocchi condivisi (S) su tutte le righe di dati lette, a meno che un blocco X non protegga già la riga dall'istruzione UPDATE.
• Blocchi di Intento Condivisi (IS) su tutte le pagine dell'indice raggruppato contenenti tali righe, a meno che la pagina non sia già protetta da un blocco IX.
• Nessun blocco sull'indice cluster o sulla tabella perché sono già protetti da IX blocchi.

Se l'istruzione SELECT acquisisce un numero di lock sufficiente ad attivare l'escalation dei lock e l'escalation ha esito positivo, il lock IX sulla tabella viene convertito in un lock X e vengono rilasciati tutti i lock su righe, pagine e indici. Sia gli aggiornamenti sia le letture sono protetti dal blocco X sulla tabella.

Ridurre il blocco e l'escalation dei blocchi

Nella maggior parte dei casi, il Motore di Database offre prestazioni ottimali quando utilizza le impostazioni predefinite per il blocco e l'escalation dei blocchi.

• Sfruttare il blocco ottimizzato.

  • Il blocco ottimizzato offre un meccanismo di blocco delle transazioni migliorato che riduce il consumo di memoria di blocco e il bloccaggio per le transazioni simultanee. Quando è abilitato il blocco ottimizzato, è molto meno probabile che si verifichi l'escalation del blocco.
  • Evitare di utilizzare indicatori di tabella con il blocco ottimizzato. Gli hint di tabella potrebbero ridurre l'efficacia del blocco ottimizzato.
  • Per ottenere il massimo vantaggio dal blocco ottimizzato, abilita l’opzione READ_COMMITTED_SNAPSHOT per il database. Questo è predefinito nel database SQL di Azure.
  • Il blocco ottimizzato richiede l'abilitazione nel database del ripristino accelerato del database (ADR).

Se un'istanza del Motore di Database genera molti blocchi e si verificano frequenti escalation dei blocchi, considerare di ridurre la quantità di blocchi adottando le seguenti strategie:

• Usare un livello di isolamento che non generi blocchi condivisi per le operazioni di lettura:

  • Livello di isolamento READ COMMITTEDquando l'opzione di database READ_COMMITTED_SNAPSHOT è ON.
  • Livello di isolamento SNAPSHOT.
  • Livello di isolamento READ UNCOMMITTED. Questo può essere usato solo per i sistemi che possono gestire le letture non pulite.

• Usare gli hint di tabella PAGLOCK o TABLOCK affinché il motore di database usi blocchi a livello di pagina, heap o indice invece di blocchi di basso livello. L'uso di questa opzione, tuttavia, aumenta i problemi degli utenti che bloccano altri utenti che tentano di accedere agli stessi dati e non devono essere usati nei sistemi con più di pochi utenti simultanei.

• Se il blocco ottimizzato non è disponibile, per le tabelle partizionate usa l'opzione LOCK_ESCALATION di ALTER TABLE per effettuare l'escalation dei blocchi a livello di partizione invece che a livello di tabella, o per disabilitare l'escalation dei blocchi per una tabella.

• Suddividere le operazioni batch di grandi dimensioni in diverse operazioni più piccole. Si supponga, ad esempio, di aver eseguito la query seguente per rimuovere centinaia di migliaia di righe obsolete da una tabella di controllo, per poi scoprire che l'operazione ha causato un'escalation di blocchi che bloccava altri utenti.

  DELETE FROM LogMessages
  WHERE LogDate < '2024-09-26'
  

  Rimuovendo queste righe alcune centinaia alla volta, è possibile ridurre notevolmente il numero di lock che si accumulano per ogni transazione e prevenire l'escalation dei lock. Per esempio:

  DECLARE @DeletedRows int;
  
  WHILE @DeletedRows IS NULL OR @DeletedRows > 0
  BEGIN
      DELETE TOP (500)
      FROM LogMessages
      WHERE LogDate < '2024-09-26'
  
      SELECT @DeletedRows = @@ROWCOUNT;
  END;
  

• ridurre l'impatto a livello di blocco di una query aumentandone il più possibile l'efficienza. Le analisi di grandi dimensioni o un numero elevato di ricerche chiave potrebbero aumentare la possibilità di escalation dei blocchi; inoltre, che aumenta la probabilità di deadlock e in genere influisce negativamente sulla concorrenza e sulle prestazioni. Dopo aver individuato la query che causa l'escalation dei blocchi, cerca opportunità di creare nuovi indici o di aggiungere colonne a un indice esistente per eliminare scansioni complete di indici o di tabelle e per massimizzare l'efficienza delle ricerche negli indici. Considerare l'uso di Database Engine Tuning Advisor per eseguire un'analisi automatica degli indici per la query. Per altre informazioni, vedere Tutorial: Database Engine Tuning Advisor. Uno degli obiettivi di questa ottimizzazione è fare in modo che le ricerche nell'indice restituiscano il minor numero di righe possibile per ridurre al minimo il costo delle ricerche tramite chiavi (massimizzazione della selettività dell'indice per la query specifica). Se il motore di database stima che un operatore logico di ricerca chiave potrebbe restituire molte righe, potrebbe usare un'ottimizzazione di prelettura per eseguire la ricerca. Se il motore di database utilizza il prefetch per una ricerca, deve elevare il livello di isolamento della transazione di una parte della query a REPEATABLE READ. Questo significa che ciò che potrebbe sembrare simile a un'istruzione SELECT a livello di isolamento READ COMMITTED potrebbe acquisire molte migliaia di lock delle chiavi (sia sull'indice clusterizzato che su uno non clusterizzato), il che può causare il superamento delle soglie di escalation dei lock. Questo è particolarmente importante se si rileva che il blocco con escalation è un blocco di tabella condiviso; tuttavia, ciò non è comune con il livello di isolamento predefinito READ COMMITTED.

  Se una ricerca di chiave con l'ottimizzazione prefetch causa l'escalation dei lock, è consigliabile aggiungere altre colonne all'indice non clusterizzato che appare nell'operatore logico Index Seek o Index Scan, sotto l'operatore logico di ricerca della chiave nel piano di query. Potrebbe essere possibile creare un indice di copertura (un indice che include tutte le colonne in una tabella usata nella query) o almeno un indice che copre le colonne utilizzate per i criteri di join o nella WHERE clausola se l'inclusione di tutti gli elementi nell'elenco SELECT di colonne non è pratica. Un join a ciclo annidato potrebbe anche usare l'ottimizzazione del prefetch e ciò comporta la stessa modalità di blocco.

• L'escalation dei blocchi non può verificarsi se uno SPID diverso sta attualmente mantenendo un blocco di tabella non compatibile. Un'escalation dei blocchi porta sempre a un blocco di tabella e mai a un blocco di pagina. Inoltre, se un tentativo di escalation blocchi ha esito negativo perché un altro SPID mantiene attivo un blocco di tabella incompatibile, durante l'attesa di un blocco di tabella la query che ha tentato l'escalation non viene bloccata. Continua invece ad acquisire blocchi al livello originale, più granulare (riga, chiave o pagina), effettuando periodicamente ulteriori tentativi di escalation. Pertanto, un metodo per evitare l'escalation dei blocchi in una determinata tabella consiste nell'acquisire e mantenere un blocco su una connessione diversa che non sia compatibile con il tipo di blocco utilizzato nell'escalation. Un blocco esclusivo di intenzione (IX) a livello di tabella non blocca righe o pagine, ma non è ancora compatibile con un blocco di tabella condiviso (S) o esclusivo (X). Si supponga, ad esempio, di dover eseguire un processo batch che modifica un grande numero di righe nella tabella mytable, causando un blocco a causa dell'escalation dei blocchi. Se il processo viene sempre completato in meno di un'ora, è possibile creare un processo di Transact-SQL contenente il codice seguente e pianificare che il nuovo processo si avvii alcuni minuti prima dell'ora di inizio del processo batch:

  BEGIN TRAN;
  
  SELECT *
  FROM mytable WITH (UPDLOCK, HOLDLOCK)
  WHERE 1 = 0;
  
  WAITFOR DELAY '1:00:00';
  
  COMMIT TRAN;
  

  Questa query acquisisce e mantiene attivo un blocco IX su mytable per un'ora, impedendo così l'escalation dei blocchi sulla tabella durante questo periodo. Questo batch non modifica i dati né blocca altre query, a meno che l'altra query non forzi un blocco di tabella con l'hint TABLOCK o se un amministratore ha disabilitato i blocchi di pagina o di riga di un indice su mytable.

• È anche possibile usare i flag di traccia 1211 e 1224 per disabilitare tutte o alcune escalation dei blocchi. Tuttavia, questi flag di traccia disabilitano l'escalation dei blocchi globalmente per l'intera istanza del Motore di database. L'escalation dei lock svolge uno scopo utile nel motore di database massimizzando l'efficienza delle query che altrimenti sarebbero rallentate dall'overhead dell'acquisizione e del rilascio di diverse migliaia di lock. L'escalation dei blocchi aiuta anche a ridurre al minimo la quantità di memoria necessaria per monitorarli. La memoria che il motore di database può allocare dinamicamente per le strutture di blocco è limitata, quindi se si disabilita l'escalation dei blocchi e la memoria di blocco aumenta sufficientemente grande, i tentativi di allocare blocchi aggiuntivi per qualsiasi query potrebbero non riuscire e si verifica l'errore seguente: Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

  Note

  Quando si verifica l'errore MSSQLSERVER_1204, viene arrestata l'elaborazione dell'istruzione corrente e viene eseguito un rollback della transazione attiva. Il rollback stesso potrebbe bloccare gli utenti o causare tempi di ripristino lunghi del database se si riavvia il servizio di database.

  Note

  L'uso di un suggerimento di blocco, come ROWLOCK, modifica solo l'acquisizione del blocco iniziale. Gli hint di blocco non impediscono l'escalation dei blocchi.

A partire da SQL Server 2008 (10.0.x), il comportamento dell'escalation dei blocchi è stato modificato con l'introduzione dell'opzione di tabella LOCK_ESCALATION. Per altre informazioni, vedere l'opzione LOCK_ESCALATION di ALTER TABLE.

Escalation di blocchi del monitor

Monitorare l'escalation blocchi usando l'evento esteso lock_escalation, come nell'esempio seguente:

-- Session creates a histogram of the number of lock escalations per database
CREATE EVENT SESSION [Track_lock_escalation] ON SERVER
ADD EVENT sqlserver.lock_escalation
    (
    SET collect_database_name=1,collect_statement=1
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.sql_text,sqlserver.username)
    )
ADD TARGET package0.histogram
    (
    SET source=N'sqlserver.database_id'
    )
GO

Bloccaggio dinamico

L'uso di blocchi di basso livello, ad esempio blocchi di riga, aumenta la concorrenza riducendo la probabilità che due transazioni richiedano blocchi sulla stessa parte di dati contemporaneamente. L'uso di lock di basso livello aumenta anche il numero di lock e delle risorse necessarie per gestirli. I blocchi di tabella e di pagina ad alto livello comportano invece una diminuzione dell'overhead, ma a spese della concorrenza.

Il motore di database usa una strategia di blocco dinamico per determinare la combinazione di blocchi più efficiente. Il tipo di blocco più appropriato durante l'esecuzione di una particolare query viene determinato in modo automatico dal motore di database in base alle caratteristiche dello schema e della query. Ad esempio, per ridurre il sovraccarico del blocco, l'utilità di ottimizzazione potrebbe scegliere blocchi a livello di pagina in un indice durante l'esecuzione di una scansione dell'indice.

Partizionamento dei blocchi

Nei sistemi di computer di grandi dimensioni, i blocchi su oggetti di riferimento utilizzati di frequente possono creare colli di bottiglia a livello delle prestazioni poiché l'acquisizione e il rilascio di blocchi determinano la contesa sulle risorse di bloccaggio interne. Il partizionamento dei blocchi migliora le prestazioni poiché suddivide una singola risorsa di blocco in più risorse di blocco. Questa funzionalità è disponibile solo per i sistemi con 16 o più CPU logiche e viene abilitata automaticamente e non può essere disabilitata. È possibile partizionare solo i blocchi oggetto. I blocchi oggetto con un sottotipo non sono partizionati. Per altre informazioni, vedere sys.dm_tran_locks (Transact-SQL).

Informazioni sul partizionamento dei lock

Le attività di blocco accedono a numerose risorse condivise, due delle quali vengono ottimizzate dal partizionamento dei blocchi:

• Spinlock

  Consente di controllare l'accesso a una risorsa di blocco, ad esempio una riga o una tabella.

  Senza il partizionamento dei blocchi, uno spinlock gestisce tutte le richieste di blocco per una singola risorsa di blocco. Nei sistemi con un volume di attività elevato, si può verificare una contesa mentre le richieste di blocco aspettano che lo spinlock diventi disponibile. In questo caso, l'acquisizione dei blocchi può determinare un collo di bottiglia e influire negativamente sulle prestazioni.

  Il partizionamento dei blocchi suddivide una singola risorsa di blocco in più risorse di blocco per ridurre la contesa per una singola risorsa di blocco e distribuisce il carico su più spinlock.

• Memory

  Viene utilizzato per archiviare le strutture delle risorse di blocco.

  Dopo che lo spinlock è stato acquisito, le strutture di blocco vengono archiviate in memoria ed è quindi possibile accedervi ed eventualmente modificarle. La distribuzione dell'accesso di blocco tra più risorse consente di eliminare la necessità di trasferire blocchi di memoria tra CPU, migliorando così le prestazioni.

Implementare e monitorare il partizionamento delle serrature

Il partizionamento dei lock è attivato per impostazione predefinita nei sistemi con 16 o più CPU. Quando è abilitato il partizionamento del blocco, nel log degli errori di SQL Server viene registrato un messaggio informativo.

Se vengono acquisiti blocchi su una risorsa partizionata:

• Su una singola partizione vengono acquisite solo le modalità di blocco NL, Sch-S, IS, IU e IX.

• L'acquisizione di lucchetti condivisi (S), esclusivi (X) e di lucchetti in modalità diverse da NL, Sch-S, IS, IU e IX deve essere eseguita su tutte le partizioni, a partire dalla partizione con ID 0 e seguendo l'ordine degli ID di partizione. Questi blocchi su una risorsa partizionata usano più memoria rispetto ai blocchi nella stessa modalità in una risorsa non partizionata, poiché ogni partizione è effettivamente un blocco separato. L'aumento della memoria è determinato dal numero delle partizioni. I contatori delle prestazioni di blocco di SQL Server visualizzano informazioni sulla memoria usata dai blocchi partizionati e non partizionati.

All'avvio della transazione, viene assegnata una transazione a una partizione. Tutte le richieste di blocco partizionabili relative alla transazione utilizzano la partizione assegnata a tale transazione. In questo modo, l'accesso alle risorse di blocco dello stesso oggetto da parte di transazioni diverse viene distribuito su partizioni diverse.

Nella colonna resource_lock_partition della Dynamic Management View sys.dm_tran_locks è disponibile l'ID di partizione di blocco per una risorsa partizionata per blocco. Per altre informazioni, vedere sys.dm_tran_locks (Transact-SQL).

Lavorare con il partizionamento dei blocchi

Negli esempi di codice seguenti viene illustrato il partizionamento dei lock. Due transazioni vengono eseguite in due sessioni diverse allo scopo di illustrare il partizionamento dei blocchi in un computer con 16 CPU.

Le seguenti istruzioni di Transact SQL creano gli oggetti di test utilizzati negli esempi successivi.

-- Create a test table.
CREATE TABLE TestTable
(
col1 int
);
GO

-- Create a clustered index on the table.
CREATE CLUSTERED INDEX ci_TestTable ON TestTable (col1);
GO

-- Populate the table.
INSERT INTO TestTable
VALUES (1);
GO

Esempio A

Sessione 1:

Un'istruzione SELECT viene eseguita in una transazione. A causa dell'hint di blocco HOLDLOCK, questa istruzione acquisisce e mantiene un blocco intenzionale condiviso (IS) sulla tabella (in questo esempio vengono ignorati i blocchi di riga e di pagina). Il lock IS viene acquisito solo sulla partizione assegnata alla transazione. Per questo esempio si presuppone che il blocco venga acquisito sull'ID partizione IS 7.

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

Sessione 2:

Viene avviata una transazione e l'istruzione SELECT in esecuzione in questa transazione acquisisce e mantiene un blocco condiviso (S) sulla tabella. Il blocco S viene acquisito in tutte le partizioni, che comporta più blocchi di tabella, uno per ogni partizione. Ad esempio, in un sistema con 16 CPU verranno assegnati 16 lock S agli ID di partizione da 0 a 15. Poiché il blocco S è compatibile con il blocco mantenuto dalla transazione nella sessione 1 sull'ID di partizione IS 7, non esiste alcun blocco tra le transazioni.

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCK, HOLDLOCK);

Sessione 1:

L'istruzione SELECT seguente viene eseguita nella transazione ancora attiva nella sessione 1. A causa dell'indicazione esclusiva di blocco della tabella (X), la transazione tenta di acquisire un blocco X sulla tabella. Tuttavia, il blocco S tenuto dalla transazione nella sessione 2 blocca il blocco X alla partizione con ID 0.

SELECT col1
FROM TestTable
WITH (TABLOCKX);

Esempio B

Sessione 1:

Un'istruzione SELECT viene eseguita all'interno di una transazione. A causa dell'hint di blocco HOLDLOCK, questa istruzione acquisisce e mantiene un blocco intenzionale condiviso (IS) sulla tabella (in questo esempio vengono ignorati i blocchi di riga e di pagina). Il lock IS viene acquisito solo sulla partizione assegnata alla transazione. Per questo esempio si presuppone che il blocco venga acquisito sull'ID partizione 6 IS.

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

Sessione 2:

Un'istruzione SELECT viene eseguita nell'ambito di una transazione. A causa dell'indicazione di blocco TABLOCKX, la transazione tenta di acquisire un blocco esclusivo (X) sulla tabella. Tenere presente che il blocco X deve essere acquisito per tutte le partizioni a partire dall'ID di partizione 0. Il blocco X viene acquisito su tutti gli ID di partizione 0-5, ma è bloccato dal blocco IS acquisito sull'ID di partizione 6.

Negli ID di partizione da 7 a 15 che non sono stati ancora raggiunti dal blocco X, le altre transazioni possono continuare ad acquisire blocchi.

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCKX, HOLDLOCK);

Livelli di isolamento basati sul controllo delle versioni delle righe nel Motore di database

A partire da SQL Server 2005 (9.x), il Motore di Database offre un'implementazione di un livello di isolamento della transazione esistente, READ COMMITTED, che fornisce uno snapshot a livello di istruzione tramite versionamento delle righe. Nel motore di database è stato inoltre introdotto un nuovo livello di isolamento della transazione, SNAPSHOT, che offre uno snapshot a livello di transazione anch'esso tramite il controllo delle versioni delle righe.

Il controllo delle versioni delle righe è un quadro generale in SQL Server che richiama un meccanismo copia-su-scrittura quando una riga viene modificata o eliminata. È pertanto necessario che durante l'esecuzione della transazione, la versione precedente della riga sia disponibile per le transazioni che richiedono un precedente stato consistente dal punto di vista transazionale. Il versionamento delle righe viene utilizzato per implementare le seguenti funzionalità:

• Compilare le tabelle inserted e deleted nei trigger. Le righe modificate dal trigger vengono versionate. Questo include le righe modificate dall'istruzione che ha avviato il trigger, nonché qualsiasi modifica ai dati eseguita dal trigger.
• Supportare più set di risultati attivi (MARS). Se una sessione MARS rilascia un'istruzione di modifica dei dati (ad esempio INSERT, UPDATEo DELETE) alla volta in cui è presente un set di risultati attivo, le righe interessate dall'istruzione di modifica vengono modificate.
• Supportare operazioni di indice che specificano l'opzione ONLINE.
• Supportare i livelli di isolamento delle transazioni basate sul controllo delle versioni delle righe:
  • Una nuova implementazione del livello di isolamento READ COMMITTED che utilizza il versionamento delle righe per la consistenza della lettura a livello di istruzione.
  • Un nuovo livello di isolamento, SNAPSHOT, per offrire consistenza di lettura a livello di transazione.

Le versioni di riga vengono archiviate in un repository delle versioni. Se ripristino accelerato del database (ADR) è abilitato in un database, l'archivio versioni viene creato in tale database. In caso contrario, l'archivio delle versioni viene creato nel database tempdb.

Il database deve disporre di spazio sufficiente per l'archivio delle versioni. Quando l'archivio delle versioni è in tempdbe il tempdb database è pieno, le operazioni di aggiornamento interrompono la generazione delle versioni ma continuano a funzionare correttamente, ma le operazioni di lettura potrebbero non riuscire perché non esiste una versione di riga specifica necessaria. Questo ha effetto su operazioni come i trigger, MARS e l'indicizzazione online.

Quando si usa ADR e lo store delle versioni è completo, le operazioni di lettura continuano a funzionare, ma le operazioni di scrittura che generano versioni, come UPDATE e DELETE, falliscono. Le operazioni INSERT continuano a avere esito positivo se il database dispone di spazio sufficiente.

L'utilizzo del versionamento delle righe per transazioni READ COMMITTED e SNAPSHOT è un processo in due passaggi:

1. Impostare una o entrambe le opzioni di database READ_COMMITTED_SNAPSHOT e ALLOW_SNAPSHOT_ISOLATION su ON.

2. Impostazione del corretto livello di isolamento delle transazioni in un'applicazione:

   • Quando l'opzione di database READ_COMMITTED_SNAPSHOT è ON, le transazioni che impostano il livello di isolamento READ COMMITTED usano il controllo delle versioni delle righe.
   • Quando l'opzione di database ALLOW_SNAPSHOT_ISOLATION è ON, le transazioni possono impostare il livello di isolamento SNAPSHOT.

Quando l'opzione di database READ_COMMITTED_SNAPSHOT o ALLOW_SNAPSHOT_ISOLATION è impostata su ON, il motore di database assegna un numero di sequenza della transazione (XSN) a ogni transazione che modifica i dati usando il controllo delle versioni delle righe. Le transazioni vengono avviate all'esecuzione di un'istruzione BEGIN TRANSACTION. Tuttavia, il numero di sequenza della transazione inizia con la prima operazione di lettura o scrittura dopo l'istruzione BEGIN TRANSACTION. Il numero di sequenza della transazione viene incrementato di uno ogni volta che viene assegnato.

Quando l'opzione di database READ_COMMITTED_SNAPSHOT o ALLOW_SNAPSHOT_ISOLATION è impostata su ON, le copie logiche (versioni) vengono mantenute per tutte le modifiche ai dati eseguite nel database. Ogni volta che una riga viene modificata da una transazione specifica, l'istanza del motore di database archivia una versione della precedente immagine della riga di cui è stato eseguito il commit nell’archivio della versione. Ogni versione è contrassegnata con il numero di sequenza della transazione che ha eseguito la modifica. Le versioni delle righe modificate vengono concatenate utilizzando un elenco di collegamenti. Il valore di riga più recente viene sempre archiviato nel database corrente e concatenato alle righe versionate nell’archivio della versione.

Le versioni delle righe vengono mantenute abbastanza a lungo da soddisfare i requisiti delle transazioni in esecuzione con livelli di isolamento basati sul versionamento delle righe. Il motore di database tiene traccia del primo numero di sequenza transazionale utile ed elimina periodicamente tutte le versioni di riga contrassegnate con numeri di sequenza transazionale inferiori al primo numero di sequenza utile.

Quando entrambe le opzioni di database sono impostate su OFF, solo le righe modificate da trigger o sessioni MARS, oppure lette da operazioni di indice online, vengono sottoposte al controllo delle versioni. Queste versioni di riga vengono rilasciate quando non sono più necessarie. Un processo in background rimuove le versioni di riga non aggiornate.

Comportamento durante la lettura dei dati

Quando le transazioni in esecuzione con isolamento basato sul controllo delle versioni delle righe leggono i dati, le operazioni di lettura non acquisiscono blocchi condivisi (S) sui dati letti e pertanto non bloccano le transazioni che modificano i dati. Inoltre, l'overhead delle risorse di blocco è ridotto al minimo in quanto il numero di blocchi acquisiti si riduce. L'isolamento READ COMMITTED tramite versionamento delle righe e l’isolamento SNAPSHOT sono pensati per assicurare la consistenza a livello di istruzione o di transazione dei dati versionati.

Tutte le query, incluse le transazioni in esecuzione con i livelli di isolamento basati sul controllo delle versioni delle righe, acquisiscono blocchi di stabilità dello schema (Sch-S) durante le fasi di compilazione ed esecuzione. Per questo motivo, le query vengono bloccate quando una transazione simultanea mantiene attivo un blocco di modifica dello schema (Sch-M) sulla tabella. Ad esempio, un'operazione DDL (Data Definition Language) acquisisce un blocco Sch-M prima di modificare le informazioni dello schema della tabella. Le transazioni, incluse quelle in esecuzione con un livello di isolamento basato sul controllo delle versioni delle righe, vengono quindi bloccate durante il tentativo di acquisizione di un blocco Sch-S. Una query con blocco Sch-S blocca invece le transazioni simultanee che tentano di acquisire un blocco Sch-M.

Quando una transazione che utilizza il livello di isolamento SNAPSHOT ha inizio, l'istanza del motore di database registra tutte le transazioni attualmente attive. Quando la transazione SNAPSHOT legge una riga che include una catena delle versioni, segue la catena e recupera la riga per la quale il numero di sequenza della transazione è:

• Il valore più vicino ma inferiore al numero di sequenza della transazione di snapshot che sta leggendo la riga.

• Non incluso nell'elenco delle transazioni attive quando la transazione snapshot ha avuto inizio.

Le operazioni di lettura eseguite da una transazione SNAPSHOT recuperano l'ultima versione di ogni riga di cui è stato eseguito il commit al momento dell'avvio della transazione SNAPSHOT. Questo determina uno snapshot dei dati, consistente dal punto di vista transazionale, nello stato in cui si trovavano all'inizio della transazione.

READ COMMITTED le transazioni che utilizzano il versionamento delle righe funzionano in modo analogo. La differenza è rappresentata dal fatto che la transazione READ COMMITTED non utilizza un proprio numero di sequenza della transazione nella scelta delle versioni delle righe. A ogni avvio di un'istruzione, la transazione READ COMMITTED legge l'ultimo numero di sequenza della transazione generato per l'istanza del motore di database. Si tratta del numero di sequenza della transazione utilizzato per selezionare le versioni di riga di quell'istruzione. Questo consente alle transazioni READ COMMITTED di visualizzare uno snapshot dei dati nello stato in cui si trovavano all'inizio di ogni istruzione.

Comportamento durante la modifica dei dati

Il comportamento delle scritture di dati è diverso con e senza il blocco ottimizzato abilitato.

Modificare i dati senza il blocco ottimizzato

In una transazione READ COMMITTED che utilizza il controllo delle versioni delle righe, la selezione delle righe da aggiornare viene eseguita tramite un'analisi di blocco in cui un blocco di aggiornamento (U) viene acquisito sulla riga di dati durante la lettura dei valori dei dati. Questo avviene anche per una transazione READ COMMITTED che non utilizza il controllo delle versioni delle righe. Se la riga di dati non rispetta i criteri di aggiornamento, il blocco di aggiornamento viene rilasciato sulla riga e la riga successiva viene bloccata e analizzata.

Le transazioni eseguite con isolamento SNAPSHOT adottano un approccio ottimistico alla modifica dei dati, in quanto acquisiscono blocchi sui dati prima di eseguire la modifica con l'unico scopo di imporre i vincoli. In caso contrario, i blocchi non vengono acquisiti sui dati finché i dati non devono essere modificati. Quando una riga di dati rispetta i criteri di aggiornamento, la transazione SNAPSHOT verifica che la riga di dati non sia stata modificata da una transazione simultanea che abbia eseguito il commit dopo l'inizio della transazione SNAPSHOT. Se la riga di dati è stata modificata all'esterno della transazione SNAPSHOT, si verifica un conflitto di aggiornamento e la transazione SNAPSHOT viene interrotta. Il conflitto di aggiornamento viene gestito dal motore di database e non è possibile disabilitare il rilevamento dei conflitti di aggiornamento.

Modificare i dati con il blocco ottimizzato

Quando il blocco ottimizzato e l'opzione di database READ_COMMITTED_SNAPSHOT (RCSI) sono abilitati e si usa il livello di isolamento predefinito READ COMMITTED, i lettori non acquisiscono blocchi, mentre i writer acquisiscono blocchi di breve durata a basso livello, anziché blocchi che scadono alla fine della transazione.

L'abilitazione di RCSI è consigliata per la maggiore efficienza con il blocco ottimizzato. 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, sia per i lettori sia per gli scrittori, con conseguente aumento del blocco e della memoria per i blocchi.

Quando RCSI è abilitato e si utilizza il livello di isolamento predefinito READ COMMITTED, i writer qualificano le righe secondo il predicato basato sulla versione più recente della riga di cui è stato eseguito il commit, senza acquisire lock U. Una query attende solo se la riga è idonea ed è presente un'altra transazione di scrittura attiva in tale riga o pagina. 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.

Se vengono rilevati conflitti di aggiornamento con RCSI e nel livello di isolamento predefinito READ COMMITTED , vengono gestiti e ritentati automaticamente senza alcun impatto sui carichi di lavoro dei clienti.

Quando il blocco ottimizzato è abilitato e si usa il livello di isolamento SNAPSHOT, il comportamento dei conflitti di aggiornamento è uguale a quando non è presente il blocco ottimizzato. I conflitti di aggiornamento devono essere gestiti e ritentati dall'applicazione.

Riepilogo del comportamento

Nella tabella seguente vengono riassunte le differenze tra l'isolamento SNAPSHOT e l'isolamento READ COMMITTED tramite il versionamento delle righe.

Uso delle risorse per il versionamento delle righe

L'infrastruttura di controllo delle versioni delle righe supporta le seguenti caratteristiche del motore di database:

• Triggers
• MARS (Set di Risultati Attivi Multipli)
• Indicizzazione online

Il framework di controllo delle versioni delle righe supporta inoltre i seguenti livelli di isolamento delle transazioni basati sulla versione delle righe:

• Quando l'opzione di database READ_COMMITTED_SNAPSHOT è impostata su ON, le transazioni READ_COMMITTED offrono consistenza di lettura a livello di dichiarazione utilizzando il versionamento delle righe.
• Quando l'opzione di database ALLOW_SNAPSHOT_ISOLATION è impostata su ON, le SNAPSHOT transazioni offrono coerenza di lettura a livello di transazioni tramite il controllo delle versioni delle righe.

I livelli di isolamento basati sul controllo delle versioni delle righe consentono di ridurre il numero di blocchi acquisiti per transazione eliminando l'utilizzo di blocchi condivisi nelle operazioni di lettura. In questo modo, vengono garantite prestazioni di sistema migliori grazie alla riduzione delle risorse utilizzate per la gestione dei blocchi. Le prestazioni risultano inoltre migliorate grazie al minor numero di volte in cui una transazione viene bloccata dai blocchi acquisiti da altre transazioni.

I livelli di isolamento basati sul controllo delle versioni delle righe consentono di ridurre le risorse necessarie per le modifiche dei dati. L'attivazione di queste opzioni comporta la versioning di tutte le modifiche dei dati per il database. Una copia dei dati precedente la modifica viene archiviata nell’archivio di versione anche quando non è presente nessuna transazione attiva che utilizza l'isolamento basato sul controllo delle versioni delle righe. I dati in seguito alla modifica includono un puntatore ai dati versionati nell'archiviazione delle versioni. Per oggetti di grandi dimensioni, nell’archivio di versione viene copiata solo la parte dell'oggetto modificata.

Spazio utilizzato in tempdb

Per ogni istanza del motore di database, nell’archivio di versione deve essere disponibile una quantità di spazio sufficiente per contenere le versioni di riga. L'amministratore del database deve assicurarsi che tempdb e altri database (se LAR è abilitata) dispongano di spazio sufficiente per supportare l'archivio versioni. Esistono due tipi di archivi di versione:

• L'archivio delle versioni per la costruzione degli indici online viene utilizzato per la compilazione degli indici online.
• L'archivio versioni comune viene utilizzato per tutte le altre operazioni di modifica dei dati.

Le versioni di riga devono essere archiviate per tutto il tempo necessario a una transazione attiva per accedervi. Periodicamente, un thread in background rimuove le versioni di riga non più necessarie e libera spazio nello store delle versioni. Una transazione con esecuzione prolungata impedisce che venga liberato lo spazio nell'archivio versioni se si verificano le condizioni seguenti:

• Viene utilizzato l'isolamento basato sul controllo delle versioni delle righe.
• Vengono utilizzati trigger, MARS o operazioni di compilazione di indici online.
• Vengono generate versioni di riga.

Se viene esaurito tutto lo spazio nell’archivio di versione tempdb e tempdb, il motore di database forza la compattazione degli archivi versioni. Durante il processo di compattazione, le transazioni con esecuzione più lunga che non hanno ancora generato versioni di riga vengono contrassegnate come vittime. Nel log degli errori viene generato un messaggio 3967 per ogni transazione vittima. Se una transazione viene contrassegnata come vittima, non può più leggere le versioni di riga nello store delle versioni. Quando la transazione tenta di leggere le versioni di riga, viene generato il messaggio di errore 3966 e viene eseguito il rollback della transazione. Se il processo di compattazione ha esito positivo, lo spazio viene reso disponibile in tempdb. In caso contrario, lo spazio di tempdb si esaurisce e si verificano le situazioni seguenti:

• Le operazioni di scrittura continuano a essere eseguite ma non generano versioni. Nel log degli errori viene visualizzato un messaggio informativo (3959), ma la transazione che scrive i dati non subisce alcun effetto.

• Transazioni che tentano di accedere alle versioni di riga che non sono state generate a causa di un tempdb rollback completo e terminano con un errore 3958.

Spazio utilizzato nelle righe di dati

Ogni riga del database può usare fino a 14 byte alla fine della riga per le informazioni sul controllo delle versioni delle righe. Le informazioni sul versionamento delle righe contengono il numero di sequenza della transazione che ha eseguito il commit della versione e il puntatore alla riga versionata. Questi 14 byte vengono aggiunti alla prima modifica della riga oppure quando viene inserita una nuova riga se si verifica una delle condizioni seguenti:

• Le opzioni READ_COMMITTED_SNAPSHOT o ALLOW_SNAPSHOT_ISOLATION sono impostate su ON.
• La tabella ha un trigger.
• Viene utilizzato MARS (Multiple Active Results Set).
• Nella tabella sono in esecuzione operazioni di compilazione di indici online.
• Il ripristino accelerato del database è abilitato.

I 14 byte vengono rimossi dalla riga del database alla prima modifica della riga quando si verificano tutte le condizioni seguenti:

• Le opzioni READ_COMMITTED_SNAPSHOT e ALLOW_SNAPSHOT_ISOLATION sono impostate su OFF.
• Il trigger non è più presente nella tabella.
• MARS non viene utilizzato.
• Le operazioni di compilazione degli indici online non sono attualmente in esecuzione.
• Il ripristino accelerato del database è disabilitato .

Se si utilizza una qualsiasi delle caratteristiche di controllo delle versioni delle righe, potrebbe essere necessario allocare spazio su disco sufficiente per includere i 14 byte per ogni riga del database. L'aggiunta delle informazioni relative al controllo delle versioni delle righe può determinare la divisione delle pagine di indice o l'allocazione di una nuova pagina di dati se nella pagina corrente non è disponibile spazio sufficiente. Se la lunghezza media della riga, ad esempio, è 100 byte, i 14 byte aggiuntivi provocano un aumento del 14 percento della tabella esistente.

La riduzione del fattore di riempimento potrebbe impedire o ridurre la frammentazione di pagine di indice. Per visualizzare le informazioni sulla densità attuale delle pagine per i dati e gli indici di una tabella o vista, è possibile usare sys.dm_db_index_physical_stats.

Spazio usato dall'archivio versioni permanente (PVS)

Quando ADR è abilitato, le versioni di riga possono essere archiviate nell'archivio versioni permanenti (PVS) in uno dei modi seguenti, a seconda delle dimensioni della riga prima della modifica:

• Se le dimensioni sono ridotte, l'intera versione precedente della riga viene archiviata come parte della riga modificata.
• Se la dimensione è intermedia, la differenza tra la versione precedente della riga e la riga modificata viene archiviata come parte della riga modificata. La differenza viene costruita in modo da consentire al motore di database di ricostruire l'intera versione precedente della riga, se necessario.
• Se le dimensioni sono elevate, l'intera versione precedente della riga viene archiviata in una tabella interna separata.

I primi due metodi vengono chiamati archiviazione delle versioni nella riga. L'ultimo metodo viene chiamato archiviazione delle versioni fuori riga. Quando le versioni in riga non sono più necessarie, vengono rimosse per liberare spazio nelle pagine. Analogamente, le pagine nella tabella interna contenenti le versioni non più necessarie fuori riga vengono rimosse dal pulitore di versioni.

L'archiviazione delle versioni di riga come parte della riga ottimizza la lettura dei dati per le transazioni che devono accedere alle versioni delle righe. Se una versione viene archiviata in riga, non è necessaria una lettura separata di una pagina PVS fuori riga.

La DMV sys.dm_db_index_physical_stats fornisce il numero e il tipo di versioni archiviate in riga e fuori riga per una partizione di un indice. Le dimensioni totali dei dati della versione archiviati nella riga vengono segnalate nella colonna total_inrow_version_payload_size_in_bytes.

Le dimensioni dell'archiviazione delle versioni fuori riga sono riportate nella colonna persistent_version_store_size_kb della DMV sys.dm_tran_persistent_version_store_stats.

Spazio usato in oggetti grandi

Il motore di database supporta vari tipi di dati in grado di includere grandi stringhe di un massimo di 2 gigabyte (GB) di lunghezza, come: nvarchar(max), varchar(max), varbinary(max), ntext, text e image. I dati archiviati estesi per cui vengono utilizzati questi tipi di dati vengono archiviati in una serie di frammenti di dati collegati alla riga di dati. Le informazioni relative al controllo delle versioni delle righe vengono archiviate in ogni frammento utilizzato per archiviare tali stringhe estese. I frammenti di dati sono archiviati in una serie di pagine dedicate a oggetti di grandi dimensioni in una tabella.

Man mano che vengono aggiunti nuovi valori di grandi dimensioni a un database, vengono allocati usando un massimo di 8040 byte di dati per frammento. Le versioni precedenti del motore di database hanno archiviato fino a 8.080 byte di ntextdati , texto image per frammento.

I dati LOB (Large Object) esistenti ntext, text e image non vengono aggiornati per lasciare spazio alle informazioni relative al controllo delle versioni delle righe quando un database viene aggiornato a SQL Server da una versione precedente di SQL Server. Tuttavia, la prima volta che i dati LOB vengono modificati, vengono aggiornati dinamicamente per abilitare l'archiviazione delle informazioni sul controllo delle versioni. Ciò si verifica anche se le versioni di riga non vengono generate. Dopo l'aggiornamento dei dati LOB, il numero massimo di byte archiviati per frammento viene ridotto da 8.080 byte a 8.040 byte. Il processo di aggiornamento equivale all'eliminazione del valore LOB e al reinserimento dello stesso valore. I dati LOB vengono aggiornati anche se viene modificato un solo byte. Si tratta di un'operazione una tantum per ogni ntextcolonna , text, o image , ma ogni operazione potrebbe generare una grande quantità di allocazioni di pagine e attività di I/O a seconda delle dimensioni dei dati LOB. Potrebbe anche generare una grande quantità di attività di registrazione se la modifica viene registrata completamente. Viene eseguita la registrazione minima delle operazioni WRITETEXT e UPDATETEXT se il modello di recupero del database non è impostato su FULL.

Per soddisfare questo requisito, è necessario allocare spazio su disco sufficiente.

Monitorare il controllo delle versioni delle righe e l'archivio versioni

Per monitorare le versioni delle righe, l'archivio versioni e i processi di isolamento dello snapshot in termini di prestazioni e problemi, il motore di database fornisce strumenti sotto forma di viste di gestione dinamica (DMV) e contatori delle prestazioni.

DMVs

I seguenti DMV forniscono informazioni sullo stato attuale del sistema di tempdb e sull'archivio versioni, nonché sulle transazioni che fanno uso del versionamento delle righe.

• sys.dm_db_file_space_usage. Restituisce informazioni sull'utilizzo dello spazio per ogni file nel database. Per altre informazioni, vedere sys.dm_db_file_space_usage (Transact-SQL).

• sys.dm_db_session_space_usage. Restituisce informazioni relative alle attività di allocazione e deallocazione delle pagine per sessione per il database. Per altre informazioni, vedere sys.dm_db_session_space_usage (Transact-SQL).

• sys.dm_db_task_space_usage. Restituisce informazioni sulle allocazioni e deallocazioni delle pagine per compito nel database. Per altre informazioni, vedere sys.dm_db_task_space_usage (Transact-SQL).

• sys.dm_tran_top_version_generators. Restituisce una tabella virtuale per gli oggetti che producono il maggior numero di versioni nell'archivio delle versioni. Raggruppa le principali 256 lunghezze dei record aggregati per database_id e rowset_id. Utilizzare questa funzione per trovare i maggiori utilizzatori dell'archivio delle versioni. Si applica all'archivio di versione tempdb solo. Per altre informazioni, vedere sys.dm_tran_top_version_generators (Transact-SQL).

• sys.dm_tran_version_store. Restituisce una tabella virtuale in cui vengono visualizzati tutti i record di versione nell'archivio versioni comune. Si applica solo all'archivio di versione tempdb. Per altre informazioni, vedere sys.dm_tran_version_store (Transact-SQL).

• sys.dm_tran_version_store_space_usage. Restituisce una tabella virtuale che visualizza lo spazio totale in tempdb usato dai record dell'archivio versioni per ogni database. Si applica solo all'archivio di versione tempdb. Per altre informazioni, vedere sys.dm_tran_version_store_space_usage (Transact-SQL).

  Note

  L'esecuzione di query sys.dm_tran_top_version_generators e sys.dm_tran_version_store può essere costosa, poiché entrambi analizzano l'intero archivio delle versioni, che potrebbe essere molto grande. sys.dm_tran_version_store_space_usage è efficiente e non è costoso da eseguire perché non passa attraverso singoli record dell'archivio versioni e restituisce invece lo spazio di archiviazione delle versioni aggregato usato in tempdb per ogni database.

• sys.dm_tran_active_snapshot_database_transactions. Restituisce una tabella virtuale per tutte le transazioni attive in tutti i database dell'istanza di SQL Server che usano il controllo delle versioni delle righe. Le transazioni di sistema non vengono visualizzate in questa DMV. Per altre informazioni, vedere sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).

• sys.dm_tran_transactions_snapshot. Restituisce una tabella virtuale in cui vengono visualizzati gli snapshot creati da ogni transazione. Lo snapshot contiene il numero di sequenza delle transazioni attive che utilizzano il versioning delle righe. Per altre informazioni, vedere sys.dm_tran_transactions_snapshot (Transact-SQL).

• sys.dm_tran_current_transaction. Restituisce una singola riga in cui vengono visualizzate le informazioni sullo stato correlate al controllo delle versioni delle righe per la transazione nella sessione corrente. Per altre informazioni, vedere sys.dm_tran_current_transaction (Transact-SQL).

• sys.dm_tran_current_snapshot. Restituisce una tabella virtuale in cui vengono visualizzate tutte le transazioni attive al momento in cui è stata avviata la transazione di isolamento dello snapshot corrente. Se la transazione corrente utilizza l'isolamento dello snapshot, questa funzione non restituisce alcuna riga. La DMV sys.dm_tran_current_snapshot è simile a sys.dm_tran_transactions_snapshot, se non per il fatto che restituisce solo le transazioni attive per lo snapshot attuale. Per altre informazioni, vedere sys.dm_tran_current_snapshot (Transact-SQL).

• sys.dm_tran_persistent_version_store_stats. Restituisce le statistiche per l'archivio delle versioni persistenti in ogni database utilizzato quando il ripristino accelerato del database è abilitato. Per altre informazioni, vedere sys.dm_tran_persistent_version_store_stats (Transact-SQL).

Contatori delle prestazioni

I seguenti contatori delle prestazioni consentono di monitorare l'archivio delle versioni in tempdb, così come le transazioni che utilizzano il versionamento delle righe. I contatori delle prestazioni sono inclusi nell'oggetto prestazione SQLServer:Transactions.

• Spazio disponibile in tempdb (KB). Esegue il monitoraggio della quantità, in kilobyte (KB), di spazio disponibile nel database tempdb. Per gestire l'archivio versioni che supporta l'isolamento mediante snapshot, lo spazio disponibile in tempdb deve essere sufficiente.

  Nella formula seguente viene eseguito un calcolo approssimativo delle dimensioni dell'archivio versioni. Per le transazioni a esecuzione prolungata, potrebbe essere utile monitorare il tasso di generazione e pulizia per stimare la dimensione massima dell'archivio delle versioni.

  [dimensioni dell'archivio versioni comune] = 2 * [dati dell'archivio versioni generati al minuto] * [tempo massimo (in minuti) di esecuzione della transazione]

  Il tempo di esecuzione più lungo delle transazioni non deve includere compilazioni di indici online. Poiché queste operazioni potrebbero richiedere molto tempo su tabelle molto grandi, le compilazioni di indici online usano un archivio versioni separato. Le dimensioni approssimative dell'archivio versioni di creazione degli indici online equivalgono alla quantità di dati modificati nella tabella, inclusi tutti gli indici, mentre la creazione dell'indice online è attiva.

• Dimensione archivio versioni (KB). Esegue il monitoraggio delle dimensioni in KB di tutti gli archivi di versione in tempdb. Queste informazioni consentono di determinare la quantità di spazio necessario nel database tempdb per l'archivio versioni. Il monitoraggio di questo contatore per un certo periodo di tempo offre una stima utile dell'ulteriore spazio necessario per tempdb.

• Frequenza generazione versioni (KB/s). Esegue il monitoraggio della frequenza di generazione delle versioni in KB al secondo in tutti gli archivi di versione in tempdb.

• Velocità pulizia versioni (KB/s). Esegue il monitoraggio della frequenza di pulizia delle versioni in KB al secondo in tutti gli archivi di versione in tempdb.

  Note

  Le informazioni incluse in Frequenza generazione versioni (KB/s) e Frequenza pulizia versioni (KB/s) possono essere utilizzate per prevedere i requisiti di spazio tempdb.

• Conteggio unità dello store delle versioni. Esegue il monitoraggio del conteggio delle unità dell'archivio versioni.

• Creazione unità archivio versioni. Monitora il numero totale di unità di archiviazione delle versioni create per conservare le versioni di riga da quando è stata avviata l'istanza.

• Troncamento unità di archiviazione versioni. Esegue il monitoraggio del numero totale di unità dell'archivio versioni troncate dal momento in cui è stata avviata l'istanza. Un'unità dell'archivio versioni viene troncata quando in SQL Server viene determinato che nessuna delle righe di versione archiviate nell'unità di archivio versioni è necessaria per eseguire transazioni attive.

• Percentuale di conflitto di aggiornamento. Esegue il monitoraggio del rapporto tra le transazione snapshot di aggiornamento in cui sono presenti conflitti di aggiornamento e il numero totale di transazioni snapshot di aggiornamento.

• Tempo massimo esecuzione transazione. Monitora il tempo massimo in secondi di qualsiasi transazione che utilizza il versionamento delle righe. Queste informazioni possono essere utilizzate per determinare se alcune transazioni vengono eseguite in una quantità di tempo inaspettata.

• Transactions. Esegue il monitoraggio del numero totale di transazioni attive. Non sono incluse le transazioni di sistema.

• Transazioni snapshot. Monitora il numero totale di transazioni snapshot attive.

• Aggiorna le transazioni snapshot. Esegue il monitoraggio del numero totale di transazioni snapshot attive tramite cui vengono eseguite operazioni di aggiornamento.

• Transazioni di versione non snapshot. Esegue il monitoraggio del numero totale di transazioni attive non snapshot che generano record di versione.

  Note

  La somma di Transazioni snapshot di aggiornamento e Transazioni di versione non snapshot rappresenta il numero totale di transazioni coinvolte nella generazione delle versioni. La differenza tra le transazioni snapshot e le transazioni snapshot di aggiornamento rappresenta il numero di transazioni snapshot di sola lettura.

Esempio di livello di isolamento basato sul versionamento delle righe

Nell'esempio seguente vengono illustrate le differenze di comportamento tra le transazioni di isolamento SNAPSHOT e le transazioni READ COMMITTED che utilizzano il controllo delle versioni delle righe.

A. Utilizzo dell'isolamento dello SNAPSHOT

In questo esempio, una transazione in esecuzione sotto l'isolamento SNAPSHOT legge dati che vengono poi modificati da un'altra transazione. La transazione SNAPSHOT non blocca l'operazione di aggiornamento eseguita dall'altra transazione e continua a leggere dati dalla riga con versione, ignorando la modifica dei dati. Quando, tuttavia, la transazione SNAPSHOT tenta di modificare dati che sono già stati modificati da un'altra transazione, viene generato un errore e la transazione SNAPSHOT termina.

Nella sessione 1:

USE AdventureWorks2022;
GO

-- Enable snapshot isolation on the database.
ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Start a snapshot transaction
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Nella sessione 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under snapshot isolation shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Nella sessione 1:

-- Reissue the SELECT statement - this shows
-- the employee having 48 vacation hours. The
-- snapshot transaction is still reading data from
-- the older, versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Nella sessione 2:

-- Commit the transaction; this commits the data
-- modification.
COMMIT TRANSACTION;
GO

Nella sessione 1:

-- Reissue the SELECT statement - this still
-- shows the employee having 48 vacation hours
-- even after the other transaction has committed
-- the data modification.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- Because the data has been modified outside of the
-- snapshot transaction, any further data changes to
-- that data by the snapshot transaction will cause
-- the snapshot transaction to fail. This statement
-- will generate a 3960 error and the transaction will
-- terminate.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

B. Utilizzare l’isolamento READ COMMITTED con il controllo delle versioni delle righe

In questo esempio una transazione READ COMMITTED che utilizza il controllo delle versioni delle righe viene eseguita simultaneamente a un'altra transazione. La transazione READ COMMITTED si comporta in modo diverso da una transazione SNAPSHOT. Come una transazione SNAPSHOT, la transazione READ COMMITTED legge le righe con versione anche dopo che i dati sono stati modificati dall'altra transazione. Tuttavia, a differenza di una transazione SNAPSHOT, la transazione READ COMMITTED:

• Legge i dati modificati dopo che l'altra transazione ha eseguito il commit delle modifiche dei dati.
• È in grado di aggiornare i dati modificati dall'altra transazione in cui la SNAPSHOT transazione non è riuscita.

Nella sessione 1:

USE AdventureWorks2022;
GO

-- Enable READ_COMMITTED_SNAPSHOT on the database.
-- For this statement to succeed, this session
-- must be the only connection to the AdventureWorks2022
-- database.
ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
GO

-- Start a read-committed transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Nella sessione 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under read-committed using row versioning shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Nella sessione 1:

-- Reissue the SELECT statement - this still shows
-- the employee having 48 vacation hours. The
-- read-committed transaction is still reading data
-- from the versioned row and the other transaction
-- has not committed the data changes yet.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Nella sessione 2:

-- Commit the transaction.
COMMIT TRANSACTION;
GO

Nella sessione 1:

-- Reissue the SELECT statement which now shows the
-- employee having 40 vacation hours. Being
-- read-committed, this transaction is reading the
-- committed data. This is different from snapshot
-- isolation which reads from the versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- This statement, which caused the snapshot transaction
-- to fail, will succeed with read-committed using row versioning.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

Abilitare livelli di isolamento basati sul controllo delle versioni delle righe

Gli amministratori di database definiscono le impostazioni a livello di database per il controllo delle versioni delle righe usando le opzioni di database READ_COMMITTED_SNAPSHOT e ALLOW_SNAPSHOT_ISOLATION dell'istruzione ALTER DATABASE.

Quando l'opzione di database READ_COMMITTED_SNAPSHOT è impostata su ON, i meccanismi usati per supportare l'opzione vengono attivati immediatamente. Quando si imposta l'opzione READ_COMMITTED_SNAPSHOT, nel database è consentita solo la connessione che esegue il comando ALTER DATABASE. Nel database non devono essere presenti altre connessioni aperte fino al completamento del comando ALTER DATABASE. Non è necessario che il database sia in modalità utente singolo.

La seguente istruzione Transact-SQL abilita READ_COMMITTED_SNAPSHOT:

ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;

Quando l'opzione di database ALLOW_SNAPSHOT_ISOLATION è impostata su ON, l'istanza del motore di database non iniza a generare versioni di riga per i dati modificati fino al completamento di tutte le transazioni attive che includono dati modificati del database. Se sono presenti transazioni di modifica attive, il motore di database imposta lo stato dell'opzione su PENDING_ON. Dopo il completamento di tutte le transazioni di modifica, lo stato dell'opzione viene modificato in ON. Gli utenti non possono avviare una SNAPSHOT transazione nel database finché l'opzione non è ON. In maniera simile, i database passa a uno stato PENDING_OFF quando l'amministratore del database imposta l'opzione ALLOW_SNAPSHOT_ISOLATION su OFF.

La seguente istruzione Transact-SQL abilita ALLOW_SNAPSHOT_ISOLATION:

ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;

Nella tabella seguente sono inclusi e descritti gli stati possibili dell'opzione ALLOW_SNAPSHOT_ISOLATION. L'utilizzo dell'istruzione ALTER DATABASE con l'opzione ALLOW_SNAPSHOT_ISOLATION non blocca l'accesso in corso da parte degli utenti ai dati del database.

Utilizzare la vista del catalogo sys.databases per determinare lo stato di entrambe le opzioni di database per il controllo delle versioni delle righe.

Tutti gli aggiornamenti alle tabelle utente e ad alcune tabelle di sistema archiviate in master e msdb generano versioni di riga.

L'opzione ALLOW_SNAPSHOT_ISOLATION è impostata automaticamente su ON nei master database e msdb e non può essere disabilitata.

Gli utenti non possono impostare l'opzione READ_COMMITTED_SNAPSHOT su ON in master, tempdbo msdb.

Usare livelli di isolamento basati sul controllo delle versioni delle righe

L'infrastruttura di gestione delle versioni delle righe è sempre abilitata e viene utilizzata da più funzionalità. Oltre a fornire livelli di isolamento basati sulla versione delle righe, è utilizzato per supportare le modifiche apportate nei trigger e in più sessioni MARS (Multiple Active Result Sets), oltre a supportare le letture dei dati per le operazioni sugli indici online.

I livelli di isolamento basati sul controllo delle versioni delle righe sono attivati a livello di database. Le applicazioni che accedono agli oggetti da database abilitati possono eseguire query utilizzando i livelli di isolamento seguenti:

• READ COMMITTED, che utilizza il controllo delle versioni delle righe mediante l'impostazione dell'opzione di database READ_COMMITTED_SNAPSHOT su ON, come illustrato nell'esempio di codice seguente:

  ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
  

  Quando il database è abilitato per READ_COMMITTED_SNAPSHOT, tutte le query in esecuzione con il livello di isolamento READ COMMITTED utilizzano il controllo delle versioni delle righe, il che significa che le operazioni di lettura non bloccano quelle di aggiornamento.

• Isolamento SNAPSHOT, mediante l'impostazione dell'opzione di database ALLOW_SNAPSHOT_ISOLATION su ON, come illustrato nell'esempio di codice seguente:

  ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
  

  Quando si utilizzano query tra database, una transazione eseguita sotto l'isolamento SNAPSHOT può accedere alle tabelle nei database che hanno l'opzione ALLOW_SNAPSHOT_ISOLATION impostata su ON. Per accedere alle tabelle nei database che non dispongono dell'opzione di database ALLOW_SNAPSHOT_ISOLATION impostata su ON, è necessario modificare il livello di isolamento. Nell'esempio di codice seguente viene ad esempio illustrata un'istruzione SELECT che unisce due tabelle mentre è in esecuzione in una transazione SNAPSHOT. Una tabella appartiene a un database in cui non è stato attivato l'isolamento SNAPSHOT. Quando l'istruzione SELECT viene eseguita nel livello di isolamento SNAPSHOT, l'esecuzione non avviene correttamente.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  BEGIN TRANSACTION;
  
  SELECT t1.col5, t2.col5
  FROM Table1 as t1
  INNER JOIN SecondDB.dbo.Table2 as t2
  ON t1.col1 = t2.col2;
  

  Nell'esempio di codice seguente viene illustrata la stessa istruzione SELECT modificata in modo da cambiare il livello di isolamento della transazione a READ COMMITTED quando si accede a una tabella specifica. Grazie a questa modifica, l'istruzione SELECT viene eseguita correttamente.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  BEGIN TRANSACTION;
  
  SELECT t1.col5, t2.col5
  FROM Table1 as t1 WITH (READCOMMITTED)
  INNER JOIN SecondDB.dbo.Table2 as t2
  ON t1.col1 = t2.col2;
  

Limiti per le transazioni che utilizzano livelli di isolamento basati sul controllo delle versioni delle righe

Quando si utilizzano livelli di isolamento basati su versioni di riga, tenere conto dei seguenti limiti:

• READ_COMMITTED_SNAPSHOT non può essere abilitato in tempdb, msdbo master.

• Le tabelle temporanee globali sono archiviate in tempdb tempdb. Quando si accede a una tabella temporanea globale all'interno di una transazione SNAPSHOT, è necessario che si verifichi una delle condizioni seguenti:

  • Impostare l'opzione di database ALLOW_SNAPSHOT_ISOLATION su ON in tempdb.
  • Utilizzare un hint di isolamento per modificare il livello di isolamento per l'istruzione.

• Le transazioni SNAPSHOT hanno esito negativo quando:

  • Quando un database viene impostato come di sola lettura dopo l'avvio della transazione SNAPSHOT, ma prima dell'accesso al database da parte della transazione SNAPSHOT.
  • Se si accede agli oggetti da più database, quando lo stato di un database è stato modificato in modo che il recupero del database sia avvenuto dopo l'avvio di una transazione SNAPSHOT, ma prima dell'accesso al database da parte della transazione SNAPSHOT. Ad esempio: il database è stato impostato su OFFLINE e quindi su ONLINE, il database è stato chiuso e riaperto automaticamente a causa dell’opzione AUTO_CLOSE impostata su ONo il database è stato scollegato e ricollegato.

• Le transazioni distribuite, incluse le query nei database partizionati distribuiti, non sono supportate in isolamento SNAPSHOT.

• Il motore di database non mantiene più versioni dei metadati di sistema. Le istruzioni DLL (Data Definition Language) in tabelle e altri oggetti di database, ad esempio indici, viste, tipi di dati, stored procedure e funzioni CLR, comportano la modifica dei metadati. Se un oggetto viene modificato da un'istruzione DLL, eventuali riferimenti simultanei all'oggetto nel livello di isolamento SNAPSHOT causano un errore della transazione SNAPSHOT. READ COMMITTED le transazioni non hanno questa limitazione quando l'opzione di READ_COMMITTED_SNAPSHOT database è impostata su ON.

  Un amministratore del database esegue, ad esempio, l'istruzione ALTER INDEX seguente.

  USE AdventureWorks2022;
  GO
  ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee REBUILD;
  GO
  

  Qualsiasi transazione snapshot attiva quando l'istruzione ALTER INDEX viene eseguita riceve un errore se tenta di fare riferimento alla HumanResources.Employee tabella dopo l'esecuzione dell'istruzione ALTER INDEX . READ COMMITTED le transazioni che usano il controllo delle versioni delle righe non sono interessate.

  Note

  BULK INSERT le operazioni potrebbero causare modifiche ai metadati della tabella di destinazione, ad esempio quando si disabilitano i controlli dei vincoli. In questo caso, le transazioni di isolamento SNAPSHOT simultanee che accedono a tabelle in cui è stato eseguito l'inserimento bulk falliscono.

Personalizzare il blocco e il versionamento delle righe

Personalizzare il timeout del blocco

Quando un'istanza del motore di database non può concedere un blocco a una transazione perché un'altra transazione possiede già un blocco in conflitto sulla risorsa, la prima transazione viene bloccata in attesa del rilascio del blocco esistente. Per impostazione predefinita, non esiste alcun periodo di timeout per le attese di blocco, pertanto una transazione potrebbe essere potenzialmente bloccata a tempo indeterminato.

L'impostazione LOCK_TIMEOUT consente a un'applicazione di definire il periodo di tempo massimo durante il quale un'istruzione rimane in attesa di una risorsa bloccata. Quando il periodo di attesa di un'istruzione supera il valore massimo impostato nell'opzione LOCK_TIMEOUT, l'istruzione bloccata viene annullata automaticamente e viene restituito il messaggio di errore 1222 (Lock request time-out period exceeded). Qualsiasi transazione contenente l'istruzione, tuttavia, non viene sottoposta a rollback. Pertanto, l'applicazione deve avere un gestore degli errori in grado di intercettare il messaggio di errore 1222. Se un'applicazione non intercetta l'errore, questa può continuare senza venire a conoscenza che una singola istruzione all'interno di una transazione è stata annullata ma che la transazione rimane attiva. Gli errori possono verificarsi perché le istruzioni successive nella transazione potrebbero dipendere dall'istruzione che non è mai stata eseguita.

L'implementazione di un gestore errori che intercetta il messaggio di errore 1222 consente a un'applicazione di gestire la situazione di timeout e di eseguire azioni correttive, ad esempio: inviare automaticamente l'istruzione bloccata o eseguire il rollback dell'intera transazione.

Per determinare l'impostazione LOCK_TIMEOUT corrente, eseguire la funzione @@LOCK_TIMEOUT:

SELECT @@LOCK_TIMEOUT;
GO

Personalizzare il livello di isolamento della transazione

READ COMMITTED è il livello di isolamento predefinito per il motore di database. Se per un'applicazione è necessario impostare un livello di isolamento diverso, è possibile utilizzare i metodi seguenti:

• Eseguire l'istruzione SET TRANSACTION ISOLATION LEVEL.
• Le applicazioni ADO.NET che usano lo spazio dei nomi Microsoft.Data.SqlClient o System.Data.SqlClient possono specificare un'opzione IsolationLevel usando il metodo SqlConnection.BeginTransaction.
• Le applicazioni che utilizzano ADO possono impostare la proprietà Autocommit Isolation Levels.
• Quando si avvia una transazione, le applicazioni che usano OLE DB possono chiamare ITransactionLocal::StartTransaction con isoLevel impostato sul livello di isolamento della transazione desiderato. Quando si specifica il livello di isolamento in modalità autocommit, le applicazioni che utilizzano OLE DB possono impostare DBPROPSET_SESSION della proprietà DBPROP_SESS_AUTOCOMMITISOLEVELS sul livello di isolamento della transazione desiderato.
• Nelle applicazioni in cui si usa ODBC si può impostare l'attributo SQL_COPT_SS_TXN_ISOLATION tramite SQLSetConnectAttr.

Quando si specifica il livello di isolamento, il comportamento del blocco per tutte le query e le istruzioni DLM nella sessione dipende dal livello di isolamento stesso. Il livello di isolamento viene mantenuto come valido fino al termine della sessione o all'impostazione di un livello di isolamento diverso.

Nell'esempio seguente viene impostato il livello di isolamento di SERIALIZABLE:

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;

SELECT BusinessEntityID
FROM HumanResources.Employee;

COMMIT;
GO

Il livello di isolamento impostato può essere sovrascritto per singole query o istruzioni DML specificando un hint a livello di tabella, se necessario. La specifica di un hint a livello di tabella non ha alcun effetto sulle altre istruzioni della sessione.

Per determinare il livello di isolamento attualmente impostato, utilizzare l'istruzione DBCC USEROPTIONS come illustrato nell'esempio seguente. Il set di risultati può variare dal set di risultati sul tuo sistema.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
DBCC USEROPTIONS;
GO

Il set di risultati è il seguente.

Set Option                   Value
---------------------------- -------------------------------------------
textsize                     2147483647
language                     us_english
dateformat                   mdy
datefirst                    7
...                          ...
Isolation level              repeatable read

(14 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Suggerimenti di blocco

È possibile specificare hint di blocco per riferimenti a una singola tabella nelle istruzioni SELECT, INSERT, UPDATE, DELETE e MERGE. Gli hint specificano il tipo di blocco o di controllo delle versioni delle righe utilizzato dall'istanza del motore di database per i dati della tabella. I suggerimenti di blocco relativi al livello di tabella possono essere utilizzati quando è necessario un migliore controllo dei tipi di blocco acquisiti su un oggetto. Questi suggerimenti di blocco prevalgono sul livello di isolamento della transazione impostato per la sessione.

Per altre informazioni su hint di blocco specifici e il relativo comportamento, vedere Hint di tabella (Transact-SQL).

È possibile che il motore di database debba acquisire blocchi durante la lettura di metadati, anche quando esegue l'elaborazione di un'istruzione con un hint di blocco che impedisce le richieste di blocchi condivisi in fase di lettura dei dati. Ad esempio, un'istruzione SELECT che viene eseguita al livello di isolamento READ UNCOMMITTED o che utilizza l'hint NOLOCK non acquisisce blocchi condivisi durante la lettura dei dati, ma può richiedere blocchi durante la lettura di una vista del catalogo di sistema. Ciò significa che è possibile bloccare un'istruzione SELECT quando una transazione simultanea modifica i metadati della tabella.

Come illustrato nell'esempio seguente, se il livello di isolamento della transazione è impostato su SERIALIZABLEe l'hint NOLOCK di blocco a livello di tabella viene usato con l'istruzione SELECT , i blocchi dell'intervallo di chiavi in genere usati per gestire SERIALIZABLE le transazioni non vengono acquisiti.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
SELECT JobTitle
FROM HumanResources.Employee WITH (NOLOCK);
GO

-- Get information about the locks held by
-- the transaction.
SELECT resource_type,
       resource_subtype,
       request_mode
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;

-- End the transaction.
ROLLBACK;
GO

L'unico blocco acquisito che fa riferimento a HumanResources.Employee è un blocco di stabilità dello schema (Sch-S). In questo caso la serializzabilità non è garantita.

L'opzione LOCK_ESCALATION di ALTER TABLE evita i blocchi delle tabelle durante l'escalation dei blocchi e abilita i blocchi HoBT (partizioni) nelle tabelle partizionate. Questa opzione non è un hint di blocco e può essere usata per ridurre l'escalation dei blocchi. Per altre informazioni, vedere ALTER TABLE (Transact-SQL).

Personalizzare il blocco di un indice

La strategia di blocco dinamico utilizzata dal motore di database prevede la selezione automatica del livello di granularità dei blocchi ottimale per le query nella maggior parte dei casi. È consigliabile non eseguire l'override dei livelli di blocco predefiniti, a meno che i modelli di accesso a tabelle o indici non siano ben compresi e coerenti e si verifichi un problema di contesa di risorse da risolvere. Se un livello di blocco viene ignorato, è possibile che l'accesso simultaneo a una tabella o a un indice venga ostacolato. Se ad esempio si specificano solo blocchi a livello di tabella in una tabella di notevoli dimensioni usata molto frequentemente, è possibile che si verifichino colli di bottiglia perché gli utenti devono attendere il rilascio del blocco a livello di tabella prima di accedere alla tabella.

In alcuni casi, se i modelli di accesso sono chiari e coerenti, la disattivazione del blocco a livello di pagina o di riga può risultare vantaggioso. Si supponga, ad esempio, che un'applicazione di database utilizzi una tabella di ricerca che viene aggiornata con frequenza settimanale in un processo batch. L'accesso alla tabella da parte di utenti simultanei avviene con un blocco condiviso (S), mentre quello dell'aggiornamento batch settimanale con un blocco esclusivo (X). Se il blocco a livello di pagina e di riga viene disattivato sulla tabella, l'overhead dei blocchi risulta ridotto nell'intera settimana, consentendo agli utenti di accedere simultaneamente alla tabella tramite blocchi a livello di tabella condivisi. Quando viene eseguito il processo batch, può completare l'aggiornamento in modo efficiente perché ottiene un blocco esclusivo sulla tabella.

La disattivazione del blocco di pagina e di riga potrebbe non essere accettabile perché l'aggiornamento batch settimanale impedisce ai lettori simultanei di accedere alla tabella durante l'esecuzione dell'aggiornamento. Se il processo batch modifica solo alcune righe o pagine, è possibile modificare il livello di blocco per consentire il blocco a livello di riga o di pagina, consentendo in questo modo la lettura della tabella da parte di altre sessioni senza blocchi. Se il processo batch ha un numero elevato di aggiornamenti, ottenere un blocco esclusivo sulla tabella potrebbe essere il modo migliore per garantire che il processo batch venga eseguito in modo efficiente.

In alcuni carichi di lavoro, si può verificare un tipo di deadlock quando due operazioni simultanee acquisiscono blocchi di riga sulla stessa tabella e si bloccano a vicenda perché entrambi devono bloccare la pagina. La disattivazione dei blocchi a livello di riga impone a una delle operazioni di attendere, evitando il deadlock. Per altre informazioni sui deadlock, vedere la Guida ai deadlock.

La granularità dei blocchi usata in un indice può essere impostata usando le istruzioni CREATE INDEX e ALTER INDEX. Inoltre, le istruzioni CREATE TABLE e ALTER TABLE possono essere usate per impostare la granularità dei blocchi nei vincoli PRIMARY KEY e UNIQUE. Per garantire la compatibilità con le versioni precedenti, la stored procedure di sistema sp_indexoption è anch'essa in grado di impostare la granularità. Per visualizzare l'opzione di blocco corrente per un determinato indice, usare la funzione INDEXPROPERTY. È possibile impedire l'applicazione di blocchi a livello di pagina, a livello di riga, o su entrambi i livelli per un determinato indice.

Informazioni avanzate sulle transazioni

Transazioni esterne e interne

Una transazione interna esplicita può essere avviata all'interno di una transazione esterna esplicita. Ciò consente principalmente il supporto delle transazioni all'interno di stored procedure che è possibile richiamare sia da un processo già incluso in una transazione che da processi privi di transazioni attive.

Nell'esempio seguente viene illustrato l'uso di transazioni esterne e interne. Se TransProc viene chiamato quando una transazione è attiva, il risultato della transazione interna in TransProc è controllato dalla transazione esterna, e le istruzioni di INSERT vengono sottoposte a commit o rollback a seconda del commit o rollback della transazione esterna. Se TransProc viene eseguito da un processo senza una transazione attiva, l'istruzione COMMIT TRANSACTION alla fine della procedura conferma le istruzioni INSERT.

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO

CREATE TABLE TestTrans
(
ColA INT PRIMARY KEY,
ColB CHAR(3) NOT NULL
);
GO

CREATE PROCEDURE TransProc
  @PriKey INT,
  @CharCol CHAR(3)
AS

BEGIN TRANSACTION InProc;

INSERT INTO TestTrans VALUES (@PriKey, @CharCol);
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol);

COMMIT TRANSACTION InProc;
GO

/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO

/* Roll back the outer transaction, this will
   roll back TransProc's inner transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO

EXECUTE TransProc 3,'bbb';
GO

/*
The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent roll back of the
outer transaction.
*/
SELECT *
FROM TestTrans;
GO

Il commit delle transazioni interne viene ignorato dal motore di database quando è attiva una transazione esterna. A seconda del commit o del rollback eseguito alla fine della transazione più esterna, viene eseguito il commit o il rollback della transazione. Se viene effettuato il commit della transazione esterna, viene effettuato anche il commit delle transazioni interne. Se la transazione esterna viene annullata, anche tutte le transazioni interne vengono annullate, indipendentemente dal fatto che le singole transazioni interne siano state o meno confermate.

Ogni chiamata a COMMIT TRANSACTION o COMMIT WORK si applica all'ultima esecuzione di BEGIN TRANSACTION. Se sono presenti più BEGIN TRANSACTION istruzioni, un'istruzione COMMIT si applica solo all'ultima istruzione, in altre parole alla transazione più interna. Anche se un'istruzione all'interno di una COMMIT TRANSACTION transaction_name transazione interna fa riferimento al nome della transazione esterna, il commit si applica solo alla transazione più interna.

Non è consentito che il parametro transaction_name di un'istruzione ROLLBACK TRANSACTION faccia riferimento alla transazione interna in un set di transazioni denominate. transaction_name può fare riferimento solo al nome della transazione più esterna.

La funzione @@TRANCOUNT registra il livello di nidificazione corrente delle transazioni. Ogni istruzione BEGIN TRANSACTION incrementa @@TRANCOUNT di uno. Ogni istruzione COMMIT TRANSACTION o COMMIT WORK decrementa @@TRANCOUNT di uno. Un'istruzione ROLLBACK WORK o ROLLBACK TRANSACTION che non dispone di un nome di transazione esegue il rollback delle transazioni esterne e di tutte le transazioni interne e decrementa @@TRANCOUNT su 0. Analogamente, un oggetto ROLLBACK TRANSACTION che usa il nome della transazione più esterna esegue il rollback delle transazioni esterne e di tutte le transazioni interne e decrementa @@TRANCOUNT su 0. Per determinare se sei già in una transazione, usa SELECT @@TRANCOUNT per verificare se è 1 o più. Se @@TRANCOUNT è 0, non si è in una transazione.

Usare sessioni associate

Le sessioni vincolate semplificano il coordinamento delle azioni tra più sessioni sullo stesso server. Le sessioni vincolate consentono a due o più sessioni di condividere la stessa transazione e gli stessi blocchi e possono lavorare sugli stessi dati senza conflitti di blocco. È possibile creare sessioni associate in base a più sessioni nella stessa applicazione oppure in base a più applicazioni con sessioni distinte.

Per partecipare a una sessione associata, una sessione chiama sp_getbindtoken o srv_getbindtoken (tramite ODS) per ottenere un token di associazione. Un token di associazione è una stringa di caratteri che identifica in modo univoco ogni transazione associata. Il token di associazione viene quindi inviato alle altre sessioni da associare alla sessione corrente. Le altre sessioni vengono associate alla transazione chiamando sp_bindsession e usando il token di associazione ricevuto dalla prima sessione.

I token di associazione devono essere trasmessi dal codice dell'applicazione che esegue la prima sessione al codice dell'applicazione che successivamente associa le sessioni alla prima sessione. Non esiste alcuna istruzione o funzione API Transact-SQL che un'applicazione può usare per ottenere il token di associazione per una transazione avviata da un altro processo. Di seguito vengono descritti alcuni dei metodi che è possibile utilizzare per la trasmissione di un token di associazione:

• Se tutte le sessioni vengono iniziate dallo stesso processo dell'applicazione, i token di associazione possono essere archiviati nella memoria globale o passati come parametri di funzioni.

• Se le connessioni vengono eseguite da processi dell'applicazione distinti, i token di associazione possono essere trasmessi utilizzando la comunicazione interprocesso (IPC), ad esempio tramite DDE (scambio dinamico dei dati) o un chiamata RPC (Remote Procedure Call).

• I token di associazione possono essere archiviati in una tabella in un'istanza del motore di database, che può essere letta dai processi che desiderano collegarsi alla prima sessione.

In un insieme di sessioni collegate, può essere attiva solo una sessione alla volta. Se una sessione esegue un'istruzione nell'istanza o è in attesa di risultati dall'istanza, le altre sessioni associate allo stesso token possono accedere all'istanza solo dopo il completamento dell'elaborazione della sessione corrente o l'annullamento dell'istruzione corrente. Se l'istanza è occupata per l'elaborazione di un'istruzione da un'altra delle sessioni associate, viene generato un errore indicante che lo spazio della transazione è in uso e che è necessario riprovare la sessione in un secondo momento.

Quando si associano sessioni, ogni sessione mantiene il relativo livello di isolamento. L'utilizzo di SET TRANSACTION ISOLATION LEVEL per modificare il livello di isolamento di una sessione non influisce sull'impostazione di altre sessioni associate allo stesso token.

Tipi di sessioni associate

I due tipi di sessioni vincolate sono locali e distribuite.

• Sessione associata locale Consente alle sessioni associate di condividere lo spazio di una singola transazione in una sola istanza del motore di database.

• Sessione associata distribuita Consente alle sessioni associate di condividere lo stesso spazio della transazione in due o più istanze fino a quando non viene eseguito il commit o il rollback dell'intera transazione tramite Microsoft Distributed Transaction Coordinator (MS DTC).

Le sessioni associate distribuite non sono identificate da un token di associazione di stringhe di caratteri; sono identificati da numeri di identificazione delle transazioni distribuite. Se una sessione associata partecipa a una transazione locale ed esegue una chiamata RPC in un server remoto con l'opzione SET REMOTE_PROC_TRANSACTIONS ON, la transazione associata locale viene automaticamente promossa a transazione associata distribuita da MS DTC e viene avviata una sessione di MS DTC.

Quando utilizzare le sessioni vincolate

Nelle versioni precedenti di SQL Server le sessioni associate vengono prevalentemente utilizzate nello sviluppo di stored procedure estese che devono eseguire istruzioni Transact-SQL per conto del processo da cui sono chiamate. Quando il processo chiamante passa un token di associazione come parametro di una stored procedure estesa, la procedura può entrare nello spazio delle transazioni del processo chiamante e integrarsi pertanto con tale processo.

Nel motore di database le stored procedure scritte utilizzando CLR sono più sicure, scalabili e stabili rispetto alle stored procedure estese. Le stored procedure CLR usano l'oggetto SqlContext, anziché sp_bindsession, per associare il contesto della sessione chiamante.

Le sessioni vincolate possono essere utilizzate per lo sviluppo di applicazioni a tre livelli in cui la logica di business è incorporata in programmi distinti che lavorano insieme in una singola transazione aziendale. Tali programmi devono essere sviluppati in modo da coordinarne accuratamente l'accesso a un database. Poiché le due sessioni condividono gli stessi blocchi, i due programmi non devono tentare di modificare gli stessi dati simultaneamente. In qualsiasi momento può essere in funzione una sola sessione come parte della transazione. L'esecuzione parallela non è possibile. La transazione può essere passata da una sessione all'altra solo in specifici punti definiti, ad esempio dopo il completamento di tutte le istruzioni DML e il recupero dei relativi risultati.

Codificare transazioni efficienti

È importante mantenere le transazioni il più brevi possibile. Quando viene avviata una transazione, un sistema di gestione di database (DBMS, Database Management System) deve tenere occupate molte risorse fino alla fine della transazione in modo da proteggerne le proprietà di atomicità, consistenza, isolamento e durevolezza (ACID, Atomicity, Consistency, Isolation and Durability). Se i dati vengono modificati, le righe modificate devono essere protette con blocchi esclusivi che ne impediscono la lettura da parte di altre transazioni. Tali blocchi devono essere mantenuti attivi fino al commit o al rollback della transazione. In base alle impostazioni del livello di isolamento della transazione, le istruzioni SELECT possono acquisire blocchi che è necessario mantenere attivi fino a quando non è stato eseguito il commit o il rollback della transazione. Nei sistemi con numerosi utenti è necessario mantenere le transazioni più brevi possibile per ridurre la possibilità che si verifichino contese di risorse tra connessioni simultanee. Le transazioni inefficienti a esecuzione prolungata potrebbero non essere un problema con un numero ridotto di utenti, ma sono estremamente problematiche in un sistema con migliaia di utenti. A partire da SQL Server 2014 (12.x), il motore di database supporta transazioni con durabilità ritardata. Le transazioni durevoli ritardate potrebbero migliorare la scalabilità e le prestazioni, ma non garantiscono la durabilità. Per altre informazioni, vedere Controllo della durabilità delle transazioni.

Linee guida per il codice

Di seguito vengono riportate le linee guida per la codifica di transazioni efficienti:

• Non richiedere l'input degli utenti durante una transazione. Richiedere agli utenti l'input necessario prima di avviare una transazione. Se durante una transazione è necessario input dell'utente aggiuntivo, è consigliabile eseguire il rollback della transazione corrente e riavviarla dopo avere ottenuto l'input desiderato. Anche se gli utenti rispondono immediatamente, i tempi di reazione umani sono in larga misura inferiori alla velocità del computer. Tutte le risorse utilizzate dalla transazione vengono tenute occupate per periodi di tempo estremamente prolungati e possono pertanto causare potenziali problemi di blocco. Se gli utenti non rispondono, la transazione rimane attiva, bloccando le risorse critiche fino a quando non rispondono, che potrebbero non verificarsi per diversi minuti o persino ore.

• Non aprire una transazione durante l'esplorazione dei dati, se possibile. Le transazioni non devono essere avviate fino al completamento di tutte le analisi preliminari dei dati.

• Mantenere la transazione il più breve possibile. Dopo avere determinato quali modifiche è necessario eseguire, avviare la transazione, eseguire le istruzioni di modifica e quindi eseguire immediatamente il commit o il rollback. Non aprire la transazione prima che sia necessaria.

• Per limitare i blocchi, è consigliabile utilizzare per le query di sola lettura un livello di isolamento basato sul controllo delle versioni delle righe.

• Utilizzare in modo intelligente i livelli bassi di isolamento delle transazioni. Molte applicazioni possono essere codificate per usare il livello di isolamento della transazione READ COMMITTED. Poche transazioni richiedono il livello di isolamento della transazione SERIALIZABLE.

• Usare in modo intelligente le opzioni di concorrenza ottimistica. In un sistema con una bassa probabilità di aggiornamenti simultanei, l'overhead di gestione di un errore occasionale, come "qualcun altro ha modificato i dati dopo averli letti", può essere molto inferiore rispetto al sovraccarico di bloccare sempre le righe durante la lettura.

• Durante una transazione, accedere alla quantità minima di dati possibile. Ciò consente di ridurre il numero di righe bloccate e pertanto anche la contesa tra le transazioni.

• Evitare gli hint di blocco pessimistici, come HOLDLOCK, quando possibile. Gli hint sul livello di isolamento come HOLDLOCK o SERIALIZABLE possono far sì che i processi attendano anche su blocchi condivisi, riducendo così la concorrenza.

• Evitare di usare transazioni implicite quando possibile. Le transazioni implicite possono introdurre comportamenti imprevedibili a causa della loro natura. Vedere Transazioni implicite e i problemi di concorrenza.

Transazioni implicite ed evitare problemi di concorrenza e risorse

Per evitare problemi di concorrenza e delle risorse, è necessario gestire le transazioni implicite con la massima attenzione. Quando si usano transazioni implicite, la successiva istruzione di Transact-SQL dopo COMMIT o ROLLBACK avvia automaticamente una nuova transazione. Ciò potrebbe comportare l'apertura di una nuova transazione mentre nell'applicazione è in corso l'esame dei dati o addirittura quando viene richiesto l'input dell'utente. Dopo che è stata completata l'ultima transazione necessaria per la protezione delle modifiche ai dati, disabilitare le transazioni implicite fino a quando non è nuovamente necessaria una transazione per la protezione delle modifiche. In questo modo, quando nell'applicazione è in corso l'esame dei dati e viene richiesto l'input dell'utente, nel motore di database viene utilizzata la modalità autocommit.

Inoltre, quando il SNAPSHOT livello di isolamento è abilitato, anche se una nuova transazione non conterrà blocchi, una transazione a esecuzione prolungata impedirà la rimozione delle versioni precedenti dall'archivio versioni.

Gestire transazioni di lunga durata

Una transazione con esecuzione prolungata è una transazione attiva su cui non è stato eseguito tempestivamente il commit o il rollback. Ad esempio, se l'inizio e la fine di una transazione sono controllati dall'utente, una causa tipica di una transazione a lungo termine è un utente che avvia una transazione e poi se ne va mentre la transazione attende una risposta dall'utente.

Una transazione con esecuzione prolungata può causare seri problemi a un database, come indicato di seguito:

• Se l'istanza di un server viene chiusa dopo che una transazione attiva ha eseguito diverse modifiche non sottoposte a commit, la fase di recupero del successivo riavvio può impiegare molto più tempo di quanto specificato dall'opzione di configurazione del server recovery intervalo dall'opzione ALTER DATABASE ... SET TARGET_RECOVERY_TIME. Queste opzioni controllano rispettivamente i checkpoint attivi e indiretti. Per altre informazioni sui tipi di checkpoint, vedere Checkpoint di database (SQL Server).

• Inoltre, sebbene una transazione in attesa di una risposta possa produrre un aumento minimo del log, posticipa il troncamento del log in modo indefinito con una conseguente crescita delle relative dimensioni con rischio di riempimento. Se il log delle transazioni si riempie, il database non può eseguire altre operazioni di scrittura. Per altre informazioni, vedere Architettura e gestione del log delle transazioni di SQL Server, Risolvere i problemi relativi a un log delle transazioni completo (Errore 9002 di SQL Server), e Log delle transazioni SQL Server.

Individuare le transazioni a esecuzione prolungata

Per individuare le transazioni con esecuzione prolungata, utilizzare una delle alternative seguenti:

• sys.dm_tran_database_transactions

  Questa vista a gestione dinamica restituisce informazioni sulle transazioni a livello di database. Per una transazione a esecuzione prolungata, le colonne più rilevanti sono quelle che indicano l'ora del primo record di log (database_transaction_begin_time), lo stato attuale della transazione (database_transaction_state) e il numero di sequenza del file di log (LSN) del record iniziale nel log delle transazioni (database_transaction_begin_lsn).

  Per altre informazioni, vedere sys.dm_tran_database_transactions (Transact-SQL).

• DBCC OPENTRAN

  Questa istruzione consente di identificare l'ID utente del proprietario della transazione, in modo da risalire all'origine della transazione per terminarla in modo più appropriato (commit o rollback). Per altre informazioni, vedere DBCC OPENTRAN (Transact-SQL).

Terminare una transazione

Per terminare una transazione in una sessione specifica, usare l'istruzione KILL. Eseguire l'istruzione con cautela, particolarmente quando sono in esecuzione processi critici. Per altre informazioni, vedere KILL (Transact-SQL).

Deadlocks

I deadlock sono un argomento complesso correlato ai blocchi, ma diverso dal bloccaggio.

• Per altre informazioni sui deadlock, tra cui il monitoraggio, la diagnosi e i campioni, vedere la Guida ai deadlock.
• Per altre informazioni sui deadlock specifici del database SQL di Azure, consultare la sezione Analizzare e prevenire deadlock nel database SQL di Azure.

Contenuti correlati

• Informazioni e risoluzione dei problemi di blocco di SQL Server
• Comprendere e risolvere i problemi che causano un blocco per il database SQL di Azure
• Funzioni e viste DMV relative alle transazioni (Transact-SQL)
• Overhead del versionamento delle righe
• sys.dm_tran_locks (Transact-SQL)