Marks the end of a successful implicit or user-defined transaction. If @@TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications performed since the start of the transaction a permanent part of the database, frees the resources held by the connection, and decrements @@TRANCOUNT to 0. If @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @@TRANCOUNT only by 1.
COMMIT [ TRAN [ SACTION ] [ transaction_name | @tran_name_variable ] ]
Is ignored by Microsoft® SQL Server™. transaction_name specifies a transaction name assigned by a previous BEGIN TRANSACTION. transaction_name must conform to the rules for identifiers, but only the first 32 characters of the transaction name are used. transaction_name can be used as a readability aid by indicating to programmers which nested BEGIN TRANSACTION the COMMIT TRANSACTION is associated with.
Is 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.
It is the responsibility of the Transact-SQL 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 Transact-SQL distributed transaction, COMMIT TRANSACTION triggers MS DTC to use a two-phase commit protocol to commit all the servers involved in the transaction. If a local transaction spans two or more databases on the same server, SQL Server uses an internal two-phase commit to commit all the databases involved in the transaction.
When used in nested transactions, commits of the inner transactions do not 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 1 simply decrements @@TRANCOUNT by 1. When @@TRANCOUNT is finally decremented to 0, the entire outer transaction is committed. Because transaction_name is ignored by SQL Server, 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 0 results in an error that there is no corresponding BEGIN TRANSACTION.
You cannot roll back a transaction after a COMMIT TRANSACTION statement is issued because the data modifications have been made a permanent part of the database.
Microsoft SQL Server 2000 increments the transaction count within a statement only when the transaction count is 0 at the start of the statement. In SQL Server version 7.0, the transaction count is always incremented, regardless of the transaction count at the start of the statement. This can cause the value returned by @@TRANCOUNT in triggers to be lower in SQL Server 2000 than it is in SQL Server version 7.0.
In SQL Server 2000, if a COMMIT TRANSACTION or COMMIT WORK statement is executed in a trigger, and there is no corresponding explicit or implicit BEGIN TRANSACTION statement at the start of the trigger, users may see different behavior than on SQL Server version 7.0. Placing COMMIT TRANSACTION or COMMIT WORK statements in a trigger is not recommended.
A. Commit a transaction.
This example increases the advance to be paid to an author when year-to-date sales of a title are greater than $8,000.
BEGIN TRANSACTION USE pubs GO UPDATE titles SET advance = advance * 1.25 WHERE ytd_sales > 8000 GO COMMIT GO
B. Commit a nested transaction.
This 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 is no relationship between the COMMIT TRANSACTION and BEGIN TRANSACTION statements. The transaction_name parameters are simply readability aids to help the programmer ensure the proper number of commits are coded to decrement @@TRANCOUNT to 0, and thereby commit the outer transaction.
CREATE TABLE TestTran (Cola INT PRIMARY KEY, Colb CHAR(3)) GO BEGIN TRANSACTION OuterTran -- @@TRANCOUNT set to 1. GO INSERT INTO TestTran VALUES (1, 'aaa') GO BEGIN TRANSACTION Inner1 -- @@TRANCOUNT set to 2. GO INSERT INTO TestTran VALUES (2, 'bbb') GO BEGIN TRANSACTION Inner2 -- @@TRANCOUNT set to 3. GO INSERT INTO TestTran VALUES (3, 'ccc') GO COMMIT TRANSACTION Inner2 -- Decrements @@TRANCOUNT to 2. -- Nothing committed. GO COMMIT TRANSACTION Inner1 -- Decrements @@TRANCOUNT to 1. -- Nothing committed. GO COMMIT TRANSACTION OuterTran -- Decrements @@TRANCOUNT to 0. -- Commits outer transaction OuterTran. GO