Lecture Notes: Optimistic vs. Pessimistic Concurrency Control

Introduction

• Topic: Difference between optimistic and pessimistic concurrency control
• Importance: Critical for database operations
• Personal experience: Learning from comments and making updates
• Promotion: Mentioned Udemy course on database engineering

Why Concurrency Control Matters

• Essential for multi-user databases (Postgres, MySQL, MongoDB)
• Not needed for single-user databases
• Concurrency is about managing multiple transactions concurrently

Lost Updates

• Concept: Preventing a value from being overwritten before it is committed
• Dangerous: Causes data corruption
• Example: Locking a row during a transaction

Pessimistic Concurrency Control

• Concept: Locking resources (rows) to prevent changes by other transactions
• Example: Carrying an umbrella even if it might not rain
• Locks: Expensive and need management; implemented differently in various databases
  • SQL Server: Locks are managed in memory, with possible lock escalation
  • Postgres: Locks are managed on disk
  • MySQL: Likely uses memory, not sure
• Implementation: Row-level, table-level, page-level locks
• Drawback: Performance and resource management cost

Optimistic Concurrency Control

• Concept: No locking during transactions, checking at commit time
• Example: Not carrying an umbrella, dealing with rain if it happens
• Rollback and retry transactions if conflicts occur
• MongoDB: Uses this model with WireTiger engine, optimized for multi-core CPUs
• Advantage: No lock management overhead
• Drawback: Burden on clients to handle retries

Conclusion

• No clear winner; depends on use case
• Pessimistic: Suits users who prefer direct control and are willing to manage locks (Postgres, SQL Server)
• Optimistic: Suits users who prefer less overhead and are fine with dealing with conflicts (MongoDB)

Personal Opinion

• Preference for pessimistic control due to mindset and lock management
• Acknowledgement of trade-offs between right amplification in Postgres and memory cost in SQL Server
• Question: What do others prefer?

Final Thoughts

• Open invitation for discussion
• Encouragement to consider specific needs and constraints in choosing concurrency control approach