Scenarios: Concurrency, Idempotency & Reliability

const { rowCount } = await pool.query(
  `UPDATE shows SET remaining = remaining - $2
   WHERE id = $1 AND remaining >= $2`,
  [showId, qty],
);
if (rowCount === 0) throw new SoldOutError(); // MySQL: affectedRows; SQL Server: @@ROWCOUNT

CREATE UNIQUE INDEX uq_active_seat ON seat_holds (show_id, seat_id)
  WHERE status IN ('held', 'booked');
-- SQL Server: filtered index cũng hỗ trợ WHERE; MySQL không có partial index → dùng generated column

async function withSerializableRetry<T>(fn: () => Promise<T>, max = 5): Promise<T> {
  for (let attempt = 1; ; attempt++) {
    try { return await fn(); }
    catch (e: any) {
      if (!['40001', '40P01'].includes(e.code) || attempt === max) throw e;
      await sleep(Math.random() * 20 * 2 ** attempt); // full jitter
    }
  }
}

UPDATE promo_codes SET user_id = $1, claimed_at = now()
WHERE id = (
  SELECT id FROM promo_codes
  WHERE user_id IS NULL AND campaign_id = $2
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
RETURNING code;

window [00:00–01:00)  count=100  (all at 00:59.5)
window [01:00–02:00)  count=100  (all at 01:00.2)
=> 200 requests within ~0.7 s

prev window  [00:00-01:00) = 80
curr window  [01:00-02:00) = 30, now = 01:15
weight = 1 - 15/60 = 0.75
estimate = 80*0.75 + 30 = 90  -> allow

async function callWithRetry<T>(fn: () => Promise<T>, max = 4): Promise<T> {
  for (let i = 0; i < max; i++) {
    try { return await fn(); }
    catch (e) {
      if (!isRetryable(e) || i === max - 1) throw e;
      const base = 2 ** i * 100;          // 100, 200, 400, 800ms
      const jitter = Math.random() * 100;
      await sleep(base + jitter);
    }
  }
  throw new Error('unreachable');
}

import CircuitBreaker from 'opossum';
const breaker = new CircuitBreaker(fetchPrice, {
  timeout: 1200, errorThresholdPercentage: 50,
  rollingCountTimeout: 10_000, volumeThreshold: 20, resetTimeout: 30_000,
});
breaker.fallback((sku: string) => priceCache.getStale(sku));
breaker.on('open', () => metrics.increment('cb.pricing.open'));

spec:
  terminationGracePeriodSeconds: 45
  containers:
    - name: api
      lifecycle:
        preStop:
          sleep: { seconds: 10 }   # older clusters: exec: { command: ["sleep", "10"] }

FROM node:22-alpine
WORKDIR /app
COPY . .
RUN npm ci --omit=dev
CMD npm start

ERROR:  deadlock detected
DETAIL:  Process 4211 waits for ShareLock on transaction 918273; blocked by process 4302.
         Process 4302 waits for ShareLock on transaction 918270; blocked by process 4211.
         Process 4211: UPDATE inventory SET qty = qty - 1 WHERE sku = 'B-2'
         Process 4302: UPDATE inventory SET qty = qty - 1 WHERE sku = 'A-1'
HINT:  See server log for query details.
CONTEXT:  while updating tuple (12,7) in relation "inventory"

apiVersion: batch/v1
kind: CronJob
spec:
  schedule: "0 2 * * *"
  timeZone: "Asia/Ho_Chi_Minh"
  concurrencyPolicy: Forbid
  startingDeadlineSeconds: 600
  jobTemplate:
    spec:
      backoffLimit: 2

app.post('/vouchers/:code/redeem', async (req, res) => {
  const v = await db.voucher.findUnique({ where: { code: req.params.code } });
  if (!v || v.redeemedAt) return res.status(409).end();
  await paymentService.applyCredit(req.user.id, v.amount); // await → yields
  await db.voucher.update({ where: { code: v.code }, data: { redeemedAt: new Date() } });
  res.json({ ok: true });
});

async function allow(userId: string): Promise<boolean> {
  const now = Date.now();
  const key = `rl:${userId}`;
  const { tokens, ts } = await redis.hgetall(key);
  const refill = Math.floor((now - ts) / 1000);
  const current = Math.min(10, (tokens ?? 10) + refill);
  if (current < 1) return false;
  await redis.hset(key, { tokens: current - 1, ts: now });
  return true;
}

-- KEYS[1]=bucket  ARGV: capacity, refill_per_ms, cost
local cap, rate, cost = tonumber(ARGV[1]), tonumber(ARGV[2]), tonumber(ARGV[3])
local t = redis.call('TIME'); local now = t[1] * 1000 + math.floor(t[2] / 1000)
local h = redis.call('HMGET', KEYS[1], 'tokens', 'ts')
local tokens = tonumber(h[1]) or cap
local ts = tonumber(h[2]) or now
tokens = math.min(cap, tokens + (now - ts) * rate)
local allowed = 0
if tokens >= cost then tokens = tokens - cost; allowed = 1 end
redis.call('HSET', KEYS[1], 'tokens', tokens, 'ts', now)
redis.call('PEXPIRE', KEYS[1], math.ceil(cap / rate))
local retry = allowed == 1 and 0 or math.ceil((cost - tokens) / rate)
return { allowed, math.floor(tokens), retry }

async function pay(req: Request) {
  const key = req.headers['idempotency-key'] as string;
  const existing = await db.idempotency.findByKey(key);
  if (existing) return existing.response;            // replay

  const inserted = await db.idempotency.insertIfAbsent(key, 'processing');
  if (!inserted) throw new ConflictError('in progress');

  const result = await chargeGateway(req.body);
  await db.idempotency.update(key, 'done', result);
  return result;
}

UPDATE idempotency_keys
SET locked_until = now() + interval '60 seconds', attempt = attempt + 1
WHERE scope = $1 AND key = $2 AND status = 'processing' AND locked_until < now()
RETURNING recovery_point, provider_ref;

-- KEYS[1]=stock KEYS[2]=buyers  ARGV[1]=user ARGV[2]=qty
if redis.call('SISMEMBER', KEYS[2], ARGV[1]) == 1 then return -1 end
local s = tonumber(redis.call('GET', KEYS[1]) or '0')
if s < tonumber(ARGV[2]) then return 0 end
redis.call('DECRBY', KEYS[1], ARGV[2])
redis.call('SADD', KEYS[2], ARGV[1])
return 1

-- 16 shards
UPDATE campaign_budget_shards
SET spent = spent + $2
WHERE campaign_id = $1 AND shard = floor(random() * 16)::int
  AND spent + $2 <= shard_cap;
SELECT sum(spent) FROM campaign_budget_shards WHERE campaign_id = $1;

async function getWithLock(key: string): Promise<string> {
  const val = await redis.get(key);
  if (val) return val;
  const lock = await redis.set(`lock:${key}`, '1', 'NX', 'EX', 10);
  if (!lock) { await sleep(50); return getWithLock(key); }
  const fresh = await queryDB(key);
  await redis.set(key, fresh, 'EX', 300);
  await redis.del(`lock:${key}`);
  return fresh;
}

const deadline = Date.now() + 1900;
const signal = AbortSignal.any([AbortSignal.timeout(1900), clientAbort.signal]);
const left = () => Math.max(0, deadline - Date.now());
const a = await callA({ signal, timeoutMs: Math.min(800, left()) });
const [b, c] = await Promise.all([
  callB(a, { signal: AbortSignal.any([signal, AbortSignal.timeout(Math.min(900, left()))]) }),
  callC(a, { signal: AbortSignal.any([signal, AbortSignal.timeout(Math.min(900, left()))]) }),
]);

process.on('SIGTERM', async () => {
  server.close();                 // stop accepting
  await drainInflightRequests(30_000);
  await db.end();
  await consumer.disconnect();
  process.exit(0);
});

livenessProbe:
  httpGet: { path: /health, port: 3000 }   # checks DB + Redis
  periodSeconds: 5
  timeoutSeconds: 1
  failureThreshold: 2
readinessProbe:
  httpGet: { path: /health, port: 3000 }

app.use(express.json());
app.post('/webhooks/payments', async (req, res) => {
  const sig = req.header('x-signature');
  const expected = crypto.createHmac('sha256', SECRET)
    .update(JSON.stringify(req.body)).digest('hex');
  if (sig !== expected) return res.status(401).end();
  await handleEvent(req.body);       // 5–15 s of work
  res.sendStatus(200);
});

async function handle(message: KafkaMessage) {
  const eventId = message.headers!.eventId!.toString();
  const seen = await db.processedEvents.exists(eventId);   // check outside tx
  if (seen) return;
  await db.transaction(async (tx) => {
    await applyBusinessLogic(tx, message);
    await tx.processedEvents.insert(eventId);
  });
  await consumer.commitOffsets([...]);
}

async function transfer(from: number, to: number, amount: number) {
  return retry(() => db.tx(async (tx) => {
    await tx.query('SELECT * FROM accounts WHERE id = $1 FOR UPDATE', [from]);
    await fraudService.check(from, to, amount);          // HTTP, 200-2000 ms
    await tx.query('SELECT * FROM accounts WHERE id = $1 FOR UPDATE', [to]);
    await tx.query('UPDATE accounts SET balance = balance - $2 WHERE id = $1', [from, amount]);
    await tx.query('UPDATE accounts SET balance = balance + $2 WHERE id = $1', [to, amount]);
  }), { retries: 5, retryOn: () => true });
}

-- on primary, after the write commits
SELECT pg_current_wal_lsn();            -- e.g. 3/A1B2C3D0
-- on a replica, before serving the read
SELECT pg_last_wal_replay_lsn() >= '3/A1B2C3D0'::pg_lsn AS caught_up;

async function sendOtp(userId: string, phone: string) {
  const code = Math.floor(100000 + Math.random() * 900000).toString();
  await redis.set(`otp:${phone}`, code);
  await sms.send(phone, `Your code is ${code}`);
}
async function verifyOtp(phone: string, code: string) {
  return (await redis.get(`otp:${phone}`)) === code;
}