多并发事务运行判断建表SQL批处理是否会因竞态条件报错

你提供的原SQL批处理代码如下：

if not exists (select * from sysobjects where name='cars' and xtype='U')
    create table cars (
        Name varchar(64) not null
    )
go

核心结论 #

• 多并发事务同时运行这段代码存在明确的报错风险，if判断和create table语句之间存在竞态条件

原因说明 #

1. if not exists的查询操作和后续的建表操作是两个独立的非原子操作，执行过程中没有锁机制阻止其他事务同时做相同判断
2. 当两个并发事务几乎同时完成if not exists的查询时，此时cars表还未被创建，两个事务的查询结果都会判定「表不存在」，进而都会尝试执行建表语句
3. 第一个完成建表的事务可以正常执行，第二个事务执行建表时就会抛出数据库中已存在名为'cars'的对象的错误，执行失败

修复方案 #

如果使用SQL Server 2016及以上版本，直接使用原生原子操作语法CREATE TABLE IF NOT EXISTS即可避免竞态：

CREATE TABLE IF NOT EXISTS cars (
    Name varchar(64) not null
)
GO

如果是更低版本的SQL Server，可以通过给系统表查询加更新锁和保持锁的方式，把判断+建表的逻辑变成原子操作：

IF NOT EXISTS (SELECT * FROM sysobjects WITH (UPDLOCK, HOLDLOCK) WHERE name='cars' and xtype='U')
CREATE TABLE cars (
    Name varchar(64) not null
)
GO

内容的提问来源于stack exchange，提问作者Hari Krishna S