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

ESTE TEMA SE APLICA A: síSQL Server (a partir de 2008)síAzure SQL DatabasesíAzure SQL Data Warehouse síAlmacenamiento de datos paralelos THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Establece el modo de BEGIN TRANSACTION en implícita, para la conexión.Sets the BEGIN TRANSACTION mode to implicit, for the connection.

Icono de vínculo de tema Convenciones de sintaxis de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxisSyntax

SET IMPLICIT_TRANSACTIONS { ON | OFF }  

ComentariosRemarks

Cuando, el sistema está en implícita modo de transacción.When ON, the system is in implicit transaction mode. Esto significa que si @@TRANCOUNT = 0, cualquiera de las instrucciones de Transact-SQL siguientes inicia una transacción nueva.This means that if @@TRANCOUNT = 0, any of the following Transact-SQL statements begins a new transaction. Es equivalente a una invisibles instrucción BEGIN TRANSACTION que se ejecuta en primer lugar:It is equivalent to an unseen BEGIN TRANSACTION being executed first:

ALTER TABLEALTER TABLE FETCHFETCH REVOKEREVOKE
BEGIN TRANSACTIONBEGIN TRANSACTION GRANTGRANT SELECT (vea la excepción a continuación)SELECT (See exception below.)
CREATECREATE INSERTINSERT TRUNCATE TABLETRUNCATE TABLE
DELETEDELETE OPENOPEN UPDATEUPDATE
DROPDROP .. ..

Cuando OFF, cada una de las instrucciones de T-SQL anteriores está limitada por una instrucción BEGIN TRANSACTION ocultas y una instrucción COMMIT TRANSACTION ocultas.When OFF, each of the preceding T-SQL statements is bounded by an unseen BEGIN TRANSACTION and an unseen COMMIT TRANSACTION statement. Cuando es OFF, se dice que es el modo de transacción confirmación automática.When OFF, we say the transaction mode is autocommit. Si el código de T-SQL visiblemente emite una instrucción BEGIN TRANSACTION, se dice que es el modo de transacción explícita.If your T-SQL code visibly issues a BEGIN TRANSACTION, we say the transaction mode is explicit.

Hay varios puntos defecación comprender:There are several clarifying point to understand:

  • Cuando el modo de transacción es implícita, no se emite ninguna instrucción BEGIN TRANSACTION invisibles if @@trancount > 0 ya.When the transaction mode is implicit, no unseen BEGIN TRANSACTION is issued if @@trancount > 0 already. Sin embargo, las instrucciones BEGIN TRANSACTION explícitas todavía incremento @@TRANCOUNT.However, any explicit BEGIN TRANSACTION statements still increment @@TRANCOUNT.

  • Cuando haya finalizado las instrucciones INSERT y cualquier otra cosa en la unidad de trabajo, debe emitir instrucciones COMMIT TRANSACTION hasta que @@TRANCOUNT disminuye hasta 0.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. O bien, puede emitir una instrucción ROLLBACK TRANSACTION.Or you can issue one ROLLBACK TRANSACTION.

  • Las instrucciones SELECT que no seleccionan nada en una tabla no inician transacciones implícitas.SELECT statements that do not select from a table do not start implicit transactions. Por ejemplo SELECT GETDATE(); o SELECT 1, 'ABC'; no requieren transacciones.For example SELECT GETDATE(); or SELECT 1, 'ABC'; do not require transactions.

  • Transacciones implícitas pueden ser inesperadamente ON debido a los valores predeterminados ANSI.Implicit transactions may unexpectedly be ON due to ANSI defaults. Para obtener más información, consulte SET ANSI_DEFAULTS ( Transact-SQL ) .For details see SET ANSI_DEFAULTS (Transact-SQL).

    IMPLICIT_TRANSACTIONS ON no es conocido.IMPLICIT_TRANSACTIONS ON is not popular. En la mayoría de los casos donde IMPLICIT_TRANSACTIONS es ON, es porque se ha realizado la opción de 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.

  • El SQL ServerSQL Server proveedor Native Client OLE DB para SQL ServerSQL Servery el SQL ServerSQL Server controlador ODBC de Native Client, establecen automáticamente IMPLICIT_TRANSACTIONS en OFF al conectarse.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. SET IMPLICIT_TRANSACTIONS tiene como valor predeterminado OFF para las conexiones con el proveedor administrado SQLClient y para solicitudes SOAP recibidas a través de extremos HTTP.SET IMPLICIT_TRANSACTIONS defaults to OFF for connections with the SQLClient managed provider, and for SOAP requests received through HTTP endpoints.

    Para ver la configuración actual de IMPLICIT_TRANSACTIONS, ejecute la consulta siguiente.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;  

EjemplosExamples

El siguiente script de Transact-SQL ejecuta unos distintos casos de prueba.The following Transact-SQL script runs a few different test cases. También se proporciona el texto de salida, que muestra el comportamiento detallado y resultados de cada caso de prueba.The text output is also provided, which shows the detailed behavior and results from each test case.

-- Transact-SQL.  
go  
-- 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 INSERTs, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));  
COMMIT TRANSACTION;  
PRINT N'[D.05] @@TranCount, after INSERTs, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));  
go  

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

A continuación figura la salida de texto de la secuencia de comandos de Transact-SQL anterior.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  

El conjunto de resultados es el siguiente.Here is the result set.

Vea tambiénSee 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)
Eliminar tabla ( Transact-SQL ) DROP TABLE (Transact-SQL)
FETCH ( Transact-SQL ) FETCH (Transact-SQL)
GRANT (Transact-SQL) GRANT (Transact-SQL)
INSERT (Transact-SQL) INSERT (Transact-SQL)
Abrir ( Transact-SQL ) OPEN (Transact-SQL)
REVOKE (Transact-SQL) REVOKE (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
Instrucciones 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)