Konspekt · Ma'lumot va saqlash
Tranzaksiyalar
Muammo
Hamyon ilovasi pulni bir hisobdan boshqasiga o'tkazadi. Tranzaksiyasiz nima buziladi, ikki server bir vaqtda yozganda tranzaksiya bilan ham nima buziladi va har holatni qaysi izolyatsiya darajasi yoki qulf tuzatadi.
Nimani o'rganasiz
- Nega
BEGIN/COMMITsiz ikkiUPDATEpulni yo'qotishi mumkin - Dirty read, lost update va write skew — haqiqiy ketma-ketlikda
READ COMMITTED,REPEATABLE READvaSERIALIZABLEnimadan himoya qiladiSELECT ... FOR UPDATEva optimistikversionustuni
Diagramma kod sifatida
Darsdagi yakuniy sxemani matn ko'rinishida oling va Jira, GitLab, GitHub, Notion yoki hujjatga qo'ying: Mermaid u yerlarda o'zi chiziladi. D2 va PlantUML (Confluence, IntelliJ) uchun alohida variantlar bor.
Mermaid: sxema
flowchart LR
n_app(["Hamyon ilovasi"])
n_api["API 1"]
n_db[("Baza")]
n_report("Hisobot job'i")
n_api2["API 2"]
n_fraud>"Firibgarlik tekshiruvi"]
n_app --> n_api
n_api --> n_db
n_report --> n_db
n_app --> n_api2
n_api2 -.->|"qayta urinish"| n_db
n_api --> n_fraudMermaid: so'rovlar ketma-ketligi
sequenceDiagram
participant n_api as API 1
participant n_db as Baza
participant n_report as Hisobot job'i
participant n_api2 as API 2
Note over n_api,n_db: O'tkazma — bu ikki yozuv
n_api->>n_db: UPDATE accounts SET balance = balance - 50000
n_db-->>n_api: UPDATE 1
Note over n_api,n_db: Server ikki yozuv orasida o'ldi
n_api-xn_db: 10:41:07.112 UPDATE acc_42 -50000 ok
Note over n_api,n_db: BEGIN ... COMMIT: yo hammasi, yo hech narsa
n_api->>n_db: BEGIN#59;
n_db-->>n_api: COMMIT
Note over n_api,n_db: Qoidalarni baza qo'riqlaydi
n_api->>n_db: BEGIN#59;
n_db-->>n_api: ERROR: new row violates check constraint #34;balance_nonneg#34;
Note over n_report,n_db: Birovning qoralamasini o'qish
n_report->>n_db: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED#59;
n_db-->>n_report: 9950000
Note over n_api2,n_db: Ikki server, bitta balans
n_api2->>n_db: READ COMMITTED: two payments from acc_42
Note over n_api,n_db: O'qiyotganda qatorni qulfla
n_api->>n_db: BEGIN#59;
n_db-->>n_api: T2 then reads 150000 and writes 120000
Note over n_api2,n_db: Tarmoq chaqiruvi davomida ushlangan qulf
n_api2->>n_db: LOG: process 4121 still waiting for ShareLock
Note over n_api,n_db: Optimistik: versiyani yozish paytida tekshir
n_api->>n_db: SELECT balance, version FROM accounts
n_db-->>n_api: UPDATE 0
Note over n_api2,n_db: Ikki tekshiruv o'tdi, qoida buzildi
n_api2->>n_db: REPEATABLE READ: u_42 may spend 100000 a day
Note over n_api2,n_db: SERIALIZABLE: go'yo birma-bir
n_api2->>n_db: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE#59;
n_db-->>n_api2: ERROR: could not serialize accessD2
direction: right
n_app: "Hamyon ilovasi" {
shape: rectangle
}
n_api: "API 1" {
shape: rectangle
}
n_db: "Baza" {
shape: cylinder
}
n_report: "Hisobot job'i" {
shape: rectangle
style.multiple: true
}
n_api2: "API 2" {
shape: rectangle
}
n_fraud: "Firibgarlik tekshiruvi" {
shape: cloud
}
n_app -> n_api
n_api -> n_db
n_report -> n_db
n_app -> n_api2
n_api2 -> n_db: "qayta urinish" {
style.stroke-dash: 3
}
n_api -> n_fraudPlantUML: so'rovlar ketma-ketligi
@startuml
participant "API 1" as n_api
database "Baza" as n_db
participant "Hisobot job'i" as n_report
participant "API 2" as n_api2
note over n_api, n_db : O'tkazma — bu ikki yozuv
n_api -> n_db : UPDATE accounts SET balance = balance - 50000
n_db --> n_api : UPDATE 1
note over n_api, n_db : Server ikki yozuv orasida o'ldi
n_api ->x n_db : [yo'qoldi] 10:41:07.112 UPDATE acc_42 -50000 ok
note over n_api, n_db : BEGIN ... COMMIT: yo hammasi, yo hech narsa
n_api -> n_db : BEGIN;
n_db --> n_api : COMMIT
note over n_api, n_db : Qoidalarni baza qo'riqlaydi
n_api -> n_db : BEGIN;
n_db --> n_api : ERROR: new row violates check constraint "balance_nonneg"
note over n_report, n_db : Birovning qoralamasini o'qish
n_report -> n_db : SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
n_db --> n_report : 9950000
note over n_api2, n_db : Ikki server, bitta balans
n_api2 -> n_db : READ COMMITTED: two payments from acc_42
note over n_api, n_db : O'qiyotganda qatorni qulfla
n_api -> n_db : BEGIN;
n_db --> n_api : T2 then reads 150000 and writes 120000
note over n_api2, n_db : Tarmoq chaqiruvi davomida ushlangan qulf
n_api2 -> n_db : LOG: process 4121 still waiting for ShareLock
note over n_api, n_db : Optimistik: versiyani yozish paytida tekshir
n_api -> n_db : [qayta urinish] SELECT balance, version FROM accounts
n_db --> n_api : UPDATE 0
note over n_api2, n_db : Ikki tekshiruv o'tdi, qoida buzildi
n_api2 -> n_db : REPEATABLE READ: u_42 may spend 100000 a day
note over n_api2, n_db : SERIALIZABLE: go'yo birma-bir
n_api2 -> n_db : [qayta urinish] SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
n_db --> n_api2 : ERROR: could not serialize access
@endumlPlantUML: komponentlar
@startuml
left to right direction
agent "Hamyon ilovasi" as n_app
component "API 1" as n_api
database "Baza" as n_db
collections "Hisobot job'i" as n_report
component "API 2" as n_api2
cloud "Firibgarlik tekshiruvi" as n_fraud
n_app --> n_api
n_api --> n_db
n_report --> n_db
n_app --> n_api2
n_api2 ..> n_db : qayta urinish
n_api --> n_fraud
@endumlXulosa
Pul yozish uchun besh qoida.
1) Bir-biriga tegishli bir nechta yozuv bitta tranzaksiyada. 2) Qoidalar bazada cheklov sifatida yashaydi. 3) O'qib-yozishga FOR UPDATE, version tekshiruvi yoki bitta atomar so'rov kerak. 4) Tarmoq chaqiruvi davomida qulf ushlamang. 5) Bir necha qatorga oid qoidalarga SERIALIZABLE kerak va har 40001 to'liq qayta urinish oladi.
Ko'rsatkichlar: boshida va oxirida
| Ko'rsatkich | Boshida | Oxirida |
|---|---|---|
| Noto'g'ri balans | 0/kun | 0/kun |
| O'tkazma p95 | 20 ms | 320 ms |
| Qayta urinish | 0/daq | 9/daq |
Qadamlar
- Holat O'tkazma — bu ikki yozuv
Foydalanuvchi
acc_42hisobidanacc_77ga 50 000 yuboradi. - Muammo Server ikki yozuv orasida o'ldi
Birinchi
UPDATEsaqlandi. - Yechim
BEGIN...COMMIT: yo hammasi, yo hech narsaEndi ikki so'rov bitta tranzaksiya ichida.
- Holat Qoidalarni baza qo'riqlaydi
Xato kod 200 000 bor hisobdan 900 000 olmoqchi bo'ladi.
- Holat Birovning qoralamasini o'qish
Tungi hisobot barcha balanslarni qo'shadi.
- Muammo Ikki server, bitta balans
Yuk oshdi, endi API serverlari ikkita.
- Yechim O'qiyotganda qatorni qulfla
Endi o'qish qator qulfini oladi: `SELECT ...
- Muammo Tarmoq chaqiruvi davomida ushlangan qulf
O'tkazma o'rtasiga firibgarlik tekshiruvi qo'shildi: qatorni qulfla, tashqi servisni chaqir, keyin yoz.
- Yechim Optimistik: versiyani yozish paytida tekshir
Qatorda endi
versionustuni bor. - Muammo Ikki tekshiruv o'tdi, qoida buzildi
Yangi qoida: foydalanuvchi kuniga ko'pi bilan 100 000 yecha oladi.
- Yechim
SERIALIZABLE: go'yo birma-birYechish endi
SERIALIZABLEdarajasida ishlaydi. - Xulosa Xulosa
Pul yozish uchun besh qoida.
Atamalar
- Tranzaksiya
- Bir-biriga tegishli bir nechta yozuvni BEGIN ... COMMIT ichida bajarish: yo hammasi saqlanadi, yo hech biri. Server o'rtada o'lsa ham yarim yozuv qolmaydi.
- Izolyatsiya darajasi
- Parallel tranzaksiyalar bir-birining o'zgarishini qanchalik ko'rishini belgilaydi: READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Daraja qanchalik yuqori bo'lsa, shuncha ko'p anomaliya yopiladi, lekin qayta urinish ko'payadi.
- Dirty read
- Boshqa tranzaksiyaning hali commit qilinmagan qoralamasini o'qish. Agar u rollback bo'lsa, o'qigan tomon hech qachon bo'lmagan ma'lumotdan foydalangan bo'ladi.
- Optimistik qulf (version)
- Qatorni oldindan qulflash o'rniga yozish paytida version ustuni o'zgarmaganini tekshirish. Agar boshqa yozuv oldinroq o'tgan bo'lsa, update hech narsani o'zgartirmaydi va amal qayta urinadi.