title: SQL Server ROWLOCK over a SELECT if not exists INSERT transaction
body: <p>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?</p>
  
  <p>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.</p>
  
  <p>To illustrate the principle, will the following code work?</p>
  
  <pre><code>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
  </code></pre>