10. Deadlocks, tempdb and how they relate to app design

Deadlock: two sessions each hold a lock the other needs. The lock monitor detects the cycle and kills the cheaper transaction (deadlock victim, error 1205); the application should retry.

Session A: UPDATE Orders (row 1, X lock) → then wants Customers (row 7)
Session B: UPDATE Customers (row 7, X lock) → then wants Orders (row 1)   → cycle → one is the victim

Prevention: access tables in the same order, keep transactions short (no user interaction or HTTP calls inside), index foreign keys and search columns (fewer rows locked), use RCSI so reads don't take shared locks, use UPDLOCK for read-then-update patterns, and retry 1205 in code. Capture graphs from the system_health Extended Events session.

tempdb — shared by the whole instance: temporary tables (#t), table variables, sort/hash spills, version store, index rebuilds with SORT_IN_TEMPDB, internal worktables. Best practices: multiple equally sized data files (typically 1 per core up to 8) to reduce allocation contention, fast storage, memory-optimised tempdb metadata (2019+).