事务等待提交，否则阻塞资源

问题描述

我想在事务块中放入一些语句，以便在发生任何错误时脚本回滚.我将事务与这样的 TRY 块结合在一起(并注意 INSERT 语句应通过语法错误):

I would like to put a few statements into a transaction block so that, in case of any error the script rolls back. I combine a transaction with a TRY block like this (and note that the INSERT statement should through a syntax error):

begin try
    begin tran
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON

    CREATE TABLE [MY_DATABASE].[dbo].[MY_TABLE](
        [Id] [nchar](10) NULL,
        [Name] [nchar](10) NULL
    ) ON [PRIMARY]

    insert into [MY_DATABASE].[dbo].[MY_TABLE] (Id,Name) values ('1','Vampire'),  ('2')
    commit tran
end try
begin catch
    rollback tran
end catch

运行此语句后，我注意到无法在Management Studio中获取其他表，并且该操作正在等待我提交.

After running this statement, I noticed that I cannot fetch my other tables in Management Studio and that the operation is waiting for me to commit.

如何更改我的transaction-try块，以便在出现任何错误的情况下，我们回滚到开始try 语句之前的位置?

How can I alter my transaction-try blocks so that in case of any error, we rollback to where we were before the begin try statement?

推荐答案

您的脚本过早停止，而不是执行 catch 块.令人不满意的答案是，这是 TRY/CATCH 无法处理.

Your script is halting prematurely rather than executing the catch block. The unsatisfying answer is that this is one of those things that TRY/CATCH doesn't handle.

SQL Server启动事务，创建表，然后崩溃

SQL Server starts the transaction, creates the table, and then crashes with

保留未完成的事务(这就是为什么您无法在Mgmt Studio中获取其他表的原因).

leaving the open transaction (which is why you can't fetch the other tables in Mgmt Studio).

begin try
    begin tran
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON

    CREATE TABLE [dbo].[MY_TABLE](
        [Id] [nchar](10) NULL,
        [Name] [nchar](10) NULL
    ) ON [PRIMARY]

    insert into [dbo].[MY_TABLE] (Id,Name) values ('1','Vampire'),  ('2')
    commit tran
end try
begin catch
    print 'Now inside the "catch" block.  You will not see this message.';
    rollback tran
end catch

go
select * from MY_TABLE;
select @@trancount as TranCount;

rollback tran;

输出如下.请注意该表和打开的事务的存在:

Output is as follows. Note the existence of the table and the open transaction:

Msg 10709, Level 16, State 1, Line 117
The number of columns for each row in a table value constructor must be the same.
Id         Name
---------- ----------

(0 rows affected)

  TranCount
-----------
          1

(1 row affected)

这篇关于事务等待提交，否则阻塞资源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！