SET IMPLICIT_TRANSACTIONS (Transact-SQL)SET IMPLICIT_TRANSACTIONS (Transact-SQL)

適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database はいAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed Instance はいAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics はいParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data Warehouse適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database はいAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed Instance はいAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics はいParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data Warehouse

接続に対して、BEGIN TRANSACTION モードを "暗黙" に設定します。Sets the BEGIN TRANSACTION mode to implicit, for the connection.

トピック リンク アイコン Transact-SQL 構文表記規則Topic link icon Transact-SQL Syntax Conventions

構文Syntax

SET IMPLICIT_TRANSACTIONS { ON | OFF }  

注意

SQL Server 2014 以前の Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

解説Remarks

ON の場合、システムは "暗黙" トランザクション モードです。When ON, the system is in implicit transaction mode. つまり、@@TRANCOUNT = 0 の場合に、次の Transact-SQL ステートメントのいずれかが新しいトランザクションを開始します。This means that if @@TRANCOUNT = 0, any of the following Transact-SQL statements begins a new transaction. これは、最初に実行される目に見えない BEGIN TRANSACTION と同じです。It is equivalent to an unseen BEGIN TRANSACTION being executed first:

ALTER TABLEALTER TABLE

FETCHFETCH

REVOKEREVOKE

BEGIN TRANSACTIONBEGIN TRANSACTION

GRANTGRANT

SELECT (以下の例外を参照)SELECT (See exception below.)

CREATECREATE

INSERTINSERT

TRUNCATE TABLETRUNCATE TABLE

DELETEDELETE

OPENOPEN

UPDATEUPDATE

DROPDROP

 

OFF の場合、前の T-SQL ステートメントはそれぞれ目に見えない BEGIN TRANSACTION と目に見えない COMMIT TRANSACTION ステートメントによってバインドされます。When OFF, each of the preceding T-SQL statements is bounded by an unseen BEGIN TRANSACTION and an unseen COMMIT TRANSACTION statement. OFF の場合、トランザクション モードは "オートコミット" です。When OFF, we say the transaction mode is autocommit. T-SQL コードが明確に BEGIN TRANSACTION を発行する場合、トランザクション モードは "明示的" です。If your T-SQL code visibly issues a BEGIN TRANSACTION, we say the transaction mode is explicit.

理解しておくべき明確な点がいくつかあります。There are several clarifying points to understand:

  • トランザクション モードが暗黙のとき、既に @@trancount > 0 の場合は目に見えない BEGIN TRANSACTION は発行されません。When the transaction mode is implicit, no unseen BEGIN TRANSACTION is issued if @@trancount > 0 already. ただし、明示的な BEGIN TRANSACTION ステートメントは依然として @@TRANCOUNT を増やします。However, any explicit BEGIN TRANSACTION statements still increment @@TRANCOUNT.

  • INSERT ステートメントと作業単位内の他のいずれかが完了したら、@@TRANCOUNT が 0 に減るまで COMMIT TRANSACTION ステートメントを発行する必要があります。When your INSERT statements and anything else in your unit of work is finished, you must issue COMMIT TRANSACTION statements until @@TRANCOUNT is decremented back down to 0. または、1 つの ROLLBACK TRANSACTION を発行することができます。Or you can issue one ROLLBACK TRANSACTION.

  • テーブルからの選択操作を伴わない SELECT ステートメントでは、暗黙のトランザクションは開始されません。SELECT statements that do not select from a table do not start implicit transactions. たとえば、SELECT GETDATE();SELECT 1, 'ABC'; にトランザクションは不要です。For example SELECT GETDATE(); or SELECT 1, 'ABC'; do not require transactions.

  • ANSI の既定値が原因で暗黙のトランザクションが予期せず ON になっている可能性があります。Implicit transactions may unexpectedly be ON due to ANSI defaults. 詳しくは、「SET ANSI_DEFAULTS (Transact-SQL)」をご覧ください。For details see SET ANSI_DEFAULTS (Transact-SQL).

    IMPLICIT_TRANSACTIONS ON は一般的ではありません。IMPLICIT_TRANSACTIONS ON is not popular. ほとんどの場合、IMPLICIT_TRANSACTIONS が ON であるのは、SET ANSI_DEFAULTS ON の選択が行われたためです。In most cases where IMPLICIT_TRANSACTIONS is ON, it is because the choice of SET ANSI_DEFAULTS ON has been made.

  • SQL ServerSQL Server Native Client OLE DB Provider for SQL ServerSQL Server および SQL ServerSQL Server Native Client ODBC ドライバーでは、接続時に自動的に IMPLICIT_TRANSACTIONS が OFF に設定されます。The SQL ServerSQL Server Native Client OLE DB Provider for SQL ServerSQL Server, and the SQL ServerSQL Server Native Client ODBC driver, automatically set IMPLICIT_TRANSACTIONS to OFF when connecting. 接続でのオプションを off、SET IMPLICIT_TRANSACTIONS の既定値、 SQLClient マネージド プロバイダーで、HTTP エンドポイント経由で受信した SOAP 要求します。SET IMPLICIT_TRANSACTIONS defaults to OFF for connections with the SQLClient managed provider, and for SOAP requests received through HTTP endpoints.

IMPLICIT_TRANSACTIONS の現在の設定を表示するには、次のクエリを実行します。To view the current setting for IMPLICIT_TRANSACTIONS, run the following query.

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

Examples

次の Transact-SQL スクリプトは、いくつかの異なるテスト ケースを実行します。The following Transact-SQL script runs a few different test cases. 動作の詳細と各テスト ケースの結果を示すテキスト出力も提供されます。The text output is also provided, which shows the detailed behavior and results from each test case.

-- 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 スクリプトからのテキスト出力です。Next is the text output from the preceding Transact-SQL script.

-- 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 INSERTs, == 1  
[D.05] @@TranCount, after INSERTs, == 0  

結果セットは次のようになります。Here is the result set.

参照See Also

ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
BEGIN TRANSACTION (Transact-SQL) BEGIN TRANSACTION (Transact-SQL)
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
DELETE (Transact-SQL) DELETE (Transact-SQL)
DROP TABLE (Transact-SQL) DROP TABLE (Transact-SQL)
FETCH (Transact-SQL) FETCH (Transact-SQL)
GRANT (Transact-SQL) GRANT (Transact-SQL)
INSERT (Transact-SQL) INSERT (Transact-SQL)
OPEN (Transact-SQL) OPEN (Transact-SQL)
REVOKE (Transact-SQL) REVOKE (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
SET ステートメント (Transact-SQL) SET Statements (Transact-SQL)
SET ANSI_DEFAULTS (Transact-SQL) SET ANSI_DEFAULTS (Transact-SQL)
@@TRANCOUNT (Transact-SQL) @@TRANCOUNT (Transact-SQL)
TRUNCATE TABLE (Transact-SQL) TRUNCATE TABLE (Transact-SQL)
UPDATE (Transact-SQL)UPDATE (Transact-SQL)