0

I am writing a stored procedure that will use by application that function is booking. And it can happen concurrent booking. To handle that, which approach will be suitable?

I am trying to use isolation level "READ COMMITTED" & "UPDLOCK" hint to handle that. Can it be workable? Any comment and solution will be appreciate.

Another question is: if I use that isolation level and hint, can it be shared lock? If so, how can I handle it?

Best Regards, Reds

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
RedsDevils
  • 1,413
  • 9
  • 26
  • 47
  • What do you do when a booking occurs? When you're reading from SQL Server, your `SELECT` statement acquires a `shared lock` - but both `INSERT` and `UPDATE` will have to get an **exclusive** lock to insert / change data ... – marc_s Jul 12 '13 at 10:35
  • When I do booking, I would like to check available quantity of package. If it's enough, I want to proceed. If Transaction for user1 is still running, another concurrent user2 will book. Assume that available quantity is 8, both user are trying to both 5 each. At that time, I would like to return appropriate msg for user2 from stored procedure and I am worrying about dead lock and shared lock will happen to my stored procedure. I want to handle it. Thanks for your comment Marc. – RedsDevils Jul 16 '13 at 05:23
  • In addition to what you're doing, make sure you update your booking status from Pending into InProgress and use an update with output clause for the update. – Uri Abramson Nov 23 '15 at 10:05

0 Answers0