SQL - Transazioni

Una transazione è un'unità di lavoro eseguita su un database. Le transazioni sono unità o sequenze di lavoro eseguite in un ordine logico, sia manualmente da un utente che automaticamente da una sorta di programma di database.

Una transazione è la propagazione di una o più modifiche al database. Ad esempio, se stai creando un record o aggiornando un record o eliminando un record dalla tabella, allora stai eseguendo una transazione su quella tabella. È importante controllare queste transazioni per garantire l'integrità dei dati e per gestire gli errori del database.

In pratica, raggrupperai molte query SQL in un gruppo e le eseguirai tutte insieme come parte di una transazione.

Proprietà delle transazioni

Le transazioni hanno le seguenti quattro proprietà standard, solitamente indicate con l'acronimo ACID.

  • Atomicity- assicura che tutte le operazioni all'interno dell'unità di lavoro siano completate con successo. In caso contrario, la transazione viene interrotta al punto di errore e tutte le operazioni precedenti vengono riportate al loro stato precedente.

  • Consistency - assicura che il database modifichi correttamente gli stati su una transazione confermata correttamente

  • Isolation - consente alle transazioni di operare in modo indipendente e trasparente l'una dall'altra.

  • Durability - assicura che il risultato o l'effetto di una transazione confermata persista in caso di guasto del sistema.

Controllo delle transazioni

I seguenti comandi vengono utilizzati per controllare le transazioni.

  • COMMIT - per salvare le modifiche.

  • ROLLBACK - per ripristinare le modifiche.

  • SAVEPOINT - crea punti all'interno dei gruppi di transazioni in cui eseguire il ROLLBACK.

  • SET TRANSACTION - Inserisce un nome su una transazione.

Comandi di controllo transazionale

I comandi di controllo transazionale vengono utilizzati solo con l'estensione DML Commandscome - solo INSERT, UPDATE e DELETE. Non possono essere utilizzati durante la creazione di tabelle o durante la loro eliminazione perché queste operazioni vengono salvate automaticamente nel database.

Il comando COMMIT

Il comando COMMIT è il comando transazionale utilizzato per salvare le modifiche invocate da una transazione nel database.

Il comando COMMIT è il comando transazionale utilizzato per salvare le modifiche invocate da una transazione nel database. Il comando COMMIT salva tutte le transazioni nel database dall'ultimo comando COMMIT o ROLLBACK.

La sintassi per il comando COMMIT è la seguente.

COMMIT;

Example

Considera la tabella CLIENTI con i seguenti record:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Di seguito è riportato un esempio che eliminerebbe quei record dalla tabella che hanno età = 25 e quindi COMMIT le modifiche nel database.

SQL> DELETE FROM CUSTOMERS
   WHERE AGE = 25;
SQL> COMMIT;

Pertanto, due righe della tabella verranno eliminate e l'istruzione SELECT produrrà il seguente risultato.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Il comando ROLLBACK

Il comando ROLLBACK è il comando transazionale utilizzato per annullare le transazioni che non sono già state salvate nel database. Questo comando può essere utilizzato solo per annullare le transazioni dall'ultimo comando COMMIT o ROLLBACK.

La sintassi per un comando ROLLBACK è la seguente:

ROLLBACK;

Example

Considera la tabella CLIENTI con i seguenti record:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Di seguito è riportato un esempio, che eliminerebbe quei record dalla tabella che hanno l'età = 25 e quindi ROLLBACK le modifiche nel database.

SQL> DELETE FROM CUSTOMERS
   WHERE AGE = 25;
SQL> ROLLBACK;

Pertanto, l'operazione di eliminazione non influirà sulla tabella e l'istruzione SELECT produrrebbe il risultato seguente.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Il comando SAVEPOINT

Un SAVEPOINT è un punto in una transazione in cui è possibile ripristinare la transazione a un certo punto senza ripristinare l'intera transazione.

La sintassi per un comando SAVEPOINT è come mostrato di seguito.

SAVEPOINT SAVEPOINT_NAME;

Questo comando serve solo nella creazione di un SAVEPOINT tra tutte le istruzioni transazionali. Il comando ROLLBACK viene utilizzato per annullare un gruppo di transazioni.

La sintassi per tornare a un SAVEPOINT è come mostrato di seguito.

ROLLBACK TO SAVEPOINT_NAME;

Di seguito è riportato un esempio in cui si prevede di eliminare i tre diversi record dalla tabella CUSTOMERS. Si desidera creare un SAVEPOINT prima di ogni eliminazione, in modo da poter eseguire il ROLLBACK di qualsiasi SAVEPOINT in qualsiasi momento per riportare i dati appropriati al loro stato originale.

Example

Considera la tabella CLIENTI con i seguenti record.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Il seguente blocco di codice contiene la serie di operazioni.

SQL> SAVEPOINT SP1;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=1;
1 row deleted.
SQL> SAVEPOINT SP2;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=2;
1 row deleted.
SQL> SAVEPOINT SP3;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=3;
1 row deleted.

Ora che le tre eliminazioni sono state effettuate, supponiamo che tu abbia cambiato idea e abbia deciso di ROLLBACK al SAVEPOINT che hai identificato come SP2. Poiché SP2 è stato creato dopo la prima eliminazione, le ultime due eliminazioni vengono annullate:

SQL> ROLLBACK TO SP2;
Rollback complete.

Si noti che è stata eseguita solo la prima eliminazione da quando è stato eseguito il rollback a SP2.

SQL> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+
6 rows selected.

Il comando RELEASE SAVEPOINT

Il comando RELEASE SAVEPOINT viene utilizzato per rimuovere un SAVEPOINT creato dall'utente.

La sintassi per un comando RELEASE SAVEPOINT è la seguente.

RELEASE SAVEPOINT SAVEPOINT_NAME;

Una volta che un SAVEPOINT è stato rilasciato, non è più possibile utilizzare il comando ROLLBACK per annullare le transazioni eseguite dall'ultimo SAVEPOINT.

Il comando SET TRANSACTION

Il comando SET TRANSACTION può essere utilizzato per avviare una transazione di database. Questo comando viene utilizzato per specificare le caratteristiche per la transazione che segue. Ad esempio, è possibile specificare una transazione in sola lettura o in lettura o scrittura.

La sintassi per un comando SET TRANSACTION è la seguente.

SET TRANSACTION [ READ WRITE | READ ONLY ];