I think this problem have pretty standard solution.
Locking should be done on application level.
As soon as clerk1 started to work on reservation query which return him
available seats mark those seat
locked (with clerk ID) and also write time of locking into this record.
Separate process (scheduled say every 5 minutes) running on database make
sure that no seats in database
get locked for more than 15 minutes, otherwise it automatically unlock them.
As clerk commit final reservation procedure check if seats still locked for
this clerk (have his ID)
or unlocked (nobody yet get them) and if yes
successfully commit reservation marking those seats as reserved, if seats
locked by other person or already reserved -
reservation failed.
Timeouts can be adjusted per business process and clerk terminal may have
indication of time expiration.
-------------------------------------------
Alexander Tarasul
Microsoft Certified Solution Developer(SQL Server,VB)
Certified Borland Delphi Client Server Developer
http://pages.ripco.com/~shleym
SQL Answers located at
http://pages.ripco.com/~shleym/sql_answers.htm
---------------
>Well, you have two reasonable options:
>1. When clerk 2 tries to finalize the reservation, it fails if it sees the
>seat has already been reserved by clerk 1 finalizing a reservation for the
>same seat first. The clerk should then be given the option to pick a
>different seat without having to re-enter all the other information before
>trying again. This could be a hassle if the data entry rate is high or if
>the time from seat assignment to finalization is long.
>2. Add an application-level locking flag to the seats table. When a clerk
>selects a seat for a reservation, the flag gets set to 1, and is returned
to
>zero during the posting transaction or if/when the reservation is cancelled
>before posting. This has the potential to leave an unreserved seat
"locked"
>if a client system crashes. You could partially fix this by using a client
>ID in the lock field instead of a zero or one, and have a client run a
query
>to unlock any of its previously held locks when it first starts up in case
>its last shut-down was abnormal.
>>Mike,
>>Thanks for your reply, let me see if I can better articulate my problem
and
>>then you can tell me if it's possible or not.
>>As an example, imagine that you have 5 airline reservations clerks working
>at 5
>>different terminals booking the flight and assisgning the seats. So, all 5
>>clerks are using the same table of available seats. My question is, when
>all
>>clerks ask for the next available best seat, clerk 1 should get 1A, clerk
2
>>should get 2A, clerk 3 should get 3A and so forth. Once 1A is used up by
>clerk
>>1, the flag would be set so that 1A is not available any longer. Under no
>>circumstances clerk 1 and 2 should both get the same seat, i.e. 1A. Does
>this
>>makes sense? Is there a better way of trying to accomplish this?
>>Again, TIA.
>>> Row level locking is not intended to be used to solve your unusual
>business
>>> requirement. There is no way to say "If this row is locked, then get
the
>>> next one." Besides, a lock should be held for the briefest period of
>time,
>>> not indefinately until a user has finished with the row.
>>> >Has anybody had any expereince with row-level locking with SQL Server
7?
>>> >Here is my problem:
by