ROWLOCK | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

ROWLOCK

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 [email protected]) SET @[email protected]+1 UPDATE LastIds SET [email protected] WHERE [email protected] 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…
Yes it does until the update is finished and based on the UPDATE in which you specify the primary key would always cause a row lock, but when SQL Server gets a batch with a bunch of these, and some of them happen to be in the same page (depending on this situation). If you are having lot of performance issues then use NOLOCK hint too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |