Locking — Row lock, Table lock, Deadlock va yechimi

~20 daq · MySQL · Transaction va Qulflar

Muammodan boshlaylik

Omborda oxirgi iPhone qoldi. Ikki mijoz bir soniyada "Sotib olish" ni bosdi. Ilova kodi:

1. SELECT soni FROM mahsulotlar WHERE id = 1      → 1
2. if (soni >= 1):
3.     buyurtma yaratish
4.     UPDATE mahsulotlar SET soni = 0 WHERE id = 1

Ikkala so'rov ham 1-qadamda 1 ni o'qidi. Ikkalasi ham buyurtma yaratdi. Bitta telefon ikki kishiga sotildi.

O'tgan darsda ko'rdik: REPEATABLE READ buni to'xtatmaydi. Kerak — qatorni qulflash: "men bu qator bilan ishlayapman, men tugatgunimcha kuting".

Qatorni qulflash: SELECT ... FOR UPDATE

START TRANSACTION;

SELECT soni FROM mahsulotlar WHERE id = 1 FOR UPDATE;   -- 🔒 qator qulflandi
-- soni = 1 → yetarli

INSERT INTO buyurtmalar ...;
UPDATE mahsulotlar SET soni = soni - 1 WHERE id = 1;

COMMIT;                                                   -- 🔓 qulf ochildi

Endi ikkinchi mijozning tranzaksiyasi:

Mijoz A: SELECT ... FOR UPDATE   → 1, qulf A da
Mijoz B: SELECT ... FOR UPDATE   → ⏳ KUTADI (A ning qulfi)
Mijoz A: INSERT, UPDATE soni = 0, COMMIT  → qulf ochildi
Mijoz B:                          → 0 (eng yangi qiymat!) → "Omborda yo'q"

FOR UPDATE:

• Qatorni eksklyuziv qulflaydi — boshqalar uni o'zgartira olmaydi va FOR UPDATE bilan o'qiy olmaydi
• Eng yangi commit qilingan qiymatni o'qiydi (surat emas)
• Qulf COMMIT yoki ROLLBACK gacha turadi
• Faqat tranzaksiya ichida ma'noga ega (autocommit da qulf darhol ochiladi)

Oddiy SELECT (qulfsiz) esa kutmaydi — MVCC tufayli eski versiyani o'qiydi.

Qulf turlari

FOR SHARE — "o'qiyapman, men tugatgunimcha hech kim o'zgartirmasin, lekin boshqalar ham o'qisin". Masalan, buyurtma yaratishda mijoz qatorini — tranzaksiya davomida mijoz o'chirilmasligi uchun.

Muhim: qulf indeks orqali qo'yiladi

InnoDB qatorlarni indeks orqali qulflaydi. Indeks yo'q bo'lsa:

-- telefon ustunida indeks YO'Q
UPDATE mijozlar SET shahar = 'Toshkent' WHERE telefon = '+998901112233';

MySQL kerakli qatorni topish uchun butun jadvalni ko'rib chiqadi va har bir ko'rgan qatorni qulflaydi. Natija — amalda butun jadval qulfi. Boshqa barcha UPDATE lar kutadi.

Qoida: UPDATE / DELETE / FOR UPDATE dagi WHERE ustunlari indekslangan bo'lsin.

Gap lock — "bo'shliq" qulfi

REPEATABLE READ da InnoDB nafaqat mavjud qatorlarni, balki ular orasidagi bo'shliqlarni ham qulflashi mumkin:

START TRANSACTION;
SELECT * FROM buyurtmalar WHERE mijoz_id = 42 FOR UPDATE;
-- Endi boshqalar mijoz_id = 42 bilan YANGI buyurtma qo'sha olmaydi

Bu phantom read larning oldini oladi, lekin kutilmagan kutishlarga sabab bo'lishi mumkin. READ COMMITTED da gap lock lar deyarli ishlatilmaydi.

Jadval qulflari

LOCK TABLES mahsulotlar WRITE;
-- ... faqat shu sessiya o'qiy va yoza oladi
UNLOCK TABLES;

InnoDB bilan deyarli hech qachon kerak emas — qator qulflari ancha samarali. Eski MyISAM jadvallari faqat jadval qulfini biladi — shuning uchun ular yuqori yuklamada "qotadi".

ALTER TABLE ham metadata qulfini (MDL) oladi. Uzoq davom etayotgan tranzaksiya jadvalni ishlatayotgan bo'lsa — ALTER kutadi, va undan keyin kelgan barcha so'rovlar ham ALTER ni kutadi. Production da ALTER dan oldin uzoq tranzaksiyalarni tekshiring.

Deadlock

Ikki tranzaksiya bir-birining qulfini kutib, abadiy qotib qolishi:

A: UPDATE mahsulotlar SET soni = soni - 1 WHERE id = 1;   🔒 1 — A da
B: UPDATE mahsulotlar SET soni = soni - 1 WHERE id = 2;   🔒 2 — B da
A: UPDATE mahsulotlar SET soni = soni - 1 WHERE id = 2;   ⏳ B ni kutadi
B: UPDATE mahsulotlar SET soni = soni - 1 WHERE id = 1;   ⏳ A ni kutadi
                                                           💀 DEADLOCK

A — B ni kutadi, B — A ni kutadi. Hech biri oldinga siljiy olmaydi.

InnoDB deadlock ni avtomatik aniqlaydi va tranzaksiyalardan birini (kamroq ish qilganini) "qurbon" qiladi:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Qurbon tranzaksiya to'liq ROLLBACK qilinadi. Ikkinchisi davom etadi.

Deadlock ni kamaytirish

1. Bir xil tartibda qulflang. Ikkala tranzaksiya ham avval 1, keyin 2 ni qulflasa — deadlock bo'lmaydi, faqat navbat:

Savatdagi mahsulotlar: [2, 1, 5] → saralang → [1, 2, 5] → shu tartibda UPDATE

2. Tranzaksiyalarni qisqa tuting. Qulf qancha qisqa ushlansa — to'qnashuv ehtimoli shuncha kam.

3. Indekslar. Indekssiz UPDATE keraksiz qatorlarni qulflaydi — to'qnashuvlar ko'payadi.

4. Kerakli qatorlarni birdaniga qulflang:

SELECT id, soni FROM mahsulotlar WHERE id IN (1, 2, 5) ORDER BY id FOR UPDATE;

Deadlock ni qayta urinish bilan hal qilish

Deadlock ni butunlay yo'q qilib bo'lmaydi — yuqori yuklamada vaqti-vaqti bilan bo'ladi. Ilova uni kutishi va tranzaksiyani qayta boshlashi kerak:

<?php
for ($urinish = 1; $urinish <= 3; $urinish++) {
    try {
        $pdo->beginTransaction();
        // ... so'rovlar
        $pdo->commit();
        break;
    } catch (PDOException $e) {
        $pdo->rollBack();
        if ($e->errorInfo[1] !== 1213 || $urinish === 3) {   // 1213 — deadlock
            throw $e;
        }
        usleep(random_int(50_000, 200_000));   // tasodifiy kichik pauza
    }
}

Oxirgi deadlock ni ko'rish

SHOW ENGINE INNODB STATUS\G

LATEST DETECTED DEADLOCK bo'limida — qaysi tranzaksiyalar, qaysi so'rovlar, qaysi qulflar.

Kutish vaqti

SELECT @@innodb_lock_wait_timeout;   -- 50 soniya

Qulfni 50 soniya kutib, ololmasa:

ERROR 1205: Lock wait timeout exceeded; try restarting transaction

Veb-ilova uchun 50 soniya — juda uzoq. Sessiya uchun kamaytirish:

SET SESSION innodb_lock_wait_timeout = 5;

MySQL 8 da kutmasdan ishlash imkoniyatlari:

SELECT * FROM mahsulotlar WHERE id = 1 FOR UPDATE NOWAIT;      -- qulflangan bo'lsa darhol xato
SELECT * FROM vazifalar WHERE holat = 'kutmoqda'
    ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;                  -- qulflanganlarni o'tkazib yubor

SKIP LOCKED — vazifalar navbati uchun ideal: bir nechta worker bir vaqtda "keyingi bo'sh vazifa" ni oladi, bir-birini kutmasdan va bitta vazifani ikki marta olmasdan.

Hozir kim kimni kutyapti

SELECT * FROM sys.innodb_lock_waits\G

Kutayotgan so'rov, uni bloklayotgan so'rov va bloklovchi ulanish ID si — KILL uchun.

Nima uchun shunday?

Nima uchun InnoDB deadlock ning oldini olmaydi, faqat aniqlab, tranzaksiyani bekor qiladi?

Deadlock ning oldini olishning yagona ishonchli yo'li — barcha kerakli qulflarni oldindan bilish va ularni bir vaqtda olish. Lekin baza tranzaksiya qaysi qatorlarga murojaat qilishini oldindan bilmaydi — so'rovlar birin-ketin keladi va har biri oldingisining natijasiga bog'liq bo'lishi mumkin. Shuning uchun amaliy yechim — "optimistik" yondashuv: hamma erkin ishlasin, kamdan-kam bo'ladigan deadlock ni tez aniqlab, bittasini qayta boshlash. Bu bloklashdan ancha samarali.

Xulosa

Eslab qoling:

• SELECT ... FOR UPDATE — qatorni eksklyuziv qulflash; eng yangi qiymatni o'qiydi
• FOR SHARE — o'qish qulfi: boshqalar o'qiydi, o'zgartira olmaydi
• Qulflar COMMIT / ROLLBACK gacha; faqat tranzaksiya ichida
• Indekssiz WHERE — amalda jadval qulfi
• Deadlock — ERROR 1213; InnoDB bittasini bekor qiladi
• Kamaytirish: bir xil tartibda qulflash, qisqa tranzaksiyalar, indekslar
• Ilovada deadlock ni qayta urinish bilan hal qiling
• NOWAIT, SKIP LOCKED — kutmasdan ishlash; navbatlar uchun

Keyingi dars

39-dars: SAVEPOINT — qisman rollback