MySQL وتصميم قواعد البيانات

الأقفال والجمود

13 دقيقة الدرس 28 من 40

الأقفال والجمود

الأقفال هي آليات تمنع المعاملات المتزامنة من التداخل مع بعضها البعض. على الرغم من أنها ضرورية لسلامة البيانات، يمكن أن تؤدي الأقفال أيضاً إلى الجمود—حالات ينتظر فيها معاملتان أو أكثر بعضها البعض بشكل غير محدد لتحرير الأقفال. فهم الأقفال والجمود أمر بالغ الأهمية لبناء تطبيقات قواعد بيانات قوية وعالية الأداء.

فهم أقفال قاعدة البيانات

عندما تصل معاملة إلى البيانات، يضع MySQL أقفالاً لضمان الاتساق. فكر في الأقفال مثل لافتات الحجز على طاولة في مطعم—تمنع الآخرين من استخدام المورد حتى تنتهي.

الغرض: تضمن الأقفال أنه عندما تعدل معاملة واحدة البيانات، ترى المعاملات الأخرى إما القيمة القديمة أو القيمة الجديدة—وليس أبداً حالة وسيطة غير متسقة.

أنواع الأقفال

يستخدم MySQL InnoDB عدة أنواع من الأقفال على مستويات دقة مختلفة:

القفل المشترك (S): يمكن لمعاملات متعددة القراءة، لا أحد يمكنه الكتابة
القفل الحصري (X): يمكن لمعاملة واحدة فقط القراءة والكتابة
أقفال النية: أقفال على مستوى الجدول تشير إلى القفل على مستوى الصف
أقفال السجلات: أقفال على سجلات الفهرس الفردية
أقفال الفجوات: أقفال على الفجوات بين سجلات الفهرس
أقفال المفتاح التالي: مزيج من قفل السجل + الفجوة

أقفال الجداول مقابل أقفال الصفوف

فهم الفرق بين أقفال الجداول والصفوف أمر أساسي:

-- قفل الجدول (يقفل الجدول بأكمله)
LOCK TABLES products WRITE;
UPDATE products SET price = price * 1.1;
UNLOCK TABLES;

-- قفل الصف (افتراضي InnoDB، يقفل فقط الصفوف المتأثرة)
START TRANSACTION;
UPDATE products SET price = price * 1.1 WHERE category = 'Electronics';
COMMIT;

أفضل ممارسة: يستخدم InnoDB أقفال على مستوى الصف تلقائياً، وهو دائماً تقريباً أفضل من أقفال الجداول لأنه يسمح بتزامن أعلى بكثير. تجنب LOCK TABLES الصريح ما لم يكن ضرورياً للغاية.

الأقفال المشتركة (أقفال القراءة)

تسمح الأقفال المشتركة لمعاملات متعددة بقراءة نفس البيانات في وقت واحد:

-- المعاملة 1
START TRANSACTION;
SELECT * FROM products WHERE product_id = 1 FOR SHARE;
-- يضع قفلاً مشتركاً

-- المعاملة 2 (في جلسة أخرى)
START TRANSACTION;
SELECT * FROM products WHERE product_id = 1 FOR SHARE;
-- هذا يعمل! الأقفال المشتركة المتعددة متوافقة

-- المعاملة 3 (في جلسة أخرى)
START TRANSACTION;
UPDATE products SET price = 99.99 WHERE product_id = 1;
-- هذا ينتظر! لا يمكن الحصول على قفل حصري بينما توجد أقفال مشتركة

الأقفال الحصرية (أقفال الكتابة)

تمنع الأقفال الحصرية أي معاملة أخرى من القراءة أو الكتابة:

-- المعاملة 1
START TRANSACTION;
SELECT * FROM products WHERE product_id = 1 FOR UPDATE;
-- يضع قفلاً حصرياً

-- المعاملة 2 (في جلسة أخرى)
START TRANSACTION;
SELECT * FROM products WHERE product_id = 1;
-- في REPEATABLE READ أو أقل: يرجع البيانات القديمة (لا ينتظر)
-- في SERIALIZABLE: ينتظر تحرير القفل

-- المعاملة 3 (في جلسة أخرى)
START TRANSACTION;
UPDATE products SET price = 99.99 WHERE product_id = 1;
-- هذا ينتظر! يجب الانتظار حتى تلتزم المعاملة 1 أو تتراجع

مهلة انتظار القفل: بشكل افتراضي، ينتظر MySQL 50 ثانية للحصول على قفل. بعد ذلك، يرمي خطأ: "تجاوزت مهلة انتظار القفل". يمكنك تكوين هذا باستخدام innodb_lock_wait_timeout.

عرض المعاملات المقفلة

توفر MySQL أدوات لمراقبة الأقفال والمعاملات المحظورة:

-- عرض الأقفال الحالية
SELECT * FROM performance_schema.data_locks;

-- عرض انتظارات القفل
SELECT * FROM performance_schema.data_lock_waits;

-- رؤية المعاملات الجارية
SELECT * FROM information_schema.INNODB_TRX;

-- إنهاء معاملة محظورة (استخدم بحذر!)
KILL transaction_id;

ما هو الجمود؟

يحدث الجمود عندما تنتظر معاملتان أو أكثر بعضها البعض لتحرير الأقفال، مما يخلق تبعية دائرية لا يمكن حلها أبداً:

-- سيناريو الجمود الكلاسيكي

-- المعاملة 1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- تحتفظ بقفل على الحساب 1

-- المعاملة 2 (في نفس الوقت في جلسة أخرى)
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 2;
-- تحتفظ بقفل على الحساب 2

-- تستمر المعاملة 1
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- تنتظر قفل المعاملة 2 على الحساب 2

-- تستمر المعاملة 2
UPDATE accounts SET balance = balance + 50 WHERE account_id = 1;
-- تنتظر قفل المعاملة 1 على الحساب 1

-- جمود! تنتظر كل معاملة الأخرى

استجابة MySQL: عندما يكتشف MySQL جموداً، يختار تلقائياً معاملة واحدة كـ "ضحية" ويتراجع عنها، مما يسمح للأخرى بالمتابعة. تتلقى المعاملة المتراجع عنها الخطأ 1213: "تم العثور على جمود عند محاولة الحصول على قفل".

كشف الجمود

يمكنك عرض معلومات تفصيلية عن الجمود:

-- عرض أحدث جمود
SHOW ENGINE INNODB STATUS;

-- ابحث عن قسم "LATEST DETECTED DEADLOCK"
-- يعرض:
-- - المعاملات المعنية
-- - الأقفال التي كانت تنتظرها
-- - المعاملة التي تم التراجع عنها

مثال جمود من الواقع

إليك سيناريو جمود عملي في نظام مخزون:

-- المستخدم A: نقل عنصر من المستودع 1 إلى المستودع 2
START TRANSACTION;
UPDATE inventory SET quantity = quantity - 10 WHERE warehouse_id = 1 AND product_id = 100;
-- مقفل: المستودع 1، المنتج 100

-- المستخدم B: نقل عنصر من المستودع 2 إلى المستودع 1 (في نفس الوقت)
START TRANSACTION;
UPDATE inventory SET quantity = quantity - 5 WHERE warehouse_id = 2 AND product_id = 100;
-- مقفل: المستودع 2، المنتج 100

-- يستمر المستخدم A
UPDATE inventory SET quantity = quantity + 10 WHERE warehouse_id = 2 AND product_id = 100;
-- ينتظر قفل المستخدم B على المستودع 2

-- يستمر المستخدم B
UPDATE inventory SET quantity = quantity + 5 WHERE warehouse_id = 1 AND product_id = 100;
-- جمود! يتراجع MySQL عن معاملة واحدة

منع الجمود

اتبع هذه الاستراتيجيات لتقليل حدوث الجمود:

1. الوصول إلى الموارد بنفس الترتيب:
اقفل الجداول/الصفوف دائماً بنفس الترتيب عبر المعاملات

2. اجعل المعاملات قصيرة:
وقت أقل للاحتفاظ بالأقفال = فرصة أقل للجمود

3. استخدم مستويات عزل أقل:
READ COMMITTED يقلل من القفل مقارنة بـ REPEATABLE READ

4. استخدم الفهارس:
قلل الصفوف الممسوحة/المقفلة باستخدام الفهارس المناسبة

5. تجنب تفاعل المستخدم في المعاملات:
لا تنتظر إدخال المستخدم أثناء الاحتفاظ بالأقفال

الاستراتيجية 1: ترتيب قفل متسق

الوصول دائماً إلى الموارد بترتيب يمكن التنبؤ به:

-- سيء: معاملات مختلفة تصل إلى الحسابات بترتيب مختلف
-- المعاملة A: تقفل الحساب 1، ثم الحساب 2
-- المعاملة B: تقفل الحساب 2، ثم الحساب 1
-- النتيجة: جمود محتمل

-- جيد: الوصول دائماً إلى الحسابات بترتيب معرّف تصاعدي
START TRANSACTION;

SET @from_account = 101, @to_account = 202;
SET @first = LEAST(@from_account, @to_account);
SET @second = GREATEST(@from_account, @to_account);

-- قفل بترتيب متسق
SELECT balance FROM accounts WHERE account_id = @first FOR UPDATE;
SELECT balance FROM accounts WHERE account_id = @second FOR UPDATE;

-- الآن التحديث بأمان
UPDATE accounts SET balance = balance - 100 WHERE account_id = @from_account;
UPDATE accounts SET balance = balance + 100 WHERE account_id = @to_account;

COMMIT;

الاستراتيجية 2: استخدم SELECT ... FOR UPDATE

اقفل الصفوف بشكل صريح في بداية معاملتك:

-- جيد: اقفل جميع الصفوف التي ستحتاجها مقدماً
START TRANSACTION;

-- قفل جميع الصفوف التي سنحتاجها
SELECT * FROM products WHERE product_id IN (1, 5, 10) FOR UPDATE;
SELECT * FROM inventory WHERE product_id IN (1, 5, 10) FOR UPDATE;

-- الآن قم بإجراء التحديثات دون خطر الجمود
UPDATE products SET stock = stock - 1 WHERE product_id = 1;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 1;

COMMIT;

نصيحة محترف: قفل جميع الصفوف الضرورية في البداية (بدلاً من الحصول على الأقفال تدريجياً) يقلل بشكل كبير من احتمالية الجمود.

معالجة الجمود في كود التطبيق

نظراً لأن الجمود أمر لا مفر منه في الأنظمة عالية التزامن، نفذ منطق إعادة المحاولة:

-- مثال PHP
$maxRetries = 3;
$attempt = 0;

while ($attempt < $maxRetries) {
    try {
        // بدء المعاملة
        $pdo->beginTransaction();

        // تنفيذ العمليات
        $pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
        $pdo->exec("UPDATE accounts SET balance = balance + 100 WHERE id = 2");

        // الالتزام
        $pdo->commit();
        break; // نجاح!

    } catch (PDOException $e) {
        $pdo->rollBack();

        // تحقق إذا كان خطأ جمود (1213)
        if ($e->getCode() == 40001 || strpos($e->getMessage(), 'Deadlock') !== false) {
            $attempt++;
            usleep(100000); // انتظر 100 مللي ثانية قبل إعادة المحاولة

            if ($attempt >= $maxRetries) {
                throw new Exception("فشلت المعاملة بعد $maxRetries محاولة");
            }
        } else {
            throw $e; // خطأ مختلف، لا تعيد المحاولة
        }
    }
}

تكوين مهلة انتظار القفل

قم بتكوين المدة التي تنتظرها المعاملات للأقفال:

-- عرض المهلة الحالية (افتراضي: 50 ثانية)
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- تعيين المهلة للجلسة الحالية (بالثواني)
SET SESSION innodb_lock_wait_timeout = 10;

-- تعيين عالمياً (يتطلب صلاحيات)
SET GLOBAL innodb_lock_wait_timeout = 30;

أقفال الفجوات وأقفال المفتاح التالي

يستخدم InnoDB أقفالاً خاصة لمنع القراءات الوهمية:

-- مثال: قفل نطاق
START TRANSACTION;

-- هذا يقفل ليس فقط الصفوف الموجودة، ولكن أيضاً الفجوات
SELECT * FROM products
WHERE price BETWEEN 100 AND 500
FOR UPDATE;

-- لا يمكن للمعاملات الأخرى إدراج منتجات بسعر 100-500
-- INSERT INTO products (price) VALUES (250); -- سينتظر!

COMMIT;

فهم أقفال الفجوات: في مستوى عزل REPEATABLE READ، يقفل InnoDB ليس فقط السجلات، ولكن أيضاً "الفجوات" بينها لمنع المعاملات الأخرى من إدراج صفوف جديدة تتطابق مع استعلامك.

تمرين عملي:

السيناريو: نفذ تحويل أموال آمن من الجمود بين الحسابات المصرفية.

المتطلبات:

نقل الأموال من الحساب A إلى الحساب B
استخدم ترتيب قفل متسق لمنع الجمود
تحقق من الرصيد الكافي قبل التحويل
تعامل مع الحالة حيث B < A أو A < B

الحل:

START TRANSACTION;

-- اقفل الحسابات دائماً بترتيب المعرّف التصاعدي
SET @from_id = 250, @to_id = 100, @amount = 500.00;

-- حدد ترتيب القفل
IF @from_id < @to_id THEN
    SELECT balance INTO @from_balance FROM accounts WHERE id = @from_id FOR UPDATE;
    SELECT balance INTO @to_balance FROM accounts WHERE id = @to_id FOR UPDATE;
ELSE
    SELECT balance INTO @to_balance FROM accounts WHERE id = @to_id FOR UPDATE;
    SELECT balance INTO @from_balance FROM accounts WHERE id = @from_id FOR UPDATE;
END IF;

-- تحقق من الرصيد الكافي
IF @from_balance >= @amount THEN
    UPDATE accounts SET balance = balance - @amount WHERE id = @from_id;
    UPDATE accounts SET balance = balance + @amount WHERE id = @to_id;

    COMMIT;
    SELECT 'نجح التحويل' AS message;
ELSE
    ROLLBACK;
    SELECT 'رصيد غير كافٍ' AS message;
END IF;

مراقبة أداء القفل

استخدم هذه الاستعلامات لمراقبة مشاكل القفل:

-- البحث عن معاملات طويلة الأمد
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id
FROM information_schema.INNODB_TRX
WHERE trx_started < NOW() - INTERVAL 30 SECOND;

-- البحث عن معاملات تنتظر الأقفال
SELECT
    waiting_trx_id,
    waiting_pid,
    blocking_trx_id,
    blocking_pid
FROM sys.innodb_lock_waits;

-- عرض إحصائيات انتظار القفل
SELECT * FROM sys.schema_table_lock_waits;

الملخص

في هذا الدرس، تعلمت:

تمنع الأقفال المعاملات المتزامنة من التداخل مع بعضها البعض
الأقفال المشتركة تسمح بالقراءة؛ الأقفال الحصرية تمنع كل الوصول
يستخدم InnoDB أقفال على مستوى الصف لتزامن أفضل
يحدث الجمود عندما تنتظر المعاملات بعضها البعض بشكل دائري
يكتشف MySQL ويحل الجمود تلقائياً بالتراجع عن معاملة واحدة
امنع الجمود بالوصول إلى الموارد بترتيب متسق والحفاظ على معاملات قصيرة
نفذ منطق إعادة المحاولة في كود التطبيق للتعامل مع الجمود الذي لا مفر منه

التالي: في الدرس التالي، سنستكشف القيود وآليات سلامة البيانات التي تفرض قواعد الأعمال على مستوى قاعدة البيانات!