Day 60: Transaction Management in SQL | Class 11, Chapter 6 - BSEB Class 11-12 Computer Science: 100-Day Bilingual Guide with MCQs, Solutions and Questions

Day 60 – Transaction Management in SQL

Class 11 Computer Science – Chapter 6

What is Transaction Management in SQL? (SQL में Transaction Management क्या है?)

Transaction management in SQL refers to managing a sequence of operations executed as a single unit to maintain data integrity and reliability in databases.
SQL में Transaction Management का मतलब डेटाबेस में डेटा की अखंडता और विश्वसनीयता बनाए रखने के लिए एक ही इकाई के रूप में निष्पादित ऑपरेशन्स की श्रृंखला को प्रबंधित करना है।

ACID Properties of Transactions (Transactions की ACID गुणधर्म)

• Atomicity: Ensures that either all operations in a transaction are completed or none.
  Atomicity: सुनिश्चित करता है कि लेन-देन की सभी ऑपरेशन्स पूरी होंगी या कोई भी नहीं।
• Consistency: Ensures the database remains in a valid state after a transaction.
  Consistency: लेन-देन के बाद डेटाबेस को एक मान्य स्थिति में बनाए रखता है।
• Isolation: Prevents concurrent transactions from interfering with each other.
  Isolation: समकालीन लेन-देन को एक-दूसरे के साथ हस्तक्षेप करने से रोकता है।
• Durability: Ensures committed changes are permanent, even after a system failure.
  Durability: सुनिश्चित करता है कि प्रतिबद्ध परिवर्तन स्थायी हैं, भले ही सिस्टम विफल हो जाए।

Transaction Control Commands (लेन-देन नियंत्रण आदेश)

SQL provides the following commands to manage transactions:

1. COMMIT

The COMMIT command is used to permanently save all changes made during the current transaction.
COMMIT कमांड का उपयोग वर्तमान लेन-देन के दौरान किए गए सभी परिवर्तनों को स्थायी रूप से सहेजने के लिए किया जाता है।

Syntax:

COMMIT;

Example:

START TRANSACTION;

UPDATE Accounts

SET Balance = Balance - 100

WHERE AccountID = 1;

UPDATE Accounts

SET Balance = Balance + 100

WHERE AccountID = 2;

COMMIT;

Result: Changes are permanently saved.

2. ROLLBACK

The ROLLBACK command is used to undo changes made during the current transaction.
ROLLBACK कमांड का उपयोग वर्तमान लेन-देन के दौरान किए गए परिवर्तनों को वापस करने के लिए किया जाता है।

Syntax:

ROLLBACK;

Example:

START TRANSACTION;

UPDATE Accounts

SET Balance = Balance - 100

WHERE AccountID = 1;

ROLLBACK;

Result: No changes are saved, and the original data is restored.

3. SAVEPOINT

The SAVEPOINT command creates a temporary point within a transaction, allowing you to roll back to that specific point.
SAVEPOINT कमांड लेन-देन के भीतर एक अस्थायी बिंदु बनाता है, जिससे आप उस विशिष्ट बिंदु पर वापस जा सकते हैं।

Syntax:

SAVEPOINT savepoint_name;

Example:

START TRANSACTION;

UPDATE Accounts

SET Balance = Balance - 100

WHERE AccountID = 1;

SAVEPOINT sp1;

UPDATE Accounts

SET Balance = Balance + 100

WHERE AccountID = 2;

ROLLBACK TO sp1;

COMMIT;

Result: Changes after SAVEPOINT are undone, and changes before it are saved.

4. SET TRANSACTION

The SET TRANSACTION command defines a new transaction’s properties, such as isolation level.
SET TRANSACTION कमांड एक नए लेन-देन की विशेषताओं को परिभाषित करता है, जैसे आइसोलेशन स्तर।

Syntax:

SET TRANSACTION [READ WRITE | READ ONLY];

Example:

SET TRANSACTION READ ONLY;

SELECT * FROM Accounts;

Result: Only read operations are allowed in this transaction.

Advantages of Transaction Management (Transaction Management के लाभ)

• Ensures data consistency and reliability.
  डेटा स्थिरता और विश्वसनीयता सुनिश्चित करता है।
• Prevents data loss during system failures.
  सिस्टम विफलताओं के दौरान डेटा हानि को रोकता है।
• Facilitates concurrent access to databases.
  डेटाबेस तक समवर्ती पहुंच की सुविधा देता है।

Practice Questions

Multiple Choice Questions (MCQs)

1. Which command saves changes permanently?
   (a) COMMIT | (b) ROLLBACK | (c) SAVEPOINT | (d) SET TRANSACTION
2. What does the ROLLBACK command do?
   (a) Saves changes | (b) Undoes changes | (c) Sets a point | (d) None
3. Which command creates a temporary point in a transaction?
   (a) SAVEPOINT | (b) COMMIT | (c) ROLLBACK | (d) None
4. Which ACID property ensures that changes are permanent?
   (a) Durability | (b) Atomicity | (c) Isolation | (d) Consistency
5. What happens if a transaction is incomplete and a ROLLBACK is issued?
   (a) Changes are saved | (b) Changes are undone | (c) Changes are committed | (d) None
6. Which command defines a transaction's isolation level?
   (a) SET TRANSACTION | (b) SAVEPOINT | (c) COMMIT | (d) ROLLBACK
7. What does the COMMIT command do?
   (a) Saves changes permanently | (b) Undoes changes | (c) Rolls back to SAVEPOINT | (d) None
8. What is the default transaction mode in most databases?
   (a) AUTO-COMMIT | (b) MANUAL-COMMIT | (c) SAVEPOINT | (d) None
9. What is the purpose of a SAVEPOINT?
   (a) Permanently save changes | (b) Define isolation level | (c) Create a rollback point | (d) None
10. Which ACID property ensures that transactions do not interfere with each other?
    (a) Atomicity | (b) Isolation | (c) Consistency | (d) Durability

Answers to MCQs:

1: (a), 2: (b), 3: (a), 4: (a), 5: (b), 6: (a), 7: (a), 8: (a), 9: (c), 10: (b)

Short Answer Questions

1. What is transaction management in SQL?
   Answer: Managing database operations as a single unit to maintain data consistency and reliability.
2. Explain COMMIT and ROLLBACK commands with examples.
   Answer:
   • COMMIT: Permanently saves changes.
   • ROLLBACK: Undoes changes made during the transaction.
3. What are ACID properties in transactions?
   Answer: Atomicity, Consistency, Isolation, and Durability.
4. What is the purpose of SAVEPOINT?
   Answer: To create a rollback point within a transaction.
5. List the advantages of transaction management.
   Answer: Ensures consistency, prevents data loss, and allows concurrent access.

Long Answer Questions

1. Explain the role of COMMIT, ROLLBACK, and SAVEPOINT in transaction management with examples.
2. Discuss the significance of ACID properties in transaction management.
3. Write SQL queries to demonstrate transaction control commands with real-world use cases.