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

  • Atomic,

  • Consistent,

  • Isolated

  • Durable.

These are popularly known as ACID properties.

Below are the commands are used to control transactions.

1.SET TRANSACTION

SYNTAX

192.png

2.COMMIT

The COMMIT command saves all the transactions to the database since the last COMMIT or ROLLBACK command.

SYNTAX

193.png

EXAMPLE

Let us consider a demo database with table name Persons.

194.png

Figure 48.1. PERSONS TABLE

QUERY

195.png

Figure 48.2. COMMIT QUERY OUTPUT

 
 

3.ROLLBACK

In case of any error, all changes need to be aborted. Rollback command can be used to revert all the last done transactions.

SYNTAX

196.png

QUERY

197.png

Figure 48.3. ROLLBACK QUERY OUTPUT

4. SAVEPOINT

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.

SYNTAX

198.png
 

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.

SYNTAX

199.png