存在 INNER JOIN 时锁定语句将无法编译

发布于 2024-10-06 13:16:37 字数 449 浏览 3 评论 0原文

我正在 SQL Server 2008 中编写一个存储过程。以下代码：

SELECT      @LastAccessed = cs.LastAccessed
FROM        [int].ClientSessions AS cs INNER JOIN
            dbo.Profiles AS p ON cs.ProfileID = p.ProfileID
WITH        (XLOCK, ROWLOCK)
WHERE       (p.ClientID = @ClientID)

...不会编译，在 XLOCK 处声明语法错误。

如果我删除 INNER JOIN 语句（这是不可能的，因为我需要连接），那么 XLOCK 就可以了。我不明白问题是什么。

注意：我只想锁定 [int].ClientSessions 表，所以我意识到这可能不是最好的方法。

原文

I'm writing a stored proc in SQL Server 2008. The following code:

SELECT      @LastAccessed = cs.LastAccessed
FROM        [int].ClientSessions AS cs INNER JOIN
            dbo.Profiles AS p ON cs.ProfileID = p.ProfileID
WITH        (XLOCK, ROWLOCK)
WHERE       (p.ClientID = @ClientID)

...won't compile, stating a syntax error at XLOCK.

If I remove the INNER JOIN statement (which is impossible because I need the join), then it's fine with the XLOCK. I don't see what the issue is.

Note: I only want to lock the [int].ClientSessions table, so I realize this may not be the best approach.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

前事休说 2024-10-13 13:16:37

您必须将 WITH (XLOCK, ROWLOCK) 放在 as cs/ as p 语句之后。

类似

SELECT      @LastAccessed = cs.LastAccessed 
FROM        [int].ClientSessions AS cs WITH (XLOCK, ROWLOCK) INNER JOIN 
            dbo.Profiles AS p ON cs.ProfileID = p.ProfileID 
WHERE       (p.ClientID = @ClientID)

查看表提示 (Transact-SQL)

You have to put the WITH (XLOCK, ROWLOCK) after the as cs/ as p statement.

Something like

SELECT      @LastAccessed = cs.LastAccessed 
FROM        [int].ClientSessions AS cs WITH (XLOCK, ROWLOCK) INNER JOIN 
            dbo.Profiles AS p ON cs.ProfileID = p.ProfileID 
WHERE       (p.ClientID = @ClientID)

Have a look at Table Hints (Transact-SQL)

回复收藏 0 原文

~没有更多了~