A transaction is a sequential unit of work accomplished in a logical order against a database.
A transaction is the propagation of one or more changes to the database.
For example, if we are creating a table or updating a record or deleting a record from the table, then you are performing a transaction on that table. It is important to control these transactions in order to ensure the data integrity and to handle the errors in the database.
The database transaction must be
These are popularly known as ACID properties.
Below are the commands are used to control transactions.
The COMMIT command saves all the transactions to the database since the last COMMIT or ROLLBACK command.
Let us consider a demo database with table name Persons.
Figure 48.1. PERSONS TABLE
Figure 48.2. COMMIT QUERY OUTPUT
In case of any error, all changes need to be aborted. Rollback command can be used to revert all the last done transactions.
Figure 48.3. ROLLBACK QUERY OUTPUT
A SAVEPOINT creates a point in a transaction in which you can roll the transaction back to a certain point without rolling back the entire transaction.
5. RELEASE SAVEPOINT
RELEASE SAVEPOINT is a command which is used to remove a SAVEPOINT that we have created.
Note: Once a SAVEPOINT has been released, we can no longer use the ROLLBACK command to undo transactions performed since the last SAVEPOINT.
Want More SQL Deep Dives