ODBC中事务锁表及INSERT语句语法问题咨询

一、事务与锁相关问题解答 #

原代码 #

ret = SQLExecDirect( stmt2, "BEGIN", SQL_NTS );
ret = SQLTables( stmt1 );
for( SQLFetch( stmt1 ); ; SQLFetch( stmt1 ) )
{
    // 获取目录
    // 获取架构
    // 获取表
    ret = SQLExecDirect( stmt2, "IF NOT EXIST( SELECT * FROM <my_table> WHERE name = <table_name> AND schema = <schema_name> ) INSERT INTO <my_table> VALUES( <table>, <schema>, .... )", SQL_NTS ); 
}
ret = SQLExecDirect( stmt2, "COMMIT", SQL_NTS );

（为简洁起见，已省略错误检查和准备步骤）

针对上述代码的三个问题：

1. 如何在事务期间锁定<my_table>禁止任何读写？
   要完全禁止<my_table>的读写操作，可在事务开始时对表加排他锁（X锁），SQL Server中执行以下语句即可：

   BEGIN TRANSACTION;
   SELECT * FROM <my_table> WITH (TABLOCKX, HOLDLOCK);
   

   • TABLOCKX：对整个表加排他锁，阻止其他会话的任何读写操作；
   • HOLDLOCK：将锁持有至事务结束，确保整个事务周期内锁持续生效。
     把这条语句放在事务起始处（BEGIN之后），就能保证事务执行期间<my_table>被完全锁定。

2. 仅为INSERT语句开启事务是否正确？
   不正确。你的代码是循环执行多次INSERT，若给单个INSERT独立开事务，会失去批量操作的原子性——一旦某一步INSERT失败，之前的操作无法回滚。而你原本的逻辑是批量处理所有表记录，应该用一个包含所有INSERT的大事务，这样所有操作要么全部成功提交，要么全部失败回滚，保证数据一致性。

3. 是否需要将记录校验与插入拆分为事务内的两个查询？
   不需要。原代码中IF NOT EXISTS(...) INSERT的写法本身是原子操作，在SQL Server中能避免并发下的重复插入问题。拆分反而会引入并发冲突风险，因为校验和插入的间隙中，其他会话可能修改数据。保持合并写法更安全高效。

二、SQL语法错误修复 #

你尝试的SQL语句存在两处语法错误：

错误代码 #

INSERT INTO my_table SELECT( 0, 'abcatcol', (SELECT object_id FROM 
sys.objects o, sys.schemas s WHERE s.schema_id = o.schema_id AND o.name = 
'abcatcol' AND s.name = 'dbo')
 WHERE NOT EXISTS(SELECT * FROM my_table WHERE abt_tnam='abcatcol' AND abt_ownr='dbo') );

错误原因与修正方案 #

1. SELECT(0, ...)的括号冗余：SELECT关键字后不需要加括号，直接罗列列值即可；
2. WHERE NOT EXISTS位置错误：该条件应放在整个SELECT语句的末尾，而非嵌套在列值定义中。

修正后的SQL #

INSERT INTO my_table 
SELECT 0, 'abcatcol', (
    SELECT object_id 
    FROM sys.objects o
    JOIN sys.schemas s ON s.schema_id = o.schema_id 
    WHERE o.name = 'abcatcol' AND s.name = 'dbo'
)
WHERE NOT EXISTS(
    SELECT * 
    FROM my_table 
    WHERE abt_tnam='abcatcol' AND abt_ownr='dbo'
);

另外建议将旧的逗号分隔表连接语法替换为JOIN语法，可读性更强且符合现代SQL规范。

内容的提问来源于stack exchange，提问作者Igor