What is the difference between optimistic and pessimistic concurrency control?
Quick Answer
Pessimistic — lock the row when you read it so nobody else can change it until you're done (SELECT ... WITH (UPDLOCK, HOLDLOCK)). Optimistic — don't lock; read a version token, and on save check it hasn't changed, failing if it has. Optimistic scales better and is the default in web apps; pessimistic suits short, high-contention critical sections.
Pessimistic locking means you assume a conflict is likely, so you lock the row as you read it and hold that lock until you commit — nobody else touches it in the meantime. Optimistic means you assume conflicts are rare, so you don't lock; you read a version stamp, and when you save you verify the row still has that version, and if it doesn't you've hit a conflict and you retry or ask the user to merge. In a web app I always go optimistic because you can't hold a database lock while a user stares at a form. Pessimistic only makes sense for very short server-side critical sections.
- Optimistic (rowversion / timestamp column, or EF concurrency token): assumes conflicts are rare. On conflict you re-read and merge or retry. No locks held across the user “think time”.
- Pessimistic: take an update lock as part of the read within a short transaction; blocks other writers. Only viable when the transaction is milliseconds long — never across a user's screen.
- Rule of thumb: web/stateless → optimistic; a tight server-side loop processing a queue with high contention on the same rows → pessimistic can be simpler.