Row-level locking in SQL Server 7

Row-level locking in SQL Server 7

Post by Albina Radielovi » Wed, 23 Jun 1999 04:00:00



Has anybody had any expereince with row-level locking with SQL Server 7?
Here is my problem:

I have a table that will be accesses by multiple users at the same time.
Once the first user (whoever that might be) accesses the first row of
that table, I need to lock that row so that the next user will get the
second row, and the third user will get the third row and so forth. I
have tried multiple ways to do row level locking but to no avail (this
is part of the SP so I can't set the isolation level unfortnatelly). Any
insight on how to solve this issue or in general how to resolve the
issue of locking a row under these conditions would be more then
appreciated.

TIA.

-Albina

 
 
 

Row-level locking in SQL Server 7

Post by Michael E. Poulio » Wed, 23 Jun 1999 04:00:00


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:


 
 
 

Row-level locking in SQL Server 7

Post by Umachandar Jayachandra » Wed, 23 Jun 1999 04:00:00


Use READPAST optimizer hint. This will skip the locked rows. For more help see BOL.

--
Umachandar Jayachandran
MCDBA, MCSE + Internet, MCP + Internet, MCSE

 
 
 

Row-level locking in SQL Server 7

Post by Albina Radielovi » Wed, 23 Jun 1999 04:00:00


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:

 
 
 

Row-level locking in SQL Server 7

Post by Glenn Crow » Wed, 23 Jun 1999 04:00:00


Albina,

    As others have pointed out, ReadPast can be used to bypass a locked row.
In the testing that I have done, this optimizer hint has some rather poor
performance characteristics.  You should be aware that this is not an
uncommon business situation and it is most often solved with business logic,
not by navigating the DBMS locking hierachy.

    What you are describing is a queue.  Multiple clients will be pulling
data from the queue and no client should recieve data that has already beeen
dished out to another client.  I would define my locking mechanism at the
application level.  Something like this...

Set RowCount 1

 Update Ticket set Status = 1, LockClerk = 'Clerk1' where  Status = 0

Set RowCount 0

This will be fairly multi-user safe and offer reasonable performance.  If is
even one row in the table that is unlocked by another user performing the
same update statement, you will get a hit.  Then....


 Select 'No Seats available'
Else
 Select Seat from TestTable where LockClerk = 'Clerk1'

This presumes that Clerk1 is only allowed to sell one seat at a time.  You
will have to make sure that Clerk1 relinquishes his lock if he doesn't sell
the seat.  You have other interesting problems yet to solve.  How does a
Clerk find and lock adjoining seats?  Others come to mind.

I think that the key in your case is to not check whether the row is
available or not, just issue you update statement and check to see if the
row gets changed.

--
Hope this helps.
Please reply to the newsgroup.
Thanks!
glennc

 
 
 

Row-level locking in SQL Server 7

Post by Steve Jorgense » Wed, 23 Jun 1999 04:00:00


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:

 
 
 

Row-level locking in SQL Server 7

Post by Kieran Owen » Thu, 24 Jun 1999 04:00:00


Steve,

I think that number 2 is the correct answer from a end user point of view as
it is less likely to annoy than 1.

Kieran


> 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:

 
 
 

Row-level locking in SQL Server 7

Post by Michael E. Poulio » Thu, 24 Jun 1999 04:00:00


Well, after reading some subsequent posts, I guess I should take back
everything I said.  READPAST?!?  That's a new one to me.  And I just
finished Delaney's book.  I guess I should've read it closer!  ;)

>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:

 
 
 

Row-level locking in SQL Server 7

Post by Alexander Tarasu » Thu, 24 Jun 1999 04:00:00


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:

 
 
 

1. row level locking in SQL Server 7

Hi,

Anybody tried that? I cannot get SQL Server to lock a single record without
locking a page or a table ...

BOL says that the type of lock is determined and escalated if necessary
automatically:
"Lock escalation thresholds are determined dynamically by SQL Server and require
no configuration."

Consider the following scenario:

create table Test (a int)
insert into Test values (1)
insert into Test values (2)
begin transaction
update b with (rowlock) set a = 1 where a = 1
-- POINT 1
commit transaction

At POINT 1, the following locking occurs:

spid   dbid   ObjId       IndId  Type Resource         Mode     Status
------ ------ ----------- ------ ---- ---------------- -------- ------
22     10     0           0      DB                    S        GRANT
22     10     581173416   0      PAG  1:86             IX       GRANT
22     10     581173416   0      RID  1:86:0           X        GRANT
22     10     581173416   0      TAB                   IX       GRANT

Why is page and table locked as well?
How to achive one record to be locked only?

Thanks,
Robert

2. Web - Related data input

3. Row-level locking in sql-server 6.5

4. Joins in Query

5. How do I apply row-level locking in SQL server with ADO

6. NEWERA for OS/2 ?

7. Row Level locking at SQL server level..... Please Help .........!!

8. Problems with corrupted Databases

9. table-level lock or row-level lock

10. Row Level Locking in SQL Server 7.0 (Please help)..........!!!!!!!!!!!

11. Row level lock in MS SQL Server 6.5 ?

12. SQL Server 7 - enforce row-level locking?

13. Row level locking -- SQL Server 7