0

I have an automation project having a remote database (MsSql) and multiple windows/web clients. Client applications check and select a suitable row on a table and mark it as reserved for operating on that row.

  1. How can I easily get value and update a selected row before another client selects same row? What options do I have for that?
  2. Can I use TransactionScope for that purpose if I want to do more than one select-update statement (for different tables) inside transaction block?

EDIT: Let's imagine a cinema ticket system (mine is a little bit more complicated). The user selects a seat and buys ticket. I want to reserve the seat to this user for two minutes to give enough time for buying. After two minutes it will be also available to others. Before updating the row I run a select query to find the first empty seat. My question is about a little time between a select and update statement of same user. I want to prevent: User1's select statement runs and at that time User2 runs same select before User1 reserves the seat.

aliassce
  • 1,197
  • 6
  • 19
  • This may be of interest: http://www.codeproject.com/Articles/114262/6-ways-of-doing-locking-in-NET-Pessimistic-and-opt#How%20can%20we%20do%20pessimistic%20locking – JohnLBevan Oct 20 '12 at 23:23

2 Answers2

2

Since you want user input between locking and performing actual action, then

update top (1) seats
set time_locked = getdate(), user_locked = <the site user>
output inserted.seat_id, inserted.time_locked, inserted.user_locked
where <your conditions> and time_locked is null

In this case you don't start a transaction before doing this, because you want to commit the change immediately. You may open a transation later, as soon as you lock a row, or at an even later point.

If you do that from a transaction, then the other processes will wait until you commit that transaction, so you will not get any concurrency. What you will then see is, one process working, all others waiting.

When you later come back to the locked row, you verify that time_locked is still non-null, and the user who locked is the same user:

update seats
set totally_taken = 1
where
  seat_id = <remembered seat id>
  and time_locked = <remembered time_locked>
  and user_locked = <remembered user_locked>
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • Thanks for answer. I think the first solution will be enough but What happens if I put a select and update query inside TransactionScope? Is that enough for achieving the result without updlock,rowlock,readpast? – aliassce Oct 20 '12 at 23:53
  • @aliassce I removed the irrelevant part of the answer based on your edit. The query will both update and select, as one. You may wrap it into a transaction, but you don't have to, as you will have to commit it immediately anyway. – GSerg Oct 20 '12 at 23:58
  • What I want to understand does a transactionScope blocks all other clients access same tables before it completed or rolled back? – aliassce Oct 21 '12 at 00:10
  • @aliassce `TransactionScope` will generate a `begin tran` call, at which point it does not matter you had TransactionScope -- it only matters a `begin tran` was issued. Whether or not other clients will be blocked depends on indices you have and how you select your data. With a `select top (1)` query it is likely that other clients will be blocked; with a `select ... where row_id = @row_id` query it is likely they won't. – GSerg Oct 21 '12 at 07:37
0

If you are using SQL Server you can do a ROWLOCK in your select.

SELECT * FROM Orders WITH ROWLOCK WHERE Status='Pending'

Klaus Stefan Gerber
  • 245
  • 2
  • 15