SQL Server ROWLOCK over a SELECT if not exists INSERT transaction

Question

I have upgraded from SQL Server 2005 to 2008. I remember that in 2005, ROWLOCK simply did not work and I had to use PAGELOCK or XLOCK to achieve any type of actual locking. I know a reader of this will ask "what did you do wrong?" Nothing. I conclusively proved that I could edit a "ROWLOCKED" row, but couldn't if I escalated the lock level. I haven't had a chance to see if this works in SQL 2008. My first question is has anyone come across this issue in 2008?

My second question is as follows. I want to test if a value exists and if so, perform an update on relevant columns, rather than an insert of the whole row. This means that if the row is found it needs to be locked as a maintenance procedure could delete this row mid-process, causing an error.

To illustrate the principle, will the following code work?

BEGIN TRAN

SELECT      ProfileID
FROM        dbo.UseSessions
WITH        (ROWLOCK)
WHERE       (ProfileID = @ProfileID)
OPTION      (OPTIMIZE FOR (@ProfileID UNKNOWN))

if @@ROWCOUNT = 0 begin
    INSERT INTO dbo.UserSessions (ProfileID, SessionID)
    VALUES      (@ProfileID, @SessionID)
end else begin
    UPDATE      dbo.UserSessions
    SET         SessionID = @SessionID, Created = GETDATE()
    WHERE       (ProfileID = @ProfileID)
end

COMMIT TRAN

Solution

An explanation...

• ROWLOCK/PAGELOCK is granularity
• XLOCK is mode

Granularity and isolation level and mode are orthogonal.

• Granularity = what is locked = row, page, table (PAGLOCK, ROWLOCK, TABLOCK)

• Isolation Level = lock duration, concurrency (HOLDLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE)

• Mode = sharing/exclusivity (UPDLOCK, XLOCK)

• "combined" eg NOLOCK, TABLOCKX

XLOCK would have locked the row exclusively as you want. ROWLOCK/PAGELOCK wouldn't have.

Comments

[IamIC]

Thanks @gbn. So "WITH (XLOCK, ROWLOCK)" would do the trick :)

[IamIC]

oddly WITH (PAGELOCK) did lock the row. From what you're saying, I don't know why it would have.

[Chris KL]

@IanC no it certainly wouldn't do the trick - read my answer above :)