CBSE Class 12 Informatics Practices Database Transactions Worksheet

CHAPTER – 14: DATABASE TRANSACTIONS

Database transaction - A database transaction is a logical unit of work that must either succeed or fail in its entirety.

ACID properties of database transaction –

• Atomicity- Atomicity ensures either all-or-none operations of a transaction are carried out.

• Consistency-This property ensures that database remains in a consistent state before the start of transaction and after the transaction is over.

• Isolation-Isolation ensures that executing transaction execution in isolation i.e. is unaware of other transactions executing concurrently in the system.

• Durability-This property ensures that after the successful completion of a transaction i. e when a transaction COMMITs, the changes made by it to the database persist i.e. remain in the database irrespective of other failures.

TCL commands supported by SQL:-

SQL supports following TCL commands

BEGIN |START TRANSACTION-Marks the beginning of a transaction

COMMIT - Ends the current transaction by saving database changes and starts a new transaction.

Syntax : COMMIT ;

COMMIT WORK;

ROLLBACK - Ends the current transaction by discarding changes and starts a new transaction.

Syntax : ROLLBACK ;

ROLLBACK WORK;

SAVEPOINT - Defines breakpoints for the transactions to allow partial rollbacks.

Syntax : SAVEPOINT ; e.g. – SAVEPOINT MARK1;

To rollback the transaction till the bookmark named MARK1, we can write the following:-

ROLLBACK TO SAVEPOINT MARK1;

SET AUTOCOMMIT-Enables or disable the default auto commit mode. By default the autocommit mode is true or 1.

@@autocommit- This command is used to check the status of autocommit.

Short Answer Type Questions

1. What is the concept of Database transaction?

2. Describe briefly the ACID Properties of the Transaction?

3. Describe the following command
a) COMMIT
b) ROLLBACK
c) SAVEPOINT

4. What will happen when COMMIT statement is issued?

5. What will happen when ROLLBACK statement is issued?

6. Write one difference between COMMIT and ROLLBACK command?