HSQLDB Transactions - Hyper SQL Database

What is a Transaction in HSQLDB?

A sequential group of database manipulation operations, which are considered and performed as one single work unit is known as Transaction. The entire transaction is complete only when all the operations are executed successfully and the entire transaction fails if any operation fails within the transaction.

What are the properties of Transactions in HSQLDB?

Four properties are supported by HSQLDB Transactions, which are known as ACID properties.

Atomicity − All the operations in the transactions are executed successfully, otherwise the transaction gets aborted at the point of failure and the previous operations are rolled back to their previous position.

Consistency − The database properly changes states upon a successfully committed transaction.

Isolation − It enables the transaction to operate independently on and transparent to each other.

Durability − The result or effect of a committed transaction persists in case of a system failure.

What are the keywords used in HSQLDB transactions?

Commit, Rollback, and Savepoint are the keywords used in HSQLDB Transactions.

Commit− Always the successful transaction should be completed by executing the COMMIT command.

Rollback − If a failure occurs in the transaction, then the ROLLBACK command should be executed to return every table referenced in the transaction to its previous state.

Savepoint − Creates a point within the group of transactions in which to rollback.

Example

The following illustrations explain the concept of transaction along with commit, rollback, and Savepoint. Table Customers is considered with the columns id, name, age, address, and salary.

Id
Name
Age
Address
Salary
1
Ramesh
32
Ahmedabad
2000.00
2
Karun
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitanya
25
Mumbai
6500.00
5
Harish
27
Bhopal
8500.00
6
Kamesh
22
MP
1500.00
7
Murali
24
Indore
10000.00

The commands used to create the customer along the lines of the above mentioned data is as follows:

Example for COMMIT

The following query deletes rows from the table having age = 25 and uses the COMMIT command to apply those changes in the database.

After execution of the above query, the output received appears as:

After successful execution of the above command, check the records of the customer table by executing the below given command.

After execution of the above query, the output received appears as follows:

Example for Rollback

Consider the same Customer table as input.

Id
Name
Age
Address
Salary
1
Ramesh
32
Ahmedabad
2000.00
2
Karun
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitanya
25
Mumbai
6500.00
5
Harish
27
Bhopal
8500.00
6
Kamesh
22
MP
1500.00
7
Murali
24
Indore
10000.00

The example query that explains about Rollback functionality by deleting records from the table having age = 25 and then ROLLBACK the changes in the database is as follows:

After successful execution of the above two queries, the record data in the Customer table is visible using the following command.

After execution of the above command, the output received appears as:

The delete query deletes the record data of customers whose age = 25. The Rollback command, rolls back those changes on the Customer table.

Example for Savepoint

Savepoint is a point in a transaction when the transaction can be rolled back to a certain point without rolling back the entire transaction.

Consider the same Customer table as input.

Id
Name
Age
Address
Salary
1
Ramesh
32
Ahmedabad
2000.00
2
Karun
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitanya
25
Mumbai
6500.00
5
Harish
27
Bhopal
8500.00
6
Kamesh
22
MP
1500.00
7
Murali
24
Indore
10000.00

In the example, it is considered to plan three different records from the Customers table. Create a Savepoint before each delete, so that one can roll back to any Savepoint at any time to return the appropriate data to its original state. The series of operations is as follows:

Now, three Savepoint are created and three records are deleted. In this situation, in order to roll back the records having Id 2 and 3 then use the following Rollback command.

Notice that only the first deletion took place since it is rolled back to SP2. Use the following query to display all the records of the customers.

After execution of the above query, the output received appears as:

Release Savepoint

The Savepoint can be released using the RELEASE command using the following syntax:

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Hyper SQL Database Topics