ACID، autocommit و مدیریت تراکنش

همه یا هیچ

ثبت یک فروش یعنی چند کار: درج سفارش، درج اقلام، کم کردن موجودی انبار و ثبت پرداخت. اگر برق وسط کار برود یا یکی از مراحل خطا بدهد، نباید سفارشی بدون اقلام یا موجودی کم‌شده بدون فروش باقی بماند. تراکنش این چند دستور را به یک واحد تجزیه‌ناپذیر تبدیل می‌کند.

نحو تراکنش

START TRANSACTION;

INSERT INTO orders (customer_id, status, total_amount) VALUES (42, 'paid', 480000000);
SET @order_id = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_id, qty, unit_price)
VALUES (@order_id, 7, 1, 480000000);

UPDATE products SET stock = stock - 1 WHERE id = 7 AND stock >= 1;
-- اگر 0 rows affected بود، موجودی کافی نبوده: ROLLBACK
SAVEPOINT before_payment;

INSERT INTO payments (order_id, amount, gateway_ref) VALUES (@order_id, 480000000, 'ZP-99812');
-- اگر فقط ثبت پرداخت مشکل داشت:
-- ROLLBACK TO SAVEPOINT before_payment;

COMMIT;

autocommit

در حالت پیش‌فرض (autocommit = 1) هر دستور به‌تنهایی یک تراکنش است و بلافاصله COMMIT می‌شود. START TRANSACTION این حالت را تا COMMIT یا ROLLBACK بعدی معلق می‌کند. اگر SET autocommit = 0 بزنید، تراکنش همیشه باز است و تا COMMIT صریح هیچ‌چیز ذخیره نمی‌شود — منشأ رایج «داده را درج کردم ولی در برنامه‌ی دیگر دیده نمی‌شود».

تراکنش در کد برنامه

import MySQLdb

conn = MySQLdb.connect(read_default_file="~/.my.cnf", db="carpet_shop", charset="utf8mb4")
try:
    with conn.cursor() as cur:
        cur.execute("UPDATE wallets SET balance = balance - %s WHERE user_id = %s AND balance >= %s",
                    (amount, buyer_id, amount))
        if cur.rowcount != 1:
            raise ValueError("موجودی کافی نیست")
        cur.execute("UPDATE wallets SET balance = balance + %s WHERE user_id = %s", (amount, seller_id))
    conn.commit()
except Exception:
    conn.rollback()
    raise

درایورهای پایتونی (DB-API) به‌طور پیش‌فرض autocommit را خاموش دارند؛ بدون commit() همه‌چیز با بسته شدن اتصال ROLLBACK می‌شود. جنگو برعکس، autocommit را روشن می‌کند و تراکنش را با transaction.atomic() می‌سازد.

نکته‌هایی که کمتر کسی می‌داند

• دستورهای DDL (CREATE، ALTER، DROP، TRUNCATE) قبل از اجرا تراکنش باز را به‌طور ضمنی COMMIT می‌کنند و خودشان قابل ROLLBACK نیستند؛ مایگریشنی که وسط کار شکست بخورد، نیمه‌کاره می‌ماند.
• تراکنشی که بی‌کار باز مانده (مثلاً برنامه‌نویسی که در DBeaver دستوری زده و COMMIT نکرده) قفل‌ها را نگه می‌دارد و undo log را رشد می‌دهد؛ SELECT * FROM information_schema.innodb_trx ORDER BY trx_started; قدیمی‌ترین‌ها را نشان می‌دهد.
• START TRANSACTION READ ONLY برای گزارش‌های طولانی به InnoDB اجازه‌ی بهینه‌سازی‌های داخلی می‌دهد و از نوشتن تصادفی هم جلوگیری می‌کند.
• جدول‌های MyISAM در تراکنش شرکت نمی‌کنند؛ ROLLBACK روی آن‌ها بی‌اثر است و فقط یک warning می‌دهد.
• درج گروهی هزاران ردیف داخل یک تراکنش (به‌جای autocommit برای هر ردیف) معمولاً ده‌ها برابر سریع‌تر است، چون هر COMMIT یک flush روی دیسک است.