Describe a challenging bug you've encountered and how you resolved it.

The Problem: We experienced intermittent database deadlocks in a high-traffic ASP.NET Core API that processed financial transactions. The deadlocks occurred randomly, affecting approximately 2-3% of requests during peak hours, causing transaction failures and customer complaints.

Investigation Process:

1. Initial Analysis:

   • Reviewed application logs and found SqlException with error code 1205 (deadlock victim)
   • Enabled SQL Server deadlock tracing using trace flags 1204 and 1222
   • Captured deadlock graphs showing two transactions locking resources in opposite order

2. Root Cause Identification:

   • Transaction A: Updated Account table, then Transaction table
   • Transaction B: Updated Transaction table, then Account table
   • Classic circular locking scenario exacerbated by long-running transactions

3. Debugging Steps:

   • Added detailed logging with correlation IDs to track transaction flow
   • Used SQL Server Profiler to analyze query execution plans
   • Discovered N+1 query problems within transactions, extending lock duration
   • Found missing indexes on foreign key columns

Resolution:

1. Immediate Fixes:

   • Implemented consistent locking order across all transaction types
   • Added WITH (UPDLOCK, ROWLOCK) hints to prevent lock escalation
   • Reduced transaction scope by moving non-critical operations outside transactions

2. Long-term Improvements:

   • Enabled eager loading to eliminate N+1 queries
   • Added appropriate indexes based on execution plan analysis
   • Implemented optimistic concurrency using row versioning
   • Added retry logic with exponential backoff for transient failures
   • Set appropriate transaction isolation levels (Read Committed Snapshot)

3. Code Example:

// Before - Problematic code
using var transaction = await context.Database.BeginTransactionAsync();
var account = await context.Accounts.FindAsync(accountId);
account.Balance += amount;
var txn = new Transaction { AccountId = accountId, Amount = amount };
context.Transactions.Add(txn);
await context.SaveChangesAsync();
await transaction.CommitAsync();

// After - Optimized code
using var transaction = await context.Database.BeginTransactionAsync();
// Consistent lock order: Account first, then Transaction
var account = await context.Accounts
    .Where(a => a.Id == accountId)
    .FirstOrDefaultAsync();
    
if (account == null)
    throw new AccountNotFoundException();

account.Balance += amount;
account.RowVersion = Guid.NewGuid(); // Optimistic concurrency

var txn = new Transaction 
{ 
    AccountId = accountId, 
    Amount = amount,
    Timestamp = DateTime.UtcNow 
};
context.Transactions.Add(txn);

await context.SaveChangesAsync();
await transaction.CommitAsync();

Results:

• Deadlocks reduced from 2-3% to less than 0.01% of requests
• Average response time improved by 40%
• Customer complaints dropped to near zero

Key Learnings:

• Always maintain consistent locking order across the application
• Keep transactions as short as possible
• Use appropriate isolation levels
• Monitor and analyze deadlock graphs proactively
• Implement proper retry mechanisms for transient failures