System Design

ACID xususiyatlari

ACID — database transactionlarining to’g’ri ishlashini ta’minlaydigan 4 ta asosiy xususiyat.

Transaction nima?

Transaction — bir nechta operatsiyalarni bitta “atom” sifatida bajarish:

BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Yoki hammasi bajariladi, yoki hech narsa.

A - Atomicity (Ajralmaslik)

Transaction ichidagi barcha operatsiyalar hammasi yoki hech biri bajariladi.

Misol: Pul o’tkazish

-- Account A'dan $100 yechish
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

--  Server crash!

-- Account B'ga $100 qo'shish (bajarilmadi!)
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

Muammo: $100 yo’qoldi!

ACID Atomicity yechimi

BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- Bu yerda crash bo'lsa, ikkala o'zgarish ham rollback

Agar crash bo’lsa: Database avtomatik rollback qiladi, hech narsa o’zgarmaydi.

Qanday ishlaydi?

Write-Ahead Log (WAL):

1. Transaction boshlanadi
2. O'zgarishlar log'ga yoziladi (disk)
3. O'zgarishlar database'ga qo'llaniladi (memory)
4. COMMIT: "Bu transaction tayyor" (log'ga yoziladi)

Crash vaqtida: Log’dan recovery qilinadi.

C - Consistency (Izchillik)

Database har doim valid holatda bo’lishi kerak.

Misol: Constraint

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  balance INTEGER CHECK (balance >= 0) -- Manfiy bo'lmasligi kerak
);

Transaction:

BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  -- Agar balance < 0 bo'lsa, transaction abort! 
COMMIT;

Consistency ta’minlaydi:

Application-level consistency

// Balance yetarli ekanligini tekshirish
const account = await db.query('SELECT balance FROM accounts WHERE id = 1');
if (account.balance < 100) {
  throw new Error('Insufficient funds');
}
await db.query('UPDATE accounts SET balance = balance - 100 WHERE id = 1');

Muammo: Ikkita client bir vaqtda balance tekshirsa? Race condition!

Yechim: Database-level constraint yoki locking.

I - Isolation (Izolyatsiya)

Parallel transaction’lar bir-biriga ta’sir qilmasligi kerak.

Muammo: Race condition

Transaction A:                Transaction B:
READ balance = $100
                              READ balance = $100
WITHDRAW $50
WRITE balance = $50
                              WITHDRAW $30
                              WRITE balance = $70  (should be $20)

Natija: Ikkalasi ham $100 ko’rdi, $80 yechildi, lekin balance $70! Lost update

Isolation Levels

1. Read Uncommitted (Eng past)

-- Transaction A
BEGIN;
UPDATE accounts SET balance = 500 WHERE id = 1;
-- (hali COMMIT qilmadi)

-- Transaction B
SELECT balance FROM accounts WHERE id = 1; 
-- 500 ko'radi (uncommitted data!) Dirty Read

Muammo: Dirty read - commit qilinmagan ma’lumot.

2. Read Committed (Default ko’pincha)

-- Transaction A
BEGIN;
UPDATE accounts SET balance = 500 WHERE id = 1;

-- Transaction B
SELECT balance FROM accounts WHERE id = 1;
-- Eski qiymatni ko'radi (100) 
-- Transaction A COMMIT qilsa, 500 ko'radi

Yaxshi: Dirty read yo’q
Muammo: Non-repeatable read

-- Transaction B
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 100
-- Transaction A commits: balance = 500
SELECT balance FROM accounts WHERE id = 1; -- 500 (o'zgargan!)

3. Repeatable Read

-- Transaction B
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 100
-- Transaction A commits: balance = 500
SELECT balance FROM accounts WHERE id = 1; -- 100 (o'zgarmagan) 
COMMIT;

Yaxshi: Bir xil transaction ichida consistent
Muammo: Phantom reads (yangi rows)

4. Serializable (Eng yuqori)

Transaction’lar ketma-ket (serial) bajarilayotgandek ishlaydi.

-- Transaction A va B parallel, lekin natija serial kabi

Afzalliklari: To’liq isolation
Kamchiliklari: Sekin, deadlock ko’p

Isolation level tanlash

-- PostgreSQL
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- MySQL
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
LevelDirty ReadNon-repeatablePhantomPerformance
Read Uncommitted
Read Committed
Repeatable Read
Serializable

Default:

D - Durability (Doimiylik)

COMMIT qilingan transaction hech qachon yo’qolmaydi.

Misol: Server crash

BEGIN TRANSACTION;
  UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT; -- Success message

--  Server crash (1 sekund keyin)

ACID Durability: Server qayta ishga tushganda balance = 500

Qanday ta’minlanadi?

1. Write-Ahead Log (WAL):

COMMIT qilishdan oldin:
1. O'zgarishlar log'ga yoziladi (fsync - diskka to'liq yozish)
2. COMMIT logged
3. Keyin in-memory data yangilanadi

Crash vaqtida: Log’dan recovery.

2. Checkpoints:

Har 5 minutda:
- Memory'dagi o'zgarishlar diskka yoziladi
- Log tozalanadi

3. Replication:

Master → Replica 1
      → Replica 2
      → Replica 3

Master crash bo’lsa → replica’dan recover

Real-world trade-offs

Performance vs ACID

Strict ACID (slow):

BEGIN;
  UPDATE ... ;
COMMIT; -- Wait for fsync (10ms)

Throughput: 100 TPS

Relaxed (fast):

UPDATE ... ; -- No transaction, no fsync wait

Throughput: 10,000 TPS

PostgreSQL fsync

-- postgresql.conf
fsync = on  # ACID durability (default)
fsync = off # Performance  (crash = data loss )

Production: Always fsync = on
Testing: fsync = off OK

Eventual consistency alternative

NoSQL databases (Cassandra, DynamoDB):

ACID: Consistency hozir
BASE: Consistency keyinroq (eventual)

Trade-off: Speed uchun immediate consistency qurbon qilish.

ACID misollar

Banking (ACID shart)

-- Pul o'tkazish
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 2, 100);
COMMIT;

Nega ACID: Pul yo’qolishi mumkin emas!

E-commerce inventory

-- Mahsulot sotish
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  SELECT stock FROM products WHERE id = 123 FOR UPDATE; -- Lock
  -- stock = 5
  IF stock > 0 THEN
    UPDATE products SET stock = stock - 1 WHERE id = 123;
    INSERT INTO orders (...) VALUES (...);
  END IF;
COMMIT;

FOR UPDATE: Row lock - parallel transactionlar kutadi.

Social media (ACID kerak emas)

// Tweet yaratish - eventual consistency OK
await db.tweets.insert({ text: 'Hello', userId: 123 });
// Agar biror kishi 1-2 sekund kechikib ko'rsa muammo yo'q

Xulosa

ACID:

Qachon kerak:

Qachon kerak emas:

Keyingi dars: SQL vs NoSQL - qaysi birini tanlash.