如果不存在INSERT事务，则通过SELECT执行SQL Server ROWLOCK [英] SQL Server ROWLOCK over a SELECT if not exists INSERT transaction

问题描述

我已从SQL Server 2005升级到2008.我记得在2005年，ROWLOCK根本不起作用，我不得不使用PAGELOCK或XLOCK来实现任何类型的实际锁定.我知道读者会问:你做错了什么?"没有.我最终证明可以编辑"ROWLOCKED"行，但如果升级锁级别则不能.我没有机会查看它是否可以在SQL 2008中使用.我的第一个问题是，有人在2008年遇到过此问题吗?

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

推荐答案

说明...

• ROWLOCK/PAGELOCK是粒度
• XLOCK是模式

粒度，隔离度和模式是正交的.

Granularity and isolation level and mode are orthogonal.

• 粒度=锁定的对象=行，页，表(PAGLOCK, ROWLOCK, TABLOCK)

隔离级别=锁定持续时间，并发(HOLDLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE)

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

模式=共享/排他性(UPDLOCK, XLOCK)

Mode = sharing/exclusivity (UPDLOCK, XLOCK)

组合"，例如NOLOCK, TABLOCKX

XLOCK将根据需要专门锁定该行. ROWLOCK/PAGELOCK不会.

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

这篇关于如果不存在INSERT事务，则通过SELECT执行SQL Server ROWLOCK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！