SET IMPLICIT_TRANSACTIONS (Transact-SQL)

文章
10/16/2024

適用於：Microsoft Fabric 中的 SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System （PDW）倉儲

將連線的 BEGIN TRANSACTION 模式設定為 implicit。

Transact-SQL 語法慣例

語法

SET IMPLICIT_TRANSACTIONS { ON | OFF }

備註

當設定為 ON 時，系統是處於「隱含」交易模式。這表示如果 @@TRANCOUNT = 0，則任何下列 Transact-SQL 陳述式都會開始新的交易。它相當於一個看不見的 BEGIN TRANSACTION 先被執行：

ALTER TABLE

FETCH

REVOKE

BEGIN TRANSACTION

GRANT

SELECT (請參閱底下的例外狀況)。

CREATE

Insert

TRUNCATE TABLE

刪除

MERGE

UPDATE

DROP

OPEN

當設定為 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'; 不需要交易。
由於 ANSI 預設值的緣故，隱含交易可能會非預期地設定為 ON。如需詳細資訊，請參閱 SET ANSI_DEFAULTS (Transact-SQL)。

IMPLICIT_TRANSACTIONS ON 並不常用。在大部分 IMPLICIT_TRANSACTIONS 為 ON 的案例中，原因是已經做了 SET ANSI_DEFAULTS ON 的選擇。
適用於 SQL Server 的 SQL Server Native Client OLE DB 提供者和 SQL Server Native Client ODBC 驅動程式在連線時，都會自動將 IMPLICIT_TRANSACTIONS 設為 OFF。對於與 SQLClient 受控提供者的連線，以及透過 HTTP 端點來接收的 SOAP 要求，SET IMPLICIT_TRANSACTIONS 預設值為 OFF。

若要檢視 IMPLICIT_TRANSACTIONS 的目前設定，請執行下列查詢。

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

範例

下列 Transact-SQL 指令碼執行幾個不同的測試案例。也提供文字輸出，其中顯示每個測試案例的詳細行為與結果。

-- 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 指令碼的文字輸出。

-- Text output from 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

結果集如下所示。

另請參閱

ALTER TABLE (Transact-SQL)
BEGIN TRANSACTION (Transact-SQL)
CREATE TABLE (Transact-SQL)
DELETE (Transact-SQL)
DROP TABLE (Transact-SQL)
FETCH (Transact-SQL)
GRANT (Transact-SQL)
INSERT (Transact-SQL)
MERGE (Transact-SQL)OPEN (Transact-SQL)
REVOKE (Transact-SQL)
SELECT (Transact-SQL)
SET 陳述式 (Transact-SQL)
SET ANSI_DEFAULTS (Transact-SQL)
@@TRANCOUNT (Transact-SQL)
TRUNCATE TABLE (Transact-SQL)
UPDATE (Transact-SQL)

分享方式：

SET IMPLICIT_TRANSACTIONS (Transact-SQL)

語法

備註

範例

另請參閱

意見反映

更多資源