Barcha yozuvlar
Dasturlash

Poyga holati va select_for_update: oxirgi chiptani ikki kishi sotib olganda

Ikki so'rov bir vaqtda qoldiqni o'qib, oxirgi chiptani ikki marta sotadi. F() bilan atomik UPDATE, select_for_update, nowait, skip_locked, deadlock'lar va version ustuni bilan optimistic locking — PostgreSQL ichidan tushuntirilgan.

Chiptalar sotuvini tasavvur qiling. Konsertga oxirgi bitta chipta qolgan va ikki kishi bir necha millisekund farq bilan «Sotib olish» tugmasini bosadi. Ikkita Gunicorn worker so'rovlarni oladi, ikkalasi ham bazadan stock = 1 ni o'qiydi, ikkalasi ham «yetadi» deb qaror qiladi, ikkalasi ham stock = 0 yozadi va ikkalasi ham buyurtma yaratadi. Baza xursand, loglar toza, ertasiga esa bitta o'ringa ikkita xaridor.

Bu lost update (yo'qolgan yangilanish): bitta yozuv ikkinchisini jimgina ustidan yozib yuboradi, chunki ikkalasi ham bir xil eskirgan o'qishga tayangan. Bu maqolada aynan shu bitta xatoni qismlarga ajratamiz: oddiy ko'rinadigan kod nega buziladi, PostgreSQL'da qator darajasidagi lock'lar aslida qanday ishlaydi, F() ifodalari va select_for_update() (nowait, skip_locked, of bilan) nima qiladi, deadlock qayerdan paydo bo'ladi va qachon version ustuni bilan optimistic locking yaxshiroq tanlov.

Boshlang'ich holat: mahsulot va qoldiq

Barcha misollarda shu modellardan foydalanamiz. Pulni tiyinda, butun son sifatida saqlaymiz, manfiy qoldiqni esa bazaning o'zi taqiqlaydi.

from django.conf import settings
from django.db import models
from django.db.models import Q


class Product(models.Model):
    name = models.CharField(max_length=200)
    price = models.BigIntegerField()  # in tiyin
    stock = models.IntegerField(default=0)
    version = models.PositiveIntegerField(default=0)

    class Meta:
        constraints = [
            models.CheckConstraint(
                condition=Q(stock__gte=0),
                name="product_stock_non_negative",
            ),
        ]


class Order(models.Model):
    product = models.ForeignKey(Product, on_delete=models.PROTECT)
    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.PROTECT)
    quantity = models.PositiveIntegerField()
    created_at = models.DateTimeField(auto_now_add=True)

CheckConstraint — oxirgi himoya chizig'i: ilova qanday xato qilmasin, PostgreSQL stock < 0 ni IntegrityError bilan rad etadi. Lekin qoldiq ikki marta 1 dan 0 ga tushganda bitta narsani ikki kishiga sotib yuborishdan u saqlamaydi, shuning uchun ilovada to'g'ri mantiq baribir kerak.

Oddiy yechim nega buziladi

Deyarli har bir loyihada dastlab mana shunday kod paydo bo'ladi:

class OutOfStock(Exception):
    pass


def buy(product_id: int, qty: int, user) -> Order:
    product = Product.objects.get(pk=product_id)
    if product.stock < qty:
        raise OutOfStock
    product.stock -= qty
    product.save()
    return Order.objects.create(product=product, user=user, quantity=qty)

stock = 1 bo'lgan mahsulotga ikkita parallel so'rov yuboramiz:

Vaqt So'rov A So'rov B
t1 SELECT ... WHERE id = 42 → stock = 1
t2 SELECT ... WHERE id = 42 → stock = 1
t3 UPDATE ... SET stock = 0
t4 UPDATE ... SET stock = 0
t5 buyurtma yaratadi buyurtma yaratadi

product.stock < qty tekshiruvi va product.save() yozuvi — ikkita alohida qadam, qaror esa Python'da, allaqachon eskirgan bo'lishi mumkin bo'lgan ma'lumot nusxasi asosida qabul qilinadi. t1 va t3 oralig'ida boshqa jarayonning xuddi shu qatorni o'qishiga hech narsa to'sqinlik qilmaydi.

Funksiyani transaction.atomic ga o'rash yordam bermaydi. PostgreSQL'ning standart izolyatsiya darajasi READ COMMITTED da oddiy SELECT qatorga lock qo'ymaydi, shuning uchun ikkala transaction ham bir xil qiymatni bemalol o'qiydi. Transaction «hammasi yoki hech narsa» kafolatini beradi, «bu qatorga boshqa hech kim tegmasin» kafolatini emas. Bu yerda ikkinchi tuzoq ham bor: update_fields siz save() barcha ustunlarni yozadi va admin parallel ravishda o'zgartirgan narxni ham ustidan yozib yuborishi mumkin.

Mexanizm: PostgreSQL'da qator lock'lari qanday ishlaydi

Buni tuzatish uchun ikkita transaction bitta qatorga murojaat qilganda PostgreSQL nima qilishini tushunishimiz kerak.

  1. Har bir UPDATE, DELETE va SELECT ... FOR UPDATE o'zi tegadigan qatorlarga qator darajasidagi lock qo'yadi. Lock alohida jadvalda emas, qatorning o'zida (tuple sarlavhasida) yoziladi, shuning uchun millionlab qatorni lock qilish xotirani tugatmaydi.
  2. Qator lock'lari transaction tugaguncha — COMMIT yoki ROLLBACK gacha ushlab turiladi. Ularni oldinroq bo'shatishning iloji yo'q.
  3. Agar B transaction A allaqachon lock qilgan qatorni lock qilmoqchi bo'lsa, B kutadi. U qatorning o'zini emas, A ning transaction ID sini kutadi — shuning uchun deadlock xabarlarida «waits for ShareLock on transaction 9811» deb yoziladi.
  4. A commit qilganda B uyg'onadi. READ COMMITTED da B bu qator uchun o'zining eski snapshot'idan foydalanmaydi: u qatorning eng so'nggi commit qilingan versiyasini oladi va WHERE shartini unda qaytadan tekshiradi. Agar qator endi shartga mos kelmasa, B uni o'tkazib yuboradi.

4-band eng oddiy yechimning kaliti. Oddiy SELECT bularning hech birida qatnashmaydi: o'quvchilar yozuvchilarni to'smaydi, yozuvchilar o'quvchilarni to'smaydi. Bu o'tkazuvchanlik uchun ajoyib va aynan shuning uchun oddiy kod buzilgan.

PostgreSQL'dagi qator lock'lari kuchlidan kuchsizga qarab: FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE. Kalit ustunlarni o'zgartirmaydigan oddiy UPDATE FOR NO KEY UPDATE oladi. Bunga keyinroq qaytamiz.

1-yechim: F() bilan atomik UPDATE

Agar «tekshir va o'zgartir» ni bitta SQL so'rov bilan ifodalash mumkin bo'lsa, bu ishni bazaning o'ziga topshiring:

from django.db import transaction
from django.db.models import F


@transaction.atomic
def buy(product_id: int, qty: int, user) -> Order:
    updated = (
        Product.objects
        .filter(pk=product_id, stock__gte=qty)
        .update(stock=F("stock") - qty)
    )
    if updated == 0:
        raise OutOfStock
    return Order.objects.create(product_id=product_id, user=user, quantity=qty)

Django buni bitta so'rovga aylantiradi:

UPDATE "shop_product"
   SET "stock" = ("shop_product"."stock" - 1)
 WHERE "shop_product"."id" = 42
   AND "shop_product"."stock" >= 1;

Poygani qaytadan o'ynab ko'ramiz. A UPDATE ni bajaradi, 42-qatorni lock qiladi va stock'ni 0 qiladi. B xuddi shu UPDATE ni bajaradi, A ning lock'iga duch keladi va kutadi. A commit qiladi. B uyg'onadi, qatorning yangi versiyasini (stock = 0) oladi, stock >= 1 ni qayta tekshiradi — yolg'on — va nol qatorni yangilaydi. update() 0 qaytaradi, biz OutOfStock ko'taramiz. Python tomonida o'qish yo'q, oraliq «oyna» ham yo'q.

Agar qaror WHERE ichiga sig'sa, alohida lock kerak emas: UPDATE'ning o'z qator lock'i va READ COMMITTED'dagi qayta tekshiruv butun ishni bajaradi.

Bu eng arzon to'g'ri variant: bitta round trip, lock juda qisqa vaqt ushlab turiladi. Qoldiqni kamaytirish va buyurtma yaratish birga commit yoki rollback bo'lishi uchun transaction.atomic baribir kerak.

F() ni queryset'da emas, obyektda ishlatganda kichik bir tuzoq bor:

product = Product.objects.get(pk=42)
product.stock = F("stock") - 1
product.save(update_fields=["stock"])

print(product.stock)  # Django 5.x: CombinedExpression, not a number
product.refresh_from_db(fields=["stock"])
print(product.stock)  # the real value from the database

Django 5.x da save() dan keyin atributda ifoda qolib ketadi. Obyektni yana saqlasangiz, ifoda qaytadan qo'llanadi va qoldiq ikki marta kamayadi. Qaysi versiyada bo'lishingizdan qat'i nazar, bunday saqlashdan keyin refresh_from_db() niyatni aniq ko'rsatib qo'yadi.

2-yechim: mantiq bitta UPDATE ga sig'maganda select_for_update

Haqiqiy qoidalar kamdan-kam hollarda bitta shartdan iborat bo'ladi. Aytaylik, bitta foydalanuvchi bitta tadbirga ko'pi bilan ikkita chipta olishi mumkin. Endi qoldiqni o'qish, foydalanuvchining oldingi buyurtmalarini sanash va shundan keyingina yozish kerak. Aynan shu yerda select_for_update() kerak bo'ladi:

from django.db import transaction
from django.db.models import Sum
from django.db.models.functions import Coalesce

MAX_PER_USER = 2


class LimitExceeded(Exception):
    pass


@transaction.atomic
def buy(product_id: int, qty: int, user) -> Order:
    product = Product.objects.select_for_update().get(pk=product_id)

    if product.stock < qty:
        raise OutOfStock

    bought = Order.objects.filter(product=product, user=user).aggregate(
        total=Coalesce(Sum("quantity"), 0)
    )["total"]
    if bought + qty > MAX_PER_USER:
        raise LimitExceeded

    product.stock -= qty
    product.save(update_fields=["stock"])
    return Order.objects.create(product=product, user=user, quantity=qty)

Birinchi qator uchun Django yuboradigan SQL:

SELECT "shop_product"."id", "shop_product"."name", ...
  FROM "shop_product"
 WHERE "shop_product"."id" = 42
 LIMIT 21
   FOR UPDATE;

(LIMIT 21 — get() «bittadan ko'p obyekt qaytdi» holatini shu orqali aniqlaydi.) Endi lock'ni o'qishning o'zi qo'yadi. B so'rov A commit qilguncha shu SELECT da to'xtab turadi, keyin esa qoldig'i allaqachon kamaygan yangi qatorni o'qiydi. 42-mahsulotning barcha xaridlari shu bitta qator orqali navbatga turadi va bu foydalanuvchi limitini tekshirishni ham xavfsiz qiladi: lock bizda turgan paytda bu mahsulotga boshqa buyurtma yaratilmaydi.

Muhim tafsilotlar:

  • select_for_update() transaction ichida bajarilishi shart. Autocommit rejimida, atomic dan tashqarida Django TransactionManagementError ko'taradi. Queryset'lar dangasa (lazy), shuning uchun so'rov qayerda yig'ilgani emas, qayerda bajarilgani muhim.
  • Lock ichki atomic blokning emas, eng tashqi atomic blokning oxirigacha yashaydi. Ichma-ich atomic savepoint yaratadi, lock'larni bo'shatmaydi.
  • Faqat o'zgartirgan ustunlarni yozish uchun save(update_fields=[...]) dan foydalaning.

nowait: kutish o'rniga xato

Standart holatda ikkinchi so'rov birinchisi lock'ni qancha ushlab tursa, shuncha kutadi. Ba'zan darhol javob bergan ma'qul: «buni hozir boshqa birov qayta ishlayapti, keyinroq urinib ko'ring».

from django.db import OperationalError, transaction
from psycopg import errors as pg_errors


class Busy(Exception):
    pass


def buy_or_busy(product_id: int, qty: int, user) -> Order:
    try:
        with transaction.atomic():
            product = Product.objects.select_for_update(nowait=True).get(
                pk=product_id
            )
            if product.stock < qty:
                raise OutOfStock
            product.stock -= qty
            product.save(update_fields=["stock"])
            return Order.objects.create(product=product, user=user, quantity=qty)
    except OperationalError as exc:
        if isinstance(exc.__cause__, pg_errors.LockNotAvailable):
            raise Busy from exc
        raise

nowait=True bilan PostgreSQL FOR UPDATE NOWAIT qo'shadi va qator lock qilingan bo'lsa, SQLSTATE 55P03 (lock_not_available) xatosini ko'taradi. Django uni OperationalError ga o'raydi, asl psycopg exception esa __cause__ da turadi. Har qanday DatabaseError ni ushlab, hammasini «band» deb hisoblagandan ko'ra sababini tekshirgan yaxshiroq. Misol psycopg 3 uchun yozilgan; psycopg2 da klass nomi psycopg2.errors.LockNotAvailable.

skip_locked: istalgan bo'sh qatorni ber

Zaldagi istalgan bo'sh o'rin to'g'ri keladigan bron qilishda lock qilingan o'rinni kutishning ma'nosi yo'q — keyingisini olamiz:

from django.conf import settings
from django.db import models, transaction


class Seat(models.Model):
    event = models.ForeignKey("Event", on_delete=models.CASCADE)
    row = models.PositiveSmallIntegerField()
    number = models.PositiveSmallIntegerField()
    holder = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        null=True,
        blank=True,
        on_delete=models.SET_NULL,
    )


@transaction.atomic
def take_any_seat(event_id: int, user) -> Seat | None:
    seat = (
        Seat.objects.select_for_update(skip_locked=True)
        .filter(event_id=event_id, holder__isnull=True)
        .order_by("row", "number")
        .first()
    )
    if seat is None:
        return None
    seat.holder = user
    seat.save(update_fields=["holder"])
    return seat
SELECT ... FROM "shop_seat"
 WHERE "shop_seat"."event_id" = 7
   AND "shop_seat"."holder_id" IS NULL
 ORDER BY "shop_seat"."row" ASC, "shop_seat"."number" ASC
 LIMIT 1
   FOR UPDATE SKIP LOCKED;

Yigirmata parallel so'rov bir-birini kutmasdan yigirmata turli o'rin oladi. PostgreSQL ustidagi oddiy vazifalar navbati (job queue) ham shu pattern asosida quriladi.

Nozik joyi: bu yerda None «hammasi sotilgan» degani emas. U «ayni damda lock qilinmagan bo'sh o'rin yo'q» degani. Shunga qarab «Chipta qolmadi» deb ko'rsatsangiz, boshqa transaction'lar rollback bo'lishiga oz qolgan paytda foydalanuvchini aldab qo'yishingiz mumkin. Halol «sotib bo'lindi» uchun alohida oddiy hisoblash qiling.

nowait va skip_locked birga ishlatilmaydi: ikkalasini bersangiz, ValueError chiqadi.

Deadlock: ikkita lock bir-birini kutganda

Lock'lar yangi turdagi nosozlikni olib keladi. Hamyonlar orasida pul o'tkazish:

from django.conf import settings
from django.db import models, transaction


class Wallet(models.Model):
    owner = models.OneToOneField(settings.AUTH_USER_MODEL, on_delete=models.PROTECT)
    balance = models.BigIntegerField()  # in tiyin


class InsufficientFunds(Exception):
    pass


@transaction.atomic
def transfer(from_id: int, to_id: int, amount: int) -> None:
    src = Wallet.objects.select_for_update().get(pk=from_id)
    dst = Wallet.objects.select_for_update().get(pk=to_id)
    if src.balance < amount:
        raise InsufficientFunds
    src.balance -= amount
    dst.balance += amount
    src.save(update_fields=["balance"])
    dst.save(update_fields=["balance"])

1-foydalanuvchi 2-foydalanuvchiga pul o'tkazmoqda, xuddi shu daqiqada 2-foydalanuvchi ham 1-foydalanuvchiga:

Vaqt transfer(1, 2) transfer(2, 1)
t1 1-hamyonni lock qiladi
t2 2-hamyonni lock qiladi
t3 2-hamyonni so'raydi → B ni kutadi
t4 1-hamyonni so'raydi → A ni kutadi

Hech biri davom eta olmaydi. PostgreSQL har bir kutishda deadlock'ni tekshirmaydi: u deadlock_timeout (standart qiymati 1 soniya) kutadi, keyin detektorni ishga tushiradi, aylanani topadi va transaction'lardan birini to'xtatadi:

ERROR:  deadlock detected
DETAIL:  Process 4121 waits for ShareLock on transaction 9811; blocked by process 4122.
         Process 4122 waits for ShareLock on transaction 9810; blocked by process 4121.
HINT:  See server log for query details.

Ikkinchi transaction ishini davom ettiradi. Hech narsa buzilmaydi, lekin bitta foydalanuvchi 500 xatosini oladi, so'rov esa kamida bir soniya davom etadi.

Davosi — lock'larning yagona tartibi. Agar har bir transaction hamyonlarni pk o'sish tartibida lock qilsa, aylana hosil bo'lolmaydi: kichik id ni kim birinchi olgan bo'lsa, ikkinchisi uni kutadi va bu orada kattasini ushlab turmaydi.

@transaction.atomic
def transfer(from_id: int, to_id: int, amount: int) -> None:
    if from_id == to_id:
        raise ValueError("cannot transfer to the same wallet")

    locked = (
        Wallet.objects.select_for_update()
        .filter(pk__in=[from_id, to_id])
        .order_by("pk")
    )
    wallets = {w.pk: w for w in locked}
    src, dst = wallets[from_id], wallets[to_id]

    if src.balance < amount:
        raise InsufficientFunds
    src.balance -= amount
    dst.balance += amount
    src.save(update_fields=["balance"])
    dst.save(update_fields=["balance"])

ORDER BY id ... FOR UPDATE li bitta so'rov qatorlarni saralangan tartibda lock qiladi. Xuddi shu qoida hamma joyda amal qiladi: saralanmagan id lar bo'yicha sikldagi bir nechta update(), avval buyurtmaga, keyin mahsulotga tegadigan Celery task'lari va buning teskarisini qiladigan view'lar. Bitta tartibni tanlang va unga amal qiling.

Intizom bo'lsa ham deadlock va serialization xatolari baribir paydo bo'lishi mumkin (foreign key'lar, trigger'lar, SERIALIZABLE transaction'lar). Ularni qayta urinish (retry) xavfsiz — lekin faqat butun transaction'ni:

import time

from django.db import OperationalError, connection
from psycopg import errors as pg_errors

RETRYABLE = (pg_errors.DeadlockDetected, pg_errors.SerializationFailure)


def with_retry(fn, *args, attempts: int = 3, **kwargs):
    if connection.in_atomic_block:
        raise RuntimeError("with_retry must wrap the outermost transaction")
    for attempt in range(1, attempts + 1):
        try:
            return fn(*args, **kwargs)
        except OperationalError as exc:
            if attempt == attempts or not isinstance(exc.__cause__, RETRYABLE):
                raise
            time.sleep(0.05 * attempt)


with_retry(transfer, 1, 2, 5_000_000)  # 50 000 so'm in tiyin

Tashqi atomic ichida qayta urinishning foydasi yo'q: xatodan keyin tashqi transaction allaqachon to'xtatilgan. Shuningdek, funksiyada commit'dan oldin bazadan tashqari yon ta'sirlar (email, to'lov provayderiga so'rov) yo'qligiga ishonch hosil qiling, aks holda retry ularni takrorlaydi.

Optimistic locking: version ustuni

Yuqoridagilarning hammasi pessimistik yondashuv: avval lock qilamiz, keyin ishlaymiz. Ba'zan buning iloji bo'lmaydi. Admin mahsulotni tahrirlash formasini ochadi, besh daqiqa o'ylaydi va «Saqlash» ni bosadi. Ikkita HTTP so'rov va besh daqiqalik o'ylash davomida bazada lock ushlab turib bo'lmaydi. Bu orada ikkinchi admin narxni o'zgartirgan, birinchisi esa uni jimgina ustidan yozib yuboradi.

Optimistic locking konfliktlar kam bo'ladi deb hisoblaydi va ularni faqat yozish paytida aniqlaydi. Forma o'zi yuklangan paytdagi version ni olib yuradi:

from django.db.models import F


class ConcurrentUpdate(Exception):
    pass


def update_price(product_id: int, new_price: int, expected_version: int) -> None:
    updated = (
        Product.objects
        .filter(pk=product_id, version=expected_version)
        .update(price=new_price, version=F("version") + 1)
    )
    if updated == 0:
        raise ConcurrentUpdate

Agar oraliqda kimdir saqlab ulgurgan bo'lsa, version allaqachon oldinga ketgan, WHERE hech narsa topmaydi, update() 0 qaytaradi va siz «Bu mahsulotni boshqa birov o'zgartirdi, sahifani yangilang» deb ko'rsatasiz. Foydalanuvchi o'ylayotgan paytda hech qanday lock ushlab turilmaydi.

Yondashuv Lock ushlab turadimi Qachon mos Raqobatda narxi
update() + F() bitta so'rov davomida qoida WHERE ga sig'adigan hisoblagichlar, qoldiq, balans minimal
select_for_update() commit'gacha bitta so'rov ichidagi ko'p bosqichli mantiq «issiq» qatorlarda so'rovlar navbatga turadi
nowait commit'gacha «band, keyinroq urinib ko'ring» UX kutish o'rniga xatolar
skip_locked commit'gacha istalgan bo'sh obyektni bron qilish, job queue yo'q, lekin umumiy manzara ham yo'q
version ustuni yo'q uzoq tahrirlash, If-Match li API konflikt xato bo'lib chiqadi, foydalanuvchi qaytadan urinadi

Production'dagi tuzoqlar

  • Lock ostida tarmoq so'rovlari. select_for_update() va commit oralig'ida to'lov provayderiga murojaat qilish yoki SMS yuborish qator tashqi API qancha sekin ishlasa, shuncha vaqt lock'da turishini anglatadi. Qolgan barcha xaridorlar uning ortidan navbatda turadi. Tashqi so'rovni tashqarida yoki transaction.on_commit() orqali bajaring.
  • select_related o'ylagandan ko'proq narsani lock qiladi. Join bo'lganda FOR UPDATE qo'shilgan barcha jadvallardagi qatorlarni lock qiladi. of dan foydalaning:
order = (
    Order.objects.select_related("product")
    .select_for_update(of=("self",))
    .get(pk=order_id)
)

Nullable bog'lanishda select_related outer join quradi, PostgreSQL esa nullable tomonda FOR UPDATE qilishni rad etadi; of=("self",) buni ham hal qiladi. - Foreign key'lar va lock kuchi. Order qo'shish u havola qilayotgan mahsulot qatoriga FOR KEY SHARE qo'yadi. Bu FOR UPDATE bilan to'qnashadi, shuning uchun xuddi shu mahsulotga buyurtma qo'shayotgan boshqa transaction'lar kutadi. Agar primary key'ni o'zgartirmayotgan bo'lsangiz, select_for_update(no_key=True) FOR NO KEY UPDATE oladi va u bunday insert'larni to'smaydi. - Cheksiz kutish. Standart holatda lock kutish hech narsa bilan cheklanmagan. Har bir transaction uchun chegara qo'ying:

from django.db import connection, transaction

with transaction.atomic():
    with connection.cursor() as cursor:
        cursor.execute("SET LOCAL lock_timeout = '2s'")
    product = Product.objects.select_for_update().get(pk=product_id)
    ...

Vaqt tugaganda PostgreSQL nowait dagi xuddi o'sha 55P03 xatosini ko'taradi. - Poygani ko'rmaydigan testlar. Django'ning TestCase i har bir testni transaction'ga o'raydi va hammasini bitta ulanish orqali bajaradi, shuning uchun parallellik xatolari ko'rinmaydi. Haqiqiy thread'lar bilan TransactionTestCase dan foydalaning; har bir thread o'z ulanishini oladi:

import threading

from django.contrib.auth import get_user_model
from django.db import connection
from django.test import TransactionTestCase

from shop.models import Product
from shop.services import OutOfStock, buy

User = get_user_model()


class BuyRaceTest(TransactionTestCase):
    def test_last_item_is_sold_once(self):
        product = Product.objects.create(name="Ticket", price=100_000, stock=1)
        users = [User.objects.create(username=f"u{i}") for i in range(2)]
        barrier = threading.Barrier(2)
        results = []

        def worker(user):
            try:
                barrier.wait()
                buy(product.pk, 1, user)
                results.append("ok")
            except OutOfStock:
                results.append("out")
            finally:
                connection.close()

        threads = [threading.Thread(target=worker, args=(u,)) for u in users]
        for t in threads:
            t.start()
        for t in threads:
            t.join()

        self.assertCountEqual(results, ["ok", "out"])
        product.refresh_from_db()
        self.assertEqual(product.stock, 0)

Poyga deterministik emas: buzuq versiya ham ba'zan bu testdan o'tib ketadi. Uni siklda ishga tushiring, shuningdek ikkita psql oynasida BEGIN bilan qo'lda takrorlang — kim kimni kutayotganini ko'rishning eng tez yo'li shu. - Taxmin emas, o'lchov. Lock uchun raqobatdan shubhalansangiz, pg_stat_activity (wait_event_type = 'Lock') va pg_locks ga qarang, log_lock_waits ni yoqing — shunda PostgreSQL deadlock_timeout dan uzoq davom etgan har bir kutishni logga yozadi.

Tekshiruv ro'yxati

  • Python'da o'qish va keyin shunga asoslangan yozish bormi? Bu poyga holatiga nomzod.
  • Qoida WHERE ga sig'sa, filter(...).update(F(...)) dan foydalaning va yangilangan qatorlar sonini tekshiring.
  • Aks holda — transaction.atomic ichida select_for_update() va update_fields bilan yozish.
  • Bir nechta qatorni bitta so'rovda, qat'iy tartibda (order_by("pk")) lock qiling.
  • Deadlock va serialization xatolarini transaction ichida emas, butun transaction atrofida qayta urining.
  • Qator lock'i ostida tashqi HTTP so'rovlar yo'q; lock_timeout o'rnating.
  • Bir nechta HTTP so'rovga cho'ziladigan tahrirlash uchun version ustunidan foydalaning.
  • CheckConstraint ni oxirgi sug'urta sifatida saqlang.
  • Parallellikni thread'li TransactionTestCase yoki ikkita psql sessiyasida sinang.

Xulosa

Poyga holati — qaror yozishdan oldin o'zgarishi mumkin bo'lgan ma'lumot asosida qabul qilinadigan har qanday joy. Davosi doim bitta: qarorni baza kafolatlay oladigan joyga ko'chirish — atomik UPDATE ning WHERE qismiga, select_for_update() bilan olingan qator lock'i ortiga yoki versiya tekshiruviga. Lock'larni yagona tartibda oling, ularni qisqa ushlang va PostgreSQL deadlock'ni uzganda butun transaction'ni qaytadan bajaring.

// izohlar

Izohlar yuklanmoqda...

    // keyingi qadam

    Keling, birga nimadir quramiz.

    Telegramda yozish
    aslbek@asldev: ~
    ❯