Optimistic vs Pessimistic Database Concurrency Control Explained in Analogy

Like my previous post on database recovery mechanisms, this article stems from leading our database theory book club. When we reached the chapter on concurrency control, I noticed that the abstract concepts of Optimistic and Pessimistic concurrency control were challenging for some members to grasp fully.

After our discussion, I searched for a more relatable analogy to explain these database concepts. That's when it occurred to me — a public library's book borrowing system serves as an excellent metaphor for understanding the different approaches to concurrency control in database systems.

The Library Book Borrowing Analogy

Imagine a busy public library with many popular books. How the library manages its book lending reflects the fundamental differences between concurrency control approaches. Let's dive into this rabbit hole together!

1. Pessimistic Concurrency Control: The Strict Librarian

In a library with pessimistic lending policies, there's an extremely strict librarian at the desk — the kind who can silence an entire room with just a raised eyebrow. When someone picks up a popular book from the shelf, the librarian immediately attaches a "Checked Out" tag to it. While this tag is attached, no one else can borrow or even reserve the book. Others must simply wait until the book is returned before they can access it.

Library scenario: Dave picks up "Database Systems: The Complete Book"
and approaches the checkout desk. The librarian immediately marks it
as unavailable in the system. When Emily comes looking for the same
book moments later, she's told it's checked out and must wait until
Dave returns it, even if Dave is still browsing other sections and
hasn't actually left the library yet. Poor Emily.

In database terms, this represents Pessimistic Concurrency Control, where the system assumes conflicts will occur and prevents them by locking resources before they're modified. The transaction acquires locks on the data it wants to change, and those locks prevent other transactions from accessing the same data until the first transaction completes.

The biggest advantage here is definitely Data consistency - conflicts are unlikely since resources are locked before modification. Multiple people cannot simultaneously check out the same book, preventing inconsistencies in the library catalog.

On the other hand, the disadvantage is seemingly Reduced throughput - popular books are quickly marked as "in use," causing many to wait, potentially reducing overall library usage efficiency. This is why you still haven't been able to check out that bestseller everyone's talking about.

2. Optimistic Concurrency Control: The Flexible Reservation System

In contrast, a library with optimistic lending policies doesn't immediately restrict access to books. Anyone freely browse the shelves and can even add their names to a reservation list for books they're interested in.

However, at the actual checkout time, the librarian performs a final verification: "Has this book been checked out by someone else since you picked it up from the shelf?" If someone else has already borrowed it, they'll say, "I'm sorry, this book is now checked out. You can either join the waiting list or look for something else." Yes, optimistic concurrency control is basically the library equivalent of "Sorry, we ran out of pizza. Want some salad instead?" It sounds healthy, and you can enjoy serendipity, but you might not be able to finish your homework.

Library scenario: Carlos picks up "Modern Database Systems" from the
shelf and continues browsing. Meanwhile, Diana books the same
book from the online booking system. When Carlos tries to
check out 15 minutes later, the librarian says, "I'm sorry, this book was just booked online.
Would you like to place a hold on it for when it's returned?"
Carlos sighs, while Diana enjoyed drinking her favourite coffee in the kitchen before going out to pick her book.

This represents Optimistic Concurrency Control, where the system assumes conflicts are rare and allows transactions to proceed without locking resources. Only at commit time does the system verify that no conflicts occurred, and if they did, one transaction must be rolled back.

The key difference from Pessimistic approach is when verification happens - at the time of picking up books, or at the time of checking outs. The advantage of Optimistic approach basically comes from the fact that it's lock-free. For example, you can expect higher throughput in theory, as database transactions can progress without waiting for locks. In practical sense, this is particularly effective for read-heavy workloads - imagine the situation when most people are just browsing books without checking them out, an optimistic approach minimizes unnecessary restrictions. If in the Pessimistic land, every time you pick up books from the bookshelf, you'll be nudged by strict librarians immediately, which is not an ideal experience!

On the other hand, if a popular book is frequently accessed, people might repeatedly attempt to check it out only to be told at the last moment that it's unavailable, requiring them to start their selection process over again. Wasted efforts on conflicts are huge. This is a disadvantage of the Optimistic approach. You might know a very optimistic person (or you're the one), who enjoy the life most of the cases without too much trouble. Mostly fine, but is deadly busy during the hard time. That's the cost of being optimistic.

Real-World Applications

The choice between pessimistic and optimistic concurrency control depends on your specific use case. Here's a handy guide for those moments when you're standing at the database crossroads, wondering which path to take:

Scenario	Preferred Approach	Reason
High-contention banking transactions	Pessimistic	Financial data integrity is critical; conflicts are likely
Social media content viewing	Optimistic	Many reads, few writes; conflicts are rare
Inventory management during sales	Combined	Pessimistic for critical inventory, optimistic for browsing

Conclusion

Coming back to our library analogy: the pessimistic approach is like having a strict librarian who prevents potential conflicts before they happen but might make the library feel restrictive. The optimistic approach creates a more flexible environment where people can browse freely, though they occasionally face disappointment at checkout time. It's essentially the difference between "rules first" and "let's see what happens."

Which library do you want to go? As being the case in answering most of real worlds software engineering questions, it depends - if you want to enjoy browsing freely for looking for serendip moments in the weekend, you'll go to the Optimistic one. If you have specific books in mind, you'll want to go to the Pessimistic one. Or, modern and combined libraries built near the station last year.

It dpeneds. Choose the concurrency control (or which library to go) wisely.