mufassall

Konspekt · Ma'lumot va saqlash

Tranzaksiyalar

14 daq · O'rta · mufassall.dtwin.uz/uz/l/transactions/

← Darsni ochish

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 ikki UPDATE pulni yo'qotishi mumkin
  • Dirty read, lost update va write skew — haqiqiy ketma-ketlikda
  • READ COMMITTED, REPEATABLE READ va SERIALIZABLE nimadan himoya qiladi
  • SELECT ... FOR UPDATE va optimistik version ustuni
Yakuniy sahna
25%

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_fraud

Mermaid: 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 access

D2

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_fraud

PlantUML: 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
@enduml

PlantUML: 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
@enduml

Xulosa

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'rsatkichBoshidaOxirida
Noto'g'ri balans0/kun0/kun
O'tkazma p9520 ms320 ms
Qayta urinish0/daq9/daq

Qadamlar

  1. Holat O'tkazma — bu ikki yozuv

    Foydalanuvchi acc_42 hisobidan acc_77 ga 50 000 yuboradi.

  2. Muammo Server ikki yozuv orasida o'ldi

    Birinchi UPDATE saqlandi.

  3. Yechim BEGIN ... COMMIT: yo hammasi, yo hech narsa

    Endi ikki so'rov bitta tranzaksiya ichida.

  4. Holat Qoidalarni baza qo'riqlaydi

    Xato kod 200 000 bor hisobdan 900 000 olmoqchi bo'ladi.

  5. Holat Birovning qoralamasini o'qish

    Tungi hisobot barcha balanslarni qo'shadi.

  6. Muammo Ikki server, bitta balans

    Yuk oshdi, endi API serverlari ikkita.

  7. Yechim O'qiyotganda qatorni qulfla

    Endi o'qish qator qulfini oladi: `SELECT ...

  8. Muammo Tarmoq chaqiruvi davomida ushlangan qulf

    O'tkazma o'rtasiga firibgarlik tekshiruvi qo'shildi: qatorni qulfla, tashqi servisni chaqir, keyin yoz.

  9. Yechim Optimistik: versiyani yozish paytida tekshir

    Qatorda endi version ustuni bor.

  10. Muammo Ikki tekshiruv o'tdi, qoida buzildi

    Yangi qoida: foydalanuvchi kuniga ko'pi bilan 100 000 yecha oladi.

  11. Yechim SERIALIZABLE: go'yo birma-bir

    Yechish endi SERIALIZABLE darajasida ishlaydi.

  12. 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.