Question

Describe commit and rollback with syntax and example.

Answer 1

Of course. Here is a detailed description of COMMIT and ROLLBACK, including their purpose, syntax, and a practical example that shows how they work together.

---

Introduction: The Concept of a Transaction

Before explaining COMMIT and ROLLBACK, it's essential to understand a Transaction. A transaction is a sequence of one or more SQL operations that are executed as a single, logical unit of work.

The classic example is a bank transfer:
1. Debit money from Account A.
2. Credit the same amount to Account B.

Both of these operations must succeed. If the first step happens but the second one fails (e.g., due to a system crash), the money is lost. Transactions ensure that this "all-or-nothing" principle, known as atomicity, is enforced. COMMIT and ROLLBACK are the commands used to control the outcome of a transaction.

---

COMMIT

• Description:
  The COMMIT command is used to permanently save all the changes made during the current transaction to the database. Once a COMMIT is executed, the changes are made durable and are visible to all other users of the database. You cannot undo the changes after a COMMIT (except by writing another transaction to reverse them).

• Analogy:
  Think of it as clicking the "Save" button in a document. All your work up to that point is written to the disk and becomes permanent.

• Syntax:
  The syntax is very simple:
  `sql
  COMMIT;
  `

---

ROLLBACK

• Description:
  The ROLLBACK command is used to undo or discard all the changes made during the current transaction. It reverts the database to the state it was in at the end of the last COMMIT. This is used when an error occurs or when the user decides to cancel the operations within the transaction.

• Analogy:
  Think of it as closing a document without saving or clicking the "Undo" button until you get back to your last saved version. All the intermediate changes are lost.

• Syntax:
  The syntax is also very simple:
  `sql
  ROLLBACK;
  `

---

Example: A Bank Transfer

Let's walk through a scenario that demonstrates both commands.

Step 1: Create a Sample Table

First, let's create a simple Accounts table and insert some data.

`sql
CREATE TABLE Accounts (

AccountHolder VARCHAR(50) PRIMARY KEY,
Balance DECIMAL(10, 2)

);

INSERT INTO Accounts (AccountHolder, Balance) VALUES ('Alice', 1000.00);
INSERT INTO Accounts (AccountHolder, Balance) VALUES ('Bob', 500.00);

-- Let's verify the initial state
SELECT * FROM Accounts;
`

Initial State:
| AccountHolder | Balance |
| :--- | :--- |
| Alice | 1000.00 |
| Bob | 500.00 |

Step 2: Perform a Successful Transaction with COMMIT

Now, let's transfer $200 from Alice to Bob.

`sql
-- Start the transaction (in many databases, this is implicit, but it's good practice)
START TRANSACTION;

-- Operation 1: Debit $200 from Alice's account
UPDATE Accounts
SET Balance = Balance - 200.00
WHERE AccountHolder = 'Alice';

-- Operation 2: Credit $200 to Bob's account
UPDATE Accounts
SET Balance = Balance + 200.00
WHERE AccountHolder = 'Bob';

-- At this point, the changes are only visible within our current session.
-- If we check the table now, we'll see the new balances, but they are not yet permanent.
SELECT * FROM Accounts;
`

Intermediate State (within the transaction):
| AccountHolder | Balance |
| :--- | :--- |
| Alice | 800.00 |
| Bob | 700.00 |

`sql
-- Everything looks correct, so we make the changes permanent.
COMMIT;

-- Now the transaction is complete and saved.
-- The changes are visible to everyone.
SELECT * FROM Accounts;
`

Final State (after COMMIT):
| AccountHolder | Balance |
| :--- | :--- |
| Alice | 800.00 |
| Bob | 700.00 |

Step 3: Perform an Unsuccessful Transaction with ROLLBACK

Let's try to transfer $100 from Bob to a non-existent person, "Charlie."

`sql
-- Start a new transaction
START TRANSACTION;

-- Operation 1: Debit $100 from Bob's account
UPDATE Accounts
SET Balance = Balance - 100.00
WHERE AccountHolder = 'Bob';

-- Check the intermediate state for Bob
SELECT * FROM Accounts WHERE AccountHolder = 'Bob';
-- Result would show Bob has $600.00

-- Operation 2: Try to credit "Charlie", who doesn't exist. This will affect 0 rows.
UPDATE Accounts
SET Balance = Balance + 100.00
WHERE AccountHolder = 'Charlie';

-- We realize something is wrong. The transfer is incomplete.
-- We must undo the changes to keep the data consistent.
ROLLBACK;

-- Now let's check the final state of the table.
SELECT * FROM Accounts;
`

Final State (after ROLLBACK):
The ROLLBACK command has undone the debit from Bob's account. The database is restored to the state it was in after the last COMMIT.
| AccountHolder | Balance |
| :--- | :--- |
| Alice | 800.00 |
| Bob | 700.00 |

Summary

| Command | Purpose | Effect | When to Use |
| :--- | :--- | :--- | :--- |
| COMMIT | Save the work. | Makes all changes in the transaction permanent. | When all operations in a transaction have completed successfully. |
| ROLLBACK | Undo the work. | Discards all changes in the transaction. | When an error occurs or the transaction needs to be canceled. |