Transaction locking for aircraft seating allocation  
Author Message
tuthach





PostPosted: Mon Mar 28 15:08:37 CST 2005 Top

SQL Server >> Transaction locking for aircraft seating allocation

hello again,

I've been reading today about transactions and locks and was hoping to get
some help on the following. I'm working on the allocation of seats for
airplanes. It all works in a disconnected manner (it's accessed remotely via
webservice, and hence an update on the DB is not reflected on the user's side
immediately).

For obivous reasons I do not wish to allocate the same seat to two
passengers so I was thinking of the following: my aim is for each connection
to read a value to see whether a seat is available (A) or unavailable (U). If
the seat is available change its value to unavailable and return some value
to the user (e.g. "true"). Else, if the seat is unavailable return some other
value to the user (e.g. "false").

To ensure however that no to user reads the initial value of the seat as
available (A) and subsequently changes that same value for that same seat to
unavailable (U) I wish to use rowlocking. I believe that I should mainly be
worried about dirty reads and lost updates. I thought of using an exclusive
lock right from the moment I read the value until I either update it (if the
seat is available) or leave it as it is (if the seat is unavailable). I could
additionally use a timeout so that if a second connection is trying to access
that same seat and there is already a lock, it should not wait until the lock
is over but should return "false" immediately. Is it correct that row locking
only works when a table has an index? I have one/more primary key(s) per
table. Is that okay ?

I would greatly appreciate everyone's input on this problem.

Thanks so much for your time

SQL Server270  
 
 
David





PostPosted: Mon Mar 28 15:08:37 CST 2005 Top

SQL Server >> Transaction locking for aircraft seating allocation


> hello again,
>
> I've been reading today about transactions and locks and was hoping to get
> some help on the following. I'm working on the allocation of seats for
> airplanes. It all works in a disconnected manner (it's accessed remotely
> via
> webservice, and hence an update on the DB is not reflected on the user's
> side
> immediately).
>
> For obivous reasons I do not wish to allocate the same seat to two
> passengers so I was thinking of the following: my aim is for each
> connection
> to read a value to see whether a seat is available (A) or unavailable (U).
> If
> the seat is available change its value to unavailable and return some
> value
> to the user (e.g. "true"). Else, if the seat is unavailable return some
> other
> value to the user (e.g. "false").
>
> To ensure however that no to user reads the initial value of the seat as
> available (A) and subsequently changes that same value for that same seat
> to
> unavailable (U) I wish to use rowlocking. I believe that I should mainly
> be
> worried about dirty reads and lost updates. I thought of using an
> exclusive
> lock right from the moment I read the value until I either update it (if
> the
> seat is available) or leave it as it is (if the seat is unavailable). I
> could
> additionally use a timeout so that if a second connection is trying to
> access
> that same seat and there is already a lock, it should not wait until the
> lock
> is over but should return "false" immediately. Is it correct that row
> locking
> only works when a table has an index? I have one/more primary key(s) per
> table. Is that okay ?
>
If you solve this problem with SQL Locking you will have to either use
READPAST or NOLOCK hints for your other sessions not to block. Moreover in
a disconnected scenario you must guarantee that that the locked rows are
eventually unlocked. This will lead you to hold the SqlConnection in
session state, and all sorts of tricky or dangerous things. In general
solving this problem using database locks is tricky in a connected,
client/server app, and nearly impossible in a disconnected app.

I would recommend that you simply return the user a list of available seats,
and handle the selection of an unavailable seat by presenting the user with
an updated list of seats.

EG:
select id from seats where status = 'A'

then later



begin
raiserror('Seat no longer available',16,1)
end

Then the application would get a new list of available seats, etc.

David


 
 
jez





PostPosted: Mon Mar 28 16:01:03 CST 2005 Top

SQL Server >> Transaction locking for aircraft seating allocation Thank you for your reply. I tried the code your provided yet for some reason
I get an error on the THEN keyword. All I did is the following :

UPDATE seat
SET seatStatus='A'



BEGIN
raiserror('seat no longer available', 16, 1)
END

"incorrect syntax near the keyword 'THEN'. Any idea what that could be ?

Just to double-check, by using the piece of code as shown above, am I
certain that two different connections (i.e. passengers) will never be able
to select the same seat?

Thanks again




> If you solve this problem with SQL Locking you will have to either use
> READPAST or NOLOCK hints for your other sessions not to block. Moreover in
> a disconnected scenario you must guarantee that that the locked rows are
> eventually unlocked. This will lead you to hold the SqlConnection in
> session state, and all sorts of tricky or dangerous things. In general
> solving this problem using database locks is tricky in a connected,
> client/server app, and nearly impossible in a disconnected app.
>
> I would recommend that you simply return the user a list of available seats,
> and handle the selection of an unavailable seat by presenting the user with
> an updated list of seats.
>
> EG:
> select id from seats where status = 'A'
>
> then later
>


> begin
> raiserror('Seat no longer available',16,1)
> end
>
> Then the application would get a new list of available seats, etc.
>
> David
 
 
David





PostPosted: Mon Mar 28 16:50:31 CST 2005 Top

SQL Server >> Transaction locking for aircraft seating allocation


> Thank you for your reply. I tried the code your provided yet for some
> reason
> I get an error on the THEN keyword. All I did is the following :
>
> UPDATE seat
> SET seatStatus='A'



> BEGIN
> raiserror('seat no longer available', 16, 1)
> END
>
> "incorrect syntax near the keyword 'THEN'. Any idea what that could be ?
>
> Just to


Sure. Leave of THEN. It's a typo.

The intent of this statement is to update the row to 'U', only if it is 'A'

or not.

Should be

UPDATE seat
SET seatStatus='U'



AND seatStatus='A'

BEGIN
raiserror('seat no longer available', 16, 1)
END

David


 
 
jez





PostPosted: Mon Mar 28 18:57:05 CST 2005 Top

SQL Server >> Transaction locking for aircraft seating allocation It seems to be working fine now, thanks! I did however change the following.
It should work fine within the context of my application. I'm just wondering
whether this 'solution' is good enough in terms of sql/concurrency problem.

Thanks again


BEGIN
SELECT 'false' as response
END

ELSE
BEGIN
SELECT 'true' as response
END



> Sure. Leave of THEN. It's a typo.
>
> The intent of this statement is to update the row to 'U', only if it is 'A'

> or not.
>
> Should be
>
> UPDATE seat
> SET seatStatus='U'



> AND seatStatus='A'

> BEGIN
> raiserror('seat no longer available', 16, 1)
> END
>
> David