Transactions in SQL

Introduction

A collection of one or more SQL statements that communicate with a database is known as a SQL transaction. A transaction on a table is, for instance, when a record is added to, updated, or deleted from it. Transactions in SQL are crucial for preserving database integrity in cases of concurrent execution of multiple related operations or concurrent interactions with the database. Every transaction starts with a particular task and finishes when every task in the group is successfully completed. A single task failure causes the entire transaction to fail. A transaction can therefore only have one of two outcomes: success or failure.

Properties of SQL Transactions

By definition, a database transaction needs to be atomic, consistent, isolated, and durable. The abbreviation These are frequently referred to as ACID properties.

• Atomicity – This property guarantees the successful completion of all tasks inside the work unit. So, in case any task fails, all prior operations are rolled back to their initial states and the transaction is aborted at the point of failure.
• Consistency – This property makes sure that after a successful committed transaction, the database's states are changed appropriately.
• Isolation – This property allows transactions to function transparently and independently of one another.
• Durability – This property makes sure that a committed transaction's outcome or impact endures in the event of a system malfunction.

Transaction Control

The commands used to manage transactions are as follows:

• BEGIN TRANSACTION – This command marks the start of each transaction.
• COMMIT – This command saves the changes to the database in a permanent manner.
• ROLLBACK – This command reverses any modifications, returning them to their original condition.
• SAVEPOINT – this command enables us to roll back transactions in parts rather than all at once by establishing points within groups of transactions.
• RELEASE SAVEPOINT – This command is used to delete an existing SAVEPOINT.
• SET TRANSACTION – This command gives us the ability to give a transaction a name and specify whether we want it to be read-only, read/write, or to belong to a certain rollback segment.

Examples of Transactions

For the next two examples of SQL transactions, we will use the following table:

Commit Example

So, the steps and commands we should follow and write when creating a COMMIT Transaction are: 1. BEGIN TRANSACTION to start the transaction 2. Various SQL statements such as INSERT, UPDATE… 3. COMMIT command to finish the transaction and save the changes.

Take a look at the following example where we insert a new order in the table shown above and we also update the number of items ordered of an already existing order in the database:

1-- Start the transaction    
2BEGIN TRANSACTION  
3-- SQL Statements  
4 INSERT INTO Table2 VALUES(15, 8112, 2021-11-12, ‘Nadia’, ‘Female’, Stuttgart, Germany, 17, 7)  
5 UPDATE Table2 SET NumberOfItems = 5 WHERE CustomerID = 2  
6 -- Commit changes   
7COMMIT TRANSACTION  

The INSERT and UPDATE statements cannot be reverted back once the transaction has been committed. If there are no errors, we will see the resulting table shown below, with each transaction's SQL query having been individually executed:

Rollback Example

All changes have to be stopped immediately if an error with any of the SQL-grouped statements occurs. Rollback refers to this action of undoing changes in such scenarios. Any transactions that haven't been recorded to the database yet will be rolled back using the ROLLBACK command, allowing us to start from the beginning of the transaction. Only actions taken after the last COMMIT or ROLLBACK command can be reversed with this command.

For example, let’s write a transaction for deleting all orders where the customer’s age is over 40, and then undo the changes, like so:

1-- Start the new transaction    
2BEGIN TRANSACTION  
3-- SQL Statements  
4 DELETE FROM Table2 WHERE Age > 40
5 -- undo changes   
6ROLLBACK TRANSACTION  

After the execution of the transaction, the resulting table would look exactly the same as before, because we rolled back the changes.

One Pager Cheat Sheet

• A SQL transaction is a collection of one or more SQL statements that communicate with a database, typically used for preserving database integrity in cases of concurrent execution of multiple related operations or interactions.
• By guaranteeing atomicity, ensuring consistency, allowing for isolation, and providing durability, SQL Transactions uphold the ACID properties.
• Atomicity is a ACID property that ensures that a transaction is either fully committed or not at all, preventing any inconsistent data from being stored in the database.
• The transaction control commands BEGIN TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT and SET TRANSACTION are used to manage, save, reverse and name transactions.
• We will perform two SQL transactions on the table shown to illustrate examples of transactions.
• By running a BEGIN TRANSACTION, entering various SQL statements such as INSERT, UPDATE commands and then COMMIT TRANSACTION, all the changes will be saved to the database and cannot be reverted back.
• All changes during a SQL transaction are undone using the ROLLBACK command, so that the resulting table looks exactly the same as before.
• The only way to recover the database to its previous state after a COMMIT statement is executed is by executing a ROLLBACK command, however this will only reverse actions taken after the last COMMIT or ROLLBACK command.