Linee guida per i livelli di isolamento delle transazioni con tabelle Memory-Optimized

In molti scenari è necessario specificare il livello di isolamento delle transazioni. L'isolamento delle transazioni per le tabelle ottimizzate per la memoria differisce dalle tabelle basate su disco.

Requisiti per specificare il livello di isolamento delle transazioni:

• TRANSACTION ISOLATION LEVEL è un'opzione obbligatoria per il blocco ATOMIC che comprende il contenuto di una stored procedure compilata in modo nativo.

• A causa delle restrizioni nell'uso dei livelli di isolamento nelle transazioni tra contenitori, l'uso delle tabelle ottimizzate per la memoria nel contesto Transact-SQL interpretato deve spesso essere accompagnato da un suggerimento di tabella che specifichi il livello di isolamento usato per accedere alla tabella. Per ulteriori informazioni sui suggerimenti del livello di isolamento e sulle transazioni tra contenitori, consultare Livelli di isolamento delle transazioni.

• Il livello di isolamento della transazione desiderato deve essere dichiarato in modo esplicito. Non è possibile usare hint di blocco (ad esempio XLOCK) per garantire l'isolamento di determinate righe o tabelle nella transazione.

• L'applicazione che accede al database deve implementare la logica di ripetizione dei tentativi per gestire gli errori risultanti da conflitti di transazioni, errori di convalida e errori di dipendenza del commit. Si noti che gli errori di dipendenza di commit possono verificarsi anche con transazioni di sola lettura.

• Le transazioni con esecuzione prolungata devono essere evitate con tabelle ottimizzate per la memoria. Tali transazioni aumentano la probabilità di conflitti e terminazioni successive delle transazioni. Una transazione a esecuzione prolungata consente anche di evitare l'operazione di Garbage Collection. Quanto più a lungo dura una transazione, tanto più a lungo OLTP In-Memory mantiene le versioni di riga eliminate di recente, che può ridurre le prestazioni di ricerca per le nuove transazioni.

Le tabelle basate su disco si basano in genere sul blocco dei record e l’ostruzione per l'isolamento delle transazioni. Le tabelle ottimizzate per la memoria si basano sul rilevamento di più versioni e conflitti per garantire l'isolamento. Per informazioni dettagliate, vedere la sezione relativa al rilevamento dei conflitti, alla convalida e al controllo delle dipendenze di commit nelle transazioni in tabelle Memory-Optimized.

Le tabelle basate su disco consentono il versionamento multiplo con i livelli di isolamento SNAPSHOT e READ_COMMITTED_SNAPSHOT. Per le tabelle ottimizzate per la memoria, tutti i livelli di isolamento sono basati su più versioni, tra cui REPEATABLE READ e SERIALIZABLE.

Tipi di transazioni

Ogni query in SQL Server viene eseguita nel contesto di una transazione.

In SQL Server sono disponibili tre tipi di transazioni:

• Eseguire il commit automatico delle transazioni. Se non è presente alcun contesto di transazione attivo e le transazioni implicite non sono impostate su ON nella sessione, ogni query ha un proprio contesto di transazione. La transazione viene avviata all'avvio dell'esecuzione dell'istruzione e viene completata al termine dell'istruzione.

• Transazioni esplicite. L'utente avvia la transazione tramite un BEGIN TRAN esplicito o BEGIN ATOMIC. La transazione viene completata dopo il COMMIT e ROLLBACK o END corrispondenti (nel caso di un blocco atomico).

• Transazioni implicite. Quando l'opzione IMPLICIT_TRANSACTIONS è impostata su ON, una transazione viene avviata in modo implicito ogni volta che l'utente esegue un'istruzione e non esiste alcun contesto di transazione attivo. La transazione viene completata mediante un COMMIT esplicito e un ROLLBACK.

Isolamento READ COMMITTED previsto

READ COMMITTED è il livello di isolamento predefinito in SQL Server.

Il livello di isolamento READ COMMITTED garantisce che le transazioni non visualizzino dati non committati delle modifiche esterne alla transazione corrente. In altre parole, la transazione legge solo i dati che sono stati già confermati nel database o che sono stati modificati dalla transazione corrente.

Tutti i livelli di isolamento supportati per le tabelle ottimizzate per la memoria offrono la garanzia "read committed". Pertanto, se la transazione non richiede garanzie più avanzate, è possibile usare uno dei livelli di isolamento supportati per le tabelle ottimizzate per la memoria. SNAPSHOT usa il minor numero di risorse di sistema rispetto ad altri livelli di isolamento.

La garanzia fornita dal livello di isolamento SNAPSHOT (il livello di isolamento più basso supportato per le tabelle ottimizzate per la memoria) include le garanzie di READ COMMITTED. Ogni istruzione nella transazione legge la stessa versione coerente del database. Non solo tutte le righe lette dalla transazione sono confermate nel database, ma anche tutte le operazioni di lettura vedono il set di modifiche effettuate dallo stesso insieme di transazioni.

Linea guida: se è richiesta solo la garanzia di isolamento READ COMMITTED, si consiglia di usare l'isolamento SNAPSHOT con stored procedure compilate nativamente e per l'accesso alle tabelle ottimizzate per la memoria mediante Transact-SQL interpretato.

Per le transazioni autocommit, il livello di isolamento READ COMMITTED viene mappato in modo implicito a SNAPSHOT per le tabelle ottimizzate in memoria. Pertanto, se l'impostazione della sessione TRANSACTION ISOLATION LEVEL è impostata su READ COMMITTED, non è necessario specificare il livello di isolamento tramite un hint di tabella quando si accede a tabelle ottimizzate per la memoria.

L'esempio di transazione autocommit seguente mostra un join tra una tabella ottimizzata per la memoria Customers e una tabella regolare [Cronologia ordini], come parte di un batch ad hoc:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
GO  
SELECT *   
FROM dbo.Customers AS c   
LEFT JOIN dbo.[Order History] AS oh   
    ON c.customer_id = oh.customer_id;  

L'esempio di transazioni esplicite o implicite seguente mostra lo stesso join, ma questa volta in una transazione utente esplicita. La tabella ottimizzata per la memoria Clienti è accessibile utilizzando l'isolamento snapshot, come indicato tramite l'hint di tabella WITH (SNAPSHOT), mentre la tabella regolare [Cronologia ordini] è accessibile con isolamento read committed.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED  
GO  
BEGIN TRAN  
SELECT * FROM dbo.Customers c with (SNAPSHOT)   
LEFT JOIN dbo.[Order History] oh   
    ON c.customer_id=oh.customer_id  
...  
COMMIT  

Differenze operative

Oltre alla garanzia read committed, esistono anche due dettagli di implementazione chiave su cui possono basarsi le applicazioni che usano tabelle basate su disco. Tenere presente quanto segue quando si converte una tabella basata su disco a cui si accede usando l'isolamento READ COMMITTED in una tabella ottimizzata per la memoria a cui si accede usando l'isolamento SNAPSHOT:

• ** L'implementazione del livello di isolamento READ COMMITTED per le tabelle basate su disco (presupponendo che READ_COMMITTED_SNAPSHOT sia OFF) usa blocchi per evitare conflitti tra lettori e scrittori. Quando un writer inizia ad aggiornare una riga, accetta un blocco e non rilascia il blocco finché non viene eseguito il commit della transazione. Tutte le operazioni di lettura vengono bloccate e attendono il completamento della transazione di scrittura.

  Alcune applicazioni possono presupporre che i lettori attendano sempre il commit degli scrittori, in particolare se è presente una sincronizzazione tra le due transazioni nel livello applicativo.

  Linea guida: Le applicazioni non possono basarsi sul comportamento di blocco. Se un'applicazione richiede la sincronizzazione tra transazioni simultanee, tale logica può essere implementata nel livello applicazione o nel livello di database, tramite sp_getapplock (Transact-SQL).

• Nelle transazioni che utilizzano l'isolamento READ COMMITTED, ogni comando visualizza la versione più recente delle righe nel database. Pertanto, le istruzioni successive visualizzano modifiche nello stato del database.

  L'interrogazione di una tabella usando un ciclo WHILE fino a quando non viene trovata una nuova riga è un esempio di modello applicativo che usa questo presupposto. Con ogni iterazione del ciclo, la query visualizzerà gli aggiornamenti più recenti nel database.

  Linea guida: Se un'applicazione deve eseguire il polling di una tabella ottimizzata per la memoria per ottenere le righe più recenti scritte nella tabella, spostare il ciclo di polling all'esterno dell'ambito della transazione.

  Di seguito è riportato un modello di applicazione di esempio che usa questo presupposto. Eseguire l'interrogazione di una tabella utilizzando un ciclo WHILE finché non si trova una nuova riga. In ogni iterazione del ciclo, la query accederà agli aggiornamenti più recenti nel database.

Lo script di esempio seguente controlla periodicamente una tabella t1 fino a quando non contiene una riga. Rimuove quindi una singola riga dalla tabella per un'ulteriore elaborazione.

Si noti che la logica di polling deve essere esterna all'ambito della transazione, perché utilizza l'isolamento istantaneo per accedere alla tabella t1. L'uso della logica di polling all'interno dell'ambito di una transazione crea una transazione di lunga durata, che è una pratica non consigliata.

-- poll table  
WHILE NOT EXISTS (SELECT 1 FROM dbo.t1)  
BEGIN   
  -- if empty, wait and poll again  
  WAITFOR DELAY '00:00:01'  
END  
  
BEGIN TRANSACTION  
  DECLARE @id int  
  SELECT TOP 1 @id=id FROM dbo.t1 WITH (SNAPSHOT)  
  DELETE FROM dbo.t1 WITH (SNAPSHOT) WHERE id=@id  
  
  -- insert processing based on @id  
COMMIT  

Hint di blocco della tabella

Gli hint di blocco (Hint di tabella (Transact-SQL)), come HOLDLOCK e XLOCK, possono essere usati con le tabelle basate su disco affinché SQL Server prenda più blocchi di quelli richiesti dal livello di isolamento specificato.

Le tabelle ottimizzate per la memoria non usano blocchi. I livelli di isolamento più elevati, ad esempio REPEATABLE READ e SERIALIZABLE, possono essere usati per dichiarare le garanzie desiderate.

Gli hint di blocco non sono supportati. Dichiarare invece le garanzie necessarie tramite i livelli di isolamento delle transazioni. NOLOCK è supportato perché SQL Server non accetta blocchi nelle tabelle ottimizzate per la memoria. Si noti che, a differenza delle tabelle basate su disco, NOLOCK non implica il comportamento READ UNCOMMITTED per le tabelle ottimizzate per la memoria.

Vedere anche

Informazioni sulle transazioni nelle tabelle Memory-Optimized
Linee guida per la logica di ripetizione dei tentativi per le transazioni nelle tabelle Memory-Optimized
Livelli di isolamento delle transazioni