Izolyatsiya darajalari: ikkita psql oynasida beshta anomaliya va yo'qolgan 50 000 so'm
Ikkala so'rov ham COMMIT bo'ldi, xato yo'q, lekin pulning bir qismi yo'qoldi. Dirty read'dan write skew'gacha har bir anomaliyani ikki psql sessiyasida takrorlaymiz va Django'da serialization xatosini to'g'ri qayta urinamiz.
Tasavvur qiling: hamyonda 100 000 so'm bor. Aynan bir vaqtda ikkita so'rov keladi. Birinchisi to'lov provayderining webhook'i: hamyonga 50 000 so'm tushdi. Ikkinchisi foydalanuvchining xaridi: 30 000 so'm yechilishi kerak. Ikkala handler ham balansni o'qiydi, ikkalasi ham 100 000 ni ko'radi. Biri 150 000 ni yozadi, ikkinchisi 70 000 ni. Kim oxirgi yozsa, o'shaniki qoladi. Agar 70 000 qolsa, 50 000 so'm izsiz yo'qoldi: xato yo'q, log'da exception yo'q, hamma narsa COMMIT bo'lgan.
Ko'pchilik bunday holatda "axir hammasi transaction.atomic() ichida edi-ku" deydi. Gap shundaki, transaction faqat "hammasi yoki hech narsa" (atomicity) ni kafolatlaydi. Parallel transaction'lar bir-birining o'zgarishlarini qanchalik ko'rishini esa izolyatsiya darajasi belgilaydi. PostgreSQL'da default daraja — Read Committed, va u yuqoridagi holatdan sizni himoya qilmaydi.
Bu maqolada beshta klassik anomaliyani ikkita psql oynasida qo'lda takrorlaymiz, PostgreSQL har bir darajada nima qilishini ko'ramiz va Django'da izolyatsiyani sozlash hamda serialization xatosida transaction'ni qayta urinishni yozamiz.
Tajriba uchun tayyorgarlik
Ikkita terminal oching, ikkalasida ham psql bilan bitta bazaga ulaning. Birini A sessiya, ikkinchisini B sessiya deb ataymiz. Misollardagi buyruqlarni aynan ko'rsatilgan tartibda, navbatma-navbat bajaring. Avval jadvallarni yaratamiz:
CREATE TABLE accounts (
id int PRIMARY KEY,
owner text NOT NULL,
balance numeric(12, 2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Ali', 100), (2, 'Vali', 100);
CREATE TABLE orders (
id serial PRIMARY KEY,
status text NOT NULL
);
INSERT INTO orders (status) VALUES ('new'), ('new'), ('paid');
CREATE TABLE doctors (
id int PRIMARY KEY,
name text NOT NULL,
shift_id int NOT NULL,
on_call boolean NOT NULL
);
INSERT INTO doctors VALUES (1, 'Dilnoza', 1, true), (2, 'Jasur', 1, true);
Har bir misol oldingisi qoldirgan holatdan davom etadi, shuning uchun balans qiymatlari ketma-ket o'zgarib boradi.
Izolyatsiya qanday ishlaydi: snapshot va MVCC
PostgreSQL MVCC (multi-version concurrency control) asosida ishlaydi. UPDATE qatorni joyida o'zgartirmaydi: qatorning yangi versiyasini yozadi, eskisini esa "falon transaction tomonidan o'chirildi" deb belgilaydi. Har bir qator versiyasida uni yaratgan (xmin) va o'chirgan (xmax) transaction ID'lari saqlanadi.
So'rov bajarilayotganda PostgreSQL snapshot oladi: shu paytda qaysi transaction'lar commit bo'lgan, qaysilari hali ishlayapti. Qator versiyasi faqat uni yaratgan transaction snapshot olingan paytda commit bo'lgan bo'lsa ko'rinadi. Izolyatsiya darajalarining asosiy farqi — snapshot qachon olinishida:
- Read Committed: har bir statement boshida yangi snapshot.
- Repeatable Read: butun transaction uchun bitta snapshot, u birinchi so'rovda olinadi (
BEGINpaytida emas). - Serializable: Repeatable Read snapshot'i ustiga SSI (Serializable Snapshot Isolation) — transaction'lar orasidagi xavfli o'qish/yozish bog'liqliklarini kuzatuvchi mexanizm.
O'qish hech qachon yozishni bloklamaydi, yozish ham o'qishni bloklamaydi. Ammo ikkita transaction bitta qatorni o'zgartirmoqchi bo'lsa, ikkinchisi birinchisining row lock'ini kutadi. Kutish tugagach nima bo'lishi ham darajaga bog'liq — buni pastda ko'ramiz.
1. Dirty read: PostgreSQL'da yo'q
Dirty read — boshqa transaction'ning hali commit qilinmagan o'zgarishini o'qish. SQL standarti Read Uncommitted darajasida bunga ruxsat beradi, lekin PostgreSQL'da Read Uncommitted xuddi Read Committed kabi ishlaydi:
-- Session A
BEGIN;
UPDATE accounts SET balance = 0 WHERE id = 1;
-- not committed yet
-- Session B
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- 100.00: B does not see A's uncommitted change
COMMIT;
-- Session A
ROLLBACK;
MVCC'da commit qilinmagan versiya boshqa snapshot'lar uchun shunchaki ko'rinmaydi, shuning uchun PostgreSQL'da dirty read'ni hech qanday daraja bilan "yoqib" bo'lmaydi.
2. Non-repeatable read: bir xil so'rov, ikki xil javob
Read Committed'da har bir statement o'z snapshot'ini oladi. Demak, bitta transaction ichida bir qatorni ikki marta o'qisangiz, oradagi commit'ni ko'rasiz:
-- Session A
BEGIN; -- READ COMMITTED (default)
SELECT balance FROM accounts WHERE id = 1;
-- 100.00
-- Session B (autocommit)
UPDATE accounts SET balance = 40 WHERE id = 1;
-- Session A
SELECT balance FROM accounts WHERE id = 1;
-- 40.00: same query, same transaction, different answer
COMMIT;
Bu ko'pincha zararsiz, lekin hisobot yoki validatsiya bir nechta so'rovdan iborat bo'lsa, ma'lumotlar bir-biriga mos kelmay qoladi: birinchi so'rov bir holatni, ikkinchisi boshqasini ko'radi. Repeatable Read'da esa butun transaction bitta snapshot bilan ishlaydi:
-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- 40.00 (the snapshot is taken here, at the first query, not at BEGIN)
-- Session B (autocommit)
UPDATE accounts SET balance = 100 WHERE id = 1;
-- Session A
SELECT balance FROM accounts WHERE id = 1;
-- still 40.00
COMMIT;
SELECT balance FROM accounts WHERE id = 1;
-- 100.00: new transaction, new snapshot
E'tibor bering: snapshot BEGIN da emas, birinchi so'rovda olinadi. BEGIN dan keyin bir necha soniya "hech narsa qilmay" turish snapshot'ni muzlatmaydi.
3. Phantom read: yangi qatorlar paydo bo'ladi
Phantom — bir xil shartli so'rovni qayta bajarganda yangi (yoki yo'qolgan) qatorlarni ko'rish. Read Committed'da bu ham bo'ladi:
-- Session A
BEGIN; -- READ COMMITTED
SELECT count(*) FROM orders WHERE status = 'new';
-- 2
-- Session B (autocommit)
INSERT INTO orders (status) VALUES ('new');
-- Session A
SELECT count(*) FROM orders WHERE status = 'new';
-- 3: a "phantom" row appeared
COMMIT;
SQL standarti Repeatable Read'da phantom'ga ruxsat beradi, lekin PostgreSQL'ning Repeatable Read'i snapshot'ga asoslangani uchun phantom'ni ham ko'rsatmaydi: yuqoridagi misolni BEGIN ISOLATION LEVEL REPEATABLE READ; bilan takrorlasangiz, ikkinchi count(*) ham 2 qaytaradi.
4. Lost update: maqola boshidagi muammo
Endi eng xavflisi. Ilova balansni o'qiydi, Python'da yangi qiymatni hisoblaydi va uni yozadi (read-modify-write). Read Committed'da:
-- Session A
BEGIN; -- READ COMMITTED
SELECT balance FROM accounts WHERE id = 1; -- 100.00, app computes 100 + 50
-- Session B
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 100.00, app computes 100 - 30
-- Session A
UPDATE accounts SET balance = 150 WHERE id = 1;
COMMIT;
-- Session B
UPDATE accounts SET balance = 70 WHERE id = 1;
COMMIT;
SELECT balance FROM accounts WHERE id = 1; -- 70.00: A's +50 is gone
B sessiyaning UPDATE'i hech qanday xato bermadi. A'ning o'zgarishi ustidan yozib yuborildi. Repeatable Read'da esa PostgreSQL buni payqaydi: agar transaction o'zgartirmoqchi bo'lgan qatorni snapshot olinganidan keyin boshqa transaction o'zgartirib, commit qilgan bo'lsa, xato beradi:
-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- 70.00
-- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- 70.00
-- Session A
UPDATE accounts SET balance = 120 WHERE id = 1;
COMMIT;
-- Session B
UPDATE accounts SET balance = 40 WHERE id = 1;
-- ERROR: could not serialize access due to concurrent update
ROLLBACK;
Bu xatoning SQLSTATE kodi 40001 (serialization_failure). Ma'nosi: "bu transaction'ni boshidan qayta bajaring". Agar A hali commit qilmagan bo'lsa, B'ning UPDATE'i kutib turadi: A commit qilsa — xato, rollback qilsa — B davom etadi.
Lost update'dan qutulish uchun Repeatable Read shart emas. Read Committed'da UPDATE boshqacha ishlaydi: qator lock qilingan bo'lsa kutadi, so'ng qatorning eng oxirgi commit qilingan versiyasini oladi, WHERE shartini unga qaytadan tekshiradi va ifodani o'sha versiya ustida hisoblaydi. Shuning uchun o'qish va yozish bitta statement'da bo'lsa, hammasi to'g'ri ishlaydi:
-- Safe even under READ COMMITTED: the read and the write are one statement
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- Withdrawal with a check, also one statement
UPDATE accounts SET balance = balance - 30
WHERE id = 1 AND balance >= 30;
-- "UPDATE 0" means insufficient funds
5. Write skew: Repeatable Read ham ojiz
Write skew eng ayyor anomaliya. Ikki transaction bir xil ma'lumotni o'qiydi, shunga qarab qaror qiladi va turli qatorlarni o'zgartiradi. Hech qaysi qator ikki marta yozilmagani uchun Repeatable Read hech qanday konfliktni ko'rmaydi.
Qoida: smenada kamida bitta shifokor navbatchi bo'lishi kerak. Dilnoza va Jasur bir vaqtda "navbatdan chiqish" tugmasini bosadi:
-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE shift_id = 1 AND on_call; -- 2, so I can leave
-- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE shift_id = 1 AND on_call; -- 2, so I can leave too
-- Session A
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT;
-- Session B
UPDATE doctors SET on_call = false WHERE id = 2;
COMMIT; -- succeeds, no error
SELECT count(*) FROM doctors WHERE shift_id = 1 AND on_call; -- 0
Har bir transaction o'z snapshot'ida mutlaqo to'g'ri ish qildi, lekin natijada qoida buzildi: navbatchi shifokor qolmadi. Pul tizimlarida xuddi shu narsa "ikki hisobning umumiy balansi manfiy bo'lmasin" yoki "bir foydalanuvchiga bitta aktiv promokod" kabi qoidalarda yuz beradi.
Serializable darajada esa PostgreSQL bu bog'liqlikni ushlaydi:
UPDATE doctors SET on_call = true; -- reset
-- Session A
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE shift_id = 1 AND on_call; -- 2
-- Session B
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE shift_id = 1 AND on_call; -- 2
-- Session A
UPDATE doctors SET on_call = false WHERE id = 1;
-- Session B
UPDATE doctors SET on_call = false WHERE id = 2;
-- Session A
COMMIT; -- succeeds
-- Session B
COMMIT;
-- ERROR: could not serialize access due to read/write dependencies among transactions
-- DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
-- HINT: The transaction might succeed if retried.
SSI qanday ishlaydi? Serializable transaction o'qigan ma'lumot uchun predicate lock (SIRead lock) qo'yadi. Bu lock hech kimni bloklamaydi, faqat "men buni o'qidim" degan belgi. Agar boshqa transaction shu ma'lumotni o'zgartirsa, ular orasida rw-bog'liqlik qayd etiladi. Ketma-ket ikkita shunday bog'liqlik (A → B → C, bunda A va C bir xil transaction bo'lishi ham mumkin) xavfli tuzilma hisoblanadi va PostgreSQL ulardan birini 40001 bilan bekor qiladi. Xato UPDATE paytida ham, COMMIT paytida ham chiqishi mumkin, shuning uchun kodda ikkalasiga tayyor turish kerak.
Taqqoslash jadvali
| Anomaliya | Read Committed | Repeatable Read | Serializable |
|---|---|---|---|
| Dirty read | yo'q | yo'q | yo'q |
| Non-repeatable read | bor | yo'q | yo'q |
| Phantom read | bor | yo'q (PostgreSQL'da) | yo'q |
| Lost update (read-modify-write) | bor | yo'q, 40001 xato |
yo'q, 40001 xato |
| Write skew | bor | bor | yo'q, 40001 xato |
Yuqori izolyatsiya darajasi anomaliyani "jimgina tuzatmaydi" — uni xatoga aylantiradi. Bu xatoni ushlab, transaction'ni qayta urinish sizning kodingizning vazifasi.
Django'da: avval to'g'ri so'rov, keyin izolyatsiya
Misollar uchun modellar:
# models.py
from django.db import models
class Account(models.Model):
owner = models.CharField(max_length=100)
balance = models.DecimalField(max_digits=12, decimal_places=2)
class Doctor(models.Model):
name = models.CharField(max_length=100)
shift_id = models.IntegerField()
on_call = models.BooleanField(default=True)
Lost update'ning Django'dagi klassik ko'rinishi:
# Wrong: read-modify-write in Python, a lost update under READ COMMITTED
def deposit(account_id, amount):
account = Account.objects.get(pk=account_id)
account.balance += amount
account.save(update_fields=["balance"])
Ko'p hollarda daraja o'zgartirish shart emas — o'qish va yozishni bitta UPDATE ga birlashtirish yoki qatorni oldindan lock qilish yetarli:
# services.py
from decimal import Decimal
from django.db import transaction
from django.db.models import F
from .models import Account
class InsufficientFunds(Exception):
pass
def deposit(account_id: int, amount: Decimal) -> None:
# UPDATE accounts SET balance = balance + %s WHERE id = %s
Account.objects.filter(pk=account_id).update(balance=F("balance") + amount)
def withdraw(account_id: int, amount: Decimal) -> None:
updated = Account.objects.filter(pk=account_id, balance__gte=amount).update(
balance=F("balance") - amount
)
if updated == 0:
raise InsufficientFunds(account_id)
def withdraw_with_fee(account_id: int, amount: Decimal, fee: Decimal) -> None:
# When the logic does not fit into one UPDATE: lock the row first
with transaction.atomic():
account = Account.objects.select_for_update().get(pk=account_id)
total = amount + fee
if account.balance < total:
raise InsufficientFunds(account_id)
account.balance -= total
account.save(update_fields=["balance"])
F() ifodasi UPDATE ... SET balance = balance + ... ga aylanadi, select_for_update() esa SELECT ... FOR UPDATE ga. Ikkinchi holatda parallel transaction birinchisi tugaguncha SELECT da kutib turadi.
Django'da izolyatsiya darajasini sozlash
Django PostgreSQL bilan default holatda Read Committed'da ishlaydi. Butun ulanish uchun darajani OPTIONS orqali o'zgartirish mumkin:
# settings.py
import os
from django.db.backends.postgresql.psycopg_any import IsolationLevel
DATABASES = {
"default": {
"ENGINE": "django.db.backends.postgresql",
"NAME": "shop",
"USER": "shop",
"PASSWORD": os.environ["DB_PASSWORD"],
"HOST": "localhost",
"PORT": "5432",
"OPTIONS": {
# default is READ COMMITTED; change it only if you mean it
"isolation_level": IsolationLevel.REPEATABLE_READ,
},
}
}
Lekin butun loyihani Serializable'ga o'tkazish kamdan-kam to'g'ri qaror: endi har qanday yozuvchi transaction 40001 olishi mumkin va har birini qayta urinish kerak bo'ladi. Odatda Serializable faqat bir nechta kritik operatsiya uchun kerak. Django'da atomic() ga izolyatsiya darajasini beradigan parametr yo'q, shuning uchun transaction ichidagi birinchi statement sifatida SET TRANSACTION ISOLATION LEVEL ni yuboramiz va shu joyning o'zida retry logikasini yozamiz:
# db.py
import functools
import random
import time
from django.db import OperationalError, connection, transaction
# 40001 = serialization_failure, 40P01 = deadlock_detected
RETRYABLE_SQLSTATES = {"40001", "40P01"}
def _sqlstate(exc: Exception) -> str | None:
cause = exc.__cause__ # the original psycopg exception
# psycopg 3 uses .sqlstate, psycopg2 uses .pgcode
return getattr(cause, "sqlstate", None) or getattr(cause, "pgcode", None)
def serializable(retries: int = 5, base_delay: float = 0.02):
"""Run the function in its own SERIALIZABLE transaction, retrying on 40001/40P01."""
def decorator(func):
@functools.wraps(func)
def wrapper(*args, **kwargs):
if connection.in_atomic_block:
raise RuntimeError(
f"{func.__name__}() must start the outermost transaction"
)
for attempt in range(retries + 1):
try:
with transaction.atomic():
with connection.cursor() as cursor:
# must be the first statement of the transaction
cursor.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
return func(*args, **kwargs)
except OperationalError as exc:
if _sqlstate(exc) not in RETRYABLE_SQLSTATES or attempt == retries:
raise
# exponential backoff with jitter
time.sleep(base_delay * (2**attempt) * random.uniform(0.5, 1.5))
return wrapper
return decorator
Bu yerda bir nechta muhim nuqta bor:
- Retry butun transaction atrofida bo'lishi shart. Transaction ichida xatoni ushlab, faqat oxirgi statement'ni qaytarish ma'nosiz: PostgreSQL transaction'ni allaqachon aborted holatga o'tkazgan.
- Shu sababli dekorator tashqi
atomic()ichida chaqirilsa, xato beradi. Ichma-ichatomic()faqat savepoint yaratadi,SET TRANSACTIONesa u yerda ishlamaydi. - Django psycopg xatosini
django.db.OperationalErrorga o'raydi, asl xato__cause__da qoladi — SQLSTATE'ni o'shandan olamiz. - Kutish vaqti har urinishda oshadi va tasodifiy qo'shimcha (jitter) oladi: aks holda to'qnashgan ikki so'rov yana bir vaqtda qayta urinib, yana to'qnashadi.
Write skew misoli Django'da:
# services.py
from django.db import transaction
from .db import serializable
from .models import Doctor
class LastDoctorError(Exception):
pass
@serializable()
def go_off_call(doctor_id: int, shift_id: int) -> None:
on_call = Doctor.objects.filter(shift_id=shift_id, on_call=True).count()
if on_call < 2:
raise LastDoctorError("at least one doctor must stay on call")
Doctor.objects.filter(pk=doctor_id).update(on_call=False)
# Alternative without SERIALIZABLE: lock the rows the decision depends on
def go_off_call_locked(doctor_id: int, shift_id: int) -> None:
with transaction.atomic():
doctors = list(
Doctor.objects.select_for_update()
.filter(shift_id=shift_id, on_call=True)
.order_by("pk") # same lock order everywhere, fewer deadlocks
)
if len(doctors) < 2:
raise LastDoctorError("at least one doctor must stay on call")
Doctor.objects.filter(pk=doctor_id).update(on_call=False)
Ikkala shifokor bir vaqtda go_off_call() ni chaqirsa, biri muvaffaqiyatli tugaydi, ikkinchisi 40001 oladi, qayta urinadi, endi yangi snapshot'da 1 ta navbatchini ko'radi va LastDoctorError bilan to'xtaydi. Ikkinchi variant (select_for_update) bu misolda ham ishlaydi, chunki qaror aniq mavjud qatorlarga bog'liq. Agar qaror "shunday qator yo'q" degan faktga bog'liq bo'lsa (masalan, bir vaqt oralig'ida boshqa bron yo'qligi), FOR UPDATE mavjud bo'lmagan qatorni lock qila olmaydi — u yerda Serializable, unique constraint yoki exclusion constraint kerak.
Production'da uchraydigan tuzoqlar
Retry ichidagi side effect'lar. Funksiya qayta bajariladi. Agar u ichida to'lov provayderiga HTTP so'rov yuborsa yoki task.delay() chaqirsa, bu ikki marta sodir bo'ladi. Tashqi ta'sirlarni transaction.on_commit() ga o'tkazing: bekor qilingan urinishning callback'lari rollback bilan birga tashlab yuboriladi va faqat muvaffaqiyatli commit'dan keyin ishlaydi.
ATOMIC_REQUESTS = True. Bunda har bir view allaqachon transaction ichida, shuning uchun yuqoridagi dekorator ishlamaydi. Bunday view'larni @transaction.non_atomic_requests bilan belgilang yoki retry'ni view'dan tashqarida, xizmat qatlamida tashkil qiling.
Testlar. Django'ning TestCase har bir testni transaction'ga o'raydi, shuning uchun serializable kodni va parallel holatlarni TransactionTestCase da, alohida thread'lar va alohida ulanishlar bilan tekshirish kerak.
Faqat Serializable transaction'lar himoyalangan. SSI faqat Serializable darajada ishlayotgan transaction'lar o'rtasidagi bog'liqliklarni kuzatadi. Agar bitta jadvalga bir joyda Serializable, boshqa joyda Read Committed bilan yozilsa, kafolat yo'qoladi.
Indekslar va false positive. Predicate lock'lar o'qilgan indeks sahifalari yoki butun jadval darajasida bo'lishi mumkin. Sequential scan butun jadvalga lock qo'yadi va haqiqiy konflikt bo'lmasa ham 40001 lar ko'payadi. Kritik so'rovlar indeksdan foydalanayotganini EXPLAIN bilan tekshiring. Qayta urinishlar sonini metrika sifatida yozib boring: shu orqali muammoni o'lchaysiz, taxmin qilmaysiz.
Uzoq o'qish transaction'lari. Serializable'da faqat o'qiydigan transaction ham bekor qilinishi mumkin. Katta hisobotlar uchun DEFERRABLE bor:
-- Long read-only report under SERIALIZABLE that is never cancelled
-- with a serialization error (it may wait a bit at start instead)
BEGIN ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE;
SELECT owner, sum(balance) FROM accounts GROUP BY owner;
COMMIT;
Uzoq transaction'lar umuman. Repeatable Read va Serializable'da transaction qancha uzoq ochiq tursa, konflikt ehtimoli shuncha yuqori va VACUUM eski qator versiyalarini shuncha uzoq tozalay olmaydi. Transaction ichida tashqi API kutmang.
Amaliy checklist
- Read-modify-write'ni topib chiqing:
obj.field += x; obj.save()— bu birinchi nomzod. - Imkon bo'lsa, o'qish va yozishni bitta
UPDATEga birlashtiring (F(),filter(...).update(...), natija sonini tekshirish). - Murakkabroq mantiq uchun qatorni
select_for_update()bilan lock qiling va lock tartibini bir xil saqlang. - Qaror "qator yo'qligi"ga bog'liq bo'lsa, constraint yoki Serializable ishlating.
- Serializable/Repeatable Read ishlatsangiz,
40001va40P01uchun butun transaction'ni qayta urining, backoff va jitter bilan. - Tashqi side effect'larni
transaction.on_commit()ga o'tkazing. - Parallel holatlarni
TransactionTestCaseva ikki ulanish bilan sinang; ikkipsqloynasida qo'lda takrorlash — eng tez tekshiruv. - Retry sonini va
40001chastotasini monitoring qiling.
Xulosa
Izolyatsiya darajasi — bu "parallel transaction'lar bir-birini qanchalik ko'radi" degan savolga javob, va PostgreSQL'ning default'i Read Committed lost update hamda write skew'ga yo'l qo'yadi. Ko'p muammolar to'g'ri yozilgan bitta UPDATE yoki select_for_update() bilan hal bo'ladi. Qaror bir nechta qatorga yoki qator yo'qligiga bog'liq bo'lsa, Serializable to'g'ri vosita — lekin faqat retry bilan birga. Har qanday yangi kritik oqimni ikkita psql oynasida bir marta "buzib ko'ring": bu bir necha daqiqa vaqt oladi, lekin pul yo'qolishining oldini oladi.
// izohlar
Izohlar yuklanmoqda...