Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-10-25 : 16:21:30
|
Matthijs writes "Hi,
I've read, and used last article of NOLOCK and ROWLOCK(great, really great article!), and I still have a question(using mssql 7.0):
I have a stored procedure(I've included it at the end of this message) that returns a random row. when I execute it, the only locks that appear are Schema-S locks, wich I can't prevent, and dont really bother(am I right?), and still there is one Exclusive lock, at it is at the stored procedure!! Whats happening here? Why is there a lock on the stored procedure?
And can I prevent this lock?? I use the following ADO command to open the SP:
rs.Open sql, dbglobalweb,adOpenForwardOnly
Thanks!
Matthijs Vader
Vakantie4u.nl
CREATE PROCEDURE sp_RandomImages_get_01 (@type tinyint)
/* Deze stored procedure haalt een random image uit tblRandomImages, en checkt op het type(indien meegegeven).
geschreven door Matthijs Vader, 9 oktober 2000
*/
AS
Declare @MaxValue int, @RandomNumber float
if (@type = '')
begin
Select @MaxValue = max(idimage) from tblRandomImages with (nolock)
Select @RandomNumber = rand() * @MaxValue
Select TOP 1 url
From tblRandomImages with (nolock)
Where idimage >= @RandomNumber + 1
Order by idimage ASC
end
else
begin
Select @MaxValue = max(SequentialFieldPerIDImageType) from tblRandomImages with (nolock) where idimagetype = @type
Select @RandomNumber = rand() * @MaxValue
Select TOP 1 url
From tblRandomImages with (nolock)
Where SequentialFieldPerIDImageType >= @RandomNumber + 1 and idimagetype = @type
Order by SequentialFieldPerIDImageType ASC
end" |
|
|
|
|
|
|
|
locking and stored procedures