MySQL में Transactions

MySQL में Transaction क्या है?

Transaction related database operations को one logical unit में group करती है। School fee payment, order checkout या bank transfer several rows और tables बदल सकते हैं; partial completion database को गलत छोड़ देगा। Transaction application को one final decision देती है: सभी valid changes COMMIT करें या सभी uncommitted changes ROLLBACK करें।

Verified Bank Transaction Lab

Schema transaction से पहले बनाएँ क्योंकि DDL implicit commit करा सकती है। Both tables explicitly InnoDB use करती हैं।

CREATE TABLE bank_accounts (
  account_id INT PRIMARY KEY,
  holder_name VARCHAR(60) NOT NULL,
  balance DECIMAL(12,2) NOT NULL,
  CONSTRAINT chk_nonnegative_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_positive_amount CHECK (amount > 0),
  CONSTRAINT fk_transfer_from FOREIGN KEY (from_account)
    REFERENCES bank_accounts(account_id),
  CONSTRAINT fk_transfer_to 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);

Total एक useful invariant है: transfer individual balances बदलेगा, पर combined 9200.00 create या destroy नहीं होना चाहिए।

Transaction Commands और Boundaries

START TRANSACTION;
-- SELECT, INSERT, UPDATE और DELETE statements
COMMIT;

START TRANSACTION;
-- statements जिन्हें रखना नहीं है
ROLLBACK;

BEGIN ad-hoc transaction start करने का alias है, लेकिन START TRANSACTION clearer है और transaction characteristics support करता है। Stored programs में BEGIN सामान्यतः compound block खोलता है, इसलिए transaction control permitted और appropriate हो तो START TRANSACTION लें।

COMMIT के साथ Safe Transfer

दोनों account rows को consistent order में lock करें, source balance validate करें, फिर debit, credit और audit insert को one unit की तरह चलाएँ।

START TRANSACTION;

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

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

-- Application ROW_COUNT() = 1 confirm करे।
UPDATE bank_accounts
SET balance = balance + 1000.00
WHERE account_id = 102;

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

COMMIT;

Conditional debit zero rows affect करे तो application blindly continue न करे। Error throw करके ROLLBACK करे। Database हर business rule SQL text से स्वयं infer नहीं कर सकता।

Controlled Test से ROLLBACK सिद्ध करें

START TRANSACTION;

UPDATE bank_accounts
SET balance = balance - 300.00
WHERE account_id = 103;

SELECT balance FROM bank_accounts
WHERE account_id = 103;
-- Current session को 900.00 दिखता है।

ROLLBACK;

SELECT balance FROM bank_accounts
WHERE account_id = 103;
-- Final balance फिर 1200.00 है।

Full ROLLBACK transaction के InnoDB locks भी release करता है। Data already committed हो जाने के बाद rollback को ordinary correction mechanism न समझें; auditable history चाहिए तो compensating business transaction record करें।

Autocommit, Session Scope और Storage Engines

MySQL sessions सामान्यतः autocommit = 1 से शुरू होती हैं। Explicit transaction के बाहर each successful statement अपनी transaction होती है और बाद वाली statement के साथ बाद में group नहीं हो सकती।

SELECT @@autocommit;       -- सामान्यतः 1
SET SESSION autocommit = 0;
-- COMMIT या ROLLBACK तक transaction open रहती है।
COMMIT;
SET SESSION autocommit = 1;

• Normal session behavior unchanged रखने के लिए clearly bounded unit पर explicit START TRANSACTION prefer करें।
• Autocommit per session है; one connection का change दूसरी को नहीं बदलता।
• InnoDB tables लें। Nontransactional table के changes ROLLBACK undo नहीं कर सकता।
• One critical transaction में storage engines mix न करें।
• Autocommit disabled connection final COMMIT के बिना close हो तो MySQL open transaction roll back करता है।

Implicit Commits और Non-Rollback Traps

कई DDL और administrative statements active transaction को implicitly end करती हैं। Common examples: CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE और CREATE INDEX। MySQL atomic DDL, DDL operation को protect करती है; इसे surrounding business transaction का part नहीं बनाती।

START TRANSACTION;
UPDATE bank_accounts SET balance = balance + 50
WHERE account_id = 101;

ALTER TABLE bank_accounts ADD COLUMN branch_code CHAR(4);
-- Later ROLLBACK earlier UPDATE undo करेगी, यह expect न करें।

Concurrency, Error Handling और Best Practice

Normal SELECT later update के लिए rows reserve नहीं करती। SELECT ... FOR UPDATE matching InnoDB records पर COMMIT या ROLLBACK तक locking read लेती है। इससे balance check और change के बीच का gap बंद होता है।

1. Transactions short रखें; locks held हों तो user input की प्रतीक्षा न करें।
2. Deadlocks घटाने के लिए shared rows same order में access करें।
3. FOR UPDATE और UPDATE predicates पर indexes रखें।
4. Debit, credit और ledger statement के बाद affected-row count check करें।
5. Every exception catch करें, ROLLBACK करें और selected deadlock जैसे transient failures ही retry करें।
6. Money के लिए DECIMAL लें, FLOAT नहीं।
7. सभी business invariants verify होने के बाद ही commit करें।

अब complete transaction example, ACID properties और COMMIT, ROLLBACK तथा SAVEPOINT पढ़ें।

Official संदर्भ

• MySQL 8.4: START TRANSACTION, COMMIT and ROLLBACK
• MySQL 8.4: InnoDB Autocommit, Commit and Rollback
• MySQL 8.4: Statements That Cause an Implicit Commit

Syntax और behavior official MySQL 8.4 manual से check किए गए हैं। Production use से पहले अपने server पर workflow, permissions और failure paths test करें।