COMMIT और ROLLBACK के साथ MySQL Transaction Example

Problem: Partial Update के बिना Money Transfer

हम Asha के account 101 से Ravi के account 102 में 1000.00 transfer करेंगे। Correct result के लिए four guarantees चाहिए: source exists और पर्याप्त balance हो; दोनों balances साथ बदलें; combined money 9200.00 रहे; और one ledger row committed transfer record करे।

यह worked example MySQL transactions foundation को आगे बढ़ाता है। इसमें DECIMAL values और InnoDB हैं, FLOAT या nontransactional engine नहीं।

Reproducible InnoDB Setup

यह setup business transaction के बाहर once run करें। DDL active transaction को implicitly commit कर सकती है।

DROP TABLE IF EXISTS transfer_ledger;
DROP TABLE IF EXISTS bank_accounts;

CREATE TABLE bank_accounts (
  account_id INT PRIMARY KEY,
  holder_name VARCHAR(60) NOT NULL,
  balance DECIMAL(12,2) NOT NULL,
  CONSTRAINT chk_balance CHECK (balance >= 0)
) ENGINE = InnoDB;

CREATE TABLE transfer_ledger (
  transfer_id BIGINT PRIMARY KEY,
  from_account INT NOT NULL,
  to_account INT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT chk_amount CHECK (amount > 0),
  FOREIGN KEY (from_account) REFERENCES bank_accounts(account_id),
  FOREIGN KEY (to_account) REFERENCES bank_accounts(account_id)
) ENGINE = InnoDB;

INSERT INTO bank_accounts VALUES
(101, 'Asha',  5000.00),
(102, 'Ravi',  3000.00),
(103, 'Meera', 1200.00);

SELECT account_id, holder_name, balance
FROM bank_accounts ORDER BY account_id;

SELECT SUM(balance) AS total_money
FROM bank_accounts;

Correct Transaction Algorithm

1. Unique transfer ID, source, destination और positive amount receive करें।
2. Transaction start करके दोनों account rows deterministic order में lock करें।
3. Exactly two distinct accounts और sufficient source funds confirm करें।
4. Balance condition के साथ debit और exactly one affected row require करें।
5. Destination credit और exactly one affected row require करें।
6. Unique ledger record insert करें।
7. Appropriate invariants verify करके COMMIT करें।
8. Any error, insufficient funds, timeout या failed check पर ROLLBACK और failure report करें।

Successful Transfer: 101 से 102

START TRANSACTION;

SELECT account_id, balance
FROM bank_accounts
WHERE account_id IN (101, 102)
ORDER BY account_id
FOR UPDATE;
-- 101 = 5000.00 और 102 = 3000.00 return।

UPDATE bank_accounts
SET balance = balance - 1000.00
WHERE account_id = 101
  AND balance >= 1000.00;

SELECT ROW_COUNT() AS debit_rows;
-- 1 return होना चाहिए। वरना ROLLBACK।

UPDATE bank_accounts
SET balance = balance + 1000.00
WHERE account_id = 102;

SELECT ROW_COUNT() AS credit_rows;
-- 1 return होना चाहिए। वरना ROLLBACK।

INSERT INTO transfer_ledger
  (transfer_id, from_account, to_account, amount, status)
VALUES
  (9001, 101, 102, 1000.00, 'COMMITTED');

COMMIT;

FOR UPDATE transaction को current locking reads देती है। Conflicting locks चाहने वाली sessions इस transaction के end तक wait या अपने operation/options के अनुसार fail करती हैं।

Insufficient Funds: ROLLBACK आवश्यक

Meera के पास 1200.00 हैं, इसलिए 2000.00 request को हर table unchanged छोड़ना चाहिए।

START TRANSACTION;

SELECT account_id, balance
FROM bank_accounts
WHERE account_id IN (102, 103)
ORDER BY account_id
FOR UPDATE;

UPDATE bank_accounts
SET balance = balance - 2000.00
WHERE account_id = 103
  AND balance >= 2000.00;

SELECT ROW_COUNT() AS debit_rows;
-- 0 return: application insufficient funds detect करती है।

ROLLBACK;

Zero-row debit के बाद credit या ledger insert execute न करें। Conditional UPDATE database-level guard है, जबकि branch application देती है: success continue; failure rollback।

Application-Side Transaction Control

SQL console statements demonstrate करती है, पर real application commit decision लेती है। PDO, JDBC या client library के transaction methods use करें और पूरे unit के लिए one database connection रखें।

transaction begin
try
    दोनों account rows sorted order में lock करें
    two different accounts और positive amount validate करें
    balance condition से source debit करें
    affected rows 1 नहीं: insufficient_funds throw करें
    destination credit करें
    affected rows 1 नहीं: destination_error throw करें
    unique ledger reference insert करें
    commit करें
catch any error
    transaction active हो तो rollback करें
    error classify करके safe response दें

• Exception catch करके COMMIT तक continue न करें।
• Commit succeed होने से पहले success return न करें।
• Parameterized prepared statements लें; user input के account IDs या amounts concatenate न करें।
• Locked transaction के अंदर external API calls या long user interaction न रखें।
• Deadlock को small bounded policy और fresh transaction से retry करें, uncertain state reuse करके नहीं।

Verification और Audit Queries

SELECT account_id, holder_name, balance
FROM bank_accounts
ORDER BY account_id;

SELECT SUM(balance) AS total_money,
       MIN(balance) AS minimum_balance
FROM bank_accounts;

SELECT transfer_id, from_account, to_account,
       amount, status
FROM transfer_ledger
ORDER BY transfer_id;

Total इस closed demonstration में money conservation confirm करता है। CHECK negative stored balance reject करता है, foreign keys missing accounts reject करती हैं और primary key duplicate transfer ID reject करती है। ये controls transaction को complement करती हैं; application logic replace नहीं करतीं।

Production Checklist और Common Mistakes

Database credentials least-privileged रखें, logs में sensitive data protect करें, ledger totals reconcile करें, failed transactions monitor करें और commit से ठीक पहले connection loss test करें। Classroom example SQL logic prove करता है; financial production systems को authentication, authorization, idempotency, fraud controls और regulatory review भी चाहिए।

Official संदर्भ

• MySQL 8.4: InnoDB Locking Reads
• MySQL 8.4: START TRANSACTION, COMMIT and ROLLBACK
• MySQL 8.4: InnoDB Deadlocks

Locking, transaction boundaries और deadlock guidance official MySQL 8.4 manual से check की गई है।