时间戳

首页 > 解决方案 > 原子 DROP 和 SELECT ... INTO 表

sql-server - 原子 DROP 和 SELECT ... INTO 表

问题描述

我会认为像下面这样的代码是原子的：如果 DeleteMe 在运行这个事务之前存在，它应该被删除并重新创建。否则应该简单地创建它：

BEGIN TRANSACTION
    IF OBJECT_ID('DeleteMe') IS NOT NULL
    DROP TABLE DeleteMe
    
    SELECT query.* 
    INTO DeleteMe
    FROM (SELECT 1 AS Value) AS query
COMMIT TRANSACTION

但是，似乎同时执行此代码多次可能会导致各种错误组合：

这是一个 LINQPad 脚本来说明我的意思。

var sql = @"
BEGIN TRANSACTION
    IF OBJECT_ID('DeleteMe') IS NOT NULL
    DROP TABLE DeleteMe
    
    SELECT query.* 
    INTO DeleteMe
    FROM (SELECT 1 AS Value) AS query
COMMIT TRANSACTION
";
await Task.WhenAll(Enumerable.Range(1, 50)
    .Select(async i =>
    {
        using var connection = new SqlConnection(this.Connection.ConnectionString);
        await connection.OpenAsync();
        await connection.ExecuteAsync(sql);
    }).Dump());

以及它的输出示例：

许多任务失败

如果我使用 SQL Server 2016 的DROP TABLE IF EXISTS功能，该部分至少看起来是原子的，但是另一个并发命令显然仍然可以在删除该表和再次创建它之间创建 DeleteMe 表。 使用 DROP TABLE IF EXISTS

问题：有什么方法可以原子地删除、创建和填充表，这样从另一个并发连接的角度来看，没有时间该表不存在？

标签： sql-server

解决方案

有没有办法原子地删除、创建和填充表，这样从另一个并发连接的角度来看，没有时间该表不存在？

当然。这就像任何事务一样：您必须在第一条语句上使用不一致的锁。在您的事务中，两个会话可以同时运行IF OBJECT_ID('DeleteMe') IS NOT NULL。然后他们都试图放下物体，只有一个成功。

DROP TABLE IF EXISTS还执行存在性检查，然后再对删除它所必需的对象进行排他模式锁定。

获得排他锁的一种简单而可靠的方法是使用sp_getapplock。

例如

BEGIN TRANSACTION
    exec sp_getapplock 'dropandcreate_DeleteMe', 'exclusive'
    DROP TABLE IF EXISTS DeleteMe
    
    SELECT query.* 
    INTO DeleteMe
    FROM (SELECT 1 AS Value) AS query
COMMIT TRANSACTION

推荐阅读