Tranzaksiyalar va ACID - Ma'lumotlar bazasini loyihalash

Tranzaksiya - bo'linmas amallar guruhi. Ular yo hammasi bajariladi, yo hech qaysisi.

Klassik misol: pul o'tkazish #

SQL

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

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

SQL

SELECT id, egasi, qoldiq FROM hisoblar ORDER BY id;

Natija

+----+----------+------------+
| id | egasi    | qoldiq     |
+----+----------+------------+
|  1 | Husanboy | 1000000.00 |
|  2 | Malika   |  500000.00 |
+----+----------+------------+

Tranzaksiya aynan shu holatning oldini oladi

`COMMIT` - tasdiqlash #

SQL

START TRANSACTION;

UPDATE hisoblar SET qoldiq = qoldiq - 300000 WHERE id = 1;
UPDATE hisoblar SET qoldiq = qoldiq + 300000 WHERE id = 2;

COMMIT;

SELECT id, egasi, qoldiq FROM hisoblar ORDER BY id;

Natija

+----+----------+-----------+
| id | egasi    | qoldiq    |
+----+----------+-----------+
|  1 | Husanboy | 700000.00 |
|  2 | Malika   | 800000.00 |
+----+----------+-----------+

`ROLLBACK` - bekor qilish #

SQL

START TRANSACTION;

UPDATE hisoblar SET qoldiq = qoldiq - 300000 WHERE id = 1;
UPDATE hisoblar SET qoldiq = qoldiq + 300000 WHERE id = 2;

-- Nimadir noto'g'ri ketdi - hammasini bekor qilamiz
ROLLBACK;

SELECT id, egasi, qoldiq FROM hisoblar ORDER BY id;

Natija

+----+----------+------------+
| id | egasi    | qoldiq     |
+----+----------+------------+
|  1 | Husanboy | 1000000.00 |
|  2 | Malika   |  500000.00 |
+----+----------+------------+

Ikkala UPDATE ham bekor qilindi - hisoblar boshlang'ich holatda.

ACID xossalari #

To'rtala kafolat birgalikda ishonchli tizim beradi

Izchillik: cheklov tranzaksiyani buzadi #

SQL

START TRANSACTION;

UPDATE hisoblar SET qoldiq = qoldiq - 2000000 WHERE id = 1;

Natija

ERROR 4025 (23000): CONSTRAINT `chk_qoldiq` failed for `bank`.`hisoblar`

SQL

-- Xatodan keyin tranzaksiyani bekor qilamiz
START TRANSACTION;
UPDATE hisoblar SET qoldiq = qoldiq - 100000 WHERE id = 1;
ROLLBACK;

SELECT qoldiq FROM hisoblar WHERE id = 1;

Natija

+------------+
| qoldiq     |
+------------+
| 1000000.00 |
+------------+

Xato tranzaksiyani avtomatik bekor qilmaydi

MariaDB da bitta buyruq xato bersa, butun tranzaksiya bekor bo'lmaydi - faqat o'sha buyruq bajarilmaydi.

SQL

START TRANSACTION;
UPDATE hisoblar SET qoldiq = -100 WHERE id = 1;   -- xato
UPDATE hisoblar SET qoldiq = 900000 WHERE id = 2; -- BU BAJARILADI
COMMIT;                                            -- ikkinchisi saqlanadi

Bu juda xavfli: dastur birinchi xatoni e'tiborsiz qoldirsa, yarim bajarilgan tranzaksiya tasdiqlanadi.

Shuning uchun ilova kodida har doim shunday yozing:

PHP

$db->beginTransaction();
try {
    $db->exec("UPDATE ...");
    $db->exec("UPDATE ...");
    $db->commit();
} catch (Exception $e) {
    $db->rollBack();     // MAJBURIY
    throw $e;
}

catch blokida rollBack bo'lmasa, ulanish qaytarilganda tranzaksiya ochiq qoladi va qulflar saqlanadi.

PostgreSQL bu masalada qat'iyroq: xatodan keyin butun tranzaksiya "buzilgan" holatga o'tadi va faqat ROLLBACK qabul qilinadi.

Avtomatik tasdiqlash #

SQL

SELECT @@autocommit AS avtomatik_tasdiqlash;

Natija

+----------------------+
| avtomatik_tasdiqlash |
+----------------------+
|                    1 |
+----------------------+

SQL

-- autocommit yoqilgan - har buyruq o'z tranzaksiyasi
UPDATE hisoblar SET qoldiq = 999999 WHERE id = 2;

-- ROLLBACK hech nima qilmaydi, chunki allaqachon tasdiqlangan
ROLLBACK;

SELECT qoldiq FROM hisoblar WHERE id = 2;

Natija

+-----------+
| qoldiq    |
+-----------+
| 999999.00 |
+-----------+

autocommit yoqilganda ROLLBACK foydasiz

Standart holatda har bir buyruq o'z tranzaksiyasida bajariladi va darhol tasdiqlanadi.

Ya'ni:

SQL

UPDATE ...;      -- avtomatik COMMIT
DELETE ...;      -- avtomatik COMMIT

Xato qilsangiz - qaytarib bo'lmaydi.

Mashhur halokat naqshi:

SQL

DELETE FROM buyurtmalar;      -- WHERE ni unutdik
-- 2 million qator o'chdi, ROLLBACK yordam bermaydi

Himoya usullari:

1. Xavfli amaldan oldin tranzaksiya oching:

SQL

START TRANSACTION;
DELETE FROM buyurtmalar WHERE sana < '2020-01-01';
SELECT ROW_COUNT();     -- nechta o'chdi? kutilgandekmi?
COMMIT;                 -- yoki ROLLBACK

2. MariaDB mijozida xavfsiz rejim:

Terminal

mysql --safe-updates

Bu rejimda WHERE yoki LIMIT siz UPDATE/DELETE rad etiladi.

3. Avval SELECT bilan tekshiring:

SQL

SELECT COUNT(*) FROM buyurtmalar WHERE sana < '2020-01-01';
-- son to'g'ri bo'lsa, DELETE ga o'zgartiring

Ishlab chiqarish bazasida ishlayotganda bu odat majburiy.

Saqlash nuqtalari #

SQL

START TRANSACTION;

UPDATE hisoblar SET qoldiq = 100 WHERE id = 1;
SAVEPOINT birinchi;

UPDATE hisoblar SET qoldiq = 200 WHERE id = 2;
SAVEPOINT ikkinchi;

UPDATE hisoblar SET qoldiq = 300 WHERE id = 1;

-- Faqat oxirgi o'zgarishni bekor qilamiz
ROLLBACK TO SAVEPOINT ikkinchi;

SELECT id, qoldiq FROM hisoblar ORDER BY id;

Natija

+----+--------+
| id | qoldiq |
+----+--------+
|  1 | 100.00 |
|  2 | 200.00 |
+----+--------+

SQL

-- Butun tranzaksiyani bekor qilamiz
ROLLBACK;

SELECT id, qoldiq FROM hisoblar ORDER BY id;

Natija

+----+------------+
| id | qoldiq     |
+----+------------+
|  1 | 1000000.00 |
|  2 |  500000.00 |
+----+------------+

Saqlash nuqtasi qachon kerak

SAVEPOINT uzun tranzaksiyaning bir qismini bekor qilish imkonini beradi.

Amaliy qo'llanishi - ichma-ich tranzaksiyalar. SQL da haqiqiy ichma-ich tranzaksiya yo'q, lekin ORM lar uni SAVEPOINT orqali taqlid qiladi:

PHP

$db->beginTransaction();          // START TRANSACTION
    $db->beginTransaction();      // SAVEPOINT sp1
        // ...
    $db->rollBack();              // ROLLBACK TO sp1
$db->commit();                    // COMMIT

Yana bir holat - ixtiyoriy qadam:

SQL

START TRANSACTION;
INSERT INTO buyurtmalar ...;

SAVEPOINT bonus;
INSERT INTO bonuslar ...;         -- bu qadam yiqilsa ham buyurtma qolsin
-- xato bo'lsa: ROLLBACK TO bonus;

COMMIT;

Diqqat: SAVEPOINT qulflarni bo'shatmaydi. Uzun tranzaksiya baribir boshqalarni kutishga majbur qiladi.

DDL yashirin tasdiqlaydi #

SQL

START TRANSACTION;

UPDATE hisoblar SET qoldiq = 777 WHERE id = 1;

-- DDL buyrug'i YASHIRIN COMMIT qiladi
CREATE TABLE vaqtinchalik (id INT);

ROLLBACK;

SELECT qoldiq FROM hisoblar WHERE id = 1;

Natija

+--------+
| qoldiq |
+--------+
| 777.00 |
+--------+

DDL tranzaksiyani buzadi

ROLLBACK yozilgan bo'lsa ham qoldiq 777 bo'lib qoldi.

Sabab: MySQL va MariaDB da DDL buyruqlari yashirin COMMIT bajaradi. Ular tranzaksiyaga kirmaydi.

Yashirin COMMIT qiladigan buyruqlar:

Buyruq	Izoh
`CREATE TABLE`, `DROP TABLE`	Jadval yaratish/o'chirish
`ALTER TABLE`	Tuzilma o'zgarishi
`CREATE INDEX`, `DROP INDEX`	Indeks
`TRUNCATE TABLE`	Tozalash
`CREATE DATABASE`	Baza
`START TRANSACTION`	Yangi tranzaksiya boshlanishi

Amaliy oqibat - migratsiyani qaytarib bo'lmaydi:

SQL

START TRANSACTION;
ALTER TABLE ...;        -- yashirin COMMIT
ALTER TABLE ...;        -- xato!
ROLLBACK;               -- birinchisini QAYTARMAYDI

Shuning uchun MySQL/MariaDB migratsiyalarida har qadam uchun teskari qadam yozish kerak (18-bo'lim).

PostgreSQL bu jihatdan kuchliroq - u tranzaksion DDL ni qo'llab-quvvatlaydi va butun migratsiyani bir tranzaksiyaga o'rash mumkin.

Bardoshlilik va `innodb_flush_log_at_trx_commit` #

SQL

SELECT
    @@innodb_flush_log_at_trx_commit AS flush_rejimi,
    @@sync_binlog                    AS binlog_sync;

Natija

+--------------+-------------+
| flush_rejimi | binlog_sync |
+--------------+-------------+
|            1 |           0 |
+--------------+-------------+

Bardoshlilik va tezlik muvozanati

innodb_flush_log_at_trx_commit COMMIT paytida jurnalning diskka qanday yozilishini belgilaydi:

Qiymat	Xatti-harakat	Yo'qotish xavfi
`1`	Har `COMMIT` da diskka yoziladi	Yo'q - to'liq ACID
`2`	OS keshiga yoziladi	Server o'chsa ~1 soniya
`0`	Sekundiga bir marta	~1 soniya, jarayon qulasa ham

Standart - 1, ya'ni to'liq bardoshlilik.

2 yoki 0 sezilarli tezlashtiradi (ba'zan 10 barobar), lekin COMMIT qaytgan tranzaksiya yo'qolishi mumkin.

Qachon 2 maqbul:

Analitik ombor (ma'lumot qayta yuklanadi);
Kesh jadvallari;
Sinov muhiti.

Qachon 1 shart:

Moliyaviy ma'lumot;
Buyurtmalar;
Har qanday "yo'qolmasligi kerak" ma'lumot.

Diqqat: disk o'zi ham keshga ega. Haqiqiy kafolat uchun quvvat himoyalangan RAID kontrolleri yoki korporativ SSD kerak.

Tranzaksiya uzunligi #

SQL

SELECT
    @@tx_isolation AS izolyatsiya,
    @@innodb_lock_wait_timeout AS qulf_kutish_soniya;

Natija

+-----------------+--------------------+
| izolyatsiya     | qulf_kutish_soniya |
+-----------------+--------------------+
| REPEATABLE-READ |                 50 |
+-----------------+--------------------+

Tranzaksiyani qisqa tuting

Uzun tranzaksiya butun tizimni sekinlashtiradi:

Muammo	Sabab
Boshqalar kutadi	Qulflar ushlab turiladi
Undo jurnal o'sadi	Eski versiyalar saqlanadi
Replikatsiya kechikadi	Katta tranzaksiya bir bo'lakda ketadi
Deadlock ehtimoli ortadi	Ko'proq qulf, ko'proq to'qnashuv

Eng ko'p uchraydigan xato - tranzaksiya ichida tashqi xizmatga murojaat:

PHP

$db->beginTransaction();
$db->exec("UPDATE buyurtmalar SET holat = 'tolangan' ...");
$tolov = $tashqiApi->tolash($summa);        // 3 SONIYA KUTADI!
$db->commit();

Uch soniya davomida qatorlar qulflangan bo'ladi.

To'g'ri tartib:

PHP

$tolov = $tashqiApi->tolash($summa);        // avval tashqi chaqiruv
$db->beginTransaction();
$db->exec("UPDATE ...");
$db->commit();                               // tranzaksiya millisekundlarda

Qoida: tranzaksiya ichida faqat ma'lumotlar bazasi amallari bo'lsin. Fayl yozish, HTTP so'rov, email yuborish - hammasi tashqarida.

Amaliy naqsh #

SQL

-- Xavfsiz pul o'tkazish
START TRANSACTION;

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

-- Nechta qator o'zgardi?
SELECT ROW_COUNT() AS ozgargan;

Natija

+----------+
| ozgargan |
+----------+
|        1 |
+----------+

SQL

START TRANSACTION;

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

UPDATE hisoblar SET qoldiq = qoldiq + 200000 WHERE id = 2;

COMMIT;

SELECT id, egasi, qoldiq FROM hisoblar ORDER BY id;

Natija

+----+----------+-----------+
| id | egasi    | qoldiq    |
+----+----------+-----------+
|  1 | Husanboy | 800000.00 |
|  2 | Malika   | 700000.00 |
+----+----------+-----------+

WHERE qoldiq >= summa - shartli yangilash

Bu naqsh poyga holatidan himoya qiladi.

Noto'g'ri yondashuv:

SQL

SELECT qoldiq FROM hisoblar WHERE id = 1;      -- 300000
-- kod tekshiradi: 300000 >= 200000, ha
UPDATE hisoblar SET qoldiq = 100000 WHERE id = 1;

Ikki parallel so'rov ikkalasi ham 300000 ni o'qishi va ikkalasi ham pul yechishi mumkin.

To'g'ri yondashuv - shartni UPDATE ning o'ziga qo'yish:

SQL

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

UPDATE atomar - ikkinchi so'rov qoldiq allaqachon kamayganini ko'radi va ROW_COUNT() = 0 qaytaradi.

Shuning uchun ROW_COUNT() ni albatta tekshiring:

PHP

if ($stmt->rowCount() === 0) {
    $db->rollBack();
    throw new Exception('Mablag yetarli emas');
}

Bu 12-bo'limdagi "avval tekshirib, keyin yozish" muammosining yana bir ko'rinishi.

Amaliy topshiriq

START TRANSACTION va COMMIT bilan pul o'tkazing.
ROLLBACK bilan ikkala amalni bekor qiling.
ACID ning to'rt harfini tushuntiring.
Xato tranzaksiyani avtomatik bekor qilmasligini ko'rsating.
autocommit yoqilganda ROLLBACK foydasizligini isbotlang.
Xavfli DELETE dan himoyalanishning uch usulini sanang.
SAVEPOINT bilan qisman bekor qiling.
DDL yashirin COMMIT qilishini ko'rsating.
Uzun tranzaksiyaning to'rt muammosini ayting.
WHERE qoldiq >= summa naqshini qo'llang.

Xulosa #

Tranzaksiya - bo'linmas amallar guruhi.
ACID: Atomarlik, Izchillik, Izolyatsiya, Bardoshlilik.
MariaDB da xato butun tranzaksiyani bekor qilmaydi.
Ilova kodida catch ichida rollBack majburiy.
autocommit yoqilganda ROLLBACK foydasiz.
Xavfli amaldan oldin tranzaksiya oching va ROW_COUNT() ni tekshiring.
SAVEPOINT qisman bekor qilish imkonini beradi.
DDL yashirin COMMIT qiladi - migratsiyani qaytarib bo'lmaydi.
innodb_flush_log_at_trx_commit = 1 - to'liq bardoshlilik.
Tranzaksiya ichida tashqi xizmatga murojaat qilmang.
Shartni UPDATE ning o'ziga qo'ying, kodda emas.

Keyingi bo'limda izolyatsiya darajalari va qulflarni ko'ramiz.

Klassik misol: pul o'tkazish #

COMMIT - tasdiqlash #

ROLLBACK - bekor qilish #

ACID xossalari #

Izchillik: cheklov tranzaksiyani buzadi #

Avtomatik tasdiqlash #

Saqlash nuqtalari #

DDL yashirin tasdiqlaydi #

Bardoshlilik va innodb_flush_log_at_trx_commit #

Tranzaksiya uzunligi #

Amaliy naqsh #

Xulosa #

Xatolik topdingizmi?

`COMMIT` - tasdiqlash #

`ROLLBACK` - bekor qilish #

Bardoshlilik va `innodb_flush_log_at_trx_commit` #