为什么以及如何有时在肯定有 Sch-M 锁的情况下出现“缺少表错误”？

非常有趣和棘手的问题。我找不到任何关于此行为的官方文档，而且我怀疑可能没有任何文档（尽管如果有人纠正我，我会很高兴！）。

我的研究使我相信计划编译步骤容易受到这种竞争条件的影响。请注意，我能够毫无错误地运行您的测试查询一个小时，但如果我反复启动您的流程，有时会立即出现错误。当它确实遇到错误时，它总是会在计划编译时立即执行。或者，您可以将“OPTION RECOMPILE”添加到循环中的 COUNT(*)，强制在每次试验时编译新计划。使用这种方法，每次运行您的脚本时，我几乎都能立即看到错误。

我能够通过一系列似乎在每次试验中都会遇到错误的受控步骤来重现错误，从而无需设置循环并依赖随机性。

我还提出了一个可能的修复方法（使用 ALTER TABLE...SWITCH），可以在您的生产环境中试用。现在，进入细节！

以下是重现的步骤：

-- Instructions: Process each step one at a time, following any instructions in that step

-- (1) Initial setup: Clean any relics and create the test table
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;
SET NOCOUNT ON;
IF @@TRANCOUNT > 0 ROLLBACK
IF object_id('test') IS NOT NULL 
    DROP TABLE test
IF object_id('TMP_test') IS NOT NULL 
    DROP TABLE TMP_test
IF object_id('test1') IS NOT NULL 
    DROP TABLE test1
CREATE TABLE test(Id INT IDENTITY CONSTRAINT PK_test PRIMARY KEY)
GO
INSERT test DEFAULT VALUES
GO 2000

-- (2) Run the COUNT(*)
-- This will acquire the Sch-S lock, and we use HOLDLOCK to retain that lock.
-- This simulates a race condition where this query is running at the time the first rename occurs.
BEGIN TRANSACTION
SELECT COUNT(*) FROM Test WITH (HOLDLOCK)
GO

-- (3) In another window, run the block of code that performs the renames
-- This will be blocked, waiting for a Sch-M lock on "test" until we complete step 4 below
CREATE TABLE TMP_test(Id INT PRIMARY KEY)
INSERT TMP_test SELECT * FROM test
EXEC sp_rename 'test', 'test1'
EXEC sp_rename 'TMP_test', 'test'
EXEC sp_rename 'test1', 'TMP_test'
DROP TABLE TMP_test
GO

-- (4) Now, commit the original COUNT(*) and immediately fire off the query again
-- We use OPTION (RECOMPILE) to make sure we need to compile a new query plan
-- The COMMIT releases the Sch-S lock, allowing (3) to acquire the Sch-M lock
-- This batch will now be waiting for the Sch-S lock again, on the same object_id,
-- but that object_id will no longer point to the correct object by the time the lock
-- is acquired.
COMMIT
SELECT COUNT(*) FROM Test OPTION (RECOMPILE)
GO

使用这些步骤，我们可以更深入地了解导致错误的原因。为此，我对以下事件进行了跟踪：SP:StmtStarting、SP:StmtCompleted、Lock:Acquired、Lock:Released。

我发现按顺序发生以下情况（但省略了两者之间的一些细节）：

1. 第一个 COUNT(*) 获取一个 Sch-S 锁
2. 第一个 COUNT(*) 获取一些统计信息的锁（大概是为了建立一个查询计划）
3. 第一个 COUNT(*) 释放一个 Sch-S 锁（计划编译结束）
4. 第一个COUNT(*)获取IS锁（开始执行），然后运行成功
5. sp_rename 运行到它需要获取 Sch-M 锁的点，然后被第一个 COUNT(*) 阻塞，该 COUNT(*) 尚未提交
6. 第一个 COUNT(*) 已提交
7. sp_renames 获取 Sch-M 锁
8. 第二个 COUNT(*) 请求一个 Sch-S 锁，并被添加到 sp_rename 后面的阻塞链
9. sp_renames 完成（此时，第二个 COUNT(*) 正在等待 Sch-S 锁以获取不再是正确 object_id 的 object_id）
10. 第二个 COUNT(*) 获取 Sch-S 锁
11. 第二个 COUNT(*) 在 sys.sysschobjs 上获取一个 Sch-S 锁，大概是作为完整性检查，因为它检测到（或需要确认）Sch-S 锁有问题，它是在 object_id 上授予的成为“测试”
12. 抛出无效对象名称“测试”错误

但是，如果在不需要计划编译时（例如在运行循环时）发生类似的事件序列，则 SQL Server 实际上足够聪明，可以检测到 object_id 已更改。我也跟踪了那个案例，我发现了第二个 COUNT(*) 执行以下序列的情况

1. 获取 Sch-S 锁（在曾经是“test”的 object_id 上）
2. 在 sys.sysschobjs 上获取 Sch-S 锁
3. 释放这两个锁
4. 获取不同 object_id 上的 IS 锁；“测试”的新 object_id！
5. 成功运行到完成

因此，正如您所假设的那样，看起来确实存在这种自适应逻辑。但看起来它可能只用于查询执行，而不用于查询编译。

正如承诺的那样，这里是第 (3) 部分的替代片段，它似乎提供了一种重命名表的方法来解决并发问题（至少在我的机器上是这样！）：

-- (3) In another window, run the block of code that performs the "renames"
-- Instead of sp_rename, we use ALTER TABLE...SWITCH
-- This appears to be a more robust way of performing the same logic
CREATE TABLE test1(Id INT PRIMARY KEY)
CREATE TABLE TMP_test(Id INT PRIMARY KEY)
INSERT TMP_test SELECT * FROM test
ALTER TABLE test SWITCH TO test1
ALTER TABLE TMP_test SWITCH TO test
ALTER TABLE test1 SWITCH TO TMP_test
DROP TABLE TMP_test
DROP TABLE test1
GO

最后，这是另一个我发现有用的链接，它可以帮助我思考如何在名称不断变化的表的上下文中考虑锁定：

• http://www.sqlnotes.info/2011/12/02/sp_rename-causes-lock-leaking/