SQL Server ROWLOCK over SELECT如果不存在INSERT事务

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?

我已经从SQL Server 2005升级到2008.我记得在2005年,ROWLOCK根本无法工作,我不得不使用PAGELOCK或XLOCK来实现任何类型的实际锁定。我知道读者会问“你做错了什么?”没有。我最终证明我可以编辑一个“ROWLOCKED”行,但如果我升级锁定级别则不行。我没有机会看到它是否适用于SQL 2008.我的第一个问题是有没有人在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

1 个解决方案

#1

An explanation...

ROWLOCK/PAGELOCK is granularity

ROWLOCK / PAGELOCK是粒度

XLOCK is mode

XLOCK是模式

Granularity and isolation level and mode are orthogonal.

粒度和隔离级别和模式是正交的。

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

粒度=锁定的内容=行,页,表(PAGLOCK,ROWLOCK,TABLOCK)
Isolation Level = lock duration, concurrency (HOLDLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE)

隔离级别=锁定持续时间,并发性(HOLDLOCK,READCOMMITTED,REPEATABLEREAD,SERIALIZABLE)
Mode = sharing/exclusivity (UPDLOCK, XLOCK)

模式=共享/排他性(UPDLOCK,XLOCK)
"combined" eg NOLOCK, TABLOCKX

“合并”,例如NOLOCK,TABLOCKX

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

XLOCK可以根据需要独占锁定行。 ROWLOCK / PAGELOCK不会。

#1