Transazione in SQL Server

12-09-2022
Toanngo92
0 Comments

Informazioni sulla transazione

Esistono molte situazioni in cui gli utenti richiedono modifiche ai dati in molte tabelle del database. In molte situazioni, i dati perderanno consistenza se eseguiti separatamente.

Si supponga che la prima istruzione venga eseguita correttamente ma l’istruzione successiva fallisca perché i dati non sono corretti.

Ad esempio, una situazione particolare sono i trasferimenti di denaro nel sistema bancario. Il trasferimento richiederà una dichiarazione INSERT e due dichiarazioni UPDATE:

L’utente deve ridurre il saldo nell’account di origine.
Quindi è necessario aumentare il saldo del conto nel sistema bancario nel record del conto di destinazione.

L’utente dovrà verificare che questa transazione sia stata salvata e se le stesse modifiche vengono apportate agli account di origine e di destinazione.

Definizione transazione

Un’unità di lavoro ragionevole deve presentare quattro proprietà, note come proprietà di Atomicità, Consistenza, Isolamento e Persistenza (ACID), per qualificarsi come transazione:

Atomicità : se la transazione ha molte operazioni, tutte dovrebbero essere impegnate. Se un’operazione nel gruppo non riesce, verrà eseguito il rollback.

Coerenza: la sequenza delle operazioni dovrebbe essere appropriata

Isolamento: le operazioni eseguite devono essere permanentemente isolate dalle altre operazioni sullo stesso database del server

Durabilità: le operazioni eseguite sul database devono essere salvate e archiviate in modo permanente nel database.

Transazione implementativa

SQL Server supporta le transazioni con diverse modalità come segue:

Transazioni di autocommit: (auto-commit) Ogni singola riga di comando viene automaticamente salvata quando riesce. In questa modalità, non è necessario scrivere dichiarazioni specifiche per iniziare e terminare la transazione. Questa è la modalità predefinita di SQL Server
Transazioni esplicite: (esplicite) ogni transazione esplicita inizia con un’istruzione BEGIN TRANSACTION e termina con una transazione ROLLBACK o COMMIT.
Transazioni implicite: (implicitamente) una transazione viene acquisita automaticamente quando viene completata una transazione precedente e ogni transazione viene completata utilizzando la sintassi ROLLBACK o COMMIT.
Transazioni con ambito batch : (con ambito batch) queste transazioni sono correlate al concetto di set di risultati attivi multipli (MARS). E ogni transazione implicita o esplicita che inizia con un pellet MARS è chiamata transazione con ambito batch.
Transazioni distribuite: (transazioni distribuite) è distribuito su 2 o più server noti come gestori di risorse. La gestione delle transazioni deve essere individuata tra il gestore delle risorse tramite un componente server denominato gestore delle transazioni. Ogni istanza in SQL Server può fungere da gestore risorse nelle transazioni distribuite, individuato da un gestore transazioni, ad esempio Microsoft Distributed Transaction Coordinator (MS DTC).

Lotti di estensione delle transazioni

Le istruzioni di transazione determinano l’esito positivo o negativo del blocco e forniscono un database in grado di eseguire il rollback delle operazioni.

È probabile che gli errori rilevati durante l’esecuzione di un batch semplice abbiano un esito parziale, il che non è il risultato desiderato quando si utilizzano le transazioni.

Questo problema porterà a conflitti logici tra le tabelle nel database.

Gli utenti possono aggiungere il codice di controllo degli errori per ripristinare la transazione allo stato precedente in caso di errore.

Il codice di gestione degli errori annullerà tutte le modifiche prima che si verifichi l’errore.

Controllo delle transazioni

Le transazioni possono essere controllate tramite l’applicazione definendo l’inizio e la fine di una transazione.

Le transazioni sono gestite dai livelli di connessione per impostazione predefinita.

Quando una transazione avvia una connessione, tutte le istruzioni T-SQL vengono eseguite sulla stessa connessione e fanno parte della connessione fino al termine della transazione.

INIZIA LA TRANSAZIONE

L’istruzione BEGIN TRANSACTION segna l’inizio di una transazione esplicita.

Per esempio:

USE AdventureWorks2019; GO DECLARE @TranName VARCHAR(30); SELECT @TranName = 'FirstTransaction'; BEGIN TRANSACTION @TranName; DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 13;

IMPEGNO TRANSAZIONE

L’istruzione COMMIT TRANSACTION contrassegna l’endpoint, che è un commit che segnala la fine di una transazione implicita o esplicita.

COMMIT [TRAN | TRANSACTION] [transaction_name | @tran_name_variable]] [;]

Per esempio:

BEGIN TRANSACTION; GO DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 11; GO COMMIT TRANSACTION; GO

IMPEGNA LAVORO

L’istruzione COMMIT WORK contrassegna l’endpoint della transazione.

Sintassi:

COMMIT [WORK] [;]

COMMIT TRANSACTION e COMMIT WORK sono identici, tranne per il fatto che COMMIT TRANSACTION accetta un nome di transazione definito dall’utente.

Crea transazione con commit:

BEGIN TRANSACTION DeleteCandidate WITH MARK N'Deleting a Job Candidate'; GO DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 11; GO COMMIT TRANSACTION DeleteCandidate;

TRANSAZIONE DI ROLLBACK

Le transazioni possono essere annullate e ripristinate al punto originale o al punto di salvataggio nella transazione.

Viene utilizzato per eliminare tutti i dati modificati generati dall’inizio della transazione o al punto di salvataggio. Rilascia anche le risorse detenute dalla transazione.

SALVA TRANSAZIONE

L’istruzione SAVE TRANSACTION imposterà il punto di salvataggio all’interno della transazione.

Sintassi:

SAVE {TRAN | TRANSACTION} {savepoint_name | @savepoint_variable} [;]

Per esempio:

CREATE PROCEDURE SaveTranExample @InputCandidateID INT AS DECLARE @TranCounter INT; SET @TranCounter = @@TRANCOUNT; IF @TranCounter > 0 SAVE TRANSACTION ProcedureSave; ELSE BEGIN TRANSACTION; DELETE HumanResources.JobCandidate WHERE JobCandidateID = @InputCandidateID; IF @TranCounter = 0 COMMIT TRANSACTION; IF @tranCounter = 1 ROLLBACK TRANSACTION ProcedureSave; GO

Nel codice sopra, la transazione savepoint viene creata all’interno della procedura. Verrà utilizzato per eseguire il rollback solo se le modifiche ai dati vengono generate dalla procedura memorizzata se una transazione valida è stata avviata prima dell’esecuzione della procedura.

@@TRANCOUNT in transazione

@@TRANCOUNT è una funzione di sistema che restituisce il valore numerico dell’istruzione di transazione, che si verifica nella connessione corrente.

Per esempio:

PRINT @@TRANCOUNT BEGIN TRAN PRINT @@TRANCOUNT BEGIN TRAN PRINT @@TRANCOUNT COMMIT PRINT @@TRANCOUNT COMMIT PRINT @@TRANCOUNT

Risultato:

Esempio utilizzando @@TRANCOUNT con ROLLBACK

PRINT @@TRANCOUNT BEGIN TRAN PRINT @@TRANCOUNT BEGIN TRAN PRINT @@TRANCOUNT ROLLBACK PRINT @@TRANCOUNT

Risultato:

Contrassegna transazione

Il contrassegno delle transazioni è utile solo quando l’utente è disposto a perdere le transazioni commesse di recente o sta verificando il relativo database.

Contrassegnare le transazioni in base a una pianificazione in ogni singolo database correlato crea una catena comune di punti di ripristino nel database.

Preoccupazioni quando si utilizza Transazione contrassegnata:

Un contrassegno di transazione consumerà spazio fisico, utilizzarlo solo per le transazioni importanti nella strategia di ripristino del database.

Quando viene eseguito il commit della transazione contrassegnata, la riga verrà aggiunta alla tabella logmarkhistory nella tabella msdb.

Se la transazione contrassegnata si estende su più database su server diversi o sullo stesso server, i contrassegni devono essere registrati nei record di tutti i database interessati.

Crea transazione contrassegnata

Per creare una transazione contrassegnata, l’utente può utilizzare la sintassi dell’istruzione BEGIN TRANSACTION con la clausola WITH MARK [DESCRIPTION].

La transazione registra la descrizione del contrassegno, il nome, l’utente, il database, le informazioni sulla data e l’ora e il numero di sequenza del registro (LSN).

Passaggi per creare una transazione contrassegnata in un set di database:

Il nome della transazione nell’istruzione BEGIN TRAN e che utilizza la clausola WITH MARK.
Eseguire un aggiornamento su tutti i database nel set.

Per esempio:

USE AdventureWorks2019; GO BEGIN TRANSACTION ListPriceUpdate WITH MARK 'UPDATE Product List prices'; GO UPDATE Production.Product SET ListPrice = ListPrice * 1.20 WHERE ProductNumber LIKE 'BK-%'; GO COMMIT TRANSACTION ListPriceUpdate; GO

Differenza tra transazione implicita ed esplicita

Implicito	Esplicito
La transazione viene gestita da SQL Server per ogni istruzione DDL e DML	Transazione definita dal compilatore
Le istruzioni DML e DDL vengono eseguite in base alla transazione implicita	Consiste in un’istruzione DML e viene eseguita come unità di query.
Il server SQL eseguirà il rollback dell’intera istruzione	Non includere le istruzioni SELECT perché non modificano i dati.

Livello di isolamento (livello di isolamento)

Le transazioni definiscono un livello di isolamento che definisce il grado in cui una transazione deve essere isolata dai dati o dalle modifiche alle risorse apportate da altre transazioni.

I livelli di isolamento sono definiti in condizioni che consentono effetti simultanei come letture sporche.

Il livello di isolamento della transazione controlla quanto segue:

Quando i dati vengono letti, sono presenti dei blocchi e che tipo di blocchi sono richiesti?
Per quanto tempo vengono mantenuti i blocchi di lettura?
Se un’operazione di lettura che fa riferimento a una riga viene modificata da un’altra transazione, si verifica una delle seguenti situazioni:
- Bloccare fino allo sblocco del meccanismo di bloccaggio unico sulla fila.
- Recupera la versione sottoposta a commit della riga che esisteva all’inizio della transazione o dell’istruzione.
- Leggi i dati non vincolati

Le transazioni richiedono una chiave univoca in ogni momento su ogni dato che governa. Quindi mantiene quel blocco fino al completamento della transazione, indipendentemente dal livello di isolamento impostato per tale transazione.

Livelli di isolamento:

Livello di isolamento	Lettura sporca	Lettura non ripetibile
Leggi impegnato	No	sì
Leggi senza impegno	sì	No
Istantanea	No	No
Lettura ripetibile	No	No
Serializzabile	No	No

Gamma e tipi di serrature

Elenco dei tipi di blocco comuni in SQL Ser

Modalità di blocco	descrizione
Aggiornare	Utilizzare nelle risorse di preparazione degli aggiornamenti
Condivisa	Utilizzare per leggere operazioni senza modificare dati come l’istruzione SELECT
Avere intenzione	Utilizzato per stabilire un blocco a cascata
Esclusivo	Utilizzato per operazioni di manipolazione dei dati come INSERT, UPDATE, DELETE.
AGGIORNAMENTO IN BLOCCO	Utilizzato quando si copiano grandi quantità di dati in una tabella.
Schema	Utilizzato quando l’operazione dipende dallo schema della tabella

Aggiorna blocchi

Questi blocchi evitano situazioni di stallo. Serializza le transazioni, la transazione legge i dati, acquisisce un blocco condiviso sulla riga o sulla pagina e la modifica dei dati richiede la conversione del blocco in un blocco esclusivo.

Serrature condivise

Questi blocchi consentono alle transazioni parallele di leggere una risorsa sotto il controllo della concorrenza.

I blocchi condivisi rilasciano le risorse una volta completata un’operazione di lettura, tranne per il fatto che il livello di isolamento è assegnato a un’azione di lettura ripetuta o superiore.

Serrature esclusive

Questi blocchi impediscono l’accesso simultaneo alle risorse all’interno della transazione.

Utilizzando il blocco esclusivo, nessuna transazione può modificare i dati e l’operazione di lettura verrà pianificata solo tramite il livello di isolamento non vincolato o la modalità NOLOCK.

Le istruzioni DML come INSERT, UPDATE, DELETE vengono utilizzate per modificare i dati.

Blocchi di intenti

Il ruolo del blocco Intento:

Impedisce ad altre transazioni di modificare i dati su risorse di livello superiore, in modo da invalidare i blocchi di livello inferiore.
per migliorare l’efficienza del motore di database nell’identificazione dei conflitti chiave con una maggiore granularità.

Elenco delle descrizioni del blocco degli intenti:

Modalità di blocco	descrizione
Intento condiviso (SI)	La protezione del blocco condiviso è richiesta su alcune risorse di livello inferiore.
Intento esclusivo (IX)	La protezione del blocco esclusivo è richiesta in alcune risorse di livello inferiore. IX è un superset (un insieme di insiemi costituito da un altro insieme) di IS, che protegge i blocchi condivisi richiesti al livello di risorse inferiore.
Condiviso con intento esclusivo (SIX)	La protezione dei blocchi condivisi è necessaria tra le risorse inferiori nella gerarchia e i blocchi esclusivi per intenzione in alcune risorse di livello inferiore. I blocchi IS simultanei (blocchi IS simultanei) sono abilitati sulle risorse di primo livello.
Aggiornamento dell’intento (UI)	Per proteggere i blocchi richiesti in tutte le risorse di livello inferiore. I blocchi IU vengono utilizzati solo sulle risorse della pagina. I blocchi IU vengono convertiti in blocchi IX se viene eseguita un’operazione di aggiornamento.
Aggiornamento dell’intento condiviso (SIU)	Fornisce una combinazione di serrature S e IU, come risultato dell’ottenimento di serrature separate e della tenuta di entrambe le serrature contemporaneamente.
Aggiorna intento esclusivo (UIX)	Fornisce una combinazione di lucchetti U e IX, come risultato dell’ottenimento di lucchetti separati e del mantenimento di entrambi i lucchetti contemporaneamente.

Blocco degli aggiornamenti in blocco

I blocchi di aggiornamento in blocco vengono utilizzati quando una grande quantità di dati viene copiata nella tabella. Questi blocchi consentiranno l’esecuzione simultanea di più thread per caricare i dati in blocco in sequenza su una tabella.

Blocchi schema

I blocchi di modifica dello schema vengono utilizzati nel Motore di database durante l’esecuzione di operazioni DDL come l’eliminazione di tabelle o colonne.

Le chiavi di stabilità dello schema vengono utilizzate dal motore di database durante la compilazione e l’esecuzione di query.

Serrature a gamma di chiavi

Questo tipo di blocchi proteggerà l’elenco dei record rappresentati nel RRset.

I blocchi dell’intervallo di chiavi impediscono le letture fantasma new corrisponde alla condizione ricercata per la transazione A. Se A esegue nuovamente la stessa condizione, otterrà un insieme di dati non uniforme.)

Gestisci le transazioni

SQL Server implementa transazioni con ambiti diversi che garantiscono le proprietà ACID di queste transazioni.

In pratica, ciò significa utilizzare i blocchi come base per le transazioni per interrogare risorse di database condivise e prevenire interferenze tra le transazioni.

Registro delle transazioni

Il registro delle transazioni è un componente importante del database, se il sistema si arresta in modo anomalo, il registro delle transazioni garantirà il ripristino dei dati allo stato corretto.

Il registro delle transazioni non deve essere eliminato o spostato finché l’utente non ne comprende le conseguenze.

Operazioni supportate dal registro delle transazioni:

Recupero di singole transazioni.
Rollback della transazione non completata all’avvio di SQL Server.
Supporto per la replica delle transazioni
Soluzione di ripristino di emergenza, a supporto di sistemi con requisiti di prestazioni elevate.
Ripristina file, database, gruppi di file o inoltra le pagine fino al punto di errore.

Tronca il registro delle transazioni

Il troncamento del registro delle transazioni libererà la memoria occupata dal file di registro per continuare la registrazione. I registri verranno automaticamente troncati quando si verificano i seguenti eventi:

In un semplice modello di recupero dopo (checkpoint) il checkpoint.
Un modello di ripristino in blocco e ripristino completo, se si verifica un checkpoing dall’ultimo backup.

Quando i registri sono attivi per molto tempo, il registro delle transazioni verrà ritardato e potrebbe riempire la memoria di sistema. I troncamenti dei log possono essere lenti per una serie di motivi, gli utenti possono scoprire se qualcosa impedisce il troncamento del log di traslazione eseguendo query sulle colonne log_reuse_wait_desc e log_reuse_wait nella vista del catalogo sys.databases.

Descrizione del valore a 2 colonne:

Log_reuse_wait	Log_reuse_wait_desc	Descrivere
0	NIENTE	Specifica che rappresenta più di un file di registro virtuale riutilizzabile
primo	PUNTO DI CONTROLLO	Determina che non sono stati visualizzati checkpoint dall’ultimo troncamento del registro o che il titolo del registro non è stato spostato al di fuori del file di registro virtuale
2	LOG_BACKUP	Specificare il backup del registro richiesto prima di eseguire il troncamento del registro.
3	ACTIVE_BACKUP_OR_RESTORE	Specifica che è in corso un backup o un ripristino.
4	ACTIVE_TRANSACTION	Determina la transazione attiva.
5	DATABASE_MIRRORING	Determina che il mirroring del database è sospeso o in modalità a prestazioni elevate, il database mirror è dietro il database principale