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
An explanation...
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.