Row Locking In a Stored Procedure 
Author Message
 Row Locking In a Stored Procedure

Quote:

>I need to implement some kind of row locking within the stored proc between
>selecting the record and updating the 'used' field.  At present it is still
>possible for two users to retrieve the same record.

When you select the record, use the "holdlock" qualifier.  This will keep
the
record locked until the end of the transaction.

Details can be found in Books Online (search for "holdlock") or in the
Transact/SQL Help (under "select").

--
Curt Hagenlocher



Mon, 23 Apr 2001 03:00:00 GMT
 Row Locking In a Stored Procedure

I'm using a stored procedure to return a single record to a user.  The
record must be unused and each time a record is selected the stored
procedure will mark it as 'used' so that other users do not get the same
record.

I need to implement some kind of row locking within the stored proc between
selecting the record and updating the 'used' field.  At present it is still
possible for two users to retrieve the same record.

The stored procedure contains two SELECT statements which are used to select
the appropriate record (if the first SELECT fails to return a record then
the second is executed) and then an UPDATE statement is used to update the
'used' field in that record.

How do I implement locking for the duration of the stored procedure?

TIA

 - Adam



Tue, 24 Apr 2001 03:00:00 GMT
 Row Locking In a Stored Procedure
I assume that you want to aquire an exclusive lock as well. Add something
like PAGLOCKX in that case...

--
Tibor Karaszi
MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please do not respond by e-mail unless explicitly asked for.


Quote:

>>I need to implement some kind of row locking within the stored proc
between
>>selecting the record and updating the 'used' field.  At present it is
still
>>possible for two users to retrieve the same record.

>When you select the record, use the "holdlock" qualifier.  This will keep
>the
>record locked until the end of the transaction.

>Details can be found in Books Online (search for "holdlock") or in the
>Transact/SQL Help (under "select").

>--
>Curt Hagenlocher




Tue, 24 Apr 2001 03:00:00 GMT
 Row Locking In a Stored Procedure
I have a similar question, but I am using VB 6.0 with SQL server 7.0 beta.
Is the holdlock used when you initially select the record using a stored
procedure?  This would be the point at which the vb text boxes are loaded
with the fields from the sql server table.  But before the user makes
changes and saves.  If the holdlock is applied at this point, would that not
prevent others from changing the record until the first user updates or
exits, i.e. pessimistic locking.

But if the holdlock is done when the user changes and saves the record, how
does one alert the user if changes were made and posted before this user
posts their changes?  Does one use a timestamp and check these?  If so, and
changes were made by another user, what then?

thanks

SM Haig

Quote:

>I'm using a stored procedure to return a single record to a user.  The
>record must be unused and each time a record is selected the stored
>procedure will mark it as 'used' so that other users do not get the same
>record.

>I need to implement some kind of row locking within the stored proc between
>selecting the record and updating the 'used' field.  At present it is still
>possible for two users to retrieve the same record.

>The stored procedure contains two SELECT statements which are used to
select
>the appropriate record (if the first SELECT fails to return a record then
>the second is executed) and then an UPDATE statement is used to update the
>'used' field in that record.

>How do I implement locking for the duration of the stored procedure?

>TIA

> - Adam



Thu, 26 Apr 2001 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. locks, stored procedure, update row

2. Row level locking use system stored procedure via ADO

3. Row Locking in Stored Procedures

4. Returning Rows from a Stored Procedure to another Stored Procedure

5. Stored Procedure that receive rows from other Stored Procedure

6. Calling a Java Stored Procedure from another Java Stored Stored Procedure

7. Page lock to simulate row level lock

8. Row lock automagically being escalated to Table Locks

9. table-level lock or row-level lock

10. Row locking versus page locking

11. SYBASE page locks outperform ORACLE row locks

12. Row-locking instead of page-locking ?


 
Powered by phpBB® Forum Software