CBSE Class 12 Informatics Practices More RDBMS Assignment

More RDBMS (Relational Database Management System)

Summary

Till now we have studied about various SQL statements manipulating data stored in a MySQL database. We executed SQL statements without concern about inconsistencies arising due to group of statements not being executed in entirety. In this lesson, we will study the basic concepts of Transaction processing and how MySQL ensures consistency of data when a group of statements is executed.

Key Points

- Work done during a transaction is a series of operations.
- If one of the operations of a transaction is not executed successfully, then the entire transaction should be cancelled. If all the operations are executed successfully, the transaction should be saved to a database.
- START TRANSACTION statement is used to start a transaction.
- The process of cancelling a transaction is called Rolling back.
- ROLLBACK statement is used to terminate a transaction and roll back the database to its original state before the transaction.
- COMMIT statement is used to save changes to the database.
- When AutoCommit is ON, each SQL statement is a transaction. The changes resulting from each statement are automatically committed.

Question 1. What do you mean by DBMS and Transaction Management? Suppose Raunak's account number is 3246 and his aunt's account number is 5135. In order to process the cheque presented by Raunak, the following two SQL commands need to be executed on the database maintained by the bank:
UPDATE Savings SET balance = balance - 2000 WHERE account_no = 5135; For Aunt's account
UPDATE Savings SET balance = balance + 2000 WHERE account_no = 3246; For Raunak's account
The above two Updates should both take place. If the first Update takes place and there is a system failure, the first updation should be undone. Either both the updations should be done and if it is not possible for both the updations to be done, then no updation should be done.
Answer: A DBMS (Database Management System) is software used for creating, managing, and manipulating databases. Transaction Management refers to the control and coordination of database operations (transactions) to ensure data consistency, integrity, and reliability, especially in cases of concurrent access or system failures. In the given scenario, both update statements must succeed together as a single logical unit of work; if any failure occurs midway, the database must revert to its original state.
In simple words: Transaction management ensures that a group of database operations either all succeed together or none of them take effect at all.

Exam Tip: Always remember the ACID properties (Atomicity, Consistency, Isolation, Durability) in transactions when answering conceptual questions about RDBMS.

Question 2. What is a Transaction?
Answer: A Transaction is a unit of work that must be done in logical order and successfully as a group or not done at all. Unit of work means that a Transaction consists of different tasks - but together they are considered as one unit. Each transaction has a beginning and an end. If anything goes wrong in between the execution of a transaction, the entire transaction (no matter to what extent has been done) should be cancelled. If it is successful, then the entire transaction should be saved to the database.
In simple words: A transaction is an indivisible set of SQL statements treated as a single operation.

Exam Tip: Emphasize that transactions follow the 'all-or-nothing' rule (Atomicity).

Question 3. How are transactions managed?
Answer: Transactions in MySQL are managed using the following 3 primary SQL statements:
1. START TRANSACTION statement
2. COMMIT statement
3. ROLLBACK statement
In simple words: Transactions are controlled by explicitly starting them, saving them permanently using commit, or undoing them using rollback.

Exam Tip: List all three transaction control commands clearly to score full marks.

Question 4. What are SavePoints? What is the benefit for inserting save points in a transaction? Give examples.
Answer: The SAVEPOINT statement defines a marker in a transaction. These markers are useful in rolling back a transaction till the marker. We can add a savepoint anywhere in a transaction. When you roll back to that savepoint, any changes made to the database after the savepoint are discarded, and any changes made prior to the savepoint are saved. It is like semi-committing a transaction.
To define a savepoint, we enter the SAVEPOINT statement like this:
SAVEPOINT <savepoint-name>;
Example: SAVEPOINT Mark1;
In the above statement, a marker (savepoint) with the name Mark1 is defined. It becomes a bookmark in the transaction.
In simple words: Savepoints let you roll back part of a transaction without losing all the work done before the marker.

Exam Tip: Mention the syntax for creating a savepoint and rolling back to it using ROLLBACK TO SAVEPOINT <name>;.

Question 5. How can we rollback any transaction up to a save point?
Answer: To rollback the transaction up to a specific bookmark, we use the ROLLBACK TO SAVEPOINT statement. For example, to rollback up to the bookmark named Mark1:
ROLLBACK TO SAVEPOINT Mark1;
In simple words: Use the ROLLBACK TO SAVEPOINT command followed by the savepoint name to undo actions performed after that specific point.

Exam Tip: Always specify the exact savepoint name as defined earlier in the transaction block.

Question 6. What is Autocommit? How can it be set?
Answer: By default, Autocommit mode is ON in MySQL. It means that MySQL does a COMMIT after every SQL statement that does not return an error. If it returns an error then either Rollback or Commit happens depending on the type of error. If we do not want individual statements of SQL to be automatically committed, we should set the autocommit mode to OFF. When Autocommit is off then we have to issue COMMIT statement explicitly to save changes made to the database.
The following statement sets the autocommit mode to off (it also starts a new transaction):
SET AUTOCOMMIT = 0;
The following statement sets the autocommit mode to ON (it also commits and terminates the current transaction):
SET AUTOCOMMIT = 1;
In simple words: Autocommit automatically saves each statement immediately unless turned off by setting autocommit to 0.

Exam Tip: Remember that Data Definition Language (DDL) commands like CREATE TABLE cause an implicit commit even if AUTOCOMMIT is set to OFF.