我有以下代码..实际上使用SQLBulkCopy将数据插入到目标.由于死锁,此代码经常在源SQL Server中失败.仅供参考,当我们执行批量复制时,可以使用正在复制的表(我的意思是某些插入/选择将在运行).

是造成问题的原因还是“ TABLOCK”提示与事情有关？根据我的理解,TABLOCK仅获取共享锁,应该没有问题.

using (var reader = srcConnection.ExecuteReader($"select * from [{DatabaseName}].[{schemaName}].[{tableName}]"))
{
    const SqlBulkCopyOptions bulkCopyOptions = SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers |
                                               SqlBulkCopyOptions.KeepNulls | //Do not replace nulls with defaults in destination
                                               SqlBulkCopyOptions.KeepIdentity;
        //Use the identity values from source, do not generate identities in destination.

    using (var bcp = new SqlBulkCopy(dstConnection.ConnectionString, bulkCopyOptions))
    {
        const int threeMinutes = 60*3;

        bcp.BulkCopyTimeout = threeMinutes; //Timeout is for a single batch
        bcp.BatchSize = 5000;
        bcp.DestinationTableName = $"[{DestinationDatabaseName}].[{schemaName}].[{tableName}]";
        bcp.EnableStreaming = true;

        foreach (var col in table.Columns.Cast<Column>().Where(c => !c.Computed))
        {
            bcp.ColumnMappings.Add(col.Name, col.Name);
        }

        bcp.WriteToServer(reader);
    }
}

解决方法:

批量插入将需要在表中插入行.插入行需要排他锁.获取的确切锁将取决于并发模型.

如果指定TableLock选项,则您的进程将尝试获取独占表锁.如果您的进程首先获得共享表锁,其他进程具有共享行锁,并且两个进程都尝试将其锁升级为互斥锁,则肯定会导致死锁.

有几种方法可以获取有关死锁的更多信息：

>启用trace flag 1222 and review the SQL Server log
>运行SQL Server Profiler with the Lock:Deadlock Graph event
>显示器extended events