Day 44: MySQL Transactions - Ensuring Data Consistency and Reliability

Today, let's explore MySQL Transactions, a crucial feature for maintaining data integrity when performing multiple operations!

What is a Transaction in MySQL?

A Transaction is a sequence of one or more SQL operations that are executed as a single unit. If any operation fails, the entire transaction can be rolled back, ensuring that the database remains consistent and error-free.

Key Features of Transactions:

?? Atomicity - All operations succeed or fail together

?? Consistency - Ensures data remains valid before and after a transaction

?? Isolation - Transactions don't interfere with each other

?? Durability - Once committed, changes persist permanently

Enabling Transactions in MySQL

By default, MySQL uses autocommit mode(each statement is executed independently). To control transactios manually, we disable autocommit.

? Start a Transaction:

START TRANSACTION;        

? Commit Changes (Save Data):

COMMIT;        

? Rollback Changes (Undo Actions):

ROLLBACK;        

(Used when an error occurs to revert changes)

2. Example: Banking Transaction - Transferring Money Between Accounts

Let's say we want to transfer 1000 rupees from Account A to Account B.

START TRANSACTION;

-- Deduct 1000 rupees from Account A
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 101;

-- Add 1000 rupees to Account B
UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 102;

-- Check if both updates were successful
IF (ROW_COUNT() >0) THEN
    COMMIT;  --Save changes
ELSE 
    ROLLBACK;  --Und if any issue occurs
END IF;        

(Ensures that both operations happen together. If one fails, no money is lost!)

3. Using SAVEPOINTS for Partial Rollbacks

Example: Handling Partial Errors

START TRANSACTION;

SAVEPOINT step1;
UPDDATE orders SET status = 'Processing' WHERE order_id = 1001;

SAVEPOINT step2;
UPDATE payments SET status = 'Failed' WHERE order_id = 1001;

-- Rollback to step1 if payment fails
ROLLBACK TO step1;

COMMIT;        

(This allows reversing only the failed payment while keeping the order update)

4. Checking the Current Transaction Status

? To Check if a Transaction is Active:

SELECT @@autocommit;        

(If @@autocommit = 0, manual transactions are enabled)

When Should We Use Transactions?

?? Banking & Finance - Ensuring safe fund transfers

?? E-Commerce - Order & Payment processing

?? Inventory Management - Preventing incorrect stock updates

?? Multistep Updates - Any process requiring multiple dependent changes

#100DaysOfCode #MySQL #SQL #Database #Transactions #DataConsistency #Learning #BackendDevelopment