Izolyatsiya darajalari va qulflar - Ma'lumotlar bazasini loyihalash

Bir vaqtda ishlaydigan tranzaksiyalar bir-birini qanchalik ko'radi? Bu savolga izolyatsiya darajasi javob beradi.

Uch klassik muammo #

Har daraja bu muammolarning ma'lum qismini yo'q qiladi

To'rt izolyatsiya darajasi #

SQL

CREATE TABLE hisoblar (
    id     INT PRIMARY KEY,
    egasi  VARCHAR(50) NOT NULL,
    qoldiq DECIMAL(12,2) NOT NULL
) ENGINE = InnoDB;

INSERT INTO hisoblar VALUES (1, 'Husanboy', 1000000), (2, 'Malika', 500000);

SQL

SELECT @@tx_isolation AS joriy_daraja;

Natija

+-----------------+
| joriy_daraja    |
+-----------------+
| REPEATABLE-READ |
+-----------------+

Daraja	Iflos o'qish	Takrorlanmaydigan	Fantom
`READ UNCOMMITTED`	Mumkin	Mumkin	Mumkin
`READ COMMITTED`	Yo'q	Mumkin	Mumkin
`REPEATABLE READ`	Yo'q	Yo'q	Deyarli yo'q
`SERIALIZABLE`	Yo'q	Yo'q	Yo'q

SQL

-- Darajani o'zgartirish
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT @@tx_isolation AS yangi_daraja;

Natija

+----------------+
| yangi_daraja   |
+----------------+
| READ-COMMITTED |
+----------------+

SQL

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT @@tx_isolation AS qaytarildi;

Natija

+-----------------+
| qaytarildi      |
+-----------------+
| REPEATABLE-READ |
+-----------------+

InnoDB da REPEATABLE READ fantomlarni ham to'xtatadi

Standart SQL bo'yicha REPEATABLE READ fantom qatorlarga ruxsat beradi. Lekin InnoDB "keyingi kalit qulflari" (next-key locks) ishlatadi va ularni ham to'xtatadi.

Shuning uchun MySQL/MariaDB da REPEATABLE READ amalda SERIALIZABLE ga juda yaqin - lekin ancha tezroq.

PostgreSQL da esa standart daraja READ COMMITTED.

Qaysi darajani tanlash:

Daraja	Qachon
`READ UNCOMMITTED`	Deyarli hech qachon
`READ COMMITTED`	Ko'p o'qiladigan tizim, PostgreSQL odati
`REPEATABLE READ`	MySQL/MariaDB standarti - qoldiring
`SERIALIZABLE`	Moliyaviy hisob, juda qat'iy talab

Standart darajani o'zgartirishga shoshilmang. U ko'p loyiha uchun to'g'ri muvozanat beradi.

Ikki sessiya bilan tajriba #

Izolyatsiyani ko'rish uchun ikki alohida ulanish kerak. Quyidagi tajribani ikki terminal oynasida bajaring:

Natija

1-terminal                          2-terminal
--------------------------------    --------------------------------
START TRANSACTION;
SELECT qoldiq FROM hisoblar
  WHERE id = 1;
-- 1000000
                                    START TRANSACTION;
                                    UPDATE hisoblar
                                      SET qoldiq = 500000
                                      WHERE id = 1;
                                    COMMIT;

SELECT qoldiq FROM hisoblar
  WHERE id = 1;
-- REPEATABLE READ: 1000000 (o'zgarmadi)
-- READ COMMITTED:   500000 (o'zgardi)
COMMIT;

REPEATABLE READ - tranzaksiya "suratni" ko'radi

InnoDB ko'p versiyali (MVCC) mexanizmni ishlatadi: har tranzaksiya boshlanganda ma'lumotning o'sha paytdagi holatini ko'radi.

Boshqalar o'zgartirsa ham, sizning tranzaksiyangiz eski versiyani o'qiyveradi.

Bu ikki foyda beradi:

Izchil hisobot - uzun hisobot davomida sonlar o'zgarmaydi;
O'quvchilar yozuvchilarni bloklamaydi - SELECT qulf qo'ymaydi.

Ikkinchisi juda muhim: eski ma'lumotlar bazalarida SELECT ham qulf qo'yardi va o'qish yozishni to'xtatib qo'yardi.

Eski versiyalar undo jurnalida saqlanadi. Uzun tranzaksiya bu jurnalni o'stiradi - 16-bo'limdagi "tranzaksiyani qisqa tuting" maslahatining yana bir sababi.

Qulf turlari #

SQL

-- Oddiy SELECT qulf qo'ymaydi (MVCC)
START TRANSACTION;
SELECT qoldiq FROM hisoblar WHERE id = 1;
COMMIT;

-- FOR UPDATE - yozish qulfi qo'yadi
START TRANSACTION;
SELECT qoldiq FROM hisoblar WHERE id = 1 FOR UPDATE;
COMMIT;

-- LOCK IN SHARE MODE - o'qish qulfi
START TRANSACTION;
SELECT qoldiq FROM hisoblar WHERE id = 1 LOCK IN SHARE MODE;
COMMIT;

SELECT 'Uchala so''rov ham bajarildi' AS natija;

Natija

+------------+
| qoldiq     |
+------------+
| 1000000.00 |
+------------+
+------------+
| qoldiq     |
+------------+
| 1000000.00 |
+------------+
+------------+
| qoldiq     |
+------------+
| 1000000.00 |
+------------+
+-----------------------------+
| natija                      |
+-----------------------------+
| Uchala so'rov ham bajarildi |
+-----------------------------+

Qulf	Qanday qo'yiladi	Kim kutadi
Yo'q	Oddiy `SELECT`	Hech kim - MVCC
Bo'lishilgan (S)	`LOCK IN SHARE MODE`	Yozuvchilar
Eksklyuziv (X)	`FOR UPDATE`, `UPDATE`, `DELETE`	Hamma

SELECT ... FOR UPDATE - o'qib, keyin yozish uchun

Ba'zan qiymatni o'qib, unga qarab yozish kerak bo'ladi:

SQL

START TRANSACTION;

SELECT qoldiq FROM hisoblar WHERE id = 1 FOR UPDATE;
-- endi bu qator QULFLANGAN, boshqa hech kim o'zgartira olmaydi

-- kod hisoblaydi...

UPDATE hisoblar SET qoldiq = qoldiq - 100 WHERE id = 1;
COMMIT;

FOR UPDATE bo'lmasa, ikki parallel tranzaksiya bir xil qiymatni o'qib, ikkalasi ham yozishi mumkin - yo'qolgan yangilanish (lost update).

Lekin 16-bo'limda ko'rganimizdek, ko'p holatda shartli UPDATE yaxshiroq:

SQL

UPDATE hisoblar SET qoldiq = qoldiq - 100
WHERE id = 1 AND qoldiq >= 100;

Bu bitta amal - qulf kerak emas, poyga holati yo'q va tezroq ishlaydi.

FOR UPDATE ni faqat murakkab mantiq bo'lganda ishlating: bir necha jadvaldan o'qib, keyin qaror qabul qilish kerak bo'lsa.

Qulf diapazoni - butun jadval qulflanishi mumkin #

SQL

-- Indeksli ustun bo'yicha - faqat kerakli qator qulflanadi
START TRANSACTION;
UPDATE hisoblar SET qoldiq = qoldiq WHERE id = 1;
COMMIT;

-- Indekssiz ustun bo'yicha - BUTUN JADVAL skanerlanadi va qulflanadi
START TRANSACTION;
UPDATE hisoblar SET qoldiq = qoldiq WHERE egasi = 'Husanboy';
COMMIT;

SELECT 'Ikkinchisi butun jadvalni qulfladi' AS eslatma;

Natija

+------------------------------------+
| eslatma                            |
+------------------------------------+
| Ikkinchisi butun jadvalni qulfladi |
+------------------------------------+

Indekssiz WHERE butun jadvalni qulflaydi

InnoDB qulflarni indeks yozuvlariga qo'yadi, qatorlarga emas.

Indeks bo'lmasa, u har bir qatorni ko'rib chiqishi kerak - va ko'rgan har bir qatorni qulflaydi.

SQL

-- egasi ustunida indeks YO'Q
UPDATE hisoblar SET qoldiq = 0 WHERE egasi = 'Husanboy';

Bitta qator o'zgarsa ham, butun jadval qulflanadi. Katta jadvalda bu butun tizimni to'xtatadi.

Bu 14-bo'limdagi indekslarning yana bir sababi: ular faqat tezlik uchun emas, parallellik uchun ham kerak.

Amaliy tekshiruv - UPDATE va DELETE ning WHERE ustunlari indekslanganmi? Agar yo'q bo'lsa, ular yashirin to'siq.

Deadlock #

Natija

1-tranzaksiya                       2-tranzaksiya
--------------------------------    --------------------------------
START TRANSACTION;                  START TRANSACTION;
UPDATE hisoblar SET ...             UPDATE hisoblar SET ...
  WHERE id = 1;                       WHERE id = 2;
-- 1-qator qulflandi                -- 2-qator qulflandi

UPDATE hisoblar SET ...
  WHERE id = 2;
-- 2-qatorni KUTADI
                                    UPDATE hisoblar SET ...
                                      WHERE id = 1;
                                    -- 1-qatorni KUTADI

-- ikkalasi bir-birini kutadi = DEADLOCK
-- InnoDB bittasini avtomatik BEKOR QILADI

Natija

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

Deadlock dan qochish

InnoDB deadlock ni avtomatik aniqlaydi va tranzaksiyalardan birini bekor qiladi. Ilova uni qayta urinib ko'rishi kerak:

PHP

for ($urinish = 1; $urinish <= 3; $urinish++) {
    try {
        $db->beginTransaction();
        // ...
        $db->commit();
        break;
    } catch (PDOException $e) {
        $db->rollBack();
        if ($e->getCode() !== '40001' || $urinish === 3) {
            throw $e;
        }
        usleep(random_int(10000, 100000));   // tasodifiy kutish
    }
}

Deadlock ehtimolini kamaytirish usullari:

Usul	Izoh
Bir xil tartibda qulflash	Har doim `id` o'sish tartibida
Tranzaksiyani qisqa tutish	Kam qulf, kam to'qnashuv
Indeks qo'yish	Kamroq qator qulflanadi
Bitta `UPDATE` ishlatish	Ko'p amal o'rniga
Past izolyatsiya darajasi	Kamroq qulf

Birinchisi eng samarali. Ikki tranzaksiya 1 → 2 tartibida qulflasa, deadlock mumkin emas. 1 → 2 va 2 → 1 bo'lsa - muqarrar.

Oxirgi deadlock sababini ko'rish:

SQL

SHOW ENGINE INNODB STATUS;

Chiqishdagi LATEST DETECTED DEADLOCK bo'limi qaysi ikki so'rov to'qnashganini aniq ko'rsatadi.

Qulf kutish vaqti #

SQL

SELECT @@innodb_lock_wait_timeout AS kutish_soniya;

Natija

+---------------+
| kutish_soniya |
+---------------+
|            50 |
+---------------+

Natija

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Ikki xil xatoni farqlang

Xato	Kod	Sabab
`Deadlock found`	1213	Ikki tranzaksiya bir-birini kutadi
`Lock wait timeout`	1205	Bittasi juda uzoq kutdi

1213 - InnoDB darhol aniqlaydi va bittasini bekor qiladi. Qayta urinish odatda yordam beradi.

1205 - hech kim aylanma kutishda emas, shunchaki qulf egasi juda sekin. Bu ko'pincha:

Ochiq qolgan tranzaksiya (COMMIT unutilgan);
Uzun ishlaydigan UPDATE;
Tranzaksiya ichida tashqi xizmat kutilishi (16-bo'lim).

Kimni kutayotganini ko'rish:

SQL

SELECT * FROM information_schema.INNODB_TRX\G

trx_started ustuni tranzaksiya qachon boshlanganini ko'rsatadi. Soatlab ochiq turgan tranzaksiya - deyarli har doim dastur xatosi.

innodb_lock_wait_timeout ni oshirish yechim emas - u faqat muammoni kechiktiradi.

Optimistik qulflash #

SQL

CREATE TABLE hujjatlar (
    id      INT PRIMARY KEY,
    matn    TEXT NOT NULL,
    versiya INT NOT NULL DEFAULT 1
) ENGINE = InnoDB;

INSERT INTO hujjatlar VALUES (1, 'Boshlangich matn', 1);

SQL

-- Foydalanuvchi hujjatni ochdi: versiya = 1 ni eslab qoldi
SELECT id, matn, versiya FROM hujjatlar WHERE id = 1;

Natija

+----+------------------+---------+
| id | matn             | versiya |
+----+------------------+---------+
|  1 | Boshlangich matn |       1 |
+----+------------------+---------+

SQL

-- Saqlashda versiyani tekshiramiz
UPDATE hujjatlar
SET matn = 'Yangi matn', versiya = versiya + 1
WHERE id = 1 AND versiya = 1;

SELECT ROW_COUNT() AS ozgardi;

Natija

+---------+
| ozgardi |
+---------+
|       1 |
+---------+

SQL

-- Boshqa foydalanuvchi eski versiya bilan saqlashga urinadi
UPDATE hujjatlar
SET matn = 'Boshqa matn', versiya = versiya + 1
WHERE id = 1 AND versiya = 1;

SELECT ROW_COUNT() AS ozgardi;

Natija

+---------+
| ozgardi |
+---------+
|       1 |
+---------+

Optimistik va pessimistik qulflash

	Pessimistik	Optimistik
Usul	`SELECT ... FOR UPDATE`	`versiya` ustuni
Qulf	Bor - boshqalar kutadi	Yo'q
Konflikt	Oldini oladi	Aniqlaydi
Uzun tahrirlash	Yaramaydi	Mos
To'qnashuv ko'p bo'lsa	Yaxshi	Ko'p qayta urinish

Optimistik usul veb ilovalarda ancha mos keladi.

Sabab: foydalanuvchi formani 10 daqiqa to'ldirishi mumkin. Shu vaqt davomida qatorni qulflab turish mumkin emas - ulanish ham uzilib ketadi.

ROW_COUNT() = 0 bo'lsa, foydalanuvchiga aniq xabar bering:

"Bu hujjatni siz ochganingizdan keyin boshqa kimdir o'zgartirdi. Yangi versiyani ko'rib chiqing."

Ba'zi tizimlar versiya o'rniga yangilangan vaqt tamg'asini ishlatadi, lekin bu kamroq ishonchli - bir soniya ichida ikki o'zgarish bo'lishi mumkin.

Amaliy topshiriq

Uch klassik muammoni o'z so'zingiz bilan tushuntiring.
To'rt izolyatsiya darajasini jadval qilib chizing.
Joriy darajani ko'rib, uni o'zgartiring.
Ikki terminalda REPEATABLE READ tajribasini o'tkazing.
MVCC nima ekanini ayting.
FOR UPDATE va shartli UPDATE ni taqqoslang.
Indekssiz WHERE nima uchun jadvalni qulflashini tushuntiring.
Deadlock ssenariysini ikki terminalda takrorlang.
1213 va 1205 xatolari farqini ayting.
versiya ustuni bilan optimistik qulflash yozing.

Xulosa #

Uch muammo: iflos o'qish, takrorlanmaydigan o'qish, fantom.
MySQL/MariaDB standarti - REPEATABLE READ.
InnoDB da u fantomlarni ham deyarli to'xtatadi.
MVCC tufayli SELECT qulf qo'ymaydi.
Uzun tranzaksiya undo jurnalini o'stiradi.
FOR UPDATE o'qib-yozish uchun; shartli UPDATE afzalroq.
Indekssiz WHERE butun jadvalni qulflaydi.
Indekslar parallellik uchun ham kerak.
Deadlock (1213) - qayta urining; timeout (1205) - sababni toping.
Qulflarni bir xil tartibda oling.
Uzun tahrirlash uchun optimistik qulflash (versiya ustuni).

Keyingi bo'limda sxema migratsiyalarini ko'ramiz.

Uch klassik muammo #

To'rt izolyatsiya darajasi #

Ikki sessiya bilan tajriba #

Qulf turlari #

Qulf diapazoni - butun jadval qulflanishi mumkin #

Deadlock #

Qulf kutish vaqti #

Optimistik qulflash #

Xulosa #

Xatolik topdingizmi?