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.

outis
  • 75,655
  • 22
  • 151
  • 221
roz
  • 791
  • 1
  • 5
  • 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. – S.Lott Jul 29 '11 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. – roz Jul 29 '11 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? – S.Lott Jul 29 '11 at 13:10
  • possible duplicate of [Optimistic vs. Pessimistic locking](http://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking) – S.Lott Jul 30 '11 at 01:51

1 Answers1

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.

Community
  • 1
  • 1
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • but internally how is this done? Throughthe use of row locks, table locks etc? Under which concurrency control mechanism, optimistic or pessimistic? – roz Jul 29 '11 at 13:08
  • 1
    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. – Tony Andrews Jul 29 '11 at 13:23