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

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di sistemi analitici (PDW)

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. Questa guida descrive i meccanismi di blocco e controllo delle versioni delle righe usati da SQL Server motore di database per garantire l'integrità fisica di ogni transazione e fornisce informazioni su come le applicazioni possono controllare le transazioni in modo efficiente.

Nota

Il blocco ottimizzato è una funzionalità di motore di database introdotta nel 2023 che riduce drasticamente la memoria di blocco e il numero di blocchi necessari per le scritture simultanee. Questo articolo è stato aggiornato per descrivere i motore di database di SQL Server con e senza blocchi ottimizzati. Attualmente, il blocco ottimizzato è disponibile solo in database SQL di Azure.

Il blocco ottimizzato ha aggiornato in modo significativo alcune sezioni di questo articolo, tra cui:

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):

Atomicità
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.

Coerenza
Al termine della transazione i dati devono essere consistenti. Per salvaguardare l'integrità dei dati in un database relazionale, è necessario che alle modifiche eseguite dalla transazione vengano applicate tutte le regole. Tutte le strutture di dati interne, ad esempio indici ad albero B o elenchi collegati doubly, devono essere corrette alla fine della transazione.

Nota

Nella documentazione di SQL Server viene usato in modo generico il termine albero B in riferimento agli indici. Negli indici rowstore SQL Server implementa un albero B+. Questo non si applica agli indici columnstore o agli archivi dati in memoria. Per ulteriori informazioni, vedi Guida sulla progettazione e sull'architettura degli indici SQL Server e Azure SQL.

Isolamento
Le modifiche eseguite da transazioni simultanee devono essere isolate dalle modifiche eseguite da qualsiasi altra transazione simultanea. Una transazione riconosce i dati nello stato in cui si trovava prima della modifica di un'altra transazione simultanea oppure riconosce i dati dopo il completamento della seconda transazione, ma non riconosce uno stato intermedio. 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.

Durabilità
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 versioni successive consentono transazioni durevoli ritardate. 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à delle transazioni ritardata, vedere l'articolo Controllare la durabilità delle transazioni.

I programmatori SQL devono avviare e terminare le transazioni in modo da garantire la consistenza logica dei dati. In particolare, il programmatore deve definire la sequenza delle modifiche dei dati in modo che lo stato dei dati risulti consistente rispetto alle regole business dell'organizzazione. Il programmatore include queste istruzioni di modifica in una singola transazione in modo che sql Server motore di database possa applicare l'integrità fisica della transazione.

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

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

  • Meccanismi di registrazione che garantiscono la durabilità delle transazioni. Per le transazioni completamente durevoli, il record del log viene finalizzato su disco prima del commit delle transazioni stesse. Pertanto, anche se l'hardware del server, il sistema operativo o l'istanza di SQL Server motore di database stesso ha esito negativo, l'istanza usa i log delle transazioni al riavvio per eseguire automaticamente il rollback delle transazioni incomplete fino al punto dell'errore di sistema. Le transazioni durevoli ritardate vengono sottoposte a commit prima che il record del log delle transazioni venga finalizzato su disco. Tali transazioni potrebbero andare perdute se si verifica un errore di sistema prima della finalizzazione del record del log su disco. Per altre informazioni sulla durabilità delle transazioni ritardata, vedere l'articolo Controllare la durabilità delle transazioni.

  • Caratteristiche di gestione delle transazioni che ne garantiscono l'atomicità e la consistenza. Dopo l'avvio di una transazione, deve essere completata correttamente (commit) o sql Server motore di database annulla tutte le modifiche apportate ai dati dall'avvio della transazione. 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. Questa operazione può essere specificata usando istruzioni Transact-SQL o funzioni API (Application Programming Interface) di 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 altre informazioni, vedere Transazioni, esecuzione di transazioni in ODBC e transazioni in SQL Server Native Client.

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

Avviare le transazioni

Usando le funzioni API e le istruzioni Transact-SQL, è possibile avviare transazioni in un'istanza di SQL Server motore di database come transazioni esplicite, autocommit o implicite.

Transazioni esplicite
Una transazione esplicita è una transazione in cui si definiscono in modo esplicito sia l'inizio che la fine della transazione tramite una funzione API o eseguendo le istruzioni Transact-SQL BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION o ROLLBACK WORK Transact-SQL. Al termine della transazione, viene ripristinata la modalità precedente all'avvio della transazione esplicita, ovvero la modalità transazione implicita o autocommit.

È possibile usare tutte le istruzioni Transact-SQL in una transazione esplicita, ad eccezione delle istruzioni 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
  • Stored procedure di sistema full-text
  • sp_dboption per impostare le opzioni di database o qualsiasi procedura di sistema che modifica il master database all'interno di transazioni esplicite o implicite.

Nota

UPDATE STATISTICS può essere utilizzato all'interno di una transazione esplicita. Tuttavia, UPDATE STATISTICS esegue il commit indipendentemente dalla transazione che la contiene e non è possibile eseguire il rollback.

Transazioni con autocommit
La modalità autocommit è la modalità predefinita per la gestione delle transazioni nel motore di database di SQL Server. Viene eseguito il commit o il rollback di ogni istruzione Transact-SQL completata. Se un'istruzione viene completata correttamente, ne viene eseguito il commit. Se invece si verifica un errore, viene eseguito il rollback. Una connessione a un'istanza di SQL Server motore di database opera in modalità autocommit ogni volta che questa modalità predefinita non è stata sottoposta a override da transazioni esplicite o implicite. La modalità autocommit è l'impostazione predefinita anche in ADO, OLE DB, ODBC e DB-Library.

Transazioni implicite
Quando una connessione viene eseguita in modalità di transazione implicita, l'istanza di SQL Server motore di database avvia automaticamente una nuova transazione dopo il commit o il rollback della transazione corrente. Non è necessario specificare l'inizio di una transazione. È sufficiente eseguirne il commit o il rollback. La modalità di esecuzione implicita delle transazioni genera il concatenamento delle transazioni. Impostare la modalità di transazione implicita su tramite una funzione API o l'istruzione Transact-SQL edizione Standard T IMPLICIT_TRANSACTIONS ON. Questa modalità è nota anche come OFF autocommit, vedere metodo setAutoCommit (SQLServer Connessione ion).This mode is also known as Autocommit OFF, see setAutoCommit Method (SQLServer Connessione ion).

Dopo che la modalità di transazione implicita è stata impostata per una connessione, l'istanza di SQL Server motore di database avvia automaticamente una transazione quando esegue per la prima volta una di queste istruzioni:

  • MODIFICA TABELLA

  • CREATE

  • DELETE

  • DROP

  • FETCH

  • GRANT

  • INSERT

  • OPEN

  • REVOKE

  • SELECT

  • TRUNCATE TABLE

  • UPDATE

  • Transazioni con ambito batch Applicabile solo a più set di risultati attivi (MARS), una transazione esplicita o implicita Transact-SQL che inizia in una sessione MARS diventa una transazione con ambito batch. Una transazione con ambito batch di cui non viene eseguito il commit o il rollback quando viene completato automaticamente il rollback di un batch da SQL Server.

  • Le transazioni distribuite Transazioni distribuite si estendono su due o più server noti come resource manager. 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ò operare come gestore risorse in transazioni distribuite coordinate da gestori transazioni, ad esempio Microsoft Distributed Transaction Coordinator (MS DTC) o altri gestori transazioni che supportano la specifica Open Group XA per l'elaborazione delle transazioni distribuite. Per ulteriori informazioni, vedere la documentazione di MS DTC.

    Una transazione all'interno di una singola istanza del motore di database di SQL Server che si estende su due o più database è in realtà una transazione distribuita. Nell'istanza le transazioni distribuite vengono gestite internamente e appaiono all'utente come transazioni locali.

    A livello dell'applicazione le transazioni distribuite vengono gestite in modo simile alle transazioni locali. Al termine della transazione l'applicazione ne richiede 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. A tale scopo il processo di commit viene gestito in due fasi, ovvero una fase preparatoria e la fase di commit effettivo. Questo tipo di commit è noto come commit in due fasi.

    • Fase di preparazione Quando gestione transazioni riceve una richiesta di commit, invia un comando di preparazione a tutti i gestori risorse coinvolti nella transazione. Ogni strumento esegue quindi tutte le operazioni necessarie per rendere la transazione durevole. Tutti i buffer contenenti immagini del log relative alla transazione vengono inoltre trasferiti su disco. Dopo avere completato la fase preparatoria, lo strumento di gestione delle risorse comunica al gestore delle transazioni l'esito, positivo o negativo, della preparazione. SQL Server 2014 (12.x) ha introdotto la durabilità ritardata delle transazioni. Le transazioni durevoli ritardate vengono sottoposte a commit prima che le immagini di log della transazione vengano scaricate su disco. Per altre informazioni sulla durabilità delle transazioni ritardata, vedere l'articolo Controllare la durabilità delle transazioni.

    • Fase di commit Se il gestore delle transazioni riceve una preparazione corretta da tutti i gestori risorse, invia i comandi di commit a ogni gestore di risorse. i quali 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 di SQL Server motore di database possono gestire transazioni distribuite tramite Transact-SQL o l'API del 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, eseguirne il commit. L'istruzione COMMIT consente di integrare in modo permanente nel database tutte le modifiche apportate dalla transazione. L'istruzione consente inoltre di liberare le risorse, ad esempio i blocchi, utilizzate dalla transazione.

  • ROLLBACK Se si verifica un errore in una transazione o se l'utente decide di annullare la transazione, eseguire il rollback della transazione. L'istruzione ROLLBACK consente di annullare tutte le modifiche apportate ai dati ripristinandone lo stato corrente all'inizio della transazione. L'istruzione consente inoltre di liberare le risorse utilizzate dalla transazione.

Nota

Nelle connessioni abilitate per supportare più MARS, non è possibile eseguire il commit di una transazione avviata tramite una funzione API mentre vi sono richieste di esecuzione in sospeso. Qualsiasi tentativo di eseguire il commit di questo tipo di transazione durante l'esecuzione di operazioni in sospeso restituisce un errore.

Errori durante l'elaborazione delle transazioni

Se un errore impedisce il completamento di una transazione, SQL Server esegue automaticamente il rollback della transazione e libera tutte le risorse contenute nella transazione. Se la connessione di rete del client a un'istanza del motore di database di SQL Server viene interrotta, viene eseguito il rollback di eventuali transazioni in sospeso per la connessione quando la rete invia una notifica all'istanza dell'interruzione. Se l'applicazione client ha esito negativo o se il computer client è inattivo o viene riavviato, la connessione viene interrotta e l'istanza di SQL Server motore di database esegue il rollback di eventuali connessioni in sospeso quando la rete lo notifica dell'interruzione. Se il client si disconnette dall'applicazione, viene eseguito il rollback delle transazioni in sospeso.

Se in un batch si verifica un errore di istruzione di runtime, ad esempio una violazione del vincolo, il comportamento predefinito nel motore di database di SQL Server consiste nel eseguire il rollback solo dell'istruzione che ha generato l'errore. È possibile modificare questo comportamento usando l'istruzione SET XACT_ABORT. Dopo l'esecuzione di SET XACT_ABORT ON, qualsiasi errore di runtime di un'istruzione comporta il rollback automatico della transazione corrente. SET XACT_ABORT non ha alcun effetto sugli errori di compilazione, ad esempio gli errori di sintassi. Per altre informazioni, vedere edizione Standard T XACT_ABORT (Transact-SQL).

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

Errori di compilazione ed esecuzione in modalità autocommit

In modalità autocommit, a volte viene visualizzato come se un'istanza di SQL Server motore di database abbia 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 a SQL Server motore di database di compilare un piano di esecuzione, quindi non viene eseguito alcun elemento nel 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. In apparenza viene eseguito il rollback delle prime due istruzioni INSERT, mentre in realtà tali istruzioni non vengono 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 run-time 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

Sql Server motore di database usa la risoluzione dei nomi posticipata, in cui i nomi degli oggetti non vengono risolti fino al tempo di esecuzione. Nell'esempio seguente vengono eseguite e sottoposte a commit le prime due INSERT istruzioni e le due righe rimangono nella TestBatch tabella dopo che la terza INSERT istruzione genera un errore di run-time facendo riferimento a una tabella che non esiste.

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 controllo delle versioni delle righe

Sql Server motore di database usa i meccanismi seguenti per garantire l'integrità delle transazioni e mantenere la coerenza dei database quando più utenti accedono contemporaneamente ai dati:

  • Blocco

    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 è abilitato un livello di isolamento basato sul controllo delle versioni delle righe, sql Server motore di database mantiene 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 della query. Se si utilizza il controllo delle versioni delle righe, le possibilità che un'operazione di lettura possa bloccare altre transazioni vengono ridotte al minimo.

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 su tali dati potrebbero produrre 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. È possibile specificare hint a livello di tabella per le singole istruzioni Transact-SQL per adattare ulteriormente il comportamento in base ai requisiti dell'applicazione.

Gestire l'accesso simultaneo ai dati

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 sistema di archiviazione dati non dispone di controllo della concorrenza, gli utenti potrebbero riscontrare gli effetti collaterali seguenti:

  • Perdita di aggiornamenti

    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 è, infatti, a conoscenza dell'esecuzione 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 e successivamente modifichino e salvino la propria copia del documento. Con questa operazione la copia originale del documento viene sovrascritta e l'ultimo revisore che esegue il salvataggio sovrascrive la copia modificata dall'altro revisore. Questo problema potrebbe essere evitato impedendo a un revisore di accedere al file finché l'altro non abbia completato l'operazione ed eseguito il commit della transazione.

  • Dipendenza non sottoposta a commit (lettura dirty)

    La dipendenza uncommitted si verifica quando una transazione seleziona una riga mentre questa viene aggiornata da un'altra transazione. La seconda transazione legge i dati di cui non è ancora stato eseguito il commit e che può essere modificato dalla transazione che aggiorna la riga.

    Si supponga, ad esempio, che un revisore stia modificando un documento in formato elettronico e che un secondo revisore apra una copia del documento contenente tutte le modifiche apportate fino a quel momento e lo distribuisca ai destinatari del documento. 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. Questo problema è molto simile alla dipendenza uncommitted, in quanto una transazione modifica i dati che una seconda transazione sta leggendo. Nel caso dell'analisi inconsistente, tuttavia, la transazione di modifica ha già eseguito il commit dei dati letti dalla transazione di lettura. 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, ovvero la lettura originale non è ripetibile. Questo problema potrebbe essere evitato impedendo all'autore di modificare il documento fino a quando il revisore non ha completato l'ultima lettura.

  • Lettura di righe fantasma

    Una lettura fantasma è una situazione che si verifica quando vengono eseguite due query identiche e la raccolta di righe restituite dalla seconda query è diversa. Nell'esempio seguente viene illustrato come può verificarsi questa condizione. Si supponga che le due transazioni seguenti 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 di righe mancanti e doppie provocate dagli aggiornamenti della riga

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

      Le transazioni eseguite al livello READ UNCOMMITTED non generano blocchi condivisi per evitare che altre transazioni possano modificare 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 della chiave di indice della riga durante la lettura, è possibile che la riga venga visualizzata nuovamente se la modifica principale sposta la riga di una posizione in avanti nell'analisi. Analogamente, la riga potrebbe non essere visualizzata se la modifica principale sposta la riga in una posizione già letta all'interno dell'indice. Per evitare questo problema, usare l'hint SERIALIZABLE o HOLDLOCK oppure il controllo delle versioni delle righe. Per ulteriori informazioni, vedere Hint di tabella (Transact-SQL).

    • Mancare una o più righe che non rappresentano l'obiettivo dell'aggiornamento

      Quando si usa READ UNCOMMITTED, se la query legge le righe usando un'analisi di ordine di allocazione (usando le pagine di IAM), è possibile che si verifichi una perdita di righe se un'altra transazione provoca la suddivisione di una pagina. Ciò non può verificarsi quando si usa read committed perché un blocco di tabella viene mantenuto durante una suddivisione di pagina e non si verifica se la tabella non dispone di un indice cluster, perché gli aggiornamenti non causano divisioni di pagina.

Tipi di concorrenza

Se molti utenti cercano di modificare contemporaneamente i dati di un database, è necessario implementare un sistema di controlli che impedisca che le modifiche apportate da un utente danneggino il lavoro di altri utenti. 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 agli utenti di modificare i dati con effetto su altri utenti. Dopo che un utente ha eseguito un'operazione che causa l'applicazione di un blocco, gli altri utenti non sono in grado di eseguire operazioni in conflitto con il blocco finché questo non viene rilasciato dal proprietario. Viene utilizzato l'aggettivo pessimistica in quanto questo tipo di controllo si utilizza principalmente in ambienti con elevata contesa dei dati, in cui il costo della protezione dei dati tramite i blocchi è inferiore al costo rappresentato dal rollback delle transazioni in caso di conflitti di concorrenza.

  • Controllo della concorrenza ottimistica

    Quando si utilizza il controllo della concorrenza ottimistica, la lettura dei dati da parte degli utenti non comporta il blocco dei dati. Quando un utente esegue l'aggiornamento dei dati, il sistema verifica se i dati sono stati modificati da un altro utente dopo la lettura. In questo caso, si verifica un errore. In genere, l'utente per cui viene visualizzato l'errore esegue il rollback della transazione e ricomincia dall'inizio. Viene utilizzato l'aggettivo ottimistica in quanto questo tipo di controllo si utilizza principalmente in ambienti con ridotta contesa dei dati, in cui il costo dell'esecuzione occasionale del rollback di una transazione è minore rispetto al costo del blocco dei dati durante la lettura.

SQL Server supporta un intervallo di controlli di 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. Questi attributi possono essere definiti usando istruzioni Transact-SQL o tramite le proprietà e gli attributi delle API (Application Programming Interface) di database, ad esempio ADO, ADO.NET, OLE DB e ODBC.

Livelli di isolamento nel motore di database di SQL Server

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 effetti secondari consentiti sulla concorrenza, ad esempio letture dirty o fantasma.

I livelli di isolamento delle transazioni controllano gli elementi seguenti:

  • Indica se i blocchi vengono acquisiti durante la lettura dei dati e il tipo di blocchi richiesti.
  • La durata dei blocchi di lettura.
  • Indica 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 di cui è stato eseguito il commit della riga esistente al momento dell'avvio dell'istruzione o della transazione.
    • Legge la modifica dei dati di cui non è stato eseguito il commit.

Importante

La scelta di un livello di isolamento delle transazioni non ha effetto sui blocchi acquisiti per proteggere le modifiche dei dati. Una transazione ottiene sempre un blocco esclusivo su qualsiasi dato da essa modificato, che mantiene fino al suo completamento, indipendentemente dal livello di isolamento impostato per la transazione. Per le operazioni di lettura, i livelli di isolamento delle transazioni definiscono essenzialmente il livello di protezione dagli effetti delle modifiche apportate da altre transazioni.

Un livello di isolamento inferiore aumenta la possibilità per un maggior numero di utenti di accedere ai dati contemporaneamente, ma anche la quantità di effetti di concorrenza (ad esempio letture dirty o perdita di aggiornamenti) potenzialmente verificabili. Per contro, un livello di isolamento elevato riduce i tipi di effetti di concorrenza verificabili, ma richiede più risorse di sistema e aumenta le probabilità che una transazione venga bloccata da 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 a ogni ripetizione di un'operazione di lettura. Tuttavia questo avviene applicando un livello di blocco con probabilità effetti su altri utenti in sistemi multiutente. Il livello di isolamento minimo, Read uncommitted, è in grado di recuperare i dati modificati ma di cui non è stato eseguito il commit da altre transazioni. In tale livello possono verificarsi tutti gli effetti secondari della concorrenza, ma l'assenza di blocco in lettura e di controllo delle versioni riduce al minimo l'overhead.

motore di database livelli di isolamento

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

Livello di isolamento Definizione
Read uncommitted Il livello di isolamento più basso in cui le transazioni sono isolate solo per garantire che i dati fisicamente danneggiati non vengano letti. In questo livello sono consentite le letture dirty, quindi una transazione può vedere le modifiche non ancora sottoposte al commit apportate da altre transazioni.
Read committed Consente a una transazione di leggere i dati letti in precedenza (ma non modificati) da un'altra transazione senza attendere che tale transazione venga completata. Sql Server motore di database mantiene i blocchi di scrittura (acquisiti sui dati selezionati) fino alla fine della transazione, ma i blocchi di lettura vengono rilasciati non appena viene eseguita l'operazione edizione Standard LECT. Si tratta del livello predefinito di SQL Server motore di database.
Repeatable read Sql Server motore di database mantiene i blocchi di lettura e scrittura acquisiti sui dati selezionati fino alla fine della transazione. Tuttavia dal momento che i blocchi di intervallo non sono gestiti, è possibile che si verifichino letture fantasma.
Serializable Il livello più alto corrispondente all'isolamento completo di una transazione dall'altra. Il motore di database di SQL Server mantiene i blocchi di lettura e scrittura acquisiti sui dati selezionati da rilasciare alla fine della transazione. I blocchi di intervallo vengono acquisiti quando un'operazione SELECT utilizza una clausola WHERE con intervallo, specialmente per evitare letture fantasma.

Nota: è possibile che le operazioni e le transazioni DDL in tabelle replicate abbiano esito negativo quando è richiesto il livello di isolamento serializable. Ciò è dovuto al fatto che le query di replica utilizzano hint che possono essere incompatibili con il livello di isolamento serializable.

SQL Server supporta anche due livelli di isolamento delle transazioni aggiuntivi che usano il controllo delle versioni delle righe. Uno è un'implementazione dell'isolamento READ COMMITTED e uno è un livello di isolamento delle transazioni, snapshot.

Livello di isolamento del controllo delle versioni delle righe Definizione
Snapshot Read Committed (RCSI) Quando l'opzione di database READ_COMMITTED_SN piattaforma di strumenti analitici HOT è impostata su ON, l'isolamento READ COMMITTED usa il controllo delle versioni delle righe per garantire la coerenza di lettura a livello di istruzione. Le operazioni di lettura richiedono solo i blocchi di livello di tabella SCH-S e nessun blocco di pagina o di riga. Ovvero, sql Server motore di database usa il controllo delle versioni delle righe per presentare ogni istruzione con uno snapshot coerente in modo transazionale dei dati così come esisteva all'inizio dell'istruzione. Non vengono utilizzati blocchi per proteggere i dati da aggiornamenti eseguiti da altre transazioni. Una funzione definita dall'utente può restituire dati sottoposti a commit dopo che è iniziata l'istruzione contenente l'UDF.

Quando l'opzione di READ_COMMITTED_SNAPSHOT database è impostata su OFF, ovvero l'impostazione predefinita, l'isolamento READ COMMITTED usa blocchi condivisi per impedire ad altre transazioni di modificare righe mentre la transazione corrente esegue un'operazione di lettura. I blocchi condivisi impediscono inoltre che l'istruzione possa leggere righe modificate da altre transazioni, fino al completamento di tali transazioni. Entrambe le implementazioni soddisfano la definizione ISO dell'isolamento READ COMMITTED.
Snapshot Il livello di isolamento dello snapshot utilizza il controllo delle versioni delle righe per assicurare consistenza in lettura a livello di transazioni. Le operazioni di lettura non acquisiscono blocchi di pagina o di riga, ma solo blocchi della tabella SCH-S. Quando viene eseguita la lettura delle righe modificate da un'altra transazione, esse recuperano la versione della riga esistente all'avvio della transazione. È possibile usare l'isolamento snapshot in un database solo quando l'opzione di database ALLOW_SNAPSHOT_ISOLATION è impostata su ON. Per impostazione predefinita, l'opzione è impostata su OFF per i database utente.

Nota: SQL Server non supporta il controllo delle versioni dei metadati. Per questo motivo, esistono restrizioni sulle operazioni DDL che possono eseguite in una transazione esplicita che viene eseguita con l'isolamento dello snapshot. Le istruzioni DDL seguenti non sono permesse con l'isolamento dello snapshot dopo un'istruzione BEGIN TRANSACTION: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME o qualsiasi istruzione DDL di Common Language Runtime (CLR). Queste istruzioni sono consentite quando si usa l'isolamento dello snapshot all'interno di transazioni implicite. Per definizione, una transazione implicita è una sola istruzione che rende possibile l'applicazione della semantica dell'isolamento dello snapshot, anche con le istruzioni DDL. Le violazioni di questo principio possono causare l'errore 3961: Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.

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

Livello di isolamento Lettura dirty Nonrepeatable read Fittizio
Read uncommitted
Read committed No
Repeatable read No No
Snapshot No No No
Serializable No No No

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

I livelli di isolamento delle transazioni possono essere impostati usando Transact-SQL o tramite un'API di database.

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

ADO
Le applicazioni ADO impostano la IsolationLevel proprietà dell'oggetto Connection su adXactReadUncommitted, adXactReadCommitted, adXactRepeatableReado adXactReadSerializable.

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

OLE DB
Quando si avvia una transazione, le applicazioni che usano la chiamata ITransactionLocal::StartTransaction OLE DB con isoLevel impostato su ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTEDISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_SNAPSHOT, o ISOLATIONLEVEL_SERIALIZABLE.

Quando si specifica il livello di isolamento delle transazioni in modalità autocommit, le applicazioni OLE DB possono impostare la DBPROPSET_SESSION proprietà DBPROP_SESS_AUTOCOMMITISOLEVELS su DBPROPVAL_TI_CHAOS, DBPROPVAL_TI_SERIALIZABLEDBPROPVAL_TI_READCOMMITTEDDBPROPVAL_TI_READUNCOMMITTEDDBPROPVAL_TI_CURSORSTABILITYDBPROPVAL_TI_REPEATABLEREADDBPROPVAL_TI_BROWSE, , DBPROPVAL_TI_ISOLATED, o .DBPROPVAL_TI_SNAPSHOT

ODBC
Le applicazioni ODBC chiamano SQLSetConnectAttr con Attribute impostato su SQL_ATTR_TXN_ISOLATION e ValuePtr impostano su SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READo 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

Il blocco è un meccanismo usato dal motore di database di SQL Server per sincronizzare l'accesso da parte di più utenti contemporaneamente allo stesso elemento di dati.

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 o esclusivi. 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, l'istanza di SQL Server motore di database sospende la transazione richiedente fino al rilascio del primo blocco.

Quando una transazione modifica una parte di dati, contiene determinati blocchi che proteggono la modifica fino alla fine della transazione. Per quanto tempo una transazione contiene i blocchi acquisiti per proteggere le operazioni di lettura dipende dall'impostazione del livello di isolamento delle transazioni e dal fatto che sia abilitato o meno il blocco ottimizzato.

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

  • Quando il blocco ottimizzato è abilitato, viene mantenuto solo un blocco ID transazione (TID) per la durata della transazione. Con il livello di isolamento predefinito, le transazioni non conterranno i blocchi di riga e di pagina necessari per le scritture fino alla fine della transazione. In questo modo si riduce la memoria di blocco necessaria e si riduce la necessità di escalation dei blocchi. Inoltre, quando è abilitato il blocco ottimizzato, l'ottimizzazione del blocco dopo la qualificazione (LAQ) valuta i predicati di una query sulla versione di cui è stato eseguito il commit più recente della riga senza acquisire un blocco, migliorando la concorrenza.

I blocchi acquisiti da una transazione vengono rilasciati al completamento della transazione, ovvero in corrispondenza del commit o del rollback.

I blocchi non vengono in genere richiesti direttamente dalle applicazioni, I blocchi vengono gestiti internamente da una parte di SQL Server motore di database denominata gestione blocchi. Quando un'istanza di SQL Server motore di database elabora un'istruzione Transact-SQL, SQL Server motore di database Query Processor determina le risorse a cui accedere. nonché i tipi di blocchi necessari per proteggere le singole risorse in base al tipo di accesso e all'impostazione del livello di isolamento della transazione. Query Processor richiede quindi i blocchi appropriati a Gestione blocchi, che li concede a meno che non si sia verificato un conflitto con blocchi acquisiti da altre transazioni.

Granularità e gerarchie di blocco

L'motore di database di SQL Server ha un blocco multigranulare che consente di bloccare diversi tipi di risorse tramite una transazione. Per ridurre al minimo il costo del blocco, SQL Server motore di database blocca automaticamente le risorse a un livello appropriato per l'attività. L'applicazione di un blocco con un livello di granularità inferiore, ad esempio a livello di riga, aumenta la concorrenza, ma anche l'overhead. Il numero dei blocchi da gestire infatti aumenta in modo proporzionale al numero di righe 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.

Sql Server motore di database spesso deve 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 una lettura di un indice, un'istanza del motore di database di SQL Server potrebbe dover acquisire blocchi di condivisione sulle righe e sui blocchi di condivisione delle finalità nelle pagine e nella tabella.

La tabella seguente illustra le risorse che l'motore di database di SQL Server può bloccare.

Risorsa Descrizione
RID ID di riga utilizzato per bloccare una singola riga all'interno di un heap.
KEY Blocco di riga all'interno di un indice utilizzato per proteggere intervalli di chiavi nelle transazioni serializzabili.
PAGE Pagina di 8 kilobyte (KB) in un database, ad esempio dati o pagine di indice.
EXTENT Gruppo contiguo di otto pagine, ad esempio di dati o di indice.
HoBT 1 Heap o albero B. Blocco che protegge un albero B (indice) o le pagine di dati dell'heap in una tabella che non dispone di un indice cluster.
TABELLA 1 Tabella intera, compresi tutti i dati e gli indici.
FILE File di database.
APPLICAZIONE Risorsa specificata dall'applicazione.
METADATI Blocchi a livello di metadati.
ALLOCATION_UNIT Unità di allocazione.
DATABASE Intero database.
XACT 2 Blocco ID transazione (TID) usato nel blocco ottimizzato. Vedere Blocco ID transazione (TID).

1 I blocchi HoBT e TABLE possono essere interessati dall'opzione LOCK_ESCALATION di ALTER TABLE.

2 Sono disponibili altre risorse di blocco per le risorse di blocco XACT, vedere Aggiunte di diagnostica per il blocco ottimizzato.

Modalità di blocco

SQL Server motore di database blocca le risorse usando diverse modalità di blocco che determinano come è possibile accedere alle risorse tramite transazioni simultanee.

Nella tabella seguente vengono illustrate le modalità di blocco delle risorse usate dal motore di database di SQL Server.

Modalità blocco Descrizione
Condiviso (S) Blocco utilizzato per operazioni di lettura che non comportano la modifica o l'aggiornamento dei dati, ad esempio l'istruzione SELECT.
Aggiornamento (U) Blocco utilizzato per risorse che possono essere aggiornate. Impedisce un tipo comune di deadlock che si verifica quando più sessioni leggono e bloccano le risorse ed eventualmente ne eseguono l'aggiornamento in un momento successivo.
Esclusivo (X) Blocco utilizzato per operazioni di modifica dei dati, ad esempio UPDATE, INSERT e DELETE. Garantisce che non possano essere eseguiti più aggiornamenti simultanei della stessa risorsa.
Intent Blocco utilizzato per definire una gerarchia di blocchi. Tra i tipi di blocchi preventivi sono inclusi i blocchi preventivi condivisi (IS), i blocchi preventivi esclusivi (IX) e i blocchi preventivi esclusivi condivisi (SIX).
Schema Blocco utilizzato quando è in esecuzione un'operazione dipendente dallo schema della tabella. Tra i tipi di blocchi di schema sono inclusi i blocchi di modifica dello schema (Sch-M) e i blocchi di stabilità dello schema (Sch-S).
Aggiornamento bulk (BU) Blocco usato durante le operazioni di copia bulk dei dati in una tabella quando viene specificato l'hint TABLOCK.
Intervalli di chiavi Modalità che consente di proteggere l'intervallo di righe lette da una query quando si utilizza il livello di isolamento della transazione Serializable. Garantisce che le altre transazioni non possano inserire righe da includere nelle query della transazione serializzabile se le query sono state nuovamente eseguite.

Blocchi condivisi

I blocchi condivisi (S) consentono la lettura (SELECT) di una risorsa da parte di transazioni simultanee con il controllo della concorrenza pessimistica. senza che altre transazioni possano modificare i dati mentre il blocco condiviso (S) è applicato alla risorsa. I blocchi condivisi 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 per l'intera durata della transazione.

Aggiornare i blocchi

Il motore di database inserisce blocchi di aggiornamento (U) durante la preparazione per l'esecuzione di un aggiornamento. I blocchi U sono compatibili con i blocchi S, ma solo una transazione può contenere un blocco U alla volta su una determinata risorsa. Questa è la chiave: molte transazioni simultanee possono contenere blocchi S, ma solo una transazione può contenere un blocco U su una risorsa. I blocchi di aggiornamento (U) vengono infine aggiornati a blocchi esclusivi (X) per aggiornare una riga.

I blocchi di aggiornamento (U) possono essere eseguiti anche da query che non eseguono un UPDATE, quando l'hint per la tabella UPDLOCK viene specificato nella query. È comune che le applicazioni usino un modello "select a row, then update the row", in cui la lettura e la scrittura sono separate in modo esplicito all'interno della transazione. In questo caso, se il livello di isolamento è ripetibile in lettura o serializzabile, gli aggiornamenti simultanei potrebbero probabilmente bloccarsi. Le applicazioni potrebbero invece seguire un modello "selezionare una riga con hint UPDLOCK, quindi aggiornare la riga".

  • In una transazione ripetibile in lettura o serializzabile, la transazione legge i dati, acquisisce un blocco condiviso (S) sulla risorsa e quindi modifica i dati, che richiedono la conversione del blocco in un blocco esclusivo (X). Se due transazioni acquisiscono blocchi condivisi (S) su una risorsa e quindi tentano di aggiornare i dati contemporaneamente, una transazione tenta la conversione del blocco in un blocco esclusivo (X). La conversione del blocco condiviso-esclusivo deve attendere perché il blocco esclusivo per una transazione non è compatibile con il blocco condiviso (S) dell'altra transazione; si verifica 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 read committed predefinito, i blocchi S sono di breve durata, rilasciati non appena vengono usati. È improbabile che i blocchi di durata breve generino deadlock.

  • Se l'hint UPDLOCK viene usato in una scrittura, la transazione deve avere accesso alla versione più recente della riga. Se la versione più recente non è più visibile, è previsto che sia possibile ricevere Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict quando è in uso l'isolamento SN piattaforma di strumenti analitici HOT. Per un esempio, vedere Usare l'isolamento dello 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 dati. 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 modifica e di lettura. 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 delle finalità

Sql Server motore di database usa i blocchi delle finalità per proteggere l'inserimento di un blocco condiviso (S) o esclusivo (X) su una risorsa inferiore nella gerarchia di blocchi. I blocchi delle finalità sono denominati "blocchi delle finalità" perché vengono acquisiti prima di un blocco a livello inferiore e, pertanto, segnalano la finalità di posizionare blocchi a un livello inferiore.

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.
  • Per migliorare l'efficienza del motore di database di SQL Server nel rilevamento dei conflitti di blocco a 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 delle finalità migliorano le prestazioni perché SQL Server motore di database esamina i blocchi delle finalità solo a livello di tabella per determinare se una transazione può acquisire in modo sicuro un blocco su tale tabella. In questo modo, l'esame di ogni singola riga o pagina della tabella non risulta più necessario.

Tra i blocchi preventivi sono inclusi i blocchi preventivi condivisi (IS), i blocchi preventivi esclusivi (IX) e i blocchi preventivi esclusivi condivisi (SIX).

Modalità blocco Descrizione
Blocco preventivo condiviso (IS) Consente di proteggere i blocchi condivisi richiesti o acquisiti su alcune risorse, ma non tutte, di livello inferiore nella gerarchia.
Blocco preventivo esclusivo (IX) Consente di proteggere i blocchi esclusivi richiesti o acquisiti su alcune risorse, ma non tutte, di livello inferiore nella gerarchia. IX rappresenta un superset di IS e consente inoltre di proteggere la richiesta di blocchi condivisi sulle risorse di livello inferiore.
Blocco condiviso preventivo esclusivo (SIX) Consente di proteggere i blocchi condivisi richiesti o acquisiti su tutte le risorse di livello inferiore nella gerarchia e i blocchi preventivi esclusivi su alcune risorse, ma non tutte, di livello inferiore. I blocchi IS simultanei possono essere applicati alle risorse di livello principale. L'acquisizione di un blocco SIX su una tabella, ad esempio, comporta inoltre l'acquisizione di blocchi preventivi esclusivi sulle pagine di cui è in corso la modifica e di blocchi esclusivi sulle righe modificate. In un momento specifico è possibile impostare su ogni risorsa un solo blocco SIX per impedire aggiornamenti della risorsa da parte di altre transazioni, le quali, tuttavia, possono leggere le risorse di livello inferiore nella gerarchia ottenendo blocchi IS a livello di tabella.
Aggiornamento preventivo (IU) Consente di proteggere i blocchi di aggiornamento richiesti o acquisiti su tutte le risorse di livello inferiore nella gerarchia. I blocchi IU vengono utilizzati solo sulle pagine. Tali blocchi vengono convertiti in blocchi IX se viene eseguita un'operazione di aggiornamento.
Condiviso preventivo aggiornamento (SIU) Combinazione di blocchi S e IU, in seguito all'acquisizione di tali blocchi in modo distinto e simultaneo mantenendo entrambi i blocchi. Una transazione, ad esempio, esegue una query con l'hint PAGLOCK e quindi effettua un'operazione di aggiornamento. La query con l'hint PAGLOCK acquisisce il blocco S, mentre l'operazione di aggiornamento acquisisce il blocco IU.
Aggiornamento preventivo esclusivo (UIX) Combinazione di blocchi U e IX, in seguito all'acquisizione di tali blocchi in modo distinto e simultaneo mantenendo entrambi i blocchi.

Blocchi dello schema

Sql Server motore di database usa blocchi di modifica dello schema (Sch-M) durante un'operazione DDL (Data Definition Language) della tabella, ad esempio l'aggiunta di una colonna o l'eliminazione di una tabella. Finché viene mantenuto attivo, il blocco Sch-M impedisce l'accesso simultaneo alla tabella, ovvero blocca tutte le operazioni esterne.

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.

Sql Server motore di database usa blocchi di stabilità dello schema (Sch-S) durante la compilazione e l'esecuzione di query. Questo tipo di blocco non esclude i blocchi transazionali, inclusi i blocchi esclusivi (X). Durante la compilazione di una query è pertanto possibile continuare l'esecuzione di altre transazioni, incluse quelle che prevedono blocchi X su tabelle. Non è tuttavia possibile eseguire su tabelle operazioni DDL e DML simultanee che acquisiscono blocchi Sch-M.

Blocchi di aggiornamento in blocco

I blocchi aggiornamenti bulk consentono a più thread di eseguire operazioni simultanee di caricamento bulk dei dati nella stessa tabella, impedendo l'accesso alla tabella ai processi che non eseguono il caricamento bulk. Sql Server motore di database usa blocchi di aggiornamento bulk quando entrambe le condizioni seguenti sono vere.

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

Suggerimento

A differenza dell'istruzione BULK IN edizione Standard RT, che contiene un blocco bulk update (BU) meno restrittivo, IN edizione Standard RT INTO... edizione Standard LECT con l'hint TABLOCK contiene un blocco esclusivo finalità (IX) sulla tabella. che non consente di inserire righe utilizzando operazioni di inserimento parallele.

Blocchi dell'intervallo di chiavi

I blocchi dell'intervallo di chiavi proteggono un intervallo di righe incluse in modo implicito in un set di record letto da un'istruzione Transact-SQL usando il livello di isolamento delle transazioni serializzabile. Il blocco di intervalli di chiavi impedisce le letture fantasma, Tramite la protezione degli intervalli di chiavi tra righe, tali blocchi impediscono inserimenti o eliminazioni fantasma in un recordset a cui accede una transazione.

Compatibilità tra blocchi

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 attende il rilascio del blocco esistente o la scadenza dell'intervallo di timeout di 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. In alternativa, se è stato applicato un blocco condiviso (S) a una risorsa, altre transazioni possono anche acquisire un blocco condiviso o un blocco di aggiornamento (U) su tale elemento anche se la prima transazione non è stata completata. Le altre transazioni, tuttavia, possono acquisire un blocco esclusivo solo dopo il rilascio del blocco condiviso.

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

Modalità concessa esistente IS S U IX SIX X
Modalità richiesta
Blocco preventivo condiviso (IS) No
Condiviso (S) No No No
Aggiornamento (U) No No No No
Blocco preventivo esclusivo (IX) No No No No
Blocco condiviso preventivo esclusivo (SIX) No No No No No
Esclusivo (X) No No No No No Numero

Nota

I blocchi preventivi esclusivi (IX) sono compatibili con la modalità di blocco IX perché tale modalità prevede l'intenzione di aggiornamento solo di alcune righe e non di tutte. Altre transazioni possono pertanto accedere alle risorse per la lettura o l'aggiornamento di alcune righe, a condizione che non utilizzino le righe già in fase di aggiornamento. Se viene eseguito il tentativo di aggiornare la stessa riga da parte di due transazioni, a entrambe viene concesso il blocco IX a livello di tabella e pagina. Un blocco X a livello di riga viene tuttavia concesso a una delle transazioni. L'altra transazione dovrà rimanere in attesa fino alla rimozione di tale blocco.

Usare la tabella seguente per determinare la compatibilità di tutte le modalità di blocco disponibili in SQL Server.

A table showing a matrix of lock conflicts and compatibility.

Blocco dell'intervallo di chiavi

I blocchi dell'intervallo di chiavi proteggono un intervallo di righe incluse in modo implicito in un set di record letto da un'istruzione Transact-SQL usando il livello di isolamento delle transazioni serializzabile. Con questo livello di isolamento, una query eseguita durante una transazione deve ottenere sempre lo stesso set di righe ogni volta che viene eseguita all'interno della stessa transazione. Il blocco di intervalli di chiavi garantisce che ciò avvenga impedendo ad altre transazioni di inserire nuove righe le cui chiavi rientrerebbero nell'intervallo di chiavi letto dalla transazione serializzabile.

Il blocco di intervalli di chiavi impedisce le letture fantasma, nonché gli inserimenti fantasma in un recordset a cui accede una transazione attraverso la protezione degli intervalli di chiavi tra le righe.

Il blocco di intervalli di chiavi viene applicato a un indice specificando i valori iniziale e finale delle chiavi. In questo modo si impedisce ogni tentativo di inserimento, aggiornamento o eliminazione di una riga con un valore di chiave che rientra nell'intervallo in quanto tali operazioni dovrebbero acquisire un blocco sull'indice. Ad esempio, una transazione serializzabile 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 dell'intervallo di chiavi

I blocchi di intervalli di chiavi includono sia un componente intervallo sia un componente riga nel formato intervallo-riga:

  • Intervallo indica la modalità di blocco che protegge l'intervallo di righe compreso tra due voci di indice consecutive.
  • Riga indica la modalità di blocco che protegge la voce di 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.
Intervallo Riga Mode Descrizione
RangeS S RangeS-S Intervallo condiviso, blocco di risorsa condiviso, analisi intervallo serializzabile.
RangeS U RangeS-U Intervallo condiviso, blocco di risorsa di aggiornamento; analisi aggiornamento serializzabile.
RangeI Null RangeI-N Intervallo di inserimento, blocco di risorsa Null; utilizzato per verificare gli intervalli prima di inserire una nuova chiave nell'indice.
RangeX X RangeX-X Intervallo esclusivo, blocco di risorsa esclusivo; utilizzato per aggiornare una chiave di un intervallo.

Nota

La modalità di blocco Null interna è compatibile con tutti gli altri tipi di blocco.

Le modalità di blocco di intervalli di chiavi sono basate sulla matrice di compatibilità illustrata di seguito che stabilisce quali blocchi sono compatibili con quelli ottenuti dalla sovrapposizione di chiavi e intervalli.

Modalità concessa esistente S U X RangeS-S RangeS-U RangeI-N RangeX-X
Modalità richiesta
Condiviso (S) No No
Aggiornamento (U) No No No No
Esclusivo (X) No No No No No No
RangeS-S No No No
RangeS-U No No No No No
RangeI-N No No No
RangeX-X No No No No No No No

Blocchi di conversione

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

Blocco 1 Blocco 2 Blocco di conversione
S RangeI-N RangeI-S
U RangeI-N RangeI-U
X RangeI-N RangeI-X
RangeI-N RangeS-S RangeX-S
RangeI-N RangeS-U RangeX-U

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 dell'intervallo serializzabile, recupero singleton, eliminazione e inserimento

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

  • Query di analisi intervallo
  • Recupero singleton di righe inesistenti
  • Operazioni di eliminazione
  • Operazioni 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. Ad esempio, la clausola WHERE in un'istruzione edizione Standard LECT potrebbe stabilire una condizione di intervallo con questo predicato: ColumnX BETW edizione Enterprise N N**'AAA'** AND N**'CZZ'**. Un blocco di intervalli di chiavi può essere acquisito solo se ColumnX è coperto da una chiave di indice.

Esempi

Gli esempi di blocco di intervalli di chiavi illustrati si basano sulla tabella e sull'indice seguenti.

A diagram of a sample of a Btree.

Query di analisi intervallo

Per assicurare che una query per l'analisi di intervalli sia serializzabile, è necessario che la query restituisca gli stessi risultati a ogni esecuzione 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 di dati 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 nome Adam di nuovi nomi che iniziano con la lettera A, 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 il nome Carlos di nuovi nomi che iniziano con la lettera C, ad esempio Clive.

Nota

Il numero di blocchi RangeS-S mantenuti attivi è n+1, dove n è il numero di righe che soddisfano la query.

Recupero singleton di dati inesistenti

Se una query all'interno di una transazione tenta di selezionare una riga che non esiste, l'esecuzione della query in un secondo momento all'interno della stessa transazione deve restituire lo stesso 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 intervalli di chiavi viene applicato alla voce di indice corrispondente all'intervallo di nomi compreso tra Ben e Bing in quanto il nome Bill verrebbe incluso alfabeticamente tra queste due voci di indice. 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 blocco ottimizzato

Quando si elimina un valore all'interno di una transazione, l'intervallo in cui il valore rientra non deve essere bloccato per la 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 il valore eliminato Bob. I tentativi di lettura, inserimento o eliminazione del valore Bob vengono invece bloccati fino a quando non viene eseguito il commit o il rollback della transazione che esegue l'eliminazione. L'opzione di database READ_COMMITTED_SN piattaforma di strumenti analitici HOT e il livello di isolamento SN piattaforma di strumenti analitici HOT consentono anche le letture da una versione riga 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 usa PAGLOCK o TABLOCK, SQL Server motore di database dealloca immediatamente una pagina di indice se tutte le righe vengono eliminate da questa pagina. Quando invece si utilizza ROWLOCK, le righe eliminate vengono semplicemente contrassegnate come eliminate e vengono rimosse dalla pagina di indice in un momento successivo tramite un processo in background.

Operazione di eliminazione, con blocco ottimizzato

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

DELETE mytable
WHERE name = 'Bob';

Un blocco TID viene posizionato su tutte le righe modificate per la durata della transazione. Un blocco viene acquisito nel TID delle voci di indice corrispondenti al nome Bob. Con il blocco ottimizzato, i blocchi di pagina e di riga continuano a essere acquisiti per gli aggiornamenti, ma ogni pagina e blocco di riga vengono rilasciati non appena ogni riga viene aggiornata. Il blocco TID protegge le righe dall'aggiornamento fino al completamento della transazione. Tutte le transazioni che tentano di leggere, inserire o eliminare il valore Bob verranno bloccate fino a quando la transazione di eliminazione non esegue il commit o il rollback. L'opzione di database READ_COMMITTED_SN piattaforma di strumenti analitici HOT e il livello di isolamento SN piattaforma di strumenti analitici HOT consentono anche le letture da una versione riga dello stato di cui è stato eseguito il commit in precedenza.

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

Operazione di inserimento senza blocco ottimizzato

Quando si inserisce un valore all'interno di una transazione, l'intervallo in cui il valore rientra non deve essere bloccato per la 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 intervallo di chiavi in modalità RangeI-N viene posizionato sulla voce di indice corrispondente al nome David per testare l'intervallo. Se il blocco viene concesso, viene inserita la voce Dan e viene impostato un blocco esclusivo (X) sul valore Dan. Il blocco intervallo di chiavi in modalità RangeI-N è necessario solo per testare 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 valore inserito Dan. Le transazioni che tuttavia tentano di leggere, inserire o eliminare il valore Dan sono bloccate fino a quando non viene eseguito il commit o il rollback della transazione di inserimento.

Operazione di inserimento con blocco ottimizzato

Quando si inserisce un valore all'interno di una transazione, l'intervallo in cui il valore rientra non deve essere bloccato per la 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 serializzabili nell'istanza. Se vengono acquisiti blocchi di riga e di pagina, vengono rilasciati rapidamente e non mantenuti per la durata della transazione. Posizionare un blocco TID esclusivo sul valore della chiave inserita fino alla fine della transazione è sufficiente per mantenere la serializzabilità. Si consideri ad esempio l'istruzione INSERT seguente:

INSERT mytable VALUES ('Dan');

Con il blocco ottimizzato, un blocco RangeI-N viene acquisito solo se nell'istanza viene usata almeno una transazione che usa il livello di isolamento edizione Standard RIALIZABLE. Il blocco intervallo di chiavi in modalità RangeI-N viene posizionato sulla voce di indice corrispondente al nome David per testare l'intervallo. Se il blocco viene concesso, viene inserita la voce Dan e viene impostato un blocco esclusivo (X) sul valore Dan. Il blocco intervallo di chiavi in modalità RangeI-N è necessario solo per testare 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 valore inserito Dan. Le transazioni che tuttavia tentano di leggere, inserire o eliminare il valore Dan sono bloccate fino a quando non viene eseguito il commit o il rollback della transazione di inserimento.

Escalation blocchi

L'escalation dei blocchi è il processo di conversione di molti blocchi con granularità fine in blocchi con granularità più grossolana, riducendo il sovraccarico del sistema aumentando la probabilità di contesa di concorrenza.

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

Escalation blocchi senza blocco ottimizzato

Man mano che sql Server motore di database acquisisce blocchi di basso livello, inserisce anche blocchi delle finalità sugli oggetti che contengono gli oggetti di livello inferiore:

  • Quando si bloccano righe o intervalli di chiavi di indice, il motore di database posiziona un blocco finalità sulle pagine che contengono le righe o le chiavi.
  • Quando si bloccano le pagine, il motore di database posiziona un blocco finalità sugli oggetti di livello superiore che contengono le pagine. Oltre al blocco finalità sull'oggetto, i blocchi di pagina delle finalità vengono richiesti negli oggetti seguenti:
    • Pagine a livello foglia di indici non cluster
    • 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, inserire blocchi di pagina in un indice non cluster, se è selezionato un numero sufficiente di chiavi contigue nel nodo dell'indice per soddisfare la query, e blocchi di riga nei dati.

Per eseguire l'escalation dei blocchi, il motore di database tenta di modificare il blocco della finalità nella tabella con il blocco completo corrispondente, ad esempio modificando un blocco esclusivo (IX) finalità in un blocco esclusivo (X) o un blocco condiviso (IS) con finalità condivisa (S). Se il tentativo di escalation blocchi ha esito positivo e viene acquisito il blocco completo della tabella, vengono rilasciati tutti i blocchi heap o albero B, pagina (PAGE) o RID (Row-Level) mantenuti dalla transazione nell'heap o nell'indice. Se non è possibile acquisire un blocco completo, non viene eseguita alcuna escalation blocchi e il motore di database continua ad acquisire blocchi a livello di riga, chiave o pagina.

Il motore di database non esegue l'escalation di blocchi di riga o intervallo di chiavi ai blocchi di pagina, ma li inoltra direttamente ai blocchi di tabella. Analogamente, i blocchi di pagina vengono sempre inoltrati 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 HoBT non blocca necessariamente gli hoBT allineati per la partizione.

Nota

I blocchi a livello hoBT aumentano in genere la concorrenza, ma introducono il potenziale di deadlock quando le transazioni che bloccano partizioni diverse, ognuna vuole espandere i blocchi esclusivi alle altre partizioni. In rari casi, la granularità del blocco TABLE potrebbe offrire prestazioni migliori.

Se un tentativo di escalation blocchi non riesce a causa di blocchi in conflitto mantenuti da transazioni simultanee, il motore di database ritenta l'escalation dei blocchi per ogni blocco aggiuntivo di 1.250 blocchi acquisiti dalla transazione.

Ogni evento di escalation opera principalmente a livello di una singola istruzione Transact-SQL. All'avvio dell'evento, il motore di database tenta di inoltrare tutti i blocchi di proprietà della transazione corrente in una delle tabelle a cui fa riferimento l'istruzione attiva, purché soddisfi i requisiti di soglia di escalation. Se l'evento di escalation inizia prima che l'istruzione abbia eseguito l'accesso a una tabella, non viene effettuato alcun tentativo di escalation dei blocchi in tale tabella. Se l'escalation dei blocchi ha esito positivo, tutti i blocchi acquisiti dalla transazione in un'istruzione precedente e mantenuti al momento dell'avvio dell'evento verranno inoltrati se la tabella fa riferimento all'istruzione corrente e viene inclusa nell'evento di escalation.

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

  • Avvio di una transazione.
  • Aggiorna TableA. In questo modo vengono generati blocchi di riga esclusivi in TableA che vengono mantenuti fino al completamento della transazione.
  • Aggiorna TableB. In questo modo vengono generati blocchi di riga esclusivi in TableB che vengono mantenuti fino al completamento della transazione.
  • Esegue un'operazione SELECT per unire in join 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 blocchi durante il recupero delle righe da TableA e prima dell'accesso a TableC.

Se l'escalation blocchi viene eseguita correttamente, verrà eseguita l'escalation solo dei blocchi attivati dalla sessione in TableA, Sono inclusi sia i blocchi condivisi dall'istruzione edizione Standard LECT che i blocchi esclusivi dell'istruzione UPDATE precedente. Mentre per determinare se è necessario eseguire l'escalation blocchi vengono conteggiati solo i blocchi acquisiti dalla sessione in TableA per l'istruzione SELECT, dopo che l'escalation ha avuto esito positivo viene eseguita l'escalation di tutti i blocchi attivati dalla sessione in TableA in un blocco esclusivo nella tabella e tutti gli altri blocchi con granularità inferiore, inclusi i blocchi preventivi, in TableA vengono rilasciati.

Non viene eseguito alcun tentativo di escalation blocchi in TableB in quanto nell'istruzione SELECT non vi erano riferimenti attivi a TableB. Analogamente, non viene eseguito alcun tentativo di escalation blocchi in TableC in quanto al momento dell'escalation non era ancora stato eseguito l'accesso alla tabella.

Escalation blocchi con blocco ottimizzato

Il blocco ottimizzato consente di ridurre la memoria di blocco perché per la durata della transazione vengono mantenuti pochissimi blocchi. Man mano che sql Server motore di database acquisisce blocchi di riga e di pagina, l'escalation dei blocchi può verificarsi in modo analogo, ma molto meno frequente. Il blocco ottimizzato in genere riesce a evitare escalation di blocchi, riducendo il numero di blocchi e la quantità di memoria di blocco necessaria.

Quando il blocco ottimizzato è abilitato e, nel livello di isolamento READ COMMITTED predefinito, il motore di database rilascia blocchi di riga e di pagina non appena la scrittura viene completata. Non vengono mantenuti blocchi di riga e di pagina per la durata della transazione, ad eccezione di un singolo blocco ID transazione (TID). In questo modo si riduce la probabilità di escalation dei blocchi.

Soglie di escalation blocchi

L'escalation dei blocchi viene attivata quando l'escalation dei blocchi non è disabilitata nella tabella usando l'opzione ALTER TABLE SET LOCK_ESCALATION e quando esiste una delle condizioni seguenti:

  • Una singola istruzione Transact-SQL acquisisce almeno 5.000 blocchi su una singola tabella o indice non partizionato.
  • Una singola istruzione Transact-SQL acquisisce almeno 5.000 blocchi su 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 le soglie di memoria o configurazione.

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

Soglia di escalation per un'istruzione Transact-SQL

Quando il motore di database verifica la presenza di possibili escalation ogni 1.250 blocchi appena acquisiti, si verificherà un'escalation dei blocchi se e solo se un'istruzione Transact-SQL ha acquisito almeno 5.000 blocchi su un singolo riferimento di una tabella. L'escalation dei blocchi viene attivata quando un'istruzione Transact-SQL acquisisce almeno 5.000 blocchi su un singolo riferimento di una tabella. Ad esempio, l'escalation dei 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 blocchi non viene attivata se un'istruzione ha un self join in una tabella e ogni riferimento alla tabella acquisisce solo 3.000 blocchi nella tabella.

L'escalation dei blocchi si verifica solo per le tabelle a cui è stato eseguito l'accesso al momento dell'attivazione dell'escalation. 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, ma non ha ancora eseguito l'accesso TableCa . Quando il motore di database rileva che l'istruzione ha acquisito almeno 5.000 blocchi di riga in TableB, tenta di inoltrare tutti i blocchi mantenuti dalla transazione corrente in TableB. Tenta anche l'escalation di tutti i blocchi posti dalla transazione corrente su TableA, ma poiché il numero di blocchi su TableA è minore di 5.000, l'escalation non riesce. In TableC non viene eseguito alcun tentativo di escalation in quanto al momento dell'escalation non è ancora stato effettuato l'accesso alla tabella.

Soglia di escalation per un'istanza del motore di database

Ogni volta che il numero di blocchi è maggiore della soglia di memoria per l'escalation dei blocchi, il motore di database attiva l'escalation dei blocchi. La soglia di memoria dipende dall'impostazione dell'opzione di configurazione locks:

  • Se l'opzione locks viene impostata sul valore predefinito 0, la soglia di escalation blocchi viene raggiunta quando la memoria usata dagli oggetti blocco rappresenta il 24% della memoria usata dal motore di database, esclusa la memoria AWE. La struttura di dati utilizzata per rappresentare un blocco è 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 per l'opzione locks viene impostato un valore diverso da 0, la soglia di escalation blocchi corrisponde al 40%, o meno in caso di elevato uso della memoria, del valore dell'opzione.

Il motore di database può scegliere qualsiasi istruzione attiva da qualsiasi sessione per l'escalation e per ogni 1.250 nuovi blocchi sceglierà le istruzioni per l'escalation, purché la memoria di blocco usata nell'istanza rimanga al di sopra della soglia.

Eseguire l'escalation dei tipi di blocco misti

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

Si supponga, ad esempio, che una sessione:

  • Avvio di una transazione.
  • Aggiornamenti una tabella contenente un indice cluster.
  • Esegue un'istruzione edizione Standard LECT che fa riferimento alla stessa tabella.

L'istruzione UPDATE acquisisce questi blocchi:

  • Blocchi esclusivi (X) sulle righe di dati aggiornate.
  • La finalità esclusiva (IX) blocca le pagine di indice cluster contenenti tali righe.
  • Un blocco IX sull'indice cluster e un altro nella tabella.

L'istruzione edizione Standard LECT acquisisce questi blocchi:

  • Condiviso (S) blocca tutte le righe di dati lette, a meno che la riga non sia già protetta da un blocco X dall'istruzione UPDATE.
  • Condivisione finalità blocca in tutte le pagine di indice cluster 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 blocchi IX.

Se l'istruzione edizione Standard LECT acquisisce blocchi sufficienti per attivare l'escalation dei blocchi e l'escalation ha esito positivo, il blocco IX nella tabella viene convertito in un blocco X e tutte le righe, le pagine e i blocchi di indice vengono liberati. Sia gli aggiornamenti che le letture sono protetti dal blocco X nella tabella.

Ridurre il blocco e l'escalation

Nella maggior parte dei casi, il motore di database offre prestazioni ottimali quando si utilizzano le impostazioni predefinite per l'escalation di blocchi e blocchi.

Se un'istanza del motore di database genera molti blocchi e visualizza frequenti escalation di blocchi, valutare la possibilità di ridurre la quantità di blocco con le strategie seguenti:

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

    • Livello di isolamento READ COMMITTED quando l'opzione di database READ_COMMITTED_SN piattaforma di strumenti analitici HOT è impostata su ON.

    • Livello di isolamento SN piattaforma di strumenti analitici HOT.

    • READ LIVELLO di isolamento UNCOMMITTED. Questo può essere usato solo per i sistemi che possono funzionare con letture dirty.

      Nota

      La modifica del livello di isolamento influisce su tutte le tabelle nell'istanza del motore di database.

  • Usare gli hint di tabella PAGLOCK o TABLOCK per fare in modo che il motore di database usi blocchi di pagina, heap o indice anziché 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.

  • Quando il blocco ottimizzato non è abilitato, per le tabelle partizionate, usare l'opzione LOCK_ESCALATION di ALTER TABLE per inoltrare i blocchi al livello HoBT anziché alla tabella o per disabilitare l'escalation dei blocchi.

  • 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 record obsoleti da una tabella di controllo, per poi scoprire che l'operazione ha causato un'escalation blocchi che blocca altri utenti:

    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    

    Rimuovendo questi record alcune centinaia alla volta, è possibile ridurre notevolmente il numero di blocchi che si accumulano per ogni transazione ed evitare l'escalation blocchi. Ad esempio:

    SET ROWCOUNT 500
    delete_more:
      DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    IF @@ROWCOUNT > 0 GOTO delete_more
    SET ROWCOUNT 0
    
  • Ridurre l'impatto a livello di blocco di una query aumentandone il più possibile l'efficienza. Le scansioni estese o la presenza di un numero elevato di ricerche di segnalibri possono aumentare la probabilità di escalation blocchi. Inoltre, aumenta la probabilità di deadlock e, in genere, vi sono effetti negativi sulla concorrenza e sulle prestazioni. Dopo aver individuato la query che causa l'escalation blocchi, cercare opportunità di creare nuovi indici o di aggiungere colonne a un indice esistente per rimuovere le analisi di indici o le scansioni di tabella e per ottimizzare l'efficienza delle ricerche nell'indice. Per eseguire un'analisi automatica degli indici per la query, provare a usare Ottimizzazione guidata motore di database. Per altre informazioni, vedere Esercitazione: Ottimizzazione guidata motore di database. 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 segnalibri (massimizzazione della selettività dell'indice per la query specifica). Se il motore di database stima che un operatore logico Bookmark Lookup possa restituire molte righe, può usare un PREFETCH per eseguire la ricerca del segnalibro. Se il motore di database usa PREFETCH per una ricerca di segnalibri, è necessario aumentare il livello di isolamento delle transazioni di una parte della query per la lettura ripetibile per una parte della query. Questo significa che un'operazione simile nell'aspetto a un'istruzione SELECT con livello di isolamento Read Committed potrebbe acquisire molte migliaia di blocchi di chiave (sia nell'indice cluster che in un indice non cluster) causando il superamento delle soglie di escalation blocchi per una query. Ciò è particolarmente importante se si rileva che il blocco con escalation è un blocco di tabella condiviso, che, tuttavia, non viene comunemente visualizzato a livello di isolamento read committed predefinito.

    Se una clausola Bookmark Lookup WITH PREFETCH causa l'escalation, è consigliabile aggiungere altre colonne all'indice non cluster visualizzato nell'operatore logico Index Seek o Index Scan sotto l'operatore logico Bookmark Lookup nel piano di query. È possibile creare un indice di copertura, ovvero un indice che includa tutte le colonne di una tabella usate nella query, o almeno un indice a copertura delle colonne usate per i criteri di join o nella clausola WHERE se includere tutto nell'elenco di selezione delle colonne è poco pratico. Anche un join a cicli annidati può usare PREFETCH e questo causa lo stesso comportamento di blocco.

  • Non è possibile eseguire l'escalation blocchi se uno SPID diverso mantiene attualmente attivo un blocco di tabella non compatibile. La destinazione di un'escalation blocchi è sempre un blocco di tabella e mai un blocco di pagina. Inoltre, se un tentativo di escalation blocchi ha esito negativo perché un altro SPID contiene un blocco TAB incompatibile, la query che ha tentato l'escalation non si blocca durante l'attesa di un blocco TAB. Continua invece ad acquisire blocchi al livello originale, più granulare (riga, chiave o pagina), effettuando periodicamente ulteriori tentativi di escalation. Pertanto, un metodo per impedire l'escalation dei blocchi in una determinata tabella consiste nell'acquisire e mantenere un blocco su una connessione diversa che non è compatibile con il tipo di blocco inoltrato. Un blocco IX (esclusivo) a livello di tabella non blocca righe o pagine, ma non è ancora compatibile con un blocco TAB (condiviso) o X (esclusivo). Si supponga, ad esempio, di dover eseguire un processo batch che modifica un numero elevato di righe nella tabella mytable e che ha causato un blocco che si verifica a causa dell'escalation blocchi. Se questo processo viene sempre completato in meno di un'ora, è possibile creare un processo Transact-SQL contenente il codice seguente e pianificare l'avvio del nuovo processo per 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 blocchi sulla tabella durante tale periodo di tempo. Questo batch non modifica dati o blocca altre query (a meno che l'altra query non forza un blocco di tabella con l'hint TABLOCK o se un amministratore ha disabilitato blocchi di pagina o di riga tramite una sp_indexoption stored procedure).

  • È anche possibile usare i flag di traccia 1211 e 1224 per disabilitare tutte o alcune escalation di blocchi. Tuttavia, questi flag di traccia disabilitano tutte le escalation dei blocchi a livello globale per l'intero motore di database. L'escalation dei blocchi serve uno scopo molto utile nella motore di database ottimizzando l'efficienza delle query che altrimenti rallentano l'overhead dell'acquisizione e del rilascio di diverse migliaia di blocchi. L'escalation blocchi è anche utile per ridurre al minimo la memoria necessaria per tenere traccia dei blocchi. La memoria che l'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.

    Nota

    Quando si verifica l'errore MSSQL edizione Standard RVER_1204, interrompe l'elaborazione dell'istruzione corrente e genera un rollback della transazione attiva. Il rollback stesso può bloccare gli utenti o causare un tempo di ripristino del database lungo se si riavvia il servizio di database.

    Nota

    L'uso di un hint di blocco, come ROWLOCK, modifica solo il piano di blocco iniziale. Gli hint di blocco non impediscono l'escalation blocchi.

Monitorare l'escalation dei blocchi

Monitorare l'escalation dei blocchi usando l'evento lock_escalation esteso (xEvent), ad esempio 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

Importante

L'evento lock_escalation esteso (xEvent) deve essere usato anziché la classe di evento Lock:Escalation in TRACCIA SQL o SQL Profiler.

Blocco dinamico

L'utilizzo dei blocchi a basso livello, ad esempio blocchi di riga, aumenta la concorrenza riducendo la probabilità che due transazioni richiedano i blocchi sullo stesso elemento di dati contemporaneamente. L'utilizzo dei blocchi a basso livello inoltre aumenta il numero dei blocchi e delle risorse necessarie a gestirli. I blocchi di tabella e di pagina ad alto livello comportano invece una diminuzione dell'overhead, ma a spese della concorrenza.

A graph of locking cost vs. concurrency cost.

Sql Server motore di database usa una strategia di blocco dinamico per determinare i blocchi più convenienti. Sql Server motore di database determina automaticamente quali blocchi sono più appropriati quando viene eseguita la query, in base alle caratteristiche dello schema e della query. Ad esempio, per ridurre l'overhead associato al blocco quando viene eseguita l'analisi di un indice, Query Optimizer potrebbe scegliere un blocco a livello di pagina per l'indice.

L'utilizzo del blocco dinamico offre i vantaggi seguenti:

  • Amministrazione del database semplificata. Gli amministratori di database non devono gestire le soglie di escalation dei blocchi.
  • Aumento delle prestazioni. SQL Server motore di database riduce al minimo il sovraccarico del sistema usando i blocchi appropriati per l'attività.
  • Gli sviluppatori di applicazioni possono dedicarsi interamente alle operazioni di sviluppo, Il motore di database di SQL Server regola automaticamente il blocco.

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

Partizionamento blocchi

Nei sistemi di computer di grandi dimensioni, i blocchi su oggetti di riferimento utilizzati di frequente può creare colli di bottiglia a livello delle prestazioni poiché l'acquisizione e il rilascio di blocchi determina la contesa nelle risorse di blocco interne. Il partizionamento dei blocchi migliora le prestazioni poiché suddivide una singola risorsa di blocco in più risorse di blocco. Questa caratteristica è disponibile unicamente per i sistemi con 16 o più CPU, viene abilitata automaticamente e non è possibile disabilitarla. È 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 blocchi

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, è possibile che si verifichi una contesa tra le risorse di blocco che rimangono in attesa della disponibilità dello spinlock. 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.

  • Memoria. Consente di 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 ai blocchi su più risorse consente di eliminare la necessità di trasferire blocchi di memoria tra le CPU e di migliorare pertanto le prestazioni.

Implementare e monitorare il partizionamento dei blocchi

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

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 dei blocchi condivisi (S) ed esclusivi (X) e degli altri blocchi in modalità diverse da NL, SCH-S, IS, IU e IX su tutte le partizioni deve essere eseguita a partire dalla partizione con ID 0 e successivamente in base all'ordine degli ID di partizione. Questi blocchi su una risorsa partizionata utilizzano una quantità di memoria maggiore rispetto ai blocchi con la stessa modalità su una risorsa non partizionata, poiché ogni partizione rappresenta in effetti un blocco distinto. L'aumento della memoria è determinato dal numero delle partizioni. I contatori di blocco di SQL Server in Windows Monitor prestazioni visualizzeranno 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 DMV sys.dm_tran_locks è disponibile l'ID di partizione di blocco per una risorsa di blocco partizionata. Per altre informazioni, vedere sys.dm_tran_locks (Transact-SQL).

Usare il partizionamento dei blocchi

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

Queste istruzioni Transact-SQL creano oggetti test usati negli esempi seguenti.

-- 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, l'istruzione acquisisce e mantiene un blocco preventivo condiviso (IS) sulla tabella. Nell'esempio, i blocchi di riga e di pagina vengono ignorati. Il blocco IS viene acquisito solo nella partizione assegnata alla transazione. In questo esempio si presume che il blocco IS venga acquisito nell'ID partizione 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 eseguita nella transazione acquisisce e mantiene un blocco condiviso (S) sulla tabella. Il blocco S verrà acquisito in tutte le partizioni, che comporta più blocchi di tabella, uno per ogni partizione. Ad esempio, in un sistema con 16 CPU verranno applicati 16 blocchi S negli ID di partizione di blocco da 0 a 15. Il blocco S è compatibile con il blocco IS mantenuto dalla transazione sull'ID di partizione 7 nella sessione 1 e pertanto non si verifica 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'hint di blocco della tabella esclusivo (X), la transazione tenta di acquisire un blocco X sulla tabella. Il blocco S mantenuto dalla transazione nella sessione 2 blocca tuttavia il blocco X in corrispondenza dell'ID di partizione 0.

SELECT col1
FROM TestTable
WITH (TABLOCKX);

Esempio B

Sessione 1:

Un'istruzione SELECT viene eseguita in una transazione. A causa dell'hint di blocco HOLDLOCK, l'istruzione acquisisce e mantiene un blocco preventivo condiviso (IS) sulla tabella. Nell'esempio, i blocchi di riga e di pagina vengono ignorati. Il blocco IS viene acquisito solo nella partizione assegnata alla transazione. In questo esempio si presume che il blocco IS venga acquisito nell'ID di partizione 6.

-- 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 in una transazione. A causa dell'hint 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. Tale blocco viene acquisito per tutti gli ID di partizione da 0 a 5 ma viene bloccato dal blocco IS acquisito per l'ID di partizione 6.

Negli ID partizione da 7 a 15 che il blocco X non è ancora stato raggiunto, 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 di SQL Server

A partire da SQL Server 2005 (9.x), SQL Server motore di database offre un'implementazione di un livello di isolamento delle transazioni esistente, read committed, che fornisce uno snapshot a livello di istruzione usando il controllo delle versioni delle righe. Nel motore di database di SQL Server è stato inoltre introdotto un nuovo livello di isolamento della transazione, ovvero 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 framework generale in SQL Server che richiama un meccanismo di 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 controllo delle versioni delle righe utilizzato per eseguire quanto segue:

  • Compilare le tabelle inserite ed eliminate nei trigger. Le righe modificate dal trigger vengono sottoposte al controllo delle versioni. 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 genera un'istruzione di modifica dei dati (ad esempio INSERT, UPDATE o DELETE) nel momento in cui è attivo un set di risultati, le righe influenzate dall'istruzione di modifica sono sottoposte al controllo delle versioni.
  • Supportare operazioni di indice che specificano l'opzione ONLINE.
  • Supportare i livelli di isolamento delle transazioni basati sul controllo delle versioni delle righe:
    • Nuova implementazione del livello di isolamento READ COMMITTED che usa il controllo delle versioni delle righe per garantire la coerenza di lettura a livello di istruzione.
    • Un nuovo livello di isolamento, snapshot, per offrire consistenza di lettura a livello di transazione.

Il database tempdb deve disporre di spazio sufficiente per l'archivio delle versioni. Quando lo spazio in tempdb è esaurito, le operazioni di aggiornamento arrestano la generazione di versioni, senza errori, mentre le operazioni di lettura possono avere esito negativo in quanto una particolare versione della riga necessaria non esiste più. Questo ha effetto su operazioni come indicizzazione online, MARS e di trigger.

L'utilizzo del controllo delle versioni delle righe per transazioni snapshot e read-committed è 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 READ_COMMITTED_SNAPSHOT di database è impostata su 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.

READ_COMMITTED_SNAPSHOT Quando l'opzione di database o ALLOW_SNAPSHOT_ISOLATION è impostata su ON, SQL Server motore di database assegna un numero di sequenza di 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 a ogni assegnazione.

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 di SQL Server motore di database archivia una versione dell'immagine di cui è stato eseguito il commit precedente della riga in tempdb. 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 con versioni archiviate in tempdb.

Nota

Per la modifica di oggetti LOB, solo il frammento modificato viene copiato nell'archivio delle versioni archiviato in tempdb.

Le versioni delle righe vengono mantenute abbastanza a lungo da soddisfare i requisiti delle transazioni in esecuzione con i livelli di isolamento basati sul controllo delle versioni delle righe. Sql Server motore di database tiene traccia del numero di sequenza delle transazioni più recente utile ed elimina periodicamente tutte le versioni di riga contrassegnate con numeri di sequenza di transazione inferiori al numero di sequenza più recente 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 thread in background viene eseguito periodicamente per rimuovere le versioni di riga non aggiornate.

Nota

Per le transazioni con esecuzione rapida, è possibile che una versione di una riga modificata venga memorizzata nel pool di buffer senza essere scritta nei file su disco del database tempdb. Se la necessità della riga con controllo delle versioni è di breve durata, essa verrà semplicemente eliminata dal pool di buffer, senza che si verifichi necessariamente un overhead I/O.

Comportamento durante la lettura dei dati

Quando le transazioni in esecuzione con l'isolamento basato sul controllo delle versioni delle righe eseguono la lettura dei dati, le operazioni di lettura non acquisiscono blocchi condivisi (S) sui dati da leggere, e pertanto non bloccano le transazioni che stanno modificando 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 controllo delle versioni delle righe e isolamento dello snapshot è pensato per assicurare consistenze a livello di istruzione o di transazione dei dati sottoposti a controllo delle versioni.

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 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 delle query, 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.

All'avvio di una transazione che usa il livello di isolamento dello snapshot, l'istanza di SQL Server motore di database registra tutte le transazioni attualmente attive. Quando la transazione snapshot legge una riga con una catena di versioni, SQL Server motore di database segue la catena e recupera la riga in cui è presente il numero di sequenza della transazione:

  • Più vicino ma inferiore al numero di sequenza della transazione 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.

Le transazioni read-committed che utilizzano il controllo delle versioni delle righe funzionano in modo analogo. La differenza è che la transazione read-committed non usa il proprio numero di sequenza di transazione quando si scelgono le versioni di riga. Ogni volta che viene avviata un'istruzione, la transazione read-committed legge il numero di sequenza di transazione più recente emesso per tale istanza del motore di database di SQL Server. Si tratta del numero di sequenza della transazione utilizzato per selezionare le versioni di riga corrette per l'istruzione. Questo consente alle transazioni read-committed di visualizzare uno snapshot dei dati nello stato in cui si trovavano all'inizio di ogni istruzione.

Nota

Anche se le transazioni read-committed che utilizzano il controllo delle versioni delle righe offrono una vista consistente dei dati dal punto di vista transazionale a livello di istruzione, le versioni delle righe generate o utilizzate da questo tipo di transazione vengono mantenute fino al completamento della transazione.

Comportamento durante la modifica dei dati

Il comportamento delle scritture di dati è notevolmente diverso con e senza blocco ottimizzato presente.

Modificare i dati senza blocco ottimizzato

In una transazione di cui è stato eseguito il commit in lettura usando il controllo delle versioni delle righe, la selezione delle righe da aggiornare viene eseguita usando un'analisi di blocco in cui viene acquisito un blocco di aggiornamento (U) nella riga di dati man mano che vengono letti i valori dei dati. Corrisponde a una transazione di cui è stato eseguito il commit in lettura che non usa il controllo delle versioni delle righe. Se la riga di dati non soddisfa i criteri di aggiornamento, il blocco di aggiornamento viene rilasciato in tale riga e la riga successiva viene bloccata e analizzata.

Le transazioni eseguite con isolamento dello 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, non vengono acquisiti blocchi sui dati fino a quando questi non devono essere modificati. Quando una riga di dati soddisfa i criteri di aggiornamento, la transazione snapshot verifica che la riga di dati non sia stata modificata da una transazione simultanea di cui è stato eseguito il commit dopo l'avvio 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 di SQL Server e non è possibile disabilitare il rilevamento dei conflitti di aggiornamento.

Nota

Le operazioni di aggiornamento in esecuzione con isolamento dello snapshot vengono eseguite internamente con isolamento READ COMMITTED quando la transazione snapshot accede a una delle operazioni seguenti:

Una tabella con un vincolo FOREIGN KEY.

Una tabella cui viene fatto riferimento nel vincolo FOREIGN KEY di un'altra tabella.

Una vista indicizzata che fa riferimento a più tabelle.

Tuttavia, anche in queste condizioni l'operazione di aggiornamento continuerà per verificare che i dati non siano stati modificati da un'altra transazione. In questo caso, la transazione snapshot rileverà un conflitto di aggiornamento e verrà interrotta. I conflitti di aggiornamento devono essere gestiti e ritentati manualmente dall'applicazione.

Modificare i dati con il blocco ottimizzato

Con il blocco ottimizzato abilitato e con l'opzione di database RCSI (READ_COMMITTED_SN piattaforma di strumenti analitici HOT) abilitata e usando il livello di isolamento READ COMMITTED predefinito, i lettori non acquisiscono blocchi e 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 maggior parte dell'efficienza con il blocco ottimizzato. Quando si usano livelli di isolamento più rigidi come lettura ripetibile o serializzabile, il motore di database viene costretto a contenere blocchi di riga e di pagina fino alla fine della transazione, sia per i lettori che per i writer, con conseguente aumento della memoria di blocco e blocco.

Con RCSI abilitato e quando si usa il livello di isolamento READ COMMITTED predefinito, i writer qualificano le righe in base al predicato in base alla versione di cui è stato eseguito il commit più recente della riga, senza acquisire blocchi U. Una query attenderà solo se la riga è idonea ed è presente una 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 solo le righe qualificate riduce il blocco e aumenta la concorrenza.

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

Con il blocco ottimizzato abilitato, usando il livello di isolamento SN piattaforma di strumenti analitici HOT, il comportamento dei conflitti di aggiornamento è lo stesso. I conflitti di aggiornamento devono essere gestiti e ritentati manualmente dall'applicazione.

Nota

Per altre informazioni sulle modifiche del comportamento con la funzionalità di blocco dopo la qualificazione (LAQ) del blocco ottimizzato, vedere Modifiche del comportamento delle query con blocco ottimizzato e RCSI.

Riepilogo

La tabella seguente riepiloga le differenze tra l'isolamento dello snapshot e l'isolamento READ COMMITTED usando il controllo delle versioni delle righe.

Proprietà Livello di isolamento read-committed che utilizza il controllo delle versioni delle righe Livello di isolamento dello snapshot
L'opzione di database che deve essere impostata su ON per attivare il supporto necessario. READ_COMMITTED_SNAPSHOT ALLOW_SNAPSHOT_ISOLATION
Modalità con cui una sessione richiede il tipo specifico di controllo delle versioni delle righe. Utilizzare il livello di isolamento read-committed predefinito o eseguire l'istruzione SET TRANSACTION ISOLATION LEVEL per specificare il livello di isolamento READ COMMITTED. L'operazione può essere eseguita dopo l'inizio della transazione. Richiede l'esecuzione di SET TRANSACTION ISOLATION LEVEL per specificare il livello di isolamento SNAPSHOT prima dell'inizio della transazione.
La versione dei dati letta dalle istruzioni. Tutti i dati di cui è stato eseguito il commit prima dell'inizio di ogni istruzione. Tutti i dati di cui è stato eseguito il commit prima dell'inizio di ogni transazione.
Procedura di gestione degli aggiornamenti. Senza blocchi ottimizzati: ripristina le versioni delle righe ai dati effettivi per selezionare le righe da aggiornare e usa i blocchi di aggiornamento nelle righe di dati selezionate. Acquisisce blocchi esclusivi sulle righe di dati effettive da modificare. Nessun rilevamento dei conflitti di aggiornamento.

Con il blocco ottimizzato: le righe vengono selezionate in base all'ultima versione di cui è stato eseguito il commit senza che vengano acquisiti blocchi. Se le righe sono idonee per l'aggiornamento, vengono acquisiti blocchi di riga o di pagina esclusivi. Se vengono rilevati conflitti di aggiornamento, vengono gestiti e ritentati automaticamente.
Utilizza le versioni delle righe per selezionare le righe da aggiornare. Tenta di acquisire un blocco esclusivo sulla riga di dati effettiva da modificare. Se i dati sono stati modificati da un'altra transazione, si verifica un conflitto di aggiornamento e la transazione snapshot viene interrotta.
Rilevamento dei conflitti di aggiornamento Senza blocco ottimizzato: nessuno.

Con il blocco ottimizzato: se vengono rilevati conflitti di aggiornamento, vengono gestiti e ritentati automaticamente.
Supporto integrato. Non disabilitabile.

Utilizzo delle risorse di controllo delle versioni delle righe

Il framework di controllo delle versioni delle righe supporta le funzionalità seguenti disponibili in SQL Server:

  • Trigger
  • MARS (Multiple Active Result Set)
  • Indicizzazione online

Il framework di controllo delle versioni delle righe supporta anche i livelli di isolamento delle transazioni basati sul controllo delle versioni delle righe seguenti, che non sono abilitati per impostazione predefinita:

  • Quando l'opzione di database READ_COMMITTED_SNAPSHOT è impostata su ON, le transazioni READ_COMMITTED offrono coerenza di lettura a livello di istruzioni tramite il controllo delle versioni delle righe.
  • Quando l'opzione di database ALLOW_SNAPSHOT_ISOLATION è impostata su ON, le transazioni SNAPSHOT offrono coerenza di lettura a livello di transizioni 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 tali opzioni comporta il controllo delle versioni di tutte le modifiche dei dati per il database. Una copia dei dati prima della modifica viene archiviata in tempdb anche quando non sono presenti transazioni attive che usano l'isolamento basato sul controllo delle versioni delle righe. I dati dopo la modifica includono un puntatore ai dati con controllo delle versioni archiviati in tempdb. Per oggetti di grandi dimensioni, viene copiata solo parte dell'oggetto modificato in tempdb.

Spazio usato in tempdb

Per ogni istanza di SQL Server motore di database, tempdb deve disporre di spazio sufficiente per contenere le versioni di riga generate per ogni database nell'istanza di . L'amministratore del database deve assicurarsi che tempdb disponga di spazio ampio per supportare l'archivio versioni. Esistono due archivi di versioni in tempdb:

  • L'archivio versioni compilazione degli indici online viene utilizzato per le compilazioni degli indici online in tutti i database.
  • L'archivio versioni comune viene utilizzato per tutte le altre operazioni di modifica dei dati in tutti i database.

Le versioni di riga devono essere archiviate per tutto il tempo necessario a una transazione attiva per accedervi. Una volta ogni minuto, un thread in background rimuove le versioni di riga che non sono più necessarie e libera lo spazio della versione in tempdb. 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.

Nota

Quando viene richiamato un trigger all'interno di una transazione, le versioni di riga create dal trigger vengono mantenute fino alla fine della transazione, anche se le versioni di riga non sono più necessarie al termine del trigger. Questa situazione riguarda inoltre le transazioni Read committed che utilizzano il controllo delle versioni delle righe. Con questo tipo di transazione, una vista del database consistente a livello di transazioni è necessaria solo per ogni istruzione della transazione. Le versioni di riga create per un'istruzione della transazione non sono pertanto più necessarie al termine dell'istruzione. Le versioni di riga create da ogni istruzione della transazione vengono tuttavia mantenute fino al termine della transazione.

Quando tempdb si esaurisce lo spazio, SQL Server motore di database forza la compattazione degli archivi delle versioni. Durante il processo di compattazione, le transazioni con esecuzione prolungata 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 nell'archivio versioni. Quando la transazione tenta di leggere le versioni di riga, viene generato un messaggio 3966 e viene eseguito il rollback della transazione. Se il processo di compattazione ha esito positivo, lo spazio diventa disponibile in tempdb. In caso contrario, si esaurisce tempdb lo spazio e si verifica quanto segue:

  • L'esecuzione delle operazioni di scrittura prosegue, ma non vengono generate versioni. Nel log degli errori viene visualizzato un messaggio informativo (3959), ma la transazione che scrive i dati non è interessata.

  • Le transazioni che tentano di accedere alle versioni di riga non generate a causa di un tempdb rollback completo terminano con un errore 3958.

Spazio utilizzato nelle righe di dati

Ogni riga del database può utilizzare fino a 14 byte alla fine della riga per le informazioni relative al controllo delle versioni delle righe. Tali informazioni contengono il numero di sequenza della transazione che ha eseguito il commit della versione e il puntatore alla riga di cui è stato eseguito il controllo delle versioni. 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 include un trigger.
  • Viene utilizzato MARS (Multiple Active Results Set).
  • Nella tabella sono in esecuzione operazioni di compilazione di indici online.

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 usato.
  • Le operazioni di compilazione di indici online non sono in esecuzione.

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 sul controllo delle versioni delle righe può causare divisioni della pagina 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 informazioni sulla frammentazione dei dati e degli indici per una tabella o visualizzazione specificata, è possibile usare sys.dm_db_index_physical_stats.

Spazio usato in oggetti di grandi dimensioni

Sql Server motore di database supporta sei tipi di dati che possono contenere stringhe di grandi dimensioni fino a 2 gigabyte (GB) di lunghezza: nvarchar(max), varchar(max), varbinary(max), ntext, texte image. Le stringhe estese per cui vengono utilizzati questi tipi di dati vengono archiviate 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 una raccolta di pagine dedicate a oggetti di grandi dimensioni in una tabella.

Man mano che nel database vengono aggiunti nuovi valori di grandi dimensioni, questi valori vengono allocati utilizzando un massimo di 8040 byte di dati per frammento. Le versioni precedenti di SQL Server motore di database archiviate fino a 8080 byte di ntext, texto image dati per frammento.

textimage I dati loB (Large Object) esistenti ntextnon vengono aggiornati per creare spazio per le informazioni sul controllo delle versioni delle righe quando un database viene aggiornato a SQL Server da una versione precedente di SQL Server. La prima volta che i dati LOB vengono modificati, tuttavia, vengono aggiornati dinamicamente per consentire l'archiviazione delle informazioni relative al controllo delle versioni. Questa operazione viene eseguita anche se non vengono generate versioni di riga. In seguito all'aggiornamento dei dati LOB, il numero massimo di byte archiviati per frammento viene ridotto da 8080 byte a 8040 byte. Il processo di aggiornamento equivale all'eliminazione del valore LOB e al reinserimento dello stesso valore. I dati LOB vengono aggiornati anche se vengono modificati solo 1 byte. Si tratta di un'operazione occasionale per ogni colonna ntext, text o image, ma ogni operazione può generare una quantità elevata di allocazioni di pagina e un'ingente attività di I/O, a seconda delle dimensioni dei dati LOB. Tali operazioni possono inoltre generare un'attività di registrazione elevata se la modifica viene registrata completamente. Le operazioni WRITETEXT e UPDATETEXT vengono registrate al minimo se il modello di recupero del database non è impostato su FULL.

I nvarchar(max)tipi di dati , varchar(max)e varbinary(max) non sono disponibili nelle versioni precedenti di SQL Server. Per questo motivo, tali tipi dati non sono interessati da problemi di aggiornamento.

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

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

Per il monitoraggio del controllo delle versioni delle righe, dell'archivio delle versioni e dei processi di isolamento degli snapshot per problemi e prestazioni, SQL Server fornisce strumenti sotto forma di DMV (Dynamic Management Views) e contatori delle prestazioni in Monitoraggio di sistema di Windows.

DMV

Le DMV seguenti forniscono informazioni sullo stato corrente del sistema e tempdb sull'archivio delle versioni, nonché sulle transazioni che usano il controllo delle versioni 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 ogni attività per il 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 indicante la maggior parte delle versioni nell'archivio versioni. Raggruppa le 256 lunghezze superiori dei record di aggregazione per database_id e rowset_id. Utilizzare questa funzione per trovare i maggiori consumer dell'archivio versioni. 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. 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 utilizzato dai record dell'archivio delle versioni per ogni database. Per altre informazioni, vedere sys.dm_tran_version_store_space_usage (Transact-SQL).

    Nota

    Gli oggetti sys.dm_tran_top_version_generators di sistema e sys.dm_tran_version_store sono funzioni potenzialmente molto costose da eseguire, poiché entrambe eseguono query sull'intero archivio delle versioni, che potrebbero essere molto grandi. Anche se 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 dell'archivio versioni aggregato utilizzato 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 all'interno dell'istanza di SQL Server che utilizzano 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 controllo delle versioni 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, ad eccezione del fatto che restituisce solo le transazioni attive per lo snapshot corrente. Per altre informazioni, vedere sys.dm_tran_current_snapshot (Transact-SQL).

Contatori delle prestazioni

I contatori delle prestazioni di SQL Server forniscono informazioni sulle prestazioni del sistema interessate dai processi di SQL Server. I contatori delle prestazioni seguenti monitorano tempdb e l'archivio delle versioni, nonché le transazioni che usano il controllo delle versioni delle righe. I contatori delle prestazioni sono contenuti nell'oggetto prestazioni SQLServer:Transactions .

  • Spazio disponibile in tempdb (KB). Esegue il monitoraggio della quantità, espressa in kilobyte (KB), dello spazio disponibile nel tempdb database. Per gestire l'archivio versioni che supporta l'isolamento dello snapshot, è necessario disporre di spazio sufficiente in tempdb .

    Nella formula seguente viene eseguito un calcolo approssimativo delle dimensioni dell'archivio versioni. Per le transazioni con esecuzione prolungata, può essere utile monitorare la frequenza di generazione e pulizia per stimare le dimensioni massime dell'archivio versioni.

    [dimensioni dell'archivio versioni comuni] = 2 * [dati dell'archivio versioni generati al minuto] * [tempo di esecuzione più lungo (minuti) della transazione]

    Il tempo massimo di esecuzione delle transazioni non deve includere le compilazioni di indici online. Poiché queste operazioni possono richiedere una quantità di tempo prolungata nelle tabelle di dimensioni molto elevate, le compilazioni di indici online utilizzano un archivio versioni distinto. Le dimensioni approssimative dell'archivio versioni compilazioni di indici online equivale alla quantità di dati modificati nella tabella, inclusi tutti gli indici, quando è attiva la compilazione di indici online.

  • Dimensioni archivio versioni (KB). Esegue il monitoraggio delle dimensioni in KB di tutti gli archivi versioni. Queste informazioni consentono di determinare la quantità di spazio necessaria nel tempdb database per l'archivio delle versioni. Il monitoraggio di questo contatore in un periodo di tempo fornisce una stima utile dello spazio aggiuntivo necessario per tempdb.

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

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

    Nota

    Le informazioni relative alla frequenza di generazione delle versioni (KB/s) e alla frequenza di pulizia della versione (KB/s) possono essere usate per stimare tempdb i requisiti di spazio.

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

  • Creazione unità archivio versioni. Esegue il monitoraggio del numero totale di unità dell'archivio versioni create per archiviare le versioni di riga dal momento in cui è stata avviata l'istanza.

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

  • Percentuale conflitti aggiornamento. Esegue il monitoraggio del rapporto tra transazioni snapshot di aggiornamento con conflitti di aggiornamento e il numero totale di transazioni snapshot di aggiornamento.

  • Tempo massimo esecuzione transazione. Esegue il monitoraggio del tempo massimo di esecuzione in secondi di qualsiasi transazione che utilizza il controllo delle versioni delle righe. Queste informazioni possono essere utilizzate per determinare se alcune transazioni vengano eseguite in una quantità di tempo eccessiva.

  • Transazioni. Esegue il monitoraggio del numero totale di transazioni attive. Ciò non include le transazioni di sistema.

  • Transazioni snapshot. Esegue il monitoraggio del numero totale di transazioni snapshot attive.

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

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

    Nota

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

Esempio di livello di isolamento basato sul controllo delle versioni delle righe

Nell'esempio seguente vengono illustrate le differenze di comportamento tra le transazioni di isolamento dello snapshot e le transazioni Read Committed che utilizzano il controllo delle versioni delle righe.

R. Usare l'isolamento dello snapshot

In questo esempio una transazione di isolamento dello snapshot legge dati che verranno successivamente modificati da un'altra transazione. La transazione snapshot non blocca l'operazione di aggiornamento eseguita dall'altra transazione e continua a leggere i dati dalla riga con controllo delle versioni, 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 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. Usare 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. A differenza di una transazione snapshot, tuttavia, 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, a differenza della transazione snapshot.

Nella sessione 1:

USE AdventureWorks2022;  -- Or any earlier version of the AdventureWorks database.
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 i livelli di isolamento basato 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.

L'istruzione Transact-SQL seguente abilita READ_COMMITTED_SNAPSHOT:

ALTER DATABASE AdventureWorks2022
    SET READ_COMMITTED_SNAPSHOT ON;

Quando l'opzione ALLOW_SNAPSHOT_ISOLATION di database è impostata su ON, l'istanza di SQL Server motore di database non genera versioni di riga per i dati modificati fino al completamento di tutte le transazioni attive che hanno modificato i dati nel database. Se sono presenti transazioni di modifica attive, SQL Server 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 transazione snapshot nel database fino a quando l'opzione non è impostata completamente su ON. Il database passa a uno stato PENDING_OFF quando l'amministratore del database imposta l'opzione ALLOW_SNAPSHOT_ISOLATION su OFF.

L'istruzione Transact-SQL seguente abiliterà ALLOW_SN piattaforma di strumenti analitici HOT_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'uso di ALTER DATABA edizione Standard con l'opzione ALLOW_SN piattaforma di strumenti analitici HOT_ISOLATION non blocca gli utenti che attualmente accedono ai dati del database.

Stato del framework di isolamento dello snapshot per il database corrente Descrizione
OFF Il supporto per le transazioni di isolamento dello snapshot non è attivato. Non è consentita alcuna transazione di isolamento dello snapshot.
PENDING_ON Il supporto per le transazioni di isolamento dello snapshot è in stato di transizione (da OFF a ON). Le transazioni aperte devono essere completate.

Non è consentita alcuna transazione di isolamento dello snapshot.
In... Il supporto per le transazioni di isolamento dello snapshot è attivato.

Le transazioni snapshot sono consentite.
PENDING_OFF Il supporto per le transazioni di isolamento dello snapshot è in stato di transizione (da ON a OFF).

Le transazioni snapshot avviate dopo questo momento non possono accedere al database. Le transazioni di aggiornamento subiscono le conseguenze negative del controllo delle versioni nel database. Le transazioni snapshot esistenti possono continuare ad accedere al database senza problemi. Lo stato PENDING_OFF non diventa OFF fino al completamento di tutte le transazioni snapshot attive al termine dello stato di isolamento dello snapshot 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 ON in master, tempdbo msdb.

Usare i livelli di isolamento basati sul controllo delle versioni delle righe

Il framework di controllo delle versioni delle righe è sempre abilitato in SQL Server e viene usato da più funzionalità. Oltre a offrire livelli di isolamento basati sul controllo delle versioni delle righe, viene utilizzata per supportare le modifiche apportate in trigger e sessioni MARS (Multiple Active Result Sets) e le letture di 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 nel livello di isolamento READ COMMITTED usano il controllo delle versioni delle righe, il che significa che le operazioni di lettura non bloccano le operazioni di aggiornamento.

  • Isolamento dello 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;
    

    Una transazione in esecuzione in un livello di isolamento dello snapshot può accedere alle tabelle nel database abilitate per lo snapshot. Per consentire l'accesso alle tabelle non abilitate per lo snapshot, è necessario modificare il livello di isolamento. Nell'esempio di codice seguente viene ad esempio illustrata un'istruzione SELECT che unisce in join due tabelle mentre è in esecuzione in una transazione snapshot. Una tabella appartiene a un database in cui l'isolamento dello snapshot non è abilitato. Quando l'istruzione SELECT viene eseguita nel livello di isolamento dello snapshot, l'esecuzione non avviene correttamente.

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
    BEGIN TRAN
        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 che il livello di isolamento della transazione sia Read Committed. Grazie a questa modifica, l'istruzione SELECT viene eseguita correttamente.

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

Limitazioni delle transazioni che usano livelli di isolamento basati sul controllo delle versioni delle righe

Quando si utilizzano livelli di isolamento basati sul controllo delle versioni delle righe, considerare i limiti seguenti:

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

  • Le tabelle temporanee globali vengono archiviate in 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 ALLOW_SNAPSHOT_ISOLATION di database ON in tempdb.
    • Utilizzare un hint di isolamento per modificare il livello di isolamento per l'istruzione.
  • Le transazioni snapshot non hanno esito positivo nei casi seguenti:

    • 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 stessa.
    • 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 stessa. Quando, ad esempio, il database è stato impostato su OFFLINE e quindi su ONLINE, si è chiuso automaticamente e quindi si è aperto oppure è stato scollegato e quindi collegato.
  • Le transazioni distribuite, incluse le query in database partizionati distribuiti, non sono supportate nel livello di isolamento dello snapshot.

  • SQL Server 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 dello snapshot causano un errore della transazione. Le transazioni Read Committed non hanno questo limite quando l'opzione di database READ_COMMITTED_SNAPSHOT è 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
    

    Le transazioni snapshot attive al momento dell'esecuzione di ALTER INDEX ricevono un errore nel caso in cui cerchino di fare riferimento alla tabella HumanResources.Employee successivamente all'esecuzione dell'istruzione ALTER INDEX. Le transazioni Read Committed che utilizzano il controllo delle versioni delle righe non sono interessate.

    Nota

    Le operazioni di tipo BULK INSERT possono comportare modifiche ai metadati della tabella di destinazione, ad esempio quando si disabilita la verifica dei vincoli. In questo caso, le transazioni di isolamento dello snapshot simultanee che accedono a tabelle in cui è stato eseguito l'inserimento bulk non hanno esito positivo.

Personalizzare il blocco e il controllo delle versioni delle righe

Personalizzare il timeout del blocco

Quando un'istanza del motore di database di Microsoft SQL Server 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 è previsto alcun periodo di timeout obbligatorio e non è disponibile alcun metodo per verificare se, prima dell'applicazione di un blocco, una risorsa è bloccata. L'unica situazione rilevabile è il tentativo di accesso ai dati con il potenziale rischio di attivazione di un blocco per un tempo indeterminato.

Nota

In SQL Server usare la sys.dm_os_waiting_tasks visualizzazione a gestione dinamica per determinare se un processo viene bloccato e chi lo blocca. Nelle versioni precedenti di SQL Server usare la sp_who stored procedure di sistema. Per altre informazioni ed esempi, vedere Informazioni e risoluzione dei problemi di blocco di SQL Server.

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 nell'applicazione viene restituito il messaggio di errore 1222 (Lock request time-out period exceeded). Qualsiasi transazione contenente l'istruzione, tuttavia, non viene eseguito il rollback o l'annullamento da SQL Server. Pertanto, l'applicazione deve avere un gestore degli errori in grado di intercettare il messaggio di errore 1222. Se un'applicazione non interceda l'errore, l'applicazione può continuare a non essere a conoscenza del fatto che una singola istruzione all'interno di una transazione è stata annullata e possono verificarsi errori perché le istruzioni successive nella transazione potrebbero dipendere dall'istruzione che non è mai stata eseguita.

Mediante l'implementazione di un gestore degli errori in grado di intercettare il messaggio di errore 1222, è possibile gestire la condizione di timeout ed eseguire gli interventi di correzione necessari, ad esempio la riesecuzione automatica dell'istruzione bloccata oppure 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 delle transazioni

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

  • Eseguire l'istruzione SET TRANSACTION ISOLATION LEVEL.
  • ADO.NET applicazioni che usano lo System.Data.SqlClient spazio dei nomi gestito possono specificare un'opzione IsolationLevel usando il SqlConnection.BeginTransaction metodo .
  • 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 usano OLE DB possono impostare la DBPROPSET_SESSION proprietà DBPROP_SESS_AUTOCOMMITISOLEVELS sul livello di isolamento della transazione desiderato.
  • Le applicazioni che usano ODBC possono impostare l'attributo SQL_COPT_SS_TXN_ISOLATION tramite SQLSetConnectAttr.

Quando viene specificato il livello di isolamento, il comportamento di blocco per tutte le query e le istruzioni DML (Data Manipulation Language) nella sessione di SQL Server opera a tale livello di isolamento. 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;
GO

Se necessario, il livello di isolamento impostato può essere ignorato per singole query o istruzioni DML specificando hint a livello di tabella, La specifica di un hint a livello di tabella non influisce su altre istruzioni nella sessione. È consigliabile utilizzare gli hint a livello di tabella per modificare il funzionamento predefinito del blocco solo se assolutamente necessario.

L'motore di database di SQL Server potrebbe dover acquisire blocchi durante la lettura dei metadati anche quando il livello di isolamento è impostato su un livello in cui non sono richiesti blocchi di condivisione durante la lettura dei dati. Ad esempio, una transazione in esecuzione a livello di isolamento read-uncommitted non acquisisce blocchi di condivisione durante la lettura dei dati, ma potrebbe richiedere blocchi durante la lettura di una vista del catalogo di sistema. È pertanto possibile che una transazione Read uncommitted provochi un blocco durante l'esecuzione di una query su una tabella quando una transazione simultanea modifica i metadati della tabella.

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

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

Questo è il set di risultati.

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.

Hint di blocco

È possibile specificare hint di blocco per riferimenti a una singola tabella nelle istruzioni SELECT, INSERT, UPDATE e DELETE. Gli hint specificano il tipo di blocco o controllo delle versioni delle righe dell'istanza del motore di database di SQL Server per i dati della tabella. Gli hint di blocco a livello di tabella possono essere utilizzati quando è necessario un controllo più fine dei tipi di blocco acquisiti su un oggetto. Questi hint sono prioritari rispetto al livello di isolamento della transazione impostato per la sessione.

Nota

Gli hint di blocco non sono consigliati per l'uso quando è abilitato il blocco ottimizzato. Anche se gli hint di tabella e query vengono rispettati, riducono il vantaggio del blocco ottimizzato. Per altre informazioni, vedere Evitare i suggerimenti di blocco con il blocco ottimizzato.

Per altre informazioni sugli hint di blocco specifici e sui relativi comportamenti, vedere Hint di tabella (Transact-SQL).For more information about the specific locking hints and their behaviors, see Table Hints (Transact-SQL).

Nota

SQL Server motore di database sceglie quasi sempre il livello di blocco corretto. È consigliabile utilizzare gli hint di blocco a livello di tabella per modificare il comportamento predefinito del blocco solo se necessario. La disattivazione di un livello di blocco può influire negativamente sulla concorrenza.

L'motore di database di SQL Server potrebbe dover acquisire blocchi durante la lettura dei metadati, anche quando si elabora una selezione con un hint di blocco che impedisce le richieste di blocchi di condivisione durante la lettura dei dati. Ad esempio, un SELECT oggetto che usa l'hint NOLOCK non acquisisce blocchi di condivisione durante la lettura dei dati, ma potrebbe richiedere blocchi durante la lettura di una vista del catalogo di sistema. Ciò significa che è possibile che un'istruzione SELECT che usa NOLOCK venga bloccata.

Come illustrato nell'esempio seguente, se il livello di isolamento delle transazioni è impostato su SERIALIZABLEe l'hint NOLOCK di blocco a livello di tabella viene usato con l'istruzione , i blocchi dell'intervallo SELECT di chiavi in genere usati per gestire le transazioni serializzabili 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 HumanResources.Employee riferimento è un blocco di stabilità dello schema (Sch-S). In questo caso la serializzabilità non è garantita.

In SQL Server, l'opzione LOCK_ESCALATION di può disfavorare i blocchi di ALTER TABLE tabella e abilitare i blocchi HoBT nelle tabelle partizionate. Questa opzione non è un hint di blocco, ma può essere usata per ridurre l'escalation dei blocchi. Per altre informazioni, vedere ALTER TABLE (Transact-SQL).

Personalizzare il blocco per un indice

Sql Server motore di database usa una strategia di blocco dinamico che sceglie automaticamente la migliore granularità di blocco per le query nella maggior parte dei casi. Si consiglia di non ignorare i livelli di blocco predefiniti, in cui è attivato il blocco a livello di pagina e di riga, a meno che i modelli di accesso a tabelle e indici non siano chiari e coerenti ed esista un problema di contesa tra 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, 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ò essere completato in modo efficiente, in quanto ottiene un blocco esclusivo sulla tabella.

La disattivazione del blocco a livello di pagina o di riga può essere o meno accettabile perché l'aggiornamento batch settimanale impedirà agli utenti di accedere simultaneamente 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 include un numero elevato di aggiornamenti, per assicurarsi che venga completato in modo efficiente è consigliabile ottenere un blocco esclusivo sulla tabella.

A volte, quando due operazioni simultanee acquisiscono blocchi a livello di riga sulla stessa tabella e quindi si bloccano perché richiedono entrambe il blocco della pagina, si verifica un deadlock. 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. Le impostazioni del blocco si applicano sia alle pagine di indice che alle pagine di tabella. 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 sp_indexoption sistema può anche 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 o di riga e della combinazione di questi due livelli di blocco per un particolare indice.

Blocchi non consentiti Accesso all'indice da parte di
Livello pagina Blocchi a livello di riga e tabella
A livello di riga Blocchi a livello di pagina e di tabella
A livello di pagina e di riga Blocchi a livello di tabella

Informazioni avanzate sulle transazioni

Annidare le transazioni

Le transazioni esplicite possono essere nidificate. 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'utilizzo delle transazioni nidificate. La procedura TransProc applica la rispettiva transazione indipendentemente dalla modalità impostata in ogni processo da cui viene eseguita. Se TransProc viene chiamato quando una transazione è attiva, la transazione nidificata in TransProc viene in gran parte ignorata e le relative INSERT istruzioni vengono sottoposte a commit o rollback in base all'azione finale acquisita per la transazione esterna. Se TransProc viene eseguito da un processo che non dispone di una transazione in sospeso, alla COMMIT TRANSACTION fine della procedura esegue effettivamente il commit delle INSERT istruzioni.

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 nested 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 rollback. */
SELECT * FROM TestTrans;
GO

Il commit delle transazioni interne viene ignorato dal motore di database di SQL Server. Viene eseguito il commit o il rollback della transazione in base all'azione acquisita alla fine della transazione più esterna. In caso di commit della transazione esterna, viene eseguito il commit anche delle transazioni nidificate e in caso di rollback della transazione esterna viene eseguito il rollback anche di tutte le transazioni interne, indipendentemente dal fatto che sia stato eseguito il commit delle singole transazioni interne.

Ogni chiamata a COMMIT TRANSACTION o COMMIT WORK si applica all'ultima esecuzione di BEGIN TRANSACTION. Se le istruzioni BEGIN TRANSACTION sono nidificate, l'istruzione COMMIT viene applicata solo all'ultima transazione nidificata, ovvero alla transazione più interna. Anche se un'istruzione COMMIT TRANSACTION transaction_name all'interno di una transazione nidificata fa riferimento al nome della transazione esterna, il commit si applica solo alla transazione più interna.

Non è valido per il parametro transaction_name di un'istruzione ROLLBACK TRANSACTION fare riferimento alle transazioni interne di un set di transazioni annidate denominate. transaction_name può fare riferimento solo al nome della transazione più esterna. Se a qualsiasi livello di un set di transazioni nidificate viene eseguita un'istruzione ROLLBACK TRANSACTION transaction_name che usa il nome della transazione esterna, viene eseguito il rollback di tutte le transazioni nidificate. Se a qualsiasi livello di un set di transazioni nidificate viene eseguita un'istruzione ROLLBACK WORK o ROLLBACK TRANSACTION priva del parametro transaction_name, viene eseguito il rollback di tutte le transazioni nidificate, compresa la 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 di tutte le transazioni annidate e decrementa @@TRANCOUNT su 0. Un'istruzione ROLLBACK TRANSACTION che usa il nome di transazione della transazione più esterna in un set di transazioni nidificate esegue il rollback di tutte le transazioni nidificate e decrementa @@TRANCOUNT a 0. Per determinare se ci si trova all'interno di una transazione, verificare che il valore di SELECT @@TRANCOUNT sia 1 o un valore maggiore. Se @@TRANCOUNT è 0, non ci si trova all'interno di una transazione.

Usare sessioni associate

Le sessioni associate semplificano il coordinamento di azioni tra più sessioni nello stesso server. Tramite le sessioni associate due o più sessioni possono condividere la stessa transazione e gli stessi blocchi, utilizzando gli 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 Open Data Services) 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 si associano alla transazione chiamando sp_bindsession, usando il token di associazione ricevuto dalla prima sessione.

Nota

Una sessione deve disporre di una transazione utente attiva affinché sp_getbindtoken o srv_getbindtoken abbia esito positivo.

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 Transact-SQL o funzione API 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 di SQL Server che possono essere letti dai processi che desiderano eseguire l'associazione alla prima sessione.

In un set di sessioni associate può essere attiva una sola sessione per volta. Se una sessione esegue un'istruzione nell'istanza o è in attesa di risultati dall'istanza, le altre sessioni associate 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'uso di edizione Standard T TRANSACTION ISOLATION LEVEL per modificare l'impostazione del livello di isolamento di una sessione non influisce sull'impostazione di qualsiasi altra sessione associata.

Tipi di sessioni associate

Sono disponibili due tipi di sessioni associate, ovvero locali e distribuite.

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

  • Sessione associata distribuita Consente alle sessioni associate di condividere la stessa transazione tra 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 tipo stringa, ma dai 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 usare sessioni associate

Nelle versioni precedenti di SQL Server, le sessioni associate sono state usate principalmente per lo sviluppo di stored procedure estese che devono eseguire istruzioni Transact-SQL per conto del processo che le chiama. Quando tramite il processo chiamante il token di associazione viene passato come parametro della stored procedure estesa, la procedura può partecipare allo spazio della transazione del processo chiamante e viene pertanto integrata in tale processo.

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

Le sessioni associate possono essere utilizzate per lo sviluppo di applicazioni a tre livelli in cui la logica di business è incorporata in programmi distinti che operano congiuntamente 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.

Transazioni efficienti del codice

È 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 non efficienti che richiedono tempi di esecuzione prolungati potrebbero non presentare alcun problema se il numero di utenti è ridotto, mentre sono assolutamente da evitare in sistemi con migliaia di utenti. A partire da SQL Server 2014 (12.x), SQL Server supporta transazioni durevoli ritardate. Tali transazioni non garantiscono la durabilità. Per altre informazioni, vedere Controllo della durabilità delle transazioni.

Linee guida per il codice

Di seguito vengono riportate alcune linee guida per la codifica di transazioni efficienti.

  • Evitare di richiedere l'input dell'utente 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 e continua a bloccare risorse critiche fino a quando non riceve una risposta, ovvero anche dopo diversi minuti o addirittura diverse ore.

  • Se possibile, evitare di aprire una transazione durante l'analisi dei dati. Le transazioni devono essere avviate solo dopo il completamento dell'analisi preliminare 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. Evitare di aprire la transazione prima che sia necessario.

  • 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 rapidamente in modo da utilizzare il livello di isolamento delle transazioni Read Committed. Non tutte le transazioni richiedono il livello di isolamento Serializable.

  • Utilizzare in modo intelligente le opzioni di concorrenza dei cursori di livello più basso, ad esempio le opzioni di concorrenza ottimistica. In un sistema in cui è improbabile che vengano eseguiti aggiornamenti simultanei, l'overhead associato all'errore occasionale causato dalla modifica dei dati da parte di altri utenti successivamente alla lettura dei dati stessi può essere molto più ridotto dell'overhead associato al blocco di ogni riga letta.

  • Durante una transazione, accedere alla quantità minima di dati possibile. Ciò consente 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 come il livello di isolamento HOLDLOCK o SERIALIZABLE possono causare l'attesa dei processi anche per i blocchi condivisi e riducono la concorrenza

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

  • Progettare gli indici con un fattore di riempimento ridotto. La riduzione del fattore di riempimento può contribuire a impedire o ridurre la frammentazione delle pagine di indice, riducendo così i tempi di ricerca negli indici, soprattutto per il recupero da disco. Per visualizzare le informazioni sulla frammentazione per i dati e gli indici di una tabella o vista, è possibile usare sys.dm_db_index_physical_stats.

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, l'istruzione Transact-SQL successiva 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. Questo processo consente a SQL Server motore di database di usare la modalità autocommit mentre l'applicazione esplora i dati e riceve l'input dall'utente.

Inoltre, se è abilitato il livello di isolamento dello snapshot, anche se una nuova transazione non manterrà attivi i blocchi, una transazione con esecuzione prolungata consentirà di evitare la rimozione delle versioni precedenti da tempdb.

Gestire le transazioni con esecuzione prolungata

Una transazione a esecuzione prolungata è una transazione attiva che non è stato eseguito il commit o il rollback della transazione in modo tempestivo. Se, ad esempio, l'inizio e la fine di una transazione sono controllati dall'utente, una causa tipica di una transazione con esecuzione prolungata è l'avvio di una transazione da parte di un utente senza la successiva immissione di una risposta.

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 Intervallo di recupero o dall'opzione ALTER DATABASE ... SET TARGET_RECOVERY_TIME. Queste opzioni controllano la frequenza di checkpoint attivi e indiretti, rispettivamente. Per altre informazioni sui tipi di checkpoint, vedere Checkpoint del database (SQL Server).For more information about the types of checkpoints, see Database checkpoints (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ò più eseguire aggiornamenti. 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 di SQL Server 9002) e al log delle transazioni.

Importante

In database SQL di Azure le transazioni inattive (transazioni che non sono state scritte nel log delle transazioni per sei ore) vengono terminate automaticamente per liberare risorse.

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 di particolare interesse includono l'ora del primo record di log (database_transaction_begin_time), lo stato corrente della transazione (database_transaction_state) e il numero di sequenza del 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, se lo si desidera, all'origine della transazione per terminarla in modo più appropriato, ovvero tramite il commit invece del rollback. Per altre informazioni, vedere DBCC OPENTRAN (Transact-SQL).

Arrestare una transazione

Potrebbe essere necessario utilizzare l'istruzione KILL. Eseguire l'istruzione con cautela, soprattutto quando sono in esecuzione processi importanti. Per altre informazioni, vedere KILL (Transact-SQL).

Deadlock

I deadlock sono un argomento complesso correlato al blocco, ma diverso dal blocco.

Contenuto correlato