Transactions و Locking

۸.۱ ACID – چهار اصل تراکنش

Transaction یا تراکنش یعنی مجموعه‌ای از عملیات که یا همگی موفق می‌شوند یا هیچ‌کدام. مثال کلاسیک: انتقال پول از حساب A به B باید هر دو UPDATE با هم اتفاق بیفتد.

• A – Atomicity (اتمیک‌بودن): همه‌چیز یا هیچ‌چیز
• C – Consistency (سازگاری): از حالت معتبر به حالت معتبر
• I – Isolation (انزوا): تراکنش‌ها روی هم تأثیر نگذارند
• D – Durability (دوام): پس از COMMIT، داده باید پایدار بماند

InnoDB ACID کامل را پشتیبانی می‌کند. MyISAM ❌ ندارد.

۸.۲ دستورات اصلی

SQL📋 کپی
-- شروع
START TRANSACTION;
-- یا
BEGIN;

-- عملیات
UPDATE accounts SET balance = balance - 1000000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000000 WHERE id = 2;

-- تأیید
COMMIT;

-- یا برگشت
ROLLBACK;

-- Savepoint (نقطه برگشت میانی)
START TRANSACTION;
INSERT INTO orders (...) VALUES (...);
SAVEPOINT order_created;

INSERT INTO order_items (...) VALUES (...);
-- اگر مشکل بود، فقط تا savepoint برگرد
ROLLBACK TO SAVEPOINT order_created;

COMMIT;

Autocommit

SQL📋 کپی
-- پیش‌فرض: ON یعنی هر statement یک تراکنش است
SHOW VARIABLES LIKE 'autocommit';

-- خاموش کردن (هر تغییر باید COMMIT شود)
SET autocommit = 0;

-- بهترین رویکرد: autocommit=1 و BEGIN/COMMIT صریح

۸.۳ Isolation Levels

چهار سطح ایزولاسیون استاندارد، با تأثیر روی consistency و concurrency:

سه نوع مشکل

• Dirty Read: خواندن داده‌ای که هنوز COMMIT نشده
• Non-Repeatable Read: در یک تراکنش، یک ردیف را دو بار می‌خوانیم؛ بار دوم متفاوت است
• Phantom Read: دو بار SELECT با شرط یکسان، تعداد ردیف متفاوت می‌دهد (ردیف اضافه شده)

SQL📋 کپی
-- بررسی سطح فعلی
SELECT @@transaction_isolation;

-- تغییر برای session جاری
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- تغییر global
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- در my.cnf
transaction-isolation = REPEATABLE-READ

۸.۴ MVCC – Multi-Version Concurrency Control

InnoDB بدون lock کردن read، از multi-version استفاده می‌کند: هر SELECT یک snapshot از داده در یک نقطه زمانی می‌بیند.

این یعنی:

• SELECT‌ها معمولاً منتظر write‌ها نمی‌مانند (و برعکس)
• قابلیت خواندن «گذشته» از طریق Undo Log
• cleanup بعد از COMMIT با background thread (purge)

۸.۵ Locking – انواع قفل

Row-level Locks (InnoDB)

• Shared Lock (S): چندین تراکنش می‌توانند بخوانند
• Exclusive Lock (X): فقط یک تراکنش می‌تواند بنویسد
• Intention Lock: اعلام قصد قفل کردن (سطح جدول)
• Gap Lock: قفل فاصله بین رکوردها (در REPEATABLE READ)
• Next-Key Lock: ترکیب Record + Gap (پیش‌فرض InnoDB)

گرفتن Lock صریح

SQL📋 کپی
-- Shared Lock (می‌توانید بخوانید، دیگران هم می‌توانند، اما نمی‌توانند تغییر دهند)
SELECT * FROM products WHERE id = 5 LOCK IN SHARE MODE;
-- یا (MySQL 8.0+)
SELECT * FROM products WHERE id = 5 FOR SHARE;

-- Exclusive Lock (دیگران تا COMMIT شما نمی‌توانند بخوانند یا بنویسند)
SELECT * FROM products WHERE id = 5 FOR UPDATE;

-- مثال کلاسیک: مدیریت موجودی
START TRANSACTION;
SELECT stock FROM products WHERE id = 5 FOR UPDATE;
-- چک می‌کنیم stock کافی است
UPDATE products SET stock = stock - 1 WHERE id = 5;
INSERT INTO orders ...;
COMMIT;

NOWAIT و SKIP LOCKED (MySQL 8.0+)

SQL📋 کپی
-- اگر قفل بود، خطا بده (به‌جای انتظار)
SELECT * FROM jobs WHERE status = 'pending'
ORDER BY created_at LIMIT 1
FOR UPDATE NOWAIT;

-- ردیف‌های قفل‌شده را skip کن (عالی برای Queue)
SELECT * FROM jobs WHERE status = 'pending'
ORDER BY created_at LIMIT 10
FOR UPDATE SKIP LOCKED;

SKIP LOCKED انقلابی در پیاده‌سازی Job Queue است. چندین worker می‌توانند همزمان از یک جدول job بکشند بدون تداخل.

۸.۶ Deadlock – تشخیص و حل

Deadlock وقتی رخ می‌دهد که دو تراکنش روی هم منتظرند:

Transaction A:                  Transaction B:
  UPDATE row 1 (lock)             UPDATE row 2 (lock)
  UPDATE row 2 → wait for B       UPDATE row 1 → wait for A
                  ↓
              DEADLOCK!
              MySQL یکی را قربانی می‌کند با خطای 1213
    

SQL📋 کپی
-- نمایش آخرین deadlock
SHOW ENGINE INNODB STATUSG

-- بخش "LATEST DETECTED DEADLOCK" کامل نشان می‌دهد
-- چه تراکنش‌هایی، روی چه رکوردهایی، کی شروع شده

-- تنظیم timeout قفل
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
SET SESSION innodb_lock_wait_timeout = 10;  -- ۱۰ ثانیه به جای ۵۰

راه‌های جلوگیری از Deadlock

1. ترتیب یکسان دسترسی: همیشه به جدول‌ها/ردیف‌ها به یک ترتیب lock بزنید
2. تراکنش‌های کوتاه: COMMIT یا ROLLBACK سریع
3. ایندکس مناسب: ایندکس کم = lock بیشتر
4. Retry پس از deadlock: در اپلیکیشن، خطای 1213 را catch و دوباره امتحان کنید

Python (Django ORM)📋 کپی
from django.db import transaction, OperationalError
import time

def transfer_with_retry(from_id, to_id, amount, max_retries=3):
    for attempt in range(max_retries):
        try:
            with transaction.atomic():
                from_acc = Account.objects.select_for_update().get(id=from_id)
                to_acc = Account.objects.select_for_update().get(id=to_id)
                from_acc.balance -= amount
                to_acc.balance += amount
                from_acc.save()
                to_acc.save()
                return True
        except OperationalError as e:
            if 'deadlock' in str(e).lower() and attempt < max_retries - 1:
                time.sleep(0.1 * (2 ** attempt))  # exponential backoff
                continue
            raise

۸.۷ Optimistic vs Pessimistic Locking

Pessimistic (FOR UPDATE)

قفل می‌گیریم، تغییر می‌دهیم، آزاد می‌کنیم. مناسب برای رقابت زیاد روی همان ردیف.

Optimistic (Version Column)

بدون قفل، با چک کردن version در زمان UPDATE:

SQL📋 کپی
-- اضافه کردن ستون version
ALTER TABLE products ADD COLUMN version INT UNSIGNED DEFAULT 0;

-- خواندن
SELECT id, price, stock, version FROM products WHERE id = 5;
-- فرض: version = 7

-- UPDATE با چک version
UPDATE products
SET price = 1500000,
    version = version + 1
WHERE id = 5 AND version = 7;

-- اگر affected_rows = 0 یعنی کس دیگری زودتر تغییر داده
-- اپلیکیشن باید دوباره بخواند و تلاش کند

Optimistic بهتر است وقتی: رقابت کم است، سرعت مهم است (عدم انتظار)

Pessimistic بهتر است وقتی: رقابت زیاد است، تضمین قطعی نیاز است

۸.۸ مثال کامل: ثبت سفارش با Concurrent Stock Check

SQL📋 کپی
-- این procedure حتی با ۱۰۰ کاربر همزمان روی یک محصول کار می‌کند
DELIMITER $$

CREATE PROCEDURE place_order(
    IN p_user_id INT,
    IN p_product_id INT,
    IN p_quantity INT
)
BEGIN
    DECLARE v_stock INT;
    DECLARE v_price BIGINT;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- قفل ردیف محصول (دیگر تراکنش‌ها صبر می‌کنند)
    SELECT stock, price INTO v_stock, v_price
    FROM products WHERE id = p_product_id FOR UPDATE;

    IF v_stock < p_quantity THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'موجودی کافی نیست';
    END IF;

    -- کم کردن موجودی
    UPDATE products SET stock = stock - p_quantity
    WHERE id = p_product_id;

    -- ثبت سفارش
    INSERT INTO orders (user_id, total, status)
    VALUES (p_user_id, v_price * p_quantity, 'pending');

    SET @order_id = LAST_INSERT_ID();

    INSERT INTO order_items (order_id, product_id, quantity, unit_price)
    VALUES (@order_id, p_product_id, p_quantity, v_price);

    COMMIT;

    SELECT @order_id AS order_id;
END$$

DELIMITER ;

۸.۹ خلاصه فصل

• InnoDB از ACID کامل پشتیبانی می‌کند، MyISAM نه
• REPEATABLE READ پیش‌فرض و مناسب اکثر موارد است
• SELECT FOR UPDATE برای رزرو ردیف، LOCK IN SHARE MODE برای read محافظت‌شده
• SKIP LOCKED بهترین راه برای پیاده‌سازی Queue است
• Deadlock‌ها در اپلیکیشن باید با Retry handle شوند
• Optimistic Locking برای رقابت کم، Pessimistic برای رقابت زیاد