通过

IMPLICIT_TRANSACTIONS （Transact-SQL）

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse Analytics分析平台系统 （PDW）Microsoft Fabric 中的仓库Microsoft Fabric 中的 SQL 数据库

将连接模式设置为BEGIN TRANSACTION隐式模式。

Transact-SQL 语法约定

语法

SET IMPLICIT_TRANSACTIONS { ON | OFF }

备注

当 ON时，系统处于 隐 式事务模式。 这意味着如果 @@TRANCOUNT = 0，以下任一 Transact-SQL 语句都开始新的交易。 这相当于一个看不见 BEGIN TRANSACTION 的人先被处决：

  • ALTER TABLE
  • BEGIN TRANSACTION
  • CREATE
  • DELETE
  • DROP
  • FETCH
  • GRANT
  • INSERT
  • MERGE
  • OPEN
  • REVOKE
  • SELECT （参见 澄清说明）
  • TRUNCATE TABLE
  • UPDATE

当 OFF时，前面每个 T-SQL 语句都被一个未见 BEGIN TRANSACTION 语句和一个未见 COMMIT TRANSACTION 语句所界定。 当 OFF时，我们称交易模式为 自动提交。 如果你的 T-SQL 代码明显发出 ， BEGIN TRANSACTION我们说该事务模式是 显式的。

有几点需要说明：

  • 当交易模式是隐式的，如果@@TRANCOUNT > 0已经存在，则不会发出未见BEGIN TRANSACTION的。 然而，任何显式 BEGIN TRANSACTION 陈述仍然会 @@TRANCOUNT递增。

  • 当你的 INSERT 账单和工作单元内的其他内容完成后，你必须发 COMMIT TRANSACTION 放账单，直到 @@TRANCOUNT 被递减回0。 或者你也可以发一个。ROLLBACK TRANSACTION

  • SELECT 不从表中选择的语句不会启动隐式事务。 例如， SELECT GETDATE();SELECT 1, 'ABC'; 或者不需要交易。

  • 隐式交易可能是 ON 由于ANSI违约而意外发生的。 详情请参见 SET ANSI_DEFAULTS。

    设置 IMPLICIT_TRANSACTIONS to ON 并不受欢迎。 在大多数 IMPLICIT_TRANSACTIONS 情况下，如果是 ON，是因为 SET ANSI_DEFAULTS ON 被设置了。

  • SQL Server 原生客户端 OLE DB Provider for SQL Server 和 SQL Server 原生客户端 ODBC 驱动，连接时会自动设置为 IMPLICIT_TRANSACTIONS 。OFF 将默认设置为IMPLICIT_TRANSACTIONSOFF与 SQLClient 托管提供者的连接，以及通过 HTTP 端点接收的 SOAP 请求。

要查看当前 IMPLICIT_TRANSACTIONS的设置，请运行以下查询。

DECLARE @IMPLICIT_TRANSACTIONS AS VARCHAR (3) = 'OFF';
IF ((2 & @@OPTIONS) = 2)
SET @IMPLICIT_TRANSACTIONS = 'ON';
SELECT @IMPLICIT_TRANSACTIONS AS IMPLICIT_TRANSACTIONS;

示例

以下 Transact-SQL 脚本运行几个不同的测试用例。 还提供了文本输出，显示了每个测试用例的详细行为和结果。

-- Preparations.
SET NOCOUNT ON;
SET IMPLICIT_TRANSACTIONS OFF;
GO
WHILE (@@TRANCOUNT > 0) COMMIT TRANSACTION;
GO
IF (OBJECT_ID(N'dbo.t1',N'U') IS NOT NULL) DROP TABLE dbo.t1;
GO
CREATE table dbo.t1 (a INT);
GO

PRINT N'-------- [Test A] ---- OFF ----';
PRINT N'[A.01] Now, SET IMPLICIT_TRANSACTIONS OFF.';
PRINT N'[A.02] @@TRANCOUNT, at start, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
SET IMPLICIT_TRANSACTIONS OFF;
GO
INSERT INTO dbo.t1 VALUES (11);
INSERT INTO dbo.t1 VALUES (12);
PRINT N'[A.03] @@TRANCOUNT, after INSERTs, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO

PRINT N' ';
PRINT N'-------- [Test B] ---- ON ----';
PRINT N'[B.01] Now, SET IMPLICIT_TRANSACTIONS ON.';
PRINT N'[B.02] @@TRANCOUNT, at start, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
SET IMPLICIT_TRANSACTIONS ON;
GO
INSERT INTO dbo.t1 VALUES (21);
INSERT INTO dbo.t1 VALUES (22);
PRINT N'[B.03] @@TRANCOUNT, after INSERTs, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO
COMMIT TRANSACTION;
PRINT N'[B.04] @@TRANCOUNT, after COMMIT, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO

PRINT N' ';
PRINT N'-------- [Test C] ---- ON, then BEGIN TRAN ----';
PRINT N'[C.01] Now, SET IMPLICIT_TRANSACTIONS ON.';
PRINT N'[C.02] @@TRANCOUNT, at start, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
SET IMPLICIT_TRANSACTIONS ON;
GO
BEGIN TRANSACTION;
INSERT INTO dbo.t1 VALUES (31);
INSERT INTO dbo.t1 VALUES (32);
PRINT N'[C.03] @@TRANCOUNT, after INSERTs, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO
COMMIT TRANSACTION;
PRINT N'[C.04] @@TRANCOUNT, after a COMMIT, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
COMMIT TRANSACTION;
PRINT N'[C.05] @@TRANCOUNT, after another COMMIT, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO

PRINT N' ';
PRINT N'-------- [Test D] ---- ON, INSERT, BEGIN TRAN, INSERT ----';
PRINT N'[D.01] Now, SET IMPLICIT_TRANSACTIONS ON.';
PRINT N'[D.02] @@TRANCOUNT, at start, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
SET IMPLICIT_TRANSACTIONS ON;
GO
INSERT INTO dbo.t1 VALUES (41);
BEGIN TRANSACTION;
INSERT INTO dbo.t1 VALUES (42);
PRINT N'[D.03] @@TRANCOUNT, after INSERTs, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO
COMMIT TRANSACTION;
PRINT N'[D.04] @@TRANCOUNT, after a COMMIT, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
COMMIT TRANSACTION;
PRINT N'[D.05] @@TRANCOUNT, after another COMMIT, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO

-- Clean up.
SET IMPLICIT_TRANSACTIONS OFF;
GO
WHILE (@@TRANCOUNT > 0) COMMIT TRANSACTION;
GO
DROP TABLE dbo.t1;
GO

下面是上述 Transact-SQL 脚本的文本输出。

-------- [Test A] ---- OFF ----
[A.01] Now, SET IMPLICIT_TRANSACTIONS OFF.
[A.02] @@TRANCOUNT, at start, == 0
[A.03] @@TRANCOUNT, after INSERTs, == 0

-------- [Test B] ---- ON ----
[B.01] Now, SET IMPLICIT_TRANSACTIONS ON.
[B.02] @@TRANCOUNT, at start, == 0
[B.03] @@TRANCOUNT, after INSERTs, == 1
[B.04] @@TRANCOUNT, after COMMIT, == 0

-------- [Test C] ---- ON, then BEGIN TRAN ----
[C.01] Now, SET IMPLICIT_TRANSACTIONS ON.
[C.02] @@TRANCOUNT, at start, == 0
[C.03] @@TRANCOUNT, after INSERTs, == 2
[C.04] @@TRANCOUNT, after a COMMIT, == 1
[C.05] @@TRANCOUNT, after another COMMIT, == 0

-------- [Test D] ---- ON, INSERT, BEGIN TRAN, INSERT ----
[D.01] Now, SET IMPLICIT_TRANSACTIONS ON.
[D.02] @@TRANCOUNT, at start, == 0
[D.03] @@TRANCOUNT, after INSERTs, == 2
[D.04] @@TRANCOUNT, after a COMMIT, == 1
[D.05] @@TRANCOUNT, after another COMMIT, == 0

相关内容

  • Last updated on