| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | ms.custom | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
COMMIT TRANSACTION (Transact-SQL) |
This statement marks the end of a successful implicit or explicit transaction. |
rwestMSFT |
randolphwest |
04/15/2024 |
sql |
t-sql |
reference |
|
|
|
|
>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric |
[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricdw-fabricsqldb]
Marks the end of a successful implicit or explicit transaction. If @@TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications since the start of the transaction a permanent part of the database, frees the transaction's resources, and decrements @@TRANCOUNT to 0. When @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @@TRANCOUNT only by 1 and the transaction stays active.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
Syntax for [!INCLUDE ssnoversion-md] and [!INCLUDE ssazure-sqldb].
COMMIT [ { TRAN | TRANSACTION }
[ transaction_name | @tran_name_variable ] ]
[ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]
[ ; ]
Syntax for Synapse Data Warehouse in Microsoft Fabric, Azure Synapse Analytics, and Parallel Data Warehouse Database.
COMMIT [ TRAN | TRANSACTION ]
[ ; ]
Applies to: [!INCLUDE ssnoversion-md] and [!INCLUDE ssazure-sqldb]
Ignored by the [!INCLUDE ssDEnoversion]. transaction_name specifies a transaction name assigned by a previous BEGIN TRANSACTION. transaction_namemust conform to the rules for identifiers, but can't exceed 32 characters. transaction_name indicates to programmers which nested BEGIN TRANSACTION the COMMIT TRANSACTION is associated with.
Applies to: [!INCLUDE ssnoversion-md] and [!INCLUDE ssazure-sqldb]
The name of a user-defined variable containing a valid transaction name. The variable must be declared with a char, varchar, nchar, or nvarchar data type. If more than 32 characters are passed to the variable, only 32 characters are used. The remaining characters are truncated.
Applies to: [!INCLUDE ssnoversion-md] and [!INCLUDE ssazure-sqldb]
Option that requests this transaction should be committed with delayed durability. The request is ignored if the database was altered with DELAYED_DURABILITY = DISABLED or DELAYED_DURABILITY = FORCED. For more information, see Control Transaction Durability.
It's the responsibility of the [!INCLUDE tsql] programmer to issue COMMIT TRANSACTION only at a point when all data referenced by the transaction is logically correct.
If the transaction committed was a [!INCLUDE tsql] distributed transaction, COMMIT TRANSACTION triggers MS DTC to use a two-phase commit protocol to commit all of the servers involved in the transaction. When a local transaction spans two or more databases on the same instance of the [!INCLUDE ssDE], the instance uses an internal two-phase commit to commit all of the databases involved in the transaction.
When used in nested transactions, commits of the inner transactions don't free resources or make their modifications permanent. The data modifications are made permanent and resources freed only when the outer transaction is committed. Each COMMIT TRANSACTION issued when @@TRANCOUNT is greater than one simply decrements @@TRANCOUNT by 1. When @@TRANCOUNT is finally decremented to 0, the entire outer transaction is committed. Because transaction_name is ignored by the [!INCLUDE ssDE], issuing a COMMIT TRANSACTION referencing the name of an outer transaction when there are outstanding inner transactions only decrements @@TRANCOUNT by 1.
Issuing a COMMIT TRANSACTION when @@TRANCOUNT is zero results in an error; there's no corresponding BEGIN TRANSACTION.
You can't roll back a transaction after a COMMIT TRANSACTION statement is issued, because the data modifications were made a permanent part of the database.
The [!INCLUDE ssDE] increments the transaction count within a statement only when the transaction count is 0 at the start of the statement.
Requires membership in the public role.
[!INCLUDE article-uses-adventureworks]
Applies to: [!INCLUDE ssnoversion-md], [!INCLUDE ssazure-sqldb], [!INCLUDE ssazuresynapse-md], and [!INCLUDE ssPDW]
The following example deletes a job candidate.
BEGIN TRANSACTION;
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 13;
COMMIT TRANSACTION;Applies to: [!INCLUDE ssnoversion-md] and [!INCLUDE ssazure-sqldb]
The following example creates a table, generates three levels of nested transactions, and then commits the nested transaction. Although each COMMIT TRANSACTION statement has a transaction_name parameter, there's no relationship between the COMMIT TRANSACTION and BEGIN TRANSACTION statements. The transaction_name parameters help the programmer ensure that the correct number of commits are coded to decrement @@TRANCOUNT to 0 and so to commit the outer transaction.
IF OBJECT_ID(N'TestTran', N'U') IS NOT NULL
DROP TABLE TestTran;
GO
CREATE TABLE TestTran (
Cola INT PRIMARY KEY,
Colb CHAR(3)
);
GO
-- This statement sets @@TRANCOUNT to 1.
BEGIN TRANSACTION OuterTran;
PRINT N'Transaction count after BEGIN OuterTran = ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
INSERT INTO TestTran
VALUES (1, 'aaa');
-- This statement sets @@TRANCOUNT to 2.
BEGIN TRANSACTION Inner1;
PRINT N'Transaction count after BEGIN Inner1 = ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
INSERT INTO TestTran
VALUES (2, 'bbb');
-- This statement sets @@TRANCOUNT to 3.
BEGIN TRANSACTION Inner2;
PRINT N'Transaction count after BEGIN Inner2 = ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
INSERT INTO TestTran
VALUES (3, 'ccc');
-- This statement decrements @@TRANCOUNT to 2.
-- Nothing is committed.
COMMIT TRANSACTION Inner2;
PRINT N'Transaction count after COMMIT Inner2 = ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
-- This statement decrements @@TRANCOUNT to 1.
-- Nothing is committed.
COMMIT TRANSACTION Inner1;
PRINT N'Transaction count after COMMIT Inner1 = ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
-- This statement decrements @@TRANCOUNT to 0 and
-- commits outer transaction OuterTran.
COMMIT TRANSACTION OuterTran;
PRINT N'Transaction count after COMMIT OuterTran = ' + CAST(@@TRANCOUNT AS NVARCHAR(10));