A transaction is a unit or sequence of work that is performed on a database. Transactions are accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.
A transaction is the propagation of one or more changes to the database. For example, if you are creating, updating or deleting a record from the table, then you are performing a transaction on that table. It is important to control these transactions to ensure the data integrity and to handle database errors.
Practically, you will club many SQL queries into a group and you will execute all of them together as a part of a transaction.
Transactions have the following four standard properties, usually referred to by the acronym ACID.
Transactional control commands are only used with the DML Commands such as – INSERT, UPDATE and DELETE. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database. Following commands are used to control transactions.
The COMMIT command is the transactional command used to save changes invoked by a transaction. It saves all the transactions occurred on the database since the last COMMIT or ROLLBACK.
The syntax for the COMMIT command is as follows.
Firstly, let us create a table names CUSTOMERS using the following query −
We are inserting some records into the above-created table −
The table will be created as follows −
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 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00
Following query would delete those records from the table which have AGE as 25 and then COMMIT the changes in the database.
The two rows from the table would be deleted and if you verify the contents of the CUSTOMERS table using the SELECT statement as −
The table will be displayed as follows −
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 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00
The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database. This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
The syntax for the ROLLBACK command is as follows.
Let us consider the CUSTOMERS table which we created earlier. We will delete one record and then roll back the transaction to undo the deletion.
Now, if you select the records from CUSTOMERS table, you will see that the deletion was undone and Hardik’s record still exists:
A SAVEPOINT is a point within a transaction that you can roll back to without affecting the entire transaction. It is used to divide a transaction into smaller parts.
The syntax for SAVEPOINT is:
Here’s an example that demonstrates how to use SAVEPOINT:
In this example, the record with ID 3 will be deleted and then rolled back. The deletion of record with ID 1 will remain because the rollback was only to the savepoint SP1.
Once a SAVEPOINT has been created, you can remove it using the RELEASE SAVEPOINT command. Once a savepoint is released, you can no longer use ROLLBACK TO that savepoint.
The syntax for RELEASE SAVEPOINT is:
This command is used to begin a new transaction. It can also define the properties of the transaction like read/write access level and isolation level.
The syntax for SET TRANSACTION is:
This command would start a transaction with read/write access.
SQL Transactions are crucial for maintaining data integrity and consistency. By using COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION, you can control the execution of multiple statements and manage changes effectively. Vista Academy emphasizes understanding these commands to ensure robust data management in real-world SQL environments.