Una transazione in SQL Server è a gruppo sequenziale di istruzioni o domande per eseguire attività singole o multiple in un database. Ogni transazione può avere singole operazioni di lettura, scrittura, aggiornamento o eliminazione o una combinazione di tutte queste operazioni. Ogni transazione deve accadere due cose in SQL Server:
- Tutte le modifiche hanno esito positivo quando la transazione viene confermata.
- Oppure, tutte le modifiche vengono annullate quando viene eseguito il rollback della transazione.
Una transazione non può avere successo finché tutte le operazioni del set non vengono completate. Ciò significa che se un argomento fallisce, l'operazione di transazione fallirà. Ogni transazione inizia con la prima istruzione SQL eseguibile e termina quando trova un commit o un rollback, in modo esplicito o implicito. Utilizza il COMMETTERE O ROLLBACK istruzioni in modo esplicito, nonché implicitamente quando viene utilizzata un'istruzione DDL.
La rappresentazione pittorica seguente spiega il processo di transazione:
Il seguente esempio spiegherà il concetto di transazione:
Questo esempio utilizzerà il sistema di database bancario per spiegare il concetto di transazione. Supponiamo che un cliente della banca desideri prelevare denaro dal proprio conto utilizzando la modalità ATM. L’ATM può realizzare questa operazione in tre fasi:
- IL primo passo consiste nel verificare la disponibilità sul conto dell'importo richiesto.
- IL Secondo passo detrae l'importo dal conto se l'importo è disponibile e quindi aggiorna il saldo del conto.
- IL terzo passo è scrivere l'operazione di prelievo di denaro nel file di registro. Questo passaggio indica se la transazione è riuscita o meno. In caso di successo, scrivere la modifica dei dati nel database. In caso contrario, la transazione verrà ripristinata allo stato precedente.
Il principio di base alla base delle transazioni è che se una delle istruzioni restituisce un errore, l'intero insieme di modifiche viene annullato per garantire l'integrità dei dati. E se le transazioni hanno esito positivo, tutte le modifiche saranno permanenti nel database. Pertanto, se si verifica un'interruzione di corrente o altri problemi durante il prelievo di denaro da un bancomat, le transazioni garantiscono che il nostro saldo rimanga coerente. Un estratto conto esegue al meglio queste operazioni perché le quattro proprietà chiave della transazione rendono tutte le operazioni più accurate e coerenti. Le quattro proprietà della transazione vengono chiamate ACID.
Proprietà della transazione
Le proprietà della transazione sono denominate proprietà ACID (Atomicità, Coerenza, Isolamento, Durabilità), che vengono discusse in dettaglio di seguito:
Atomicita: Questa proprietà garantisce che tutte le istruzioni o operazioni incluse nella transazione debbano essere eseguite correttamente. In caso contrario, l'intera transazione verrà interrotta e tutte le operazioni verranno riportate allo stato precedente quando un'operazione fallisce.
Consistenza: Questa proprietà garantisce che il database cambi stato solo quando una transazione verrà eseguita correttamente. È anche responsabile della protezione dei dati da arresti anomali.
Isolamento: Questa proprietà garantisce che tutte le transazioni siano isolate dalle altre transazioni, il che significa che ogni operazione nella transazione viene gestita in modo indipendente. Garantisce inoltre che le dichiarazioni siano trasparenti tra loro.
Durabilità: Questa proprietà garantisce che il risultato delle transazioni salvate persista permanentemente nel database anche se il sistema si arresta in modo anomalo o fallisce.
Modalità di transazione in SQL Server
Esistono tre diverse modalità di transazione che SQL Server può utilizzare:
Modalità di transazione con commit automatico: È la modalità di transazione predefinita di SQL Server. Valuterà ogni istruzione SQL come una transazione e i risultati verranno sottoposti a commit o rollback di conseguenza. Pertanto le istruzioni riuscite vengono immediatamente sottoposte a commit, mentre le istruzioni fallite vengono immediatamente ripristinate.
Modalità di transazione implicita. Questa modalità consente a SQL Server di iniziare la transazione implicita per ogni istruzione DML, ma richiede esplicitamente l'uso di comandi commit o rollback alla fine delle istruzioni.
Modalità di transazione esplicita: Questa modalità è definita dall'utente e ci permette di identificare esattamente il punto di inizio e di fine di una transazione. Si interromperà automaticamente in caso di errore fatale.
Controllo delle transazioni
Di seguito sono riportati i comandi utilizzati per controllare le transazioni:
NOTA: possiamo utilizzare solo istruzioni DML (INSERT, UPDATE e DELETE) per i comandi Transaction Control Language. Non possiamo usarli durante la creazione o l'eliminazione di tabelle perché queste operazioni vengono eseguite automaticamente nel database.
Stato della transazione
Indica come vanno le transazioni durante la loro vita. Descrive lo stato attuale della transazione e il modo in cui la transazione verrà elaborata in futuro. Questi stati definiscono le regole che determinano se una transazione viene confermata o interrotta.
Descriviamo gli stati di ciascuna transazione in SQL Server:
Stato attivo: La transazione è in uno stato attivo mentre le istruzioni della transazione vengono eseguite. Cambia in 'Stato parzialmente impegnato' se tutte le operazioni di lettura e scrittura vengono completate senza errori. Se un'istruzione fallisce, passa allo 'stato fallito'.
Parzialmente impegnato: Una volta completate tutte le operazioni di lettura e scrittura, la modifica viene apportata alla memoria principale o al buffer locale. Lo Stato andrebbe a 'stato impegnato' se le modifiche vengono rese permanenti nel database. Altrimenti si passa allo “stato fallito”.
Stato fallito: Una transazione passa allo stato non riuscito quando un'istruzione di transazione fallisce o una modifica permanente sul database fallisce.
Stato interrotto: La transazione si muove da a 'stato fallito' ad 'stato interrotto' quando si verifica qualsiasi tipo di guasto. Le modifiche vengono rimosse o annullate perché vengono apportate solo al buffer locale o alla memoria principale negli stati precedenti.
Stato impegnato: Una transazione è completa e passa in questo stato quando le modifiche vengono rese permanenti nel database e terminate nel file 'stato terminato'.
Stato terminato: Se non è presente alcun rollback e la transazione è nel file 'stato impegnato', il sistema è coerente e pronto per una nuova transazione mentre quella vecchia viene terminata.
json dall'oggetto Java
Implementazione della transazione in SQL Server
Facciamo alcuni esempi per capire come possiamo implementare la transazione in SQL Server. Qui useremo il 'Prodotto' tabella per dimostrare tutti gli stati delle transazioni.
I seguenti script SQL creano la tabella Product nel database selezionato:
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
Successivamente, esegui gli script seguenti per inserire i dati in questa tabella:
INSERT INTO Product VALUES(111, 'Mobile', 10000, 10), (112, 'Laptop', 20000, 15), (113, 'Mouse', 300, 20), (114, 'Hard Disk', 4000, 25), (115, 'Speaker', 3000, 20);
Eseguire l'istruzione SELECT per verificare i dati:
Esempio di transazione COMMIT
È una buona idea dividere le istruzioni SQL utilizzate nella transazione in più parti logiche. E poi, possiamo decidere se eseguire il commit o il rollback dei dati. I seguenti passaggi illustrano come creare una transazione:
- Avvia la transazione utilizzando il INIZIA LA TRANSAZIONE comando.
- Scriviamo le istruzioni SQL e dividiamole in base alle nostre esigenze
- Usa il COMMETTERE dichiarazione per completare la transazione e salvare le modifiche in modo permanente.
Di seguito sono riportati i comandi che spiegano le operazioni COMMIT in SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements INSERT INTO Product VALUES(116, 'Headphone', 2000, 30) UPDATE Product SET Price = 450 WHERE Product_id = 113 -- Commit changes COMMIT TRANSACTION
Se non viene trovato alcun errore, vedremo il seguente output in cui ciascuna istruzione SQL della transazione viene eseguita in modo indipendente:
Non è possibile eseguire il rollback delle istruzioni INSERT e UPDATE dopo il commit della transazione. Quando verifichiamo la tabella dopo l'operazione di commit, vedremo i seguenti dati:
Esempio di transazione ROLLBACK
Utilizzeremo il comando ROLLBACK per annullare tutte le transazioni che non sono state ancora salvate nel database e tornare al punto in cui è iniziata la transazione. L'esempio seguente spiega l'operazione ROLLBACK in SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116
Una volta eseguita la transazione di cui sopra, possiamo vedere che verrà eseguita con successo. Tuttavia, ciò non influenzerà alcuna modifica nel database perché finché non si esegue l'istruzione COMMIT o ROLLBACK, le modifiche non possono diventare permanenti. Pertanto, abbiamo la possibilità di utilizzare il comando di transazione ROLLBACK per ripristinare tutte le operazioni del database. Ecco l'estratto conto completo della transazione:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116 --Undo Changes ROLLBACK TRANSACTION
Utilizzo della variabile globale @@Error nelle transazioni:
Questa variabile è utilizzato per verificare se c'è un errore o meno. L'esempio seguente ne spiega il concetto. Qui inizieremo prima la transazione utilizzando il comando BEGIN e poi scriveremo due istruzioni di inserimento. Successivamente, utilizzeremo la variabile di sistema globale @@ERRORE nel Dichiarazione SE per verificare un errore. Se il valore è maggiore di 0, significa che c'è qualche errore. Ora la transazione è in rollback; in caso contrario, la transazione viene confermata.
BEGIN TRANSACTION INSERT INTO Product VALUES(115,'Speaker', 3000, 25) -- Check for error IF(@@ERROR > 0) BEGIN ROLLBACK TRANSACTION END ELSE BEGIN COMMIT TRANSACTION END
Quando la transazione di cui sopra viene eseguita, noteremo che è stato eseguito il rollback. È dovuto al nostro tentativo di inserire un valore duplicato nella colonna Chiave primaria.
Transazione di rollback automatico
La maggior parte delle transazioni contiene più di una query. Durante l'esecuzione della transazione, se una qualsiasi delle istruzioni SQL produce un errore, non viene apportata alcuna modifica al database e le restanti istruzioni non vengono eseguite. Questo concetto è noto come transazione di rollback automatico in SQL Server. Usiamo un semplice esempio per dimostrare questo processo.
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
Questa transazione produce il seguente output:
In questo output, possiamo vedere che l'istruzione insert è stata eseguita correttamente. Tuttavia, l'esecuzione dell'istruzione di aggiornamento ha rilevato un errore dovuto al problema di conversione del tipo di dati. In questo caso, SQL Server non consente alcuna modifica nel database, il che significa che l'operazione di inserimento non aggiunge alcun valore e l'istruzione select non viene eseguita.
Punto di salvataggio nelle transazioni
Il punto di salvataggio inserisce un contrassegno speciale in una transazione che ci consente di ripristinare tutte le modifiche eseguite dopo il punto di salvataggio. Viene utilizzato anche per eseguire il rollback di una parte specifica della transazione anziché dell'intera transazione. Possiamo definirlo utilizzando il SALVA TRANSAZIONE sp_name dichiarazione. L'esempio seguente spiegherà l'uso del punto di salvataggio nelle transazioni che eseguono il commit dell'istruzione insert e il rollback dell'istruzione delete.
BEGIN TRANSACTION INSERT INTO Product VALUES(117, 'USB Drive', 1500, 10) SAVE TRANSACTION InsertStatement DELETE FROM Product WHERE Product_id = 116 SELECT * FROM Product ROLLBACK TRANSACTION InsertStatement COMMIT SELECT * FROM Product;
Guarda il risultato seguente in cui possiamo vedere che l'ID prodotto 116 è stato eliminato e 117 è stato inserito nel primo output. Tuttavia, nel secondo output, l'operazione di eliminazione viene annullata a causa del punto di salvataggio.
Come rilasciare un punto di salvataggio in una transazione?
alfabeto per numero
Il punto di salvataggio di rilascio viene utilizzato per rimuovere il punto di salvataggio denominato dalla transazione corrente senza ripristinare i risultati delle query eseguite dopo il punto di salvataggio. MySQL dispone di questo comando, ma SQL Server non fornisce alcun comando per rilasciare un punto di salvataggio. Vengono invece rilasciati automaticamente al termine di una transazione di commit o rollback, quindi non dobbiamo preoccuparcene nel frattempo.
Transazione implicita in SQL Server
Possiamo definire una transazione implicita abilitando l'opzione IMPLICIT_TRANSACTIONS. Il seguente esempio spiegherà facilmente questo concetto:
SET IMPLICIT_TRANSACTIONS ON UPDATE Product SET Quantity = 10 WHERE Product_id = 113 SELECT IIF(@@OPTIONS & 2 = 2, 'Implicit Transaction Mode ON', 'Implicit Transaction Mode OFF' ) AS 'Transaction Mode' SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
In questa transazione, abbiamo utilizzato due opzioni @@OPZIONE e @@TRANSCOUNT. @@OPTOPN fornisce le informazioni relative alle opzioni SET correnti e @@TRANCOUNT fornisce l'istruzione BEGIN TRANSACTION nella sessione corrente.
Ora, l'esecuzione della transazione restituirà l'output seguente:
Transazione esplicita in SQL Server
Una transazione esplicita deve essere definita tramite il comando BEGIN TRANSACTION perché identifica il punto iniziale della transazione esplicita. Possiamo definire la transazione esplicita in SQL Server come di seguito:
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
Nella sintassi, l'opzione trans_name indica un nome univoco di una transazione. IL @nome_trans_var indica una variabile definita dall'utente che memorizza il nome della transazione. Infine, il SEGNO l'opzione ci consente di contrassegnare una transazione specifica nel file di registro.
La transazione esplicita tramite il comando BEGIN TRANSACTION ha acquisito un blocco in base al livello di isolamento delle risorse relative alla transazione. Aiuta a ridurre i problemi di blocco. Vedi l'esempio seguente:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Ecco l'output:
Transazione contrassegnata in SQL Server
La transazione contrassegnata viene utilizzata per aggiungere una descrizione a una transazione specifica nei file di registro. Possiamo usarlo come punto di ripristino al posto di una data e un'ora quando si ripristina un database a uno stato precedente. Dobbiamo sapere che il contrassegno viene aggiunto nei file di registro solo quando la transazione contrassegnata modifica il database. Possiamo comprenderne il concetto utilizzando il seguente esempio.
Supponiamo di aver modificato accidentalmente il database e di non conoscere il momento esatto dell'alterazione dei dati; in tal caso, il recupero dei dati può richiedere molto tempo. Tuttavia, se utilizziamo le transazioni contrassegnate, può essere uno strumento utile per determinare il momento esatto delle alterazioni dei dati.
La sintassi seguente illustra la transazione contrassegnata in SQL Server:
BEGIN TRANSACTION trans_name WITH MARK 'description';
Qui dobbiamo definire il nome della transazione e quindi aggiungere l'opzione WITH MARK. Nell'esempio seguente, elimineremo i record e aggiungeremo il segno nel file di registro:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
IL logmarkhistory la tabella è inclusa nel banca dati msdb e memorizza le informazioni relative a ciascuna transazione contrassegnata che è stata impegnata. Esegui l'istruzione seguente per ottenere i dettagli dalla tabella logmarkhistory:
SELECT * FROM msdb.dbo.logmarkhistory
Transazione denominata in SQL Server
Possiamo anche fornire un nome per la nostra transazione in SQL Server. È sempre consigliabile utilizzare la transazione denominata quando si lavora con molte transazioni in un'unica query. L'esempio seguente spiega come rinominare una transazione:
BEGIN TRANSACTION AddProduct INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Product_name = 'Pen Drive' WHERE Product_id = 117 COMMIT TRANSACTION AddProduct
Ecco l'output:
Conclusione
Questo articolo fornirà una panoramica completa della transazione nelle istruzioni di SQL Server. Le transazioni sono utili nei sistemi di database relazionali perché garantiscono l'integrità del database.