ROWLOCK - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

ROWLOCK

Author

Topic

tedmanowar
Starting Member

31 Posts

Posted - 2007-05-17 : 09:54:14

Hello all,

Does anyone know the lifetime of a ROWLOCK when stated in a sproc like this one below ?

ALTER PROCEDURE GetLastId
@idType nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @lastId int

SET @lastId= (SELECT lastId
FROM LastIds WITH (ROWLOCK)
WHERE idType=@idType)

SET @lastId=@lastId+1

UPDATE LastIds SET lastId=@lastId WHERE idType=@idType

SELECT (@lastID) as LastID

END
GO

I am wondering whether the lock will exist until the end of the sproc or not...

Many thanks in advance...

TedManowar

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-17 : 09:56:15

no it won't.
it will exist until the end of your select statement whic you can also rewrite like this
SELECT @lastId = lastId
FROM LastIds WITH (ROWLOCK)
WHERE idType=@idType

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

tedmanowar
Starting Member

31 Posts

Posted - 2007-05-17 : 09:57:59

Thanks for your quick reply man!

Do you know of a way to lock a particular row for the duration of the sproc ?

TedManowar

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-17 : 10:03:58

try with this:

begin tran
select ....
from ... WITH (ROWLOCK, XLOCK)
...
update...
commit

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources