Backend Live Coding

-- ======================================================================
-- Q: How do you find which query is holding a lock and blocking the others?
--
-- A common cause: a transaction left open (idle in transaction) or a migration
-- waiting for an ACCESS EXCLUSIVE lock.
-- ======================================================================

SELECT pid, pg_blocking_pids(pid) AS blocked_by, state,
       now() - xact_start AS xact_age, left(query, 80)
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- if needed, stop the blocking query
SELECT pg_cancel_backend(<pid>);      -- cancel first, then pg_terminate_backend

# ======================================================================
# Q: Code review: find the problems.
#
# A common exercise: the interviewer shows code and asks "what would you comment
# on in this pull request?". Find as many issues as you can before reading the
# answer.
# ======================================================================

@api_view(['POST'])
def apply_discount(request):
    order = Order.objects.get(id=request.data['order_id'])
    code = request.data['code']
    coupon = Coupon.objects.raw(f"SELECT * FROM coupon WHERE code = '{code}'")[0]
    if coupon.uses < coupon.max_uses:
        order.total = order.total - order.total * coupon.percent / 100
        order.save()
        coupon.uses += 1
        coupon.save()
    for item in order.items.all():
        print(item.product.name)
    return Response({'total': float(order.total)})

# ======================================================================
# Q: Write a test for an endpoint using pytest, factory_boy and a mock of an
# external API.
# ======================================================================

class ProductFactory(factory.django.DjangoModelFactory):
    class Meta:
        model = Product
    sku = factory.Sequence(lambda n: f'SKU-{n}')
    price = Decimal('10.00')
    stock = 5

@pytest.mark.django_db
def test_place_order_reduces_stock(api_client, customer_user, mocker):
    product = ProductFactory(stock=5)
    charge = mocker.patch('orders.services.psp.charge', return_value='ch_123')
    api_client.force_authenticate(customer_user)

    r = api_client.post('/api/v1/orders/',
                        {'items': [{'product': product.pk, 'quantity': 2}]}, format='json')

    assert r.status_code == 201
    product.refresh_from_db()
    assert product.stock == 3
    charge.assert_called_once()

@pytest.mark.django_db
def test_cannot_order_more_than_stock(api_client, customer_user):
    product = ProductFactory(stock=1)
    api_client.force_authenticate(customer_user)
    r = api_client.post('/api/v1/orders/',
                        {'items': [{'product': product.pk, 'quantity': 2}]}, format='json')
    assert r.status_code == 409

# ======================================================================
# Q: Implement cache-aside with protection from a stampede.
#
# Alternatives: stale-while-revalidate (serve the stale value and refresh in the
# background with Celery), or refresh before expiry.
# ======================================================================

def get_or_compute(key, compute, ttl=300):
    value = cache.get(key)
    if value is not None:
        return value
    # only one computes. cache.add is atomic (SET NX)
    if cache.add(f'{key}:lock', 1, timeout=30):
        try:
            value = compute()
            cache.set(key, value, ttl + random.randint(0, ttl // 10))   # jitter
            return value
        finally:
            cache.delete(f'{key}:lock')
    time.sleep(0.05)                  # the others wait a little
    return cache.get(key) or compute()

# ======================================================================
# Q: How do you implement a distributed lock with Redis correctly?
#
# The TTL prevents an "eternal" lock if the process dies. The token prevents me
# from deleting a lock that someone else acquired after mine expired. redis-py
# has a ready-made r.lock(name, timeout=...). For critical correctness I prefer
# PostgreSQL advisory locks or database constraints.
# ======================================================================

token = uuid.uuid4().hex
if r.set('lock:erp-sync', token, nx=True, px=60_000):     # only if it does not exist, with a TTL
    try:
        run_sync()
    finally:
        # release only if the lock is still mine (atomic with Lua)
        r.eval("if redis.call('get', KEYS[1]) == ARGV[1] then "
               "return redis.call('del', KEYS[1]) else return 0 end", 1, 'lock:erp-sync', token)