October 1, 2026

MySQL Transactions, COMMIT, ROLLBACK & Data Safety – Complete Guide for Beginners

Introduction

MySQL mein jab hum simple INSERT, UPDATE aur DELETE queries run karte hain, to har query database mein changes kar sakti hai.

Lekin real-world applications mein kai baar multiple SQL queries ko ek single operation ki tarah execute karna padta hai.

For example:

• Bank account se money transfer
• E-commerce order create karna
• Order ke saath order items save karna
• Payment status update karna
• Stock quantity reduce karna
• Employee salary update karna
• Wallet balance update karna

Imagine karo bank transfer mein:

Account A → ₹5,000 deduct
Account B → ₹5,000 add

Agar pehli query successfully execute ho gayi, lekin doosri query fail ho gayi, to database inconsistent ho sakta hai.

Isi problem ko solve karne ke liye MySQL Transactions use ki jaati hain.

Is tutorial mein hum beginner-friendly practical examples ke saath seekhenge:

• Transaction kya hoti hai?
• START TRANSACTION
• COMMIT
• ROLLBACK
• SAVEPOINT
• ROLLBACK TO SAVEPOINT
• RELEASE SAVEPOINT
• ACID properties
• Autocommit
• Transaction aur data consistency
• Bank transfer example
• E-commerce order example
• Stock management
• PHP transaction example
• CodeIgniter 4 transaction example
• Common mistakes
• Data safety best practices
• Practice exercises

---

1. Transaction Kya Hai?

Transaction SQL operations ka ek logical group hota hai jo database mein ek unit of work ki tarah execute hota hai.

Simple words mein:

Multiple SQL queries ko ek single logical operation ke roop mein execute karna = Transaction

Example:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 5000
WHERE id = 1;

UPDATE accounts
SET balance = balance + 5000
WHERE id = 2;

COMMIT;

Yahan dono UPDATE queries ek transaction ka part hain.

Agar sab kuch successfully complete ho gaya:

COMMIT;

Agar problem aa gayi:

ROLLBACK;

---

2. Real-World Example – Bank Transfer

Suppose Rahul ke account mein:

₹50,000

Aur Amit ke account mein:

₹20,000

Rahul ko Amit ko ₹5,000 transfer karne hain.

Process:

Rahul Account
₹50,000
   ↓
- ₹5,000
   ↓
₹45,000

Amit Account
₹20,000
   ↓
+ ₹5,000
   ↓
₹25,000

Yahan do operations hain:

UPDATE accounts
SET balance = balance - 5000
WHERE id = 1;

and:

UPDATE accounts
SET balance = balance + 5000
WHERE id = 2;

Dono operations successfully complete hone chahiye.

Agar first operation successful ho aur second fail ho jaye, to problem create ho sakti hai.

Isliye transaction:

START TRANSACTION;

-- Money deduct
UPDATE accounts
SET balance = balance - 5000
WHERE id = 1;

-- Money add
UPDATE accounts
SET balance = balance + 5000
WHERE id = 2;

COMMIT;

---

3. Transaction Ka Basic Syntax

Basic syntax:

START TRANSACTION;

-- SQL Query 1
-- SQL Query 2
-- SQL Query 3

COMMIT;

Agar error/problem ho:

ROLLBACK;

Complete structure:

START TRANSACTION;

SQL_QUERY_1;
SQL_QUERY_2;
SQL_QUERY_3;

COMMIT;

Failure case:

START TRANSACTION;

SQL_QUERY_1;
SQL_QUERY_2;

ROLLBACK;

---

4. START TRANSACTION

Transaction start karne ke liye:

START TRANSACTION;

MySQL mein:

BEGIN;

bhi transaction start karne ke liye use kiya ja sakta hai.

Example:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;

Ab change transaction ke context mein hai.

---

5. COMMIT Kya Hai?

COMMIT transaction ke changes ko permanently save karne ke liye use hota hai.

Example:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;

COMMIT;

Flow:

START TRANSACTION
        ↓
    SQL Changes
        ↓
      COMMIT
        ↓
 Changes Saved

---

6. ROLLBACK Kya Hai?

ROLLBACK transaction ke uncommitted changes ko undo karta hai.

Example:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 5000
WHERE id = 1;

ROLLBACK;

Agar update transaction ke andar successfully execute hua tha lekin commit nahi hua, to rollback ke baad wo change undo ho jayega.

Flow:

START TRANSACTION
        ↓
    SQL Changes
        ↓
     Problem
        ↓
     ROLLBACK
        ↓
 Changes Reverted

---

7. COMMIT vs ROLLBACK

COMMIT	ROLLBACK
Changes save karta hai	Uncommitted changes undo karta hai
Transaction complete karta hai	Transaction ko previous committed state par laata hai
Success ke baad use hota hai	Failure/error ke case mein use hota hai
Changes permanent ho jaate hain	Changes revert ho jaate hain

Easy way:

COMMIT
↓
SAVE

ROLLBACK
↓
UNDO

---

8. Simple Transaction Example

Practice ke liye table:

CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_holder VARCHAR(100) NOT NULL,
    balance DECIMAL(12,2) NOT NULL
);

Data:

INSERT INTO accounts
(account_holder, balance)
VALUES
('Rahul', 50000),
('Amit', 20000);

Check:

SELECT *
FROM accounts;

Output:

id	account_holder	balance
1	Rahul	50000.00
2	Amit	20000.00

---

9. Successful Bank Transfer

Rahul se Amit ko ₹5,000 transfer:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 5000
WHERE id = 1;

UPDATE accounts
SET balance = balance + 5000
WHERE id = 2;

COMMIT;

Final result:

id	account_holder	balance
1	Rahul	45000.00
2	Amit	25000.00

---

10. Bank Transfer Fail Hone Par ROLLBACK

Suppose first query execute ho gayi:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 5000
WHERE id = 1;

Lekin second operation mein problem aa gayi.

Application error detect karke:

ROLLBACK;

kar sakti hai.

Result:

id	account_holder	balance
1	Rahul	50000.00
2	Amit	20000.00

Uncommitted transaction changes revert ho gaye.

---

11. Transaction Ka Main Purpose

Transactions ka primary purpose hai:

Related database operations ko consistent unit ke roop mein manage karna.

Example:

Order Create
     ↓
Order Items Insert
     ↓
Stock Update
     ↓
Payment Record

Agar order create ho gaya but stock update fail ho gaya, application ko carefully decide karna chahiye ki complete operation ko rollback karna hai ya kisi compensating workflow ki zarurat hai.

Aise multi-step database operations mein transaction bahut important hoti hai.

---

12. ACID Properties Kya Hain?

Database transactions ko samajhne ke liye ACID bahut important concept hai.

ACID:

A → Atomicity
C → Consistency
I → Isolation
D → Durability

---

13. Atomicity

Atomicity ka meaning:

Transaction ke operations ek logical unit ke roop mein treat hote hain.

Conceptually:

All required operations succeed
        ↓
      COMMIT

Problem
        ↓
    ROLLBACK

Example:

Money Deduct
+
Money Add

Transfer workflow ko partial state mein chhodna undesirable hai.

---

14. Consistency

Consistency ka meaning hai ki transaction database ko valid state se doosri valid state mein le jaye, according to the database's constraints and application rules.

Example:

Agar bank transfer:

Rahul - ₹5,000
Amit  + ₹5,000

hai, to transfer ke baad balances aur defined constraints valid hone chahiye.

Database constraints jaise:

PRIMARY KEY
FOREIGN KEY
UNIQUE
NOT NULL
CHECK

consistency enforce karne mein help karte hain.

---

15. Isolation

Isolation ka relation concurrent transactions ke interaction se hai.

Suppose ek hi time par:

Transaction A
Transaction B

database ko access kar rahe hain.

Isolation determine karti hai ki ek transaction ke intermediate/uncommitted changes doosri transaction ko kis level par visible honge.

MySQL InnoDB mein transaction isolation levels available hain, including:

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

MySQL InnoDB ka default isolation level commonly:

REPEATABLE READ

hota hai.

---

16. Durability

Durability ka meaning:

Successfully committed transaction ke changes ko database system ko preserve karna chahiye, including after a normal crash/restart, subject to the storage engine and durability configuration.

Simple understanding:

COMMIT
   ↓
Saved Transaction
   ↓
Database Restart
   ↓
Committed Data Should Remain

MySQL ki durability behavior ko storage engine aur server configuration affect kar sakte hain.

---

17. ACID Quick Revision

Property	Simple Meaning
Atomicity	Transaction as a unit
Consistency	Valid database state maintain karna
Isolation	Concurrent transactions ko control karna
Durability	Committed changes ko preserve karna

Easy trick:

A → All or logical unit
C → Correct state
I → Independent/concurrent behavior
D → Data stays after commit

---

18. InnoDB and Transactions

MySQL mein transaction-based applications ke liye InnoDB storage engine commonly use hota hai.

Example:

CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_holder VARCHAR(100),
    balance DECIMAL(12,2)
) ENGINE=InnoDB;

Check engine:

SHOW TABLE STATUS LIKE 'accounts';

Ya:

SHOW CREATE TABLE accounts;

---

19. Why InnoDB Is Important?

InnoDB provides important transactional capabilities such as:

• Transactions
• COMMIT
• ROLLBACK
• Row-level locking
• Foreign key support
• Crash recovery mechanisms

Isliye transactional business applications mein InnoDB commonly preferred hota hai.

---

20. Autocommit Kya Hai?

MySQL sessions mein autocommit generally enabled hota hai.

Check:

SELECT @@autocommit;

Agar result:

1

hai, to autocommit enabled hai.

Is mode mein individual statements normally apne transaction mein automatically commit ho jaate hain if they succeed.

---

21. Autocommit Disable Karna

Session level par:

SET autocommit = 0;

Iske baad statements automatically commit nahi honge; aapko explicitly:

COMMIT;

ya:

ROLLBACK;

use karna padega.

Autocommit ko globally/session-wide change karne se pehle application behavior ko samajhna important hai.

Most application code mein explicit transaction boundaries use karna clearer hota hai.

---

22. Autocommit Enable Karna

SET autocommit = 1;

Isse autocommit enable ho jayega.

Current setting:

SELECT @@autocommit;

se check kar sakte hain.

---

23. Important: COMMIT Ke Baad ROLLBACK Kaam Karega?

Suppose:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;

COMMIT;

ROLLBACK;

Yahan ROLLBACK pehle committed update ko undo nahi karega.

Reason:

UPDATE
 ↓
COMMIT
 ↓
Transaction Completed

Once transaction is committed, us transaction ke changes ko normal ROLLBACK se undo nahi kiya ja sakta.

Agar committed data ko reverse karna hai, to usually new compensating transaction ya business-level reversal operation use kiya jata hai.

---

24. SAVEPOINT Kya Hai?

SAVEPOINT transaction ke andar ek intermediate point create karta hai.

Example:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;

SAVEPOINT after_debit;

UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;

Ab agar second operation ko undo karna hai:

ROLLBACK TO SAVEPOINT after_debit;

Isse transaction savepoint tak rollback hogi.

---

25. SAVEPOINT Ka Flow

START TRANSACTION
       ↓
   Operation 1
       ↓
   SAVEPOINT
       ↓
   Operation 2
       ↓
Problem in Operation 2
       ↓
ROLLBACK TO SAVEPOINT
       ↓
Continue / COMMIT

Important:

ROLLBACK TO SAVEPOINT poori transaction ko cancel nahi karta.

---

26. Complete SAVEPOINT Example

START TRANSACTION;

UPDATE accounts
SET balance = balance - 5000
WHERE id = 1;

SAVEPOINT transfer_started;

UPDATE accounts
SET balance = balance + 5000
WHERE id = 2;

ROLLBACK TO SAVEPOINT transfer_started;

COMMIT;

Is example mein savepoint ke baad wale changes rollback ho sakte hain, lekin savepoint se pehle ke changes transaction mein remain karte hain.

Note: Real bank transfer mein aise partial commit logic ko blindly use nahi karna chahiye; business rules ke according complete transfer ko atomic rakhna usually more appropriate hota hai.

---

27. RELEASE SAVEPOINT

Agar savepoint ki zarurat nahi hai:

RELEASE SAVEPOINT transfer_started;

Ye savepoint ko remove karta hai.

Example:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;

SAVEPOINT step1;

UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;

RELEASE SAVEPOINT step1;

COMMIT;

---

28. ROLLBACK vs ROLLBACK TO SAVEPOINT

Command	Effect
ROLLBACK	Entire current transaction ke uncommitted changes revert
ROLLBACK TO SAVEPOINT	Sirf specified savepoint ke baad ke changes revert
COMMIT	Current transaction changes save
RELEASE SAVEPOINT	Savepoint remove

---

29. E-Commerce Order Transaction

Suppose customer order place karta hai.

Process:

1. Create Order
2. Add Order Items
3. Reduce Stock
4. Create Payment Record

Agar application chahti hai ki ye database changes ek atomic unit mein commit hon, transaction use kar sakti hai.

Example:

START TRANSACTION;

INSERT INTO orders
(customer_id, total_amount)
VALUES
(101, 5000);

INSERT INTO order_items
(order_id, product_id, quantity, price)
VALUES
(1, 10, 2, 2500);

UPDATE products
SET stock = stock - 2
WHERE id = 10
  AND stock >= 2;

INSERT INTO payments
(order_id, amount, status)
VALUES
(1, 5000, 'SUCCESS');

COMMIT;

Important: Production application mein generated order_id, affected rows, stock availability aur payment state ko programmatically verify karna zaroori hai. Sirf SQL sequence likh dene se complete business transaction automatically safe nahi ho jaati.

---

30. Stock Management Transaction

Suppose product stock:

100

Customer ne 3 products order kiye.

START TRANSACTION;

UPDATE products
SET stock = stock - 3
WHERE id = 10
  AND stock >= 3;

COMMIT;

Lekin production application mein UPDATE ke affected rows check karna important hai.

Agar:

affected rows = 0

to iska matlab ho sakta hai ki product exist nahi karta ya stock insufficient hai.

Application ko transaction rollback/error handling ke according proceed karna chahiye.

---

31. Stock Negative Hone Se Kaise Bachayein?

Bad approach:

UPDATE products
SET stock = stock - 3
WHERE id = 10;

Agar stock 2 tha, result negative ho sakta hai.

Better condition:

UPDATE products
SET stock = stock - 3
WHERE id = 10
  AND stock >= 3;

Phir application affected rows check kare.

Agar affected rows 0:

Stock available nahi hai.

---

32. Transaction + Validation

Transaction ke andar sirf SQL queries run karna enough nahi hai.

Application ko:

Validate
   ↓
Start Transaction
   ↓
Execute Query
   ↓
Check Result
   ↓
Execute Next Query
   ↓
Commit

follow karna chahiye.

Failure:

Error
 ↓
Rollback
 ↓
Return Error

---

33. Transaction Error Handling

Generic application flow:

START TRANSACTION

      ↓

Query 1
      ↓
Success?

      ↓ Yes

Query 2
      ↓
Success?

      ↓ Yes

Query 3
      ↓
Success?

      ↓ Yes

COMMIT

Failure:

Any Query Failed
       ↓
    ROLLBACK
       ↓
Return Error

---

34. PHP Mein Transaction

PHP mein MySQLi ya PDO ke through transactions use ki ja sakti hain.

PDO example:

<?php

$pdo->beginTransaction();

try {

    $stmt = $pdo->prepare("
        UPDATE accounts
        SET balance = balance - ?
        WHERE id = ?
    ");

    $stmt->execute([5000, 1]);

    $stmt = $pdo->prepare("
        UPDATE accounts
        SET balance = balance + ?
        WHERE id = ?
    ");

    $stmt->execute([5000, 2]);

    $pdo->commit();

} catch (Throwable $e) {

    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    throw $e;
}

Concept:

beginTransaction()
       ↓
Queries
       ↓
commit()

Error
 ↓
rollBack()

---

35. CodeIgniter 4 Transaction

CodeIgniter 4 mein database transactions ke liye database connection methods use kiye ja sakte hain.

Basic example:

$db = \Config\Database::connect();

$db->transStart();

$db->table('accounts')
   ->where('id', 1)
   ->set('balance', 'balance - 5000', false)
   ->update();

$db->table('accounts')
   ->where('id', 2)
   ->set('balance', 'balance + 5000', false)
   ->update();

$db->transComplete();

Transaction status check:

if ($db->transStatus() === false) {
    // Transaction failed
}

Agar transaction successful hai, CodeIgniter transaction complete kar sakta hai; failure ke case mein rollback behavior framework transaction handling ke according hota hai.

---

36. CodeIgniter 4 – Explicit Transaction

Aap explicit methods bhi use kar sakte hain:

$db->transBegin();

try {

    // Query 1
    // Query 2
    // Query 3

    if ($db->transStatus() === false) {
        throw new \RuntimeException('Transaction failed.');
    }

    $db->transCommit();

} catch (\Throwable $e) {

    $db->transRollback();

    throw $e;
}

Ye approach complex business logic mein explicit control provide karti hai.

---

37. Transaction With Order Creation

CodeIgniter 4 style example:

$db = \Config\Database::connect();

$db->transBegin();

try {

    $db->table('orders')->insert([
        'customer_id' => 101,
        'total_amount' => 5000
    ]);

    $orderId = $db->insertID();

    $db->table('order_items')->insert([
        'order_id' => $orderId,
        'product_id' => 10,
        'quantity' => 2,
        'price' => 2500
    ]);

    $db->table('products')
       ->where('id', 10)
       ->where('stock >=', 2)
       ->set('stock', 'stock - 2', false)
       ->update();

    if ($db->transStatus() === false) {
        throw new \RuntimeException('Order transaction failed.');
    }

    $db->transCommit();

} catch (\Throwable $e) {

    $db->transRollback();

    throw $e;
}

Real application mein stock update ke affected rows bhi check karna chahiye.

---

38. Transaction Ka Use Kahan Hota Hai?

Transactions especially useful hain:

Banking

Debit + Credit

E-Commerce

Order + Items + Stock

Payment

Payment + Order Status + Transaction Log

Wallet

Debit Wallet + Credit Wallet + Ledger

Inventory

Stock Out + Inventory Log

Payroll

Salary Update + Payroll Record

---

39. Transaction Ka Use Kahan Nahi Karna Chahiye?

Har single query ko manually long transaction mein wrap karna zaroori nahi.

Example:

START TRANSACTION;

SELECT *
FROM products;

COMMIT;

Agar koi business reason nahi hai, to transaction unnecessary complexity add kar sakti hai.

Transaction ka scope:

Jitna short ho aur logical unit ko cover kare, utna better.

---

40. Long-Running Transactions Se Problem

Long-running transactions:

START TRANSACTION
       ↓
Long Processing
       ↓
Many Operations
       ↓
COMMIT

issues create kar sakti hain, depending on workload:

• Locks longer hold ho sakte hain
• Other transactions wait kar sakti hain
• Undo/history storage grow kar sakta hai
• Concurrency reduce ho sakti hai

Isliye transactions ko unnecessarily open na rakhein.

---

41. Transaction Ke Andar External API Call?

Suppose:

START TRANSACTION

Insert Order

Call Payment Gateway

Wait 5 seconds

Update Payment

COMMIT

Ye design problematic ho sakta hai because database transaction unnecessarily long time tak open rahegi.

Better architecture often:

Create Order
     ↓
Payment Processing
     ↓
Payment Result
     ↓
Short Database Transaction
     ↓
Update Final State

Exact architecture business requirements par depend karega.

---

42. Important: Transaction Sirf Database Changes Ko Control Karti Hai

Transaction ko external systems par automatically rollback nahi kiya ja sakta.

Example:

Database Update
+
Email Sent

Agar email successfully send ho gaya aur baad mein database transaction rollback ho gayi, to email automatically unsend nahi hogi.

Similarly:

Database
+
Payment Gateway

ek single MySQL ROLLBACK se dono systems ko reverse nahi kiya ja sakta.

Distributed workflows ke liye application-level compensation, idempotency, queues, outbox patterns etc. use kiye ja sakte hain.

---

43. DDL and Transactions – Important Note

CREATE, ALTER, DROP, TRUNCATE jaise DDL statements ko normal INSERT/UPDATE/DELETE transaction statements ki tarah treat nahi karna chahiye.

MySQL mein many DDL statements implicit commit cause kar sakti hain.

For example:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;

ALTER TABLE accounts
ADD COLUMN note VARCHAR(255);

ROLLBACK;

Aise mixed DML + DDL workflows mein expected rollback behavior assume nahi karna chahiye.

Best practice: Schema changes ko application data transactions se separate rakhein.

---

44. COMMIT Ke Baad Backup Ka Importance

Transaction COMMIT data ko save karta hai, lekin:

COMMIT backup ka replacement nahi hai.

Database safety ke liye:

Transactions
+
Constraints
+
Access Control
+
Backups
+
Monitoring
+
Recovery Testing

sab important hain.

---

45. Database Backup

Important production database ke liye regular backups maintain karein.

Typical strategy:

Regular Backup
      ↓
Store Safely
      ↓
Test Restore
      ↓
Disaster Recovery Ready

Backup bana lena enough nahi.

Restore test bhi important hai.

---

46. Data Safety Best Practices

1. Transactions Use Karein

Related operations ko transaction mein group karein.

2. InnoDB Use Karein

Transactional tables ke liye InnoDB commonly appropriate hai.

3. Error Handling Rakhein

Failure par rollback ensure karein.

4. Transactions Short Rakhein

Unnecessary operations transaction ke andar na rakhein.

5. External API Calls Avoid Karein

Long transaction ke andar external network calls avoid karna generally better hai.

6. Constraints Use Karein

PRIMARY KEY
FOREIGN KEY
UNIQUE
NOT NULL
CHECK

7. Input Validation Karein

Application level validation + database constraints dono useful hain.

8. Backup Rakhein

Critical data ke regular backups rakhein.

9. Restore Test Karein

Backup actually restore ho sakta hai ya nahi, verify karein.

10. Production Data Par Carefully Test Karein

Destructive queries ko directly production par run na karein.

---

47. Transaction vs Backup

Beginners kabhi-kabhi transaction aur backup ko same samajh lete hain.

Ye different concepts hain.

Transaction	Backup
Current database operations ko control karta hai	Data ki copy/recovery mechanism
COMMIT/ROLLBACK	Backup/restore
Short-term operation safety	Disaster/data recovery
Query execution ke around	Database/system level

Simple:

Transaction
→ Current operation safety

Backup
→ Data recovery safety

---

48. Transaction vs SAVEPOINT

Transaction	SAVEPOINT
Logical unit of work	Transaction ke andar intermediate point
START TRANSACTION	SAVEPOINT name
COMMIT / ROLLBACK	ROLLBACK TO SAVEPOINT
Entire transaction manage karta hai	Partial rollback allow karta hai

---

49. COMMIT vs ROLLBACK vs SAVEPOINT

Command	Purpose
START TRANSACTION	Transaction start
COMMIT	Changes save
ROLLBACK	Uncommitted changes revert
SAVEPOINT	Intermediate rollback point
ROLLBACK TO SAVEPOINT	Savepoint tak rollback
RELEASE SAVEPOINT	Savepoint remove

---

50. Transaction Isolation Levels

MySQL InnoDB mein commonly available isolation levels:

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

Ye define karte hain ki concurrent transactions ek doosre ke changes ko kis tarah observe karti hain.

---

51. READ UNCOMMITTED

Lowest isolation level.

Conceptually, transaction uncommitted changes ko read kar sakti hai.

Isse:

Dirty Reads

possible ho sakte hain.

General business applications mein is level ko carefully evaluate karna chahiye.

---

52. READ COMMITTED

Is isolation level mein transaction generally sirf committed data read karti hai.

Ye dirty reads avoid karta hai.

However, same transaction mein same query ko repeat karne par committed data change ho sakta hai depending on concurrent activity.

---

53. REPEATABLE READ

MySQL InnoDB ka default isolation level commonly:

REPEATABLE READ

hai.

Ye consistent reads ke liye snapshot-based behavior provide karta hai, with InnoDB's locking behavior for certain operations.

---

54. SERIALIZABLE

Highest standard isolation level.

Ye concurrent transactions ko zyada restrict karta hai.

Benefit:

Higher isolation

but possible cost:

More locking/waiting
Less concurrency

Isliye isolation level business requirement ke according choose karna chahiye.

---

55. Isolation Level Check Karna

Current transaction isolation level dekhne ke liye:

SELECT @@transaction_isolation;

Ya MySQL version/environment ke according:

SELECT @@session.transaction_isolation;

use kiya ja sakta hai.

---

56. Isolation Level Set Karna

Session level par example:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Next transaction ke liye specific level set karna:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Production mein isolation level change karne se pehle application ke concurrency behavior ko properly test karein.

---

57. Locks and Transactions

Concurrent transactions mein database rows par locks important role play karte hain.

Example:

START TRANSACTION;

SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;

FOR UPDATE locking read ke through selected rows ko update-oriented transaction workflow mein lock karne ke liye use kiya ja sakta hai.

Typical bank/wallet workflow:

START TRANSACTION
       ↓
SELECT account FOR UPDATE
       ↓
Check Balance
       ↓
UPDATE Balance
       ↓
COMMIT

Ye concurrency-sensitive operations mein useful pattern hai.

---

58. Bank Transfer With FOR UPDATE

Conceptual example:

START TRANSACTION;

SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;

Application balance check kare:

Balance >= 5000 ?

Then:

UPDATE accounts
SET balance = balance - 5000
WHERE id = 1;

UPDATE accounts
SET balance = balance + 5000
WHERE id = 2;

COMMIT;

Real implementation mein error handling, deadlock handling, account ordering aur affected-row validation bhi important hai.

---

59. Deadlock Kya Hai?

Kabhi-kabhi do transactions ek doosre ke locks ka wait kar sakti hain.

Example:

Transaction A
locks Row 1
waits for Row 2

Transaction B
locks Row 2
waits for Row 1

Result:

Deadlock

MySQL/InnoDB deadlock detect karke typically ek transaction ko rollback kar sakta hai.

Application ko transaction failure ko properly handle karna chahiye, aur suitable workloads mein retry strategy bhi implement ki ja sakti hai.

---

60. Deadlock Se Bachne Ke Tips

Consistent Lock Order

Agar multiple rows lock karni hain, possible ho to same order follow karein.

Example:

Always Account ID ASC

rather than:

Transaction A → 1 then 2
Transaction B → 2 then 1

Short Transactions

Transaction ko unnecessarily long na rakhein.

Proper Indexes

Poor indexing unnecessary locking/work ko increase kar sakti hai.

Retry Strategy

Transient deadlocks ko application level par safely retry karna useful ho sakta hai.

---

61. Transaction Error Handling – Simple Pattern

Generic pseudocode:

BEGIN

try:
    Query 1
    Query 2
    Query 3

    COMMIT

catch error:
    ROLLBACK
    return error

Ye pattern almost har backend language mein conceptually same hota hai.

---

62. Real-World Payment Example

Suppose customer payment successfully complete karta hai.

Application ko database mein:

Payment Record
+
Order Status
+
Payment Transaction ID

save karna hai.

Possible flow:

Payment Gateway Success
          ↓
START TRANSACTION
          ↓
Insert Payment
          ↓
Update Order
          ↓
Insert Payment Log
          ↓
COMMIT

Agar database operation fail ho:

ROLLBACK

Payment gateway ko automatically rollback nahi karega; payment provider ke API/business workflow ke according separate reconciliation or reversal process ho sakta hai.

---

63. Transaction Logging

Financial or important systems mein transaction history maintain karna useful hota hai.

Example:

CREATE TABLE account_transactions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    account_id INT NOT NULL,
    transaction_type VARCHAR(20) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Transfer ke time:

Account Update
+
Transaction Log

same database transaction mein maintain kiye ja sakte hain.

---

64. Ledger-Based Design

Financial applications mein balance ke saath transaction history bhi important hoti hai.

Example:

Account Balance
+
Debit Entry
+
Credit Entry

Transaction:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 5000
WHERE id = 1;

INSERT INTO account_transactions
(account_id, transaction_type, amount)
VALUES
(1, 'DEBIT', 5000);

UPDATE accounts
SET balance = balance + 5000
WHERE id = 2;

INSERT INTO account_transactions
(account_id, transaction_type, amount)
VALUES
(2, 'CREDIT', 5000);

COMMIT;

Isse balance aur ledger entries ko same database transaction mein maintain kiya ja sakta hai.

---

65. Transaction Design – Golden Rule

Ek important rule:

Transaction mein wahi operations rakhein jo logically ek hi business operation ka part hain.

Example:

Good

Create Order
+
Create Order Items
+
Update Stock

Questionable

Create Order
+
Send Email
+
Call External API
+
Generate PDF
+
Wait 10 seconds
+
Update Stock

Long-running non-database operations ko transaction ke andar rakhna generally avoid karna better hota hai.

---

66. Common Beginner Mistake – COMMIT Bhool Jana

Example:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;

Aur COMMIT nahi kiya.

Application connection close hone par uncommitted changes rollback ho sakte hain, depending on the connection/session lifecycle.

Best practice:

START
 ↓
Operations
 ↓
COMMIT / ROLLBACK

transaction boundary clearly manage karein.

---

67. Common Beginner Mistake – ROLLBACK After COMMIT

Wrong expectation:

COMMIT;
ROLLBACK;

ROLLBACK previous committed transaction ko undo nahi karta.

Once committed:

COMMIT
↓
Transaction complete

---

68. Common Beginner Mistake – Transaction Too Large

Avoid:

START TRANSACTION

1000 unrelated operations

External API
File processing
Long calculations
Email sending

COMMIT

Better:

Short
Focused
Database-related
Transaction

---

69. Common Beginner Mistake – Error Check Na Karna

Sirf:

START TRANSACTION;

UPDATE ...;
UPDATE ...;

COMMIT;

likh dena enough nahi hai.

Application ko important queries ke result check karne chahiye.

Especially:

Affected rows
Constraints
Business rules
Exceptions

---

70. Common Beginner Mistake – Transaction Ko Backup Samajhna

Transaction:

ROLLBACK

sirf current uncommitted transaction ke context mein useful hai.

Ye:

• Accidental committed deletion ka backup nahi hai.
• Database corruption ka replacement nahi hai.
• Disaster recovery ka replacement nahi hai.

Isliye:

Transactions + Backups

dono important hain.

---

71. Common Beginner Mistake – Production Database Par Direct Testing

Testing ke liye:

Development Database
↓
Staging Database
↓
Production

workflow use karna safer hota hai.

Especially transactions, migrations aur destructive queries ko production par test na karein.

---

72. Data Safety Checklist

Production application ke liye:

✓ InnoDB
✓ Transactions
✓ COMMIT / ROLLBACK
✓ Proper constraints
✓ Input validation
✓ Error handling
✓ Short transactions
✓ Proper indexes
✓ Deadlock handling
✓ Regular backups
✓ Restore testing
✓ Access control
✓ Monitoring
✓ Audit/logging where required

---

73. Complete Bank Transfer Example

Assume:

Rahul = ₹50,000
Amit  = ₹20,000

Transfer:

₹5,000

SQL:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 5000
WHERE id = 1
  AND balance >= 5000;

UPDATE accounts
SET balance = balance + 5000
WHERE id = 2;

COMMIT;

Production application mein pehle update ka affected row verify karna aur second update bhi successful hai ya nahi check karna important hai. Agar koi step fail ho, transaction rollback honi chahiye.

---

74. Complete Transaction Flow

                START TRANSACTION
                       │
                       ↓
                Validate Data
                       │
                       ↓
                 Execute Query 1
                       │
                  Success?
                  /       \
                Yes        No
                 │          │
                 ↓          ↓
          Execute Query 2  ROLLBACK
                 │
             Success?
            /        \
          Yes         No
           │           │
           ↓           ↓
    Execute Query 3  ROLLBACK
           │
        Success?
       /        \
     Yes         No
      │           │
      ↓           ↓
   COMMIT       ROLLBACK

---

75. Quick Reference

Start Transaction

START TRANSACTION;

Begin

BEGIN;

Save Changes

COMMIT;

Undo Uncommitted Changes

ROLLBACK;

Create Savepoint

SAVEPOINT step1;

Rollback to Savepoint

ROLLBACK TO SAVEPOINT step1;

Remove Savepoint

RELEASE SAVEPOINT step1;

Check Autocommit

SELECT @@autocommit;

Check Isolation

SELECT @@transaction_isolation;

---

76. Important Difference Table

Concept	Purpose
Transaction	Related operations ko logical unit mein manage karna
START TRANSACTION	Transaction start
COMMIT	Changes save
ROLLBACK	Uncommitted changes revert
SAVEPOINT	Intermediate rollback point
ROLLBACK TO SAVEPOINT	Partial rollback
RELEASE SAVEPOINT	Savepoint remove
Autocommit	Individual statements ko automatically commit karne ka behavior
ACID	Transaction reliability properties
InnoDB	MySQL ka transactional storage engine
Backup	Disaster/data recovery
FOR UPDATE	Locking read for suitable transactional workflows
Isolation Level	Concurrent transaction visibility/behavior control

---

77. Practice Exercises

Exercise 1 – Basic Transaction

Ek accounts table banayein aur do users ke balances insert karein.

Phir:

User 1 → ₹1,000 deduct
User 2 → ₹1,000 add

Transaction ke andar perform karein.

---

Exercise 2 – ROLLBACK

Ek transaction start karein:

START TRANSACTION;

Balance update karein aur:

ROLLBACK;

execute karein.

Check karein ki data previous state mein aa gaya ya nahi.

---

Exercise 3 – COMMIT

Same operation ko:

COMMIT;

ke saath execute karein.

Phir SELECT se data check karein.

---

Exercise 4 – SAVEPOINT

Transaction mein:

1. First update karein.
2. Savepoint create karein.
3. Second update karein.
4. ROLLBACK TO SAVEPOINT karein.
5. Final state check karein.

---

Exercise 5 – E-Commerce Order

Create:

orders
order_items
products

tables.

Transaction mein:

Create Order
+
Create Order Item
+
Reduce Stock

implement karein.

---

Exercise 6 – Stock Validation

Aisa update likhein jisme stock negative na ho:

UPDATE products
SET stock = stock - 5
WHERE id = 1
AND stock >= 5;

Affected rows check karne ka application logic design karein.

---

Exercise 7 – Payment Transaction

Design karein:

Payment Insert
+
Order Status Update
+
Payment Log Insert

sabko transaction mein execute karein.

---

Exercise 8 – CodeIgniter 4

CodeIgniter 4 mein:

transBegin()
transCommit()
transRollback()

use karke simple bank transfer implementation banayein.

---

Exercise 9 – Deadlock Understanding

Do transactions imagine karein:

Transaction A:
Lock Row 1 → Lock Row 2

Transaction B:
Lock Row 2 → Lock Row 1

Explain karein ki deadlock kaise ho sakta hai aur consistent lock order ise kaise reduce kar sakta hai.

---

Exercise 10 – Complete Real-World Project

Ek mini e-commerce checkout transaction design karein:

Customer
    ↓
Order
    ↓
Order Items
    ↓
Stock
    ↓
Payment Record
    ↓
COMMIT

Aur kisi bhi critical step ke fail hone par:

ROLLBACK

implement karein.

---

78. Easy Way to Remember

Transactions ko simply is formula se yaad rakhein:

START
  ↓
WORK
  ↓
SUCCESS?
 /     \
YES     NO
 |       |
 ↓       ↓
COMMIT ROLLBACK

Aur:

SAVEPOINT
    ↓
Partial Rollback

ACID:

A → Atomicity
C → Consistency
I → Isolation
D → Durability

---

Conclusion

MySQL Transactions database applications mein data consistency aur reliable multi-step operations ke liye bahut important concept hain.

Is tutorial mein humne seekha:

• Transaction kya hoti hai.
• START TRANSACTION aur BEGIN ka use.
• COMMIT changes ko save karta hai.
• ROLLBACK uncommitted changes ko revert karta hai.
• SAVEPOINT transaction ke andar intermediate rollback point create karta hai.
• ROLLBACK TO SAVEPOINT partial rollback ke liye use hota hai.
• ACID transaction behavior ko samajhne ka fundamental model hai.
• InnoDB transactional applications ke liye commonly used storage engine hai.
• Autocommit individual statements ke behavior ko affect karta hai.
• Isolation levels concurrent transactions ke behavior ko control karte hain.
• FOR UPDATE concurrency-sensitive workflows mein useful ho sakta hai.
• Deadlocks ko consistent locking order, short transactions aur retry handling se manage kiya ja sakta hai.
• PHP aur CodeIgniter 4 applications mein transactions important business operations ke liye use ki ja sakti hain.
• Transactions backup ka replacement nahi hain.
• Production systems ke liye backups, restore testing, constraints, validation aur proper error handling bhi important hain.

Agar aap PHP ya CodeIgniter 4 ke saath MySQL-based application bana rahe hain, to transactions ko properly samajhna especially orders, payments, wallets, inventory aur financial operations ke liye essential hai.