0

I have the following simple scenario.

Three computers will be updating a cinema booking table. Should I lock the whole table or lock the rows corresponding to seats that will be updates? Can I consider optimistic concurrency control here?

I was just searching about optimistic and pessimistic concurrency control and stumbled about different locks and was wondering which would be the most suitable for which scenarios.

Improve this question
4
  • 1
    If this is homework, please use the [homework] tag. If this is not homework, please name the actual database you'll actually be using for this application. Most RDBMS do row locking, but you may be using one that doesn't do row locking. Commented Jul 29, 2011 at 13:02
  • this is not homework. I was just searching about optimistic and pessimistic concurrency control and stumbled about different locks and was wondering which would be the most suitable for which scenarios. Commented Jul 29, 2011 at 13:07
  • 1
    "I was just searching about optimistic and pessimistic concurrency control and stumbled about different locks". Okay. A link would be helpful so we know what you're talking about. "wondering which would be the most suitable for which scenarios." There's only one scenario in the question. Further, this is an automatic feature of an RDBMS. Are you asking about overriding the default locking strategy? Commented Jul 29, 2011 at 13:10
  • possible duplicate of Optimistic vs. Pessimistic locking Commented Jul 30, 2011 at 1:51

1 Answer 1

Reset to default
1

Your DBMS should take care of such locking, you just need to ensure you don't try to update a seat that has already been booked - e.g.

update seat
set status = 'BOOKED', booking_ref = :booking_ref
where seat_no = :seat_no -- identify the seat to book
and status = 'FREE';     -- ensure it is currently free

If 2 session try to book the same seat at the same time, one will succeed and the other will fail. Your code needs to check whether it succeeded or not.

Optimistic vs. pessimistic locking is an application rather than DBMS issue. Rather than go into it all here I'll refer you to an existing SO question.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

but internally how is this done? Throughthe use of row locks, table locks etc? Under which concurrency control mechanism, optimistic or pessimistic?
The answer to that may vary by DBMS. Ideally, a row lock - lock the minimum that needs to be locked to achieve the purpose. Optimistic v. pessimistic locking is a different matter altogether, it is an application issue not a database issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.