Transactional operations (Begin/commit/rollback/save TRANSACTION)

BEGIN TRANSACTION

Marks the starting point for an explicit local transaction. BEGIN TRANSACTION The @ @TRANCOUNT by 1 increments.

BEGIN TRANSACTION represents a point where data referenced by a connection is logically and physically consistent at that point. In the event of an error, all data changes after BEGIN TRANSACTION can be rolled back to return the data to a known consistent state. Each transaction continues to execute until it is complete without error and commits a permanent change to the database with COMMIT TRANSACTION, or errors are encountered and all changes are erased with the ROLLBACK TRANSACTION statement.

Begin TRANSACTION initiates a local transaction for the connection that issued this statement. Depending on the setting of the current transaction isolation level, many of the resources obtained to support the Transact-SQL statements issued by the connection are locked by the transaction until the transaction is completed using the COMMIT TRANSACTION or ROLLBACK TRANSACTION statement. A transaction that is in an incomplete state for a long time prevents other users from accessing those locked resources and also prevents log truncation.

Although BEGIN TRANSACTION initiates a local transaction, it is not recorded in the transaction log until the application next performs an operation that must be logged, such as executing an INSERT, UPDATE, or DELETE statement. An application can perform operations such as acquiring a lock to secure the transaction isolation level of a SELECT statement, but it is not logged until the application performs a modification operation.

Grammar

BEGIN {TRAN | TRANSACTION}

[{transaction_name | @tran_name_variable}

[With MARK [' description ']]

]

[ ; ]

Parameters

Transaction_name

The name assigned to the transaction. Transaction_name must conform to an identifier rule, but the identifier cannot contain more than 32 characters. Only at the outermost begin...commit or BEGIN ... ROLLBACK uses the transaction name in nested statement pairs. Transaction_name are always case-sensitive, even if the SQL Server instance is case insensitive.

@tran_name_variable

The name of a user-defined variable that contains a valid transaction name. Variables must be declared with char, varchar, nchar, or nvarchar data types. If the character passed to the variable is than characters to 32, only the preceding 32 characters are used, and the remaining characters are truncated.

with MARK [' description ']

Specifies that transactions are marked in the log. Description is a string that describes the tag. A description longer than 128 characters is truncated to 128 characters before it is stored in the Msdb.dbo.logmarkhistory table.

If you use with MARK, you must specify a transaction name. With Mark allows the transaction log to be restored to a named tag.

COMMIT TRANSACTION

Flags the end of a successful implicit transaction or explicit transaction. The COMMIT TRANSACTION command should be issued only if all the data referenced by the transaction is logically correct. If @ @TRANCOUNT is 1,commit TRANSACTION makes all data modifications since the start of the transaction a permanent part of the database, freeing up the resources occupied by the transactions and reducing the @ @TRANCOUNT to 0. If the @ @TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements the @ @TRANCOUNT by 1 and the transaction remains active.

If the committed transaction is a Transact-SQL distributed transaction, commit TRANSACTION will trigger MS DTC to use the two-phase commit protocol to commit all servers involved in the transaction. If a local transaction spans two or more databases on the same instance of the database engine, the instance uses an internal two-phase commit to commit all databases that involve the transaction. A COMMIT TRANSACTION when @ @TRANCOUNT is 0 causes an error, because there is no corresponding BEGIN TRANSACTION.

The transaction cannot be rolled back after a COMMIT TRANSACTION statement is issued because the data modification has become a permanent part of the database.

The database engine increases the transaction count within a statement only if the transaction count is 0 o'clock at the beginning of the statement.

Grammar

COMMIT [{TRAN | TRANSACTION} [transaction_name | @tran_name_variable]] [with (delayed_durability = {OFF | on})]

[ ; ]

Parameters

Transaction_name

The SQL Server database engine ignores this parameter. TRANSACTION_NAME Specifies the name of the transaction assigned by the preceding BEGIN transaction. Transaction_name must conform to the rules for identifiers, but cannot exceed 32 characters. Transaction_name is a way to help read by indicating to the programmer that the COMMIT transaction is associated with the BEGIN transaction.

@tran_name_variable

The name of a user-defined variable that contains a valid transaction name. Variables must be declared with char, varchar, nchar, or nvarchar data types. If the number of characters passed to the variable exceeds 32, only 32 characters are used, and the remaining characters are truncated.

Delayed_durability

The option to request that this transaction be submitted together with deferred persistence. If the database has been changed with delayed_durability = DISABLED or delayed_durability = forced, the request is ignored.

ROLLBACK TRANSACTION

Rolls back an explicit or implicit transaction to the start of a transaction or to a savepoint within a transaction. You can use ROLLBACK TRANSACTION to clear all data modifications from the start of a transaction or to a savepoint. It also frees resources that are controlled by the transaction. The ROLLBACK TRANSACTION statement does not generate a message that is displayed to the user. If you need a warning in a stored procedure or trigger, use the RAISERROR or PRINT statement. RAISERROR is the preferred statement used to indicate errors.

Grammar

ROLLBACK {TRAN | TRANSACTION}

[Transaction_name | @tran_name_variable

| Savepoint_name | @savepoint_variable]

[ ; ]

Parameters

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 a transaction is nested, transaction_name must be the name in the outermost BEGIN transaction statement. Transaction_name are always case-sensitive, even if the SQL Server instance is case insensitive.

@ tran_name_variable

The name of a user-defined variable that contains a valid transaction name. Variables must be declared with char, varchar, nchar, or nvarchar data types.

Savepoint_name

Is the savepoint_name in the SAVE TRANSACTION statement. Savepoint_name must conform to the rules for identifiers. You can use savepoint_name when a conditional rollback should affect only part of a transaction.

@ savepoint_variable

is the name of a user-defined variable that contains a valid savepoint name. Variables must be declared with char, varchar, nchar, or nvarchar data types.

SAVE TRANSACTION

Set a savepoint within a transaction. A savepoint can define a location that a transaction can return after it has been partially canceled by a condition. If the transaction is rolled back to the savepoint, other remaining Transact-SQL statements and COMMIT TRANSACTION statements must be completed as needed, or the transaction must be completely canceled by rolling the transaction back to the starting point. To cancel the entire transaction, use the ROLLBACK TRANSACTION transaction_name statement. This will undo all the statements and procedures for the transaction. A duplicate savepoint name is allowed in a transaction, but the ROLLBACK TRANSACTION statement that specifies the name of the savepoint rolls back only the transaction to the most recent save TRANSACTION using that name.

Grammar

SAVE {TRAN | TRANSACTION} {savepoint_name | @savepoint_variable}

[ ; ]

Parameters

Savepoint_name

The name assigned to the save point. The save point name must conform to the rules of the identifier, but cannot exceed 32 characters in length. Transaction_name are always case-sensitive, even if the SQL Server instance is case insensitive.

@savepoint_variable

The name of the user-defined variable that contains the valid savepoint name. Variables must be declared with char, varchar, nchar, or nvarchar data types. If the length is longer than 32 characters, it can be passed to the variable, but only the first 32 characters are used.

Example

The following example shows how to use the transaction savepoint to roll back only the changes made by the stored procedure if the active transaction was started before the stored procedure was executed.

Use AdventureWorks2012;

GO

IF EXISTS (SELECT name from sys.objects

WHERE name = N ' Savetranexample ')

DROP PROCEDURE savetranexample;

GO

CREATE PROCEDURE Savetranexample

@InputCandidateID INT

As

--Check whether the stored procedure is called within the active transaction (nested transactions)

--@TranCounter = 0 means that it is not called in the active transaction

--@TranCounter > 0 means that there is already an active transaction before the stored procedure call

DECLARE @TranCounter INT;

SET @TranCounter = @ @TRANCOUNT;

IF @TranCounter > 0

--there is already an active transaction before the stored procedure call. Creates a savepoint that, if this stored procedure is faulted, rolls back only to the operation before the stored procedure is executed.

SAVE TRANSACTION Proceduresave;

ELSE

--Create a new transaction

BEGIN TRANSACTION;

--Modify database.

BEGIN TRY

DELETE humanresources.jobcandidate

WHERE Jobcandidateid = @InputCandidateID;

.

IF @TranCounter = 0

--@TranCounter = 0 Only There is a transaction in this stored procedure, the transaction must be committed

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

--error occurred, need to determine rollback level

IF @TranCounter = 0

--The transaction rolls back the entire transaction only in this stored procedure

-Roll back to complete transaction.

ROLLBACK TRANSACTION;

ELSE

--The transaction was created before this stored procedure starts (nested transactions)

--Xact_state (), a scalar function that reports the state of the user transaction for the currently running request. Xact_state indicates whether the request has an active user transaction, and whether the transaction can be committed.

--xact_state () = 1, the current request has an active user transaction. Requests can perform any operation, including writing data and committing transactions.

--xact_state () = 0, the current request does not have an active user transaction.

--xact_state () =-1, the current request has an active user transaction, but an error occurred that caused the transaction to be categorized as a transaction that could not be committed. The request cannot commit the transaction or rolls back to the savepoint; it can only request a full rollback of the transaction.

--The request cannot perform any write operations until the transaction is rolled back. Requests can only perform read operations before rolling back a transaction. After a transaction is rolled back, the request can perform read-write operations and start a new transaction.

IF xact_state () <>-1

--rollback to the error before this stored procedure begins.

ROLLBACK TRANSACTION Proceduresave;

--Output error message

DECLARE @ErrorMessage NVARCHAR (4000);

DECLARE @ErrorSeverity INT;

DECLARE @ErrorState INT;

SELECT @ErrorMessage = Error_message ();

SELECT @ErrorSeverity = error_severity ();

SELECT @ErrorState = Error_state ();

RAISERROR (@ErrorMessage,

@ErrorSeverity,

@ErrorState

);

END CATCH

GO

Transactional operations (Begin/commit/rollback/save TRANSACTION)