telegram-bot-workers/OPTIMISTIC_LOCKING_SUMMARY.md

Optimistic Locking Implementation Summary

Overview

Implemented Optimistic Locking pattern to prevent data inconsistencies in deposit operations where D1 batch() is not a true transaction.

Files Created

1. Database Migration

File: migrations/002_add_version_columns.sql

• Adds version INTEGER NOT NULL DEFAULT 1 column to user_deposits table
• Creates index idx_deposits_user_version on (user_id, version)
• Includes rollback instructions in comments

2. Optimistic Lock Utility

File: src/utils/optimistic-lock.ts

• OptimisticLockError class for version conflict detection
• executeWithOptimisticLock<T>() function with:
  • Automatic retry with exponential backoff (100ms, 200ms, 400ms)
  • Max 3 attempts
  • Structured logging for debugging
  • Generic type support

3. Reconciliation Job

File: src/utils/reconciliation.ts

• reconcileDeposits() function to verify data integrity
• Compares user_deposits.balance vs SUM(confirmed transactions)
• Detects and logs discrepancies
• formatReconciliationReport() for admin notifications

Files Modified

1. deposit-agent.ts

Changes:

• Added import of executeWithOptimisticLock and OptimisticLockError
• Updated request_deposit (auto_matched case) - lines 83-150
  • Wrapped balance update in optimistic lock
  • Version check before UPDATE
  • Automatic retry on version conflict
• Updated confirm_deposit - lines 302-358
  • Same optimistic locking pattern
  • Transaction status update + balance increase

Pattern Used:

try {
  await executeWithOptimisticLock(db, async (attempt) => {
    // Get current version
    const current = await db.prepare(
      'SELECT balance, version FROM user_deposits WHERE user_id = ?'
    ).bind(userId).first<{ balance: number; version: number }>();

    // Update with version check
    const result = await db.prepare(
      'UPDATE user_deposits SET balance = balance + ?, version = version + 1 WHERE user_id = ? AND version = ?'
    ).bind(amount, userId, current.version).run();

    if (!result.success || result.meta.changes === 0) {
      throw new OptimisticLockError('Version mismatch');
    }

    return result;
  });
} catch (error) {
  if (error instanceof OptimisticLockError) {
    logger.warn('동시성 충돌 감지', { userId, amount });
    throw new Error('처리 중 오류가 발생했습니다. 잠시 후 다시 시도해주세요.');
  }
  throw error;
}

2. index.ts

Changes:

• Added import of reconcileDeposits and formatReconciliationReport
• Added reconciliation job to scheduled() handler (lines 234-256)
  • Runs after expiry cleanup
  • Sends admin notification if inconsistencies found
  • Graceful error handling (doesn't break cron)

3. schema.sql

Changes:

• Added version INTEGER NOT NULL DEFAULT 1 column to user_deposits table
• Added index idx_deposits_user_version ON user_deposits(user_id, version)

4. CLAUDE.md

Changes:

• Added "Transaction Isolation & Optimistic Locking" section under "Deposit System"
• Documents problem, solution, implementation details
• Includes migration commands and verification steps
• Provides concurrency scenario example

How It Works

Optimistic Locking Flow

1. Request arrives (deposit or confirm)
   ↓
2. Read current balance and version
   ↓
3. Perform operations
   ↓
4. UPDATE balance with version check:
   WHERE user_id = ? AND version = ?
   ↓
5. Check if changes = 0 (version mismatch)
   ↓
   YES: Throw OptimisticLockError → Retry with backoff
   NO: Success → Return result

Retry Strategy

• Attempt 1: Immediate execution
• Attempt 2: Wait 100ms, retry
• Attempt 3: Wait 200ms, retry
• Attempt 4: Wait 400ms, retry (final)
• Max retries exhausted: Return user-friendly error message

Reconciliation (Daily Cron)

Every day at KST 00:00:
1. Compare user_deposits.balance with SUM(confirmed transactions)
2. Detect discrepancies
3. Log all inconsistencies with details
4. Send admin notification if issues found
5. Generate detailed report

Testing

Local Testing

# 1. Apply migration to local database
wrangler d1 execute telegram-conversations --local --file=migrations/002_add_version_columns.sql

# 2. Verify version column
wrangler d1 execute telegram-conversations --local --command "SELECT user_id, balance, version FROM user_deposits LIMIT 5"

# 3. Start local dev server
npm run dev

# 4. Test deposit flow
curl -X POST http://localhost:8787/webhook \
  -H "Content-Type: application/json" \
  -H "X-Telegram-Bot-Api-Secret-Token: test-secret" \
  -d '{"message":{"chat":{"id":123},"text":"홍길동 10000원 입금"}}'

# 5. Check logs for optimistic lock messages
npm run tail

Production Deployment

# 1. BACKUP database first (important!)
wrangler d1 export telegram-conversations --output=backup-$(date +%Y%m%d).sql

# 2. Apply migration
wrangler d1 execute telegram-conversations --file=migrations/002_add_version_columns.sql

# 3. Verify migration
wrangler d1 execute telegram-conversations --command "PRAGMA table_info(user_deposits)"

# 4. Deploy code changes
npm run deploy

# 5. Monitor logs
npm run tail

# 6. Test with actual deposit
# (Use Telegram bot to test deposit flow)

# 7. Wait for next cron run (KST 00:00) to test reconciliation
# Or manually trigger: wrangler d1 execute --command "SELECT ..."

Concurrency Test Scenario

Simulate concurrent requests:

1. User has balance: 10,000원 (version=1)
2. Send two deposit requests simultaneously:
   • Request A: +5,000원
   • Request B: +3,000원
3. Expected behavior:
   • Request A: Reads version=1, updates to version=2 ✅
   • Request B: Reads version=1, fails (version mismatch), retries
   • Request B: Reads version=2, updates to version=3 ✅
4. Final state: 18,000원 (version=3)

Benefits

Data Integrity

• Before: Batch operations could partially fail, causing balance/transaction mismatch
• After: Version conflicts detected and automatically retried, guaranteeing consistency

Concurrency Safety

• Before: Simultaneous deposits could overwrite each other
• After: Version column prevents lost updates through automatic retry

Monitoring

• Before: No automated integrity verification
• After: Daily reconciliation job detects and alerts on any discrepancies

Performance

• Impact: Minimal - version check adds negligible overhead
• Index: idx_deposits_user_version ensures fast version lookups

Rollback Plan

If issues occur:

# 1. Revert code changes
git revert <commit-hash>
npm run deploy

# 2. (Optional) Remove version column
# Note: SQLite doesn't support DROP COLUMN directly
# Alternative: Keep column but remove code dependency

# 3. Manual data fix (if needed)
wrangler d1 execute telegram-conversations --command "
  UPDATE user_deposits SET balance = (
    SELECT COALESCE(SUM(
      CASE
        WHEN type = 'deposit' AND status = 'confirmed' THEN amount
        WHEN type IN ('withdrawal', 'refund') AND status = 'confirmed' THEN -amount
        ELSE 0
      END
    ), 0)
    FROM deposit_transactions
    WHERE user_id = user_deposits.user_id
  )
"

Future Enhancements

1. Metrics Dashboard:

   • Track optimistic lock conflicts per day
   • Monitor retry success rate
   • Alert on high conflict rate (>10% of operations)

2. Compensation Transaction:

   • Automatic rollback on catastrophic failures
   • Transaction log for audit trail

3. Read Replicas:

   • Use version for cache invalidation
   • Enable eventual consistency patterns

4. Admin Tools:

   • Manual reconciliation trigger
   • Balance adjustment with audit log
   • Transaction replay for debugging

References

• Migration: migrations/002_add_version_columns.sql
• Utility: src/utils/optimistic-lock.ts, src/utils/reconciliation.ts
• Integration: src/deposit-agent.ts, src/index.ts
• Documentation: CLAUDE.md (Transaction Isolation section)