如果不存在 INSERT 事务，则 SQL Server 对 SELECT 进行 ROWLOCK
我已经从 SQL Server 2005 升级到 2008。我记得在 2005 年，ROWLOCK 根本不起作用，我必须使用 PAGELOCK 或 XLOCK 来实现任何类型的实际锁定。我知道读者会问“你做错了什么？”没有什么。我最终证明我可以编辑“ROWLOCKED”行，但如果我升级锁定级别则无法编辑。我还没有机会看看这在 SQL 2008 中是否有效。我的第一个问题是有人在 2008 年遇到过这个问题吗？
我的第二个问题如下。我想测试某个值是否存在，如果存在，则对相关列执行更新，而不是插入整行。这意味着如果找到该行，则需要将其锁定，因为维护过程可能会在过程中删除该行，从而导致错误。
为了说明原理，下面的代码行得通吗？
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
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
一个解释...
粒度和隔离级别和模式是正交的。
粒度 = 锁定的内容 = 行、页、表 (
PAGLOCK、ROWLOCK、TABLOCK)隔离级别 = 锁定持续时间、并发性 (
HOLDLOCK、READCOMMITTED、REPEATABLEREAD、SERIALIZABLE)模式 = 共享/独占（
UPDLOCK、XLOCK）“组合”例如
NOLOCK, TABLOCKXXLOCK 将按照您的需要独占锁定该行。 ROWLOCK/PAGELOCK 就没有。
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, TABLOCKXXLOCK would have locked the row exclusively as you want. ROWLOCK/PAGELOCK wouldn't have.