quote:

---

Rollbacks in Stored Procedures and Triggers
If @@TRANCOUNT has a different value when a stored procedure finishes than it had when the procedure was executed, an informational error 266 is generated. This error is not generated by the same condition in triggers.

A 266 error is generated when a stored procedure is called with an @@TRANCOUNT of 1 or greater and the procedure executes a ROLLBACK TRANSACTION or ROLLBACK WORK statement. This is because ROLLBACK rolls back all outstanding transactions and decrements @@TRANCOUNT to 0, which is a lower value than it had when the procedure was called.

If a ROLLBACK TRANSACTION is issued in a trigger:

All data modifications made to that point in the current transaction are rolled back, including any that were made by the trigger.


The trigger continues executing any remaining statements after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back. No nested triggers are fired by the execution of these remaining statements.


None of the statements in the batch after the statement that fired the trigger are executed.


A ROLLBACK in a trigger closes and deallocates all cursors that were declared and opened in the batch containing the statement that fired the trigger. This includes cursors declared and opened in stored procedures called by the batch that fired the trigger. Cursors declared in a batch prior to the batch that fired the trigger are only closed, except that STATIC or INSENSITIVE cursors are left open if:
CURSOR_CLOSE_ON_COMMIT is set OFF.


The static cursor is either synchronous, or a fully populated asynchronous cursor.
A trigger always operates as if there were an outstanding transaction in effect when the trigger is executed. This is definitely true if the statement firing the trigger is in an implicit or explicit transaction. It is also true in autocommit mode. When a statement begins executing in autocommit mode, there is an implied BEGIN TRANSACTION to allow the recovery of all modifications generated by the statement if it encounters an error. This implied transaction has no effect on the other statements in the batch because it is either committed or rolled back when the statement completes. This implied transaction is still in effect, however, when a trigger is called.

This means that any time a BEGIN TRANSACTION statement is issued in the trigger, it is actually beginning a nested transaction. Because a nested BEGIN TRANSACTION statement is ignored when rolling back nested transactions, ROLLBACK TRANSACTION issued in the trigger always rolls back past any BEGIN TRANSACTION statements issued by the trigger itself. ROLLBACK rolls back to the outermost BEGIN TRANSACTION.

You must use the SAVE TRANSACTION statement to do a partial rollback in a trigger, even if it is always called in autocommit mode. This is illustrated by the following trigger:

CREATE TRIGGER TestTrig ON TestTab FOR UPDATE AS
SAVE TRANSACTION MyName
INSERT INTO TestAudit
SELECT * FROM inserted
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION MyName
END

This also affects COMMIT TRANSACTION statements that follow a BEGIN TRANSACTION statement in a trigger. Because BEGIN TRANSACTION starts a nested transaction, a subsequent COMMIT statement applies only to the nested transaction. If a ROLLBACK TRANSACTION statement is executed after COMMIT, ROLLBACK rolls back everything to the outermost BEGIN TRANSACTION. This is illustrated by the following trigger:

CREATE TRIGGER TestTrig ON TestTab FOR UPDATE AS
BEGIN TRANSACTION
INSERT INTO TrigTarget
SELECT * FROM inserted
COMMIT TRANSACTION
ROLLBACK TRANSACTION

This trigger will never insert into the TrigTarget table. BEGIN TRANSACTION always starts a nested transaction. COMMIT TRANSACTION commits only the nested transaction, while the following ROLLBACK TRANSACTION rolls everything back to the outermost BEGIN TRANSACTION.


See Also

@@TRANCOUNT

ROLLBACK WORK

ROLLBACK TRANSACTION

Nesting Transactions

---

quote:

---

ROLLBACK TRANSACTION
Rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside a transaction.

Syntax
ROLLBACK [ TRAN [ SACTION ]
[ transaction_name | @tran_name_variable
| savepoint_name | @savepoint_variable ] ]

Arguments
transaction_name

Is the name assigned to the transaction on BEGIN TRANSACTION. transaction_name must conform to the rules for identifiers, but only the first 32 characters of the transaction name are used. When nesting transactions, transaction_name must be the name from the outermost BEGIN TRANSACTION statement.

@tran_name_variable

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.

savepoint_name

Is savepoint_name from a SAVE TRANSACTION statement. savepoint_name must conform to the rules for identifiers. Use savepoint_name when a conditional rollback should affect only part of the transaction.

@savepoint_variable

Is name of a user-defined variable containing a valid savepoint name. The variable must be declared with a char, varchar, nchar, or nvarchar data type.

Remarks
ROLLBACK TRANSACTION erases all data modifications made since the start of the transaction or to a savepoint. It also frees resources held by the transaction.

ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.

A ROLLBACK TRANSACTION statement specifying a savepoint_name does not free any locks.

ROLLBACK TRANSACTION cannot reference a savepoint_name in distributed transactions started either explicitly with BEGIN DISTRIBUTED TRANSACTION or escalated from a local transaction.

A transaction cannot be rolled back after a COMMIT TRANSACTION statement is executed.

Within a transaction, duplicate savepoint names are allowed, but a ROLLBACK TRANSACTION using the duplicate savepoint name rolls back only to the most recent SAVE TRANSACTION using that savepoint name.

In stored procedures, ROLLBACK TRANSACTION statements without a savepoint_name or transaction_name roll back all statements to the outermost BEGIN TRANSACTION. A ROLLBACK TRANSACTION statement in a stored procedure that causes @@TRANCOUNT to have a different value when the trigger completes than the @@TRANCOUNT value when the stored procedure was called produces an informational message. This message does not affect subsequent processing.

If a ROLLBACK TRANSACTION is issued in a trigger:

All data modifications made to that point in the current transaction are rolled back, including any made by the trigger.


The trigger continues executing any remaining statements after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back. No nested triggers are fired by the execution of these remaining statements.


The statements in the batch after the statement that fired the trigger are not executed.
@@TRANCOUNT is incremented by one when entering a trigger, even when in autocommit mode. (The system treats a trigger as an implied nested transaction.)

ROLLBACK TRANSACTION statements in stored procedures do not affect subsequent statements in the batch that called the procedure; subsequent statements in the batch are executed. ROLLBACK TRANSACTION statements in triggers terminate the batch containing the statement that fired the trigger; subsequent statements in the batch are not executed.

A ROLLBACK TRANSACTION statement does not produce any messages to the user. If warnings are needed in stored procedures or triggers, use the RAISERROR or PRINT statements. RAISERROR is the preferred statement for indicating errors.

The effect of a ROLLBACK on cursors is defined by these three rules:

With CURSOR_CLOSE_ON_COMMIT set ON, ROLLBACK closes but does not deallocate all open cursors.


With CURSOR_CLOSE_ON_COMMIT set OFF, ROLLBACK does not affect any open synchronous STATIC or INSENSITIVE cursors or asynchronous STATIC cursors that have been fully populated. Open cursors of any other type are closed but not deallocated.


An error that terminates a batch and generates an internal rollback deallocates all cursors that were declared in the batch containing the error statement. All cursors are deallocated regardless of their type or the setting of CURSOR_CLOSE_ON_COMMIT. This includes cursors declared in stored procedures called by the error batch. Cursors declared in a batch before the error batch are subject to rules 1 and 2. A deadlock error is an example of this type of error. A ROLLBACK statement issued in a trigger also automatically generates this type of error.
Permissions
ROLLBACK TRANSACTION permissions default to any valid user.

---

quote:

---

Autocommit Transactions
Autocommit mode is the default transaction management mode of Microsoft® SQL Server™. Every Transact-SQL statement is committed or rolled back when it completes. If a statement completes successfully, it is committed; if it encounters any error, it is rolled back. A SQL Server connection operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions. Autocommit mode is also the default mode for ADO, OLE DB, ODBC, and DB-Library.

A SQL Server connection operates in autocommit mode until a BEGIN TRANSACTION statement starts an explicit transaction, or implicit transaction mode is set on. When the explicit transaction is committed or rolled back, or when implicit transaction mode is turned off, SQL Server returns to autocommit mode.

Compile and Run-time Errors
In autocommit mode, it sometimes appears as if SQL Server has rolled back an entire batch instead of just one SQL statement. This happens only if the error encountered is a compile error, not a run-time error. A compile error prevents SQL Server from building an execution plan, so nothing in the batch is executed. Although it appears that all the statements before the one generating the error were rolled back, the error prevented anything in the batch from being executed. In this example, none of the INSERT statements in the third batch are executed because of a compile error. It appears that the first two INSERT statements are rolled back when they are never executed.

USE pubs
GO
CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3))
GO
INSERT INTO TestBatch VALUES (1, 'aaa')
INSERT INTO TestBatch VALUES (2, 'bbb')
INSERT INTO TestBatch VALUSE (3, 'ccc') /* Syntax error */
GO
SELECT * FROM TestBatch /* Returns no rows */
GO

In this example, the third INSERT statement generates a run-time duplicate primary key error. The first two INSERT statements are successful and committed, so they remain after the run-time error.

USE pubs
GO
CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3))
GO
INSERT INTO TestBatch VALUES (1, 'aaa')
INSERT INTO TestBatch VALUES (2, 'bbb')
INSERT INTO TestBatch VALUES (1, 'ccc') /* Duplicate key error */
GO
SELECT * FROM TestBatch /* Returns rows 1 and 2 */
GO

SQL Server uses delayed name resolution, in which object names are not resolved until execution time. In this example, the first two INSERT statements are executed and committed, and those two rows remain in the TestBatch table after the third INSERT statement generates a run-time error by referring to a table that does not exist.

USE pubs
GO
CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3))
GO
INSERT INTO TestBatch VALUES (1, 'aaa')
INSERT INTO TestBatch VALUES (2, 'bbb')
INSERT INTO TestBch VALUES (3, 'ccc') /* Table name error */
GO
SELECT * FROM TestBatch /* Returns rows 1 and 2 */
GO

---

quote:

---

COMMIT TRANSACTION
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.

Syntax
COMMIT [ TRAN [ SACTION ] [ transaction_name | @tran_name_variable ] ]

Arguments
transaction_name

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.

@tran_name_variable

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.

Remarks
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.

Examples
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

---

quote:

---

Transaction Recovery
Every Microsoft® SQL Server™ 2000 database has a transaction log that records data modifications made in the database. The log records the start and end of every transaction and associates each modification with a transaction. An instance of SQL Server stores enough information in the log to either redo (roll forward) or undo (roll back) the data modifications that make up a transaction. Each record in the log is identified by a unique log sequence number (LSN). All of the log records for a transaction are chained together.

An instance of SQL Server records many different types of information in the transaction log. Instances of SQL Server 2000 primarily log the logical operations performed. The operation is reapplied to roll forward a modification, and the opposite of the logical operation is performed to roll back a modification.

Each instance of SQL Server controls when modifications are written from its data buffers to disk. An instance of SQL Server may cache modifications in buffers for a period of time to optimize disk writes. A buffer page that contains modifications that have not yet written to disk is known as a dirty page. Writing a dirty buffer page to disk is called flushing the page. When modifications are cached, care must be taken to ensure that no data modification is flushed before the corresponding log image is written to the log file. This could create a modification that could not be rolled back if necessary. To ensure that they can recover all modifications, instances of SQL Server use a write-ahead log, which means that all log images are written to disk before the corresponding data modification.

A commit operation forces all log records for a transaction to the log file so that the transaction is fully recoverable even if the server is shut down. A commit operation does not have to force all the modified data pages to disk as long as all the log records are flushed to disk. A system recovery can roll the transaction forward or backward using only the log records.

Periodically, each instance of SQL Server ensures that all dirty log and data pages are flushed. This is called a checkpoint. Checkpoints reduce the time and resources needed to recover when an instance of SQL Server is restarted. For more information on checkpoint processing, see Checkpoints and the Active Portion of the Log.

Rolling Back an Individual Transaction
If any errors occur during a transaction, the instance of SQL Server uses the information in the log file to roll back the transaction. This rollback does not affect the work of any other users working in the database at the same time. Usually, the error is returned to the application, and if the error indicates a possible problem with the transaction, the application issues a ROLLBACK statement. Some errors, such as a 1205 deadlock error, roll back a transaction automatically. If anything stops the communication between the client and an instance of SQL Server while a transaction is active, the instance rolls back the transaction automatically when notified of the stoppage by the network or operating system. This could happen if the client application terminates, if the client computer is shut down or restarted, or if the client network connection is broken. In all of these error conditions, any outstanding transaction is rolled back to protect the integrity of the database.

Recovery of All Outstanding Transactions at Start-up
It is possible for an instance of SQL Server to sometimes stop processing (for example, if an operator restarts the server while users are connected and working in databases). This can create two problems:

There may be an unknown number of SQL Server transactions partially completed at the time the instance stopped. These incomplete transactions need to be rolled back.


There may be an unknown number of data modifications recorded in the SQL Server database log files, but the corresponding modified data pages were not flushed to the data files before the server stopped. Any committed modifications must be rolled forward.
When an instance of SQL Server is started, it must find out if either of these conditions exist and address them. The following steps are taken in each SQL Server database that is in the instance:

The LSN of the last checkpoint is read from the database boot block along with the Minimum Recovery LSN.


The transaction log is scanned from the Minimum Recovery LSN to the end of the log. All committed dirty pages are rolled forward by redoing the logical operation recorded in the log record.


The instance of SQL Server then scans backward through the log file rolling back all uncompleted transactions by applying the opposite of the logical operation recorded in the log records.
The RESTORE statement also uses this type of recovery, unless a user specifies the NORECOVERY option. When restoring a sequence of database, differential, or log backups to recover a database to a point of failure, you specify NORECOVERY on all RESTORE statements except when restoring the last log backup. When the last backup in the sequence is restored, the RESTORE statement also has to ensure that all uncompleted transactions are rolled back. You specify the RECOVERY option on this RESTORE statement, in which case it uses the same logic as the startup recovery process to roll back all transactions that are still marked incomplete at the end of the last log.

©1988-2000 Microsoft Corporation. All Rights Reserved.

---