Transaction Control Using COMMIT and ROLLBACK

💡 Concept Name

Transaction Control in SQL – Mechanisms to ensure that a set of SQL operations either complete entirely or have no effect at all using COMMIT and ROLLBACK.

📘 Quick Intro

Transactions ensure data integrity in multi-step operations. SQL offers COMMIT to make changes permanent, and ROLLBACK to undo changes in case of error.

🧠 Analogy / Short Story

Think of writing a letter in pencil. You write multiple lines (SQL statements). If you're satisfied, you trace it with a pen (COMMIT). If you spot a mistake, you erase it all (ROLLBACK). This ensures only valid, complete letters are sent.

🔧 Technical Explanation

BEGIN TRANSACTION marks the start of a transactional block.
COMMIT makes all changes since the transaction started permanent.
ROLLBACK undoes all changes made after the transaction began.
Transaction control is vital in banking, inventory, and other multi-step business operations.
Nested transactions may behave differently depending on the RDBMS.

💻 Real Code Example

-- Example: Transfer money between accounts
BEGIN TRANSACTION;

UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

-- Check if both updates were successful
IF @ERROR = 0
    COMMIT;
ELSE
    ROLLBACK;

❓ Interview Q&A

Q1: What is a transaction in SQL?
A: A group of SQL operations executed as a single unit of work.

Q2: What does COMMIT do?
A: It permanently saves changes made during the transaction.

Q3: What is the purpose of ROLLBACK?
A: To undo changes made during the current transaction.

Q4: Why are transactions important?
A: They maintain data consistency and integrity, especially during multi-step operations.

Q5: What is the syntax to start a transaction?
A: Use BEGIN TRANSACTION.

Q6: Can you COMMIT part of a transaction?
A: No, you must COMMIT the whole transaction unless you split operations.

Q7: What happens if you don’t COMMIT or ROLLBACK?
A: Changes remain uncommitted and may be lost or locked depending on the DBMS.

Q8: Can ROLLBACK be partial?
A: Only if you use savepoints (not covered here); otherwise, it undoes the entire transaction.

Q9: Are transactions supported in all databases?
A: Most modern RDBMS support transactions, but not all operations may be transactional.

Q10: Is COMMIT automatic in SQL?
A: By default, some DBMS use auto-commit unless explicitly turned off.

📝 MCQs

Q1. Which SQL command makes a transaction permanent?

ROLLBACK
SAVE
COMMIT
END

Q2. What does ROLLBACK do?

Save data
Redo changes
Undo changes since BEGIN TRANSACTION
Delete table

Q3. How do you start a transaction?

START SQL
BEGIN TRANSACTION
INIT
BEGIN PROCESS

Q4. Why are transactions important?

For fast execution
To manage users
They ensure data integrity
To write logs

Q5. What happens if COMMIT is not issued?

Changes auto-save
Nothing happens
Changes may not be saved
Database shuts down

Q6. Is ROLLBACK reversible?

Yes
Only in MySQL
No
Sometimes

Q7. What command checks for errors after SQL execution?

@ERROR
GETERROR()
@@ERROR
SQL.ERROR

Q8. Can COMMIT be used outside transactions?

No
Yes, but has no effect
Only in SELECT
Only after DELETE

Q9. What is a savepoint?

A type of COMMIT
A table backup
A named point to roll back to
A database snapshot

Q10. Is auto-commit default in SQL Server?

No
Yes
Only in Oracle
Depends on DB version

💡 Bonus Insight

Using transactions is essential in high-stakes systems like banking or inventory. For finer control, use savepoints to allow partial rollbacks within a larger transaction.

📄 PDF Download

Need a handy summary for your notes? Download this topic as a PDF!

📄 PDF Download Coming Soon