1. BEGIN Statement:

The BEGIN statement is used to start a new transaction. Once a transaction is started, any changes made to the database will not be saved permanently until a COMMIT statement is executed.

Example:

sqlCopy code
BEGIN;

• This simple BEGIN statement initiates a new transaction. From this point on, any SQL statements executed are considered part of the transaction until a COMMIT or ROLLBACK statement is encountered.

2. COMMIT Statement:

The COMMIT statement is used to permanently save all the changes made during the current transaction. If the transaction completes successfully, the changes are saved to the database; otherwise, they are rolled back.

Example:

sqlCopy code
-- Assume we have made changes to the database during the transaction
-- ...

COMMIT;

• The COMMIT statement is used to save all the changes made during the current transaction to the database.

3. ROLLBACK Statement:

The ROLLBACK statement is used to undo all the changes made during the current transaction and return the database to its previous state. It is typically used when an error occurs or when you want to cancel a transaction.

Example:

sqlCopy code
-- Assume an error occurred during the transaction or we want to cancel it
-- ...

ROLLBACK;

• The ROLLBACK statement undoes all the changes made during the current transaction, effectively canceling it.

Real-Time Example: Money Transfer Transaction

Let's consider a real-world example of a money transfer transaction between two bank accounts. We want to ensure that the money is deducted from one account and added to another account atomically to maintain data integrity.

sqlCopy code
BEGIN;

-- Deduct $100 from Account A
UPDATE accounts
SET balance = balance - 100
WHERE account_number = 'A123';

-- Add $100 to Account B
UPDATE accounts
SET balance = balance + 100
WHERE account_number = 'B456';

-- Check if the transaction is valid
-- If any error occurs, we ROLLBACK the transaction
-- If the transaction is valid, we COMMIT it
-- ...

COMMIT;

• In this example, we start a transaction with BEGIN.
• We then deduct $100 from Account A and add $100 to Account B.