Geek Answers Handbook

SQL Server ROWLOCK over SELECT if INSERT transaction does not exist

I upgraded from SQL Server 2005 to 2008. I remember that in 2005 ROWLOCK just didn't work, and I had to use PAGELOCK or XLOCK to achieve any type of actual lock. I know that the reader will ask: "What did you do wrong?" Nothing. I convincingly proved that I can edit the "ROWLOCKED" line, but could not if I increased the lock level. I did not have the opportunity to find out if this works in SQL 2008. My first question is: did anyone encounter this problem in 2008?

My second question is as follows. I want to check if a value exists, and if so, update for the corresponding columns, rather than insert the entire row. This means that if a row is found, it must be locked, since the maintenance procedure may delete this intermediate sequence of rows, 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
+6
sql-server locking sql-server-2008 sql-server-2005 rowlocking
source share
1 answer

Explanation...

  • ROWLOCK / PAGELOCK is granularity.
  • XLOCK is a mode

The 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", for example NOLOCK, TABLOCKX

XLOCK would lock the row as you want. ROWLOCK / PAGELOCK would not be.

+13
source share

More articles:


All Articles