Explicit transaction definition

An explicit TRANSACTION begins with a BEGIN TRANSACTION statement and ends with a COMMIT or ROLLBACK statement.

note

BEGIN TRANSACTION increments @@TranCount by 1.

BEGIN TRANSACTION represents a point at which the data referenced by the connection is logically and physically consistent. If an error occurs, all data changes made after BEGIN TRANSACTION can be rolled back to return the data to a known consistent state. Each TRANSACTION continues until it completes without error and makes permanent changes to the database with COMMIT TRANSACTION, or an error is encountered and all changes are erased with the ROLLBACK TRANSACTION statement.

BEGIN TRANSACTION starts a local TRANSACTION for the connection that issued this statement. Depending on the current TRANSACTION isolation level Settings, many resources acquired to support transact-SQL statements issued by this connection are locked by the TRANSACTION until the TRANSACTION is completed using COMMIT TRANSACTION or ROLLBACK TRANSACTION statements. Transactions that remain unfinished for a long time prevent other users from accessing these locked resources and prevent log truncation.

Although BEGIN TRANSACTION starts a local TRANSACTION, it is not recorded in the TRANSACTION log until the application performs a subsequent operation that must be recorded, such as an INSERT, UPDATE, or DELETE statement. An application can perform some operations, such as acquiring a lock to protect the transaction isolation level of a SELECT statement, but the logs are not recorded until the application performs a modification operation.

Naming multiple transactions with a single transaction name in a series of nested transactions has no effect on that transaction. The system only registers the first (most external) transaction name. Rolling back to any name other than a valid save name produces an error. In fact, any statement executed prior to rollback will not be rolled back when an error occurs. These statements are rolled back only when the outer transaction is rolled back.

A local TRANSACTION started by a BEGIN TRANSACTION statement will be upgraded to a distributed TRANSACTION if the following operations are performed before the statement is committed or rolled back:

Execute an INSERT, DELETE, or UPDATE statement that references a remote table on the linked server. INSERT, UPDATE, or DELETE statements fail if the OLE DB access interface used to access the linked server does not support the ITransactionJoin interface.

When the REMOTE_PROC_TRANSACTIONS option is enabled, the remote stored procedure is invoked.

The local copy of SQL Server becomes the transaction controller and uses the Microsoft Distributed Transaction Coordinator (MS DTC) to manage distributed transactions.

BEGIN DISTRIBUTED TRANSACTION enables transactions to be executed explicitly as DISTRIBUTED transactions. For more information, see BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

When SET IMPLICIT_TRANSACTIONS is SET to ON, the BEGIN TRANSACTION statement creates two nested transactions. For more information, see SET IMPLICIT_TRANSACTIONS (Transact-SQL)

Marked transaction

The WITH MARK option causes the transaction name to be placed in the transaction log. When restoring the database to its earlier state, you can use marked transactions instead of dates and times. For more information, see using flagged transactions to consistently RESTORE transactions (full recovery mode) and RESTORE (Transact-SQL) of related databases.

In addition, to restore a set of related databases to a logically consistent state, transaction log tokens must be used. Tags can be placed by distributed transactions in the transaction log of the relevant database. Restoring this set of related databases to these tags will result in a set of databases that are transactionally consistent. Placing tags in related databases requires special procedures.

Tags are placed in the transaction log only when the database is updated by a tag transaction. Transactions that do not modify data are not marked.

Create explicit transactions using t-SQL scripts

Grammar:

Use database name; go

begin { tran| transaction } [ { transaction_name | @tran_name_variable } [ with mark [ ‘description’ ] ]

begin

Business code 1;

save { tran | transaction } { savepoint_name | @savepoint_variable };

rollback { tran | transaction } [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ];

commit { tran | transaction } { transaction_name | @tran_name_variable } with(delayed_durability=on);

end

go

Syntax:

— Begin Transaction Transaction_name — Scope: SQL Server (as of 2008) and Azure SQL Database — name assigned to transaction. Transaction_name must comply with the identifier rules, but the identifier cannot contain more than 32 characters. Only in the outermost BEGIN… COMMIT or the BEGIN… Use transaction names in ROLLBACK nested statement pairs. Transaction_name is always case sensitive, even if the SQL Server instance is case insensitive.

— BEGIN Transaction @Tran_name_variable — Scope: SQL Server (as of 2008) and Azure SQL Database — user-defined variable names with valid transaction names. The variable must be declared using the CHAR, VARCHAR, NCHAR, or nvARCHAR data types. If more than 32 characters are passed to the variable, only the first 32 characters are used; The remaining characters are truncated.

— With mark [‘description’] — Applicable to SQL Server (as of 2008) and Azure SQL Database — specifies to mark transactions in logs. Description is a string that describes the tag. Truncate a description longer than 128 characters to 128 characters before storing it in the MSDB.dbo.logmarkHistory table. If WITH MARK is used, the transaction name must be specified. WITH MARK allows you to restore transaction logs to named tags.

— Save Transaction Savepoint_name — Name assigned to savepoint. Savepoint names must comply with the rules for identifiers, but cannot exceed 32 characters in length. Savepoint_name is always case sensitive, even if the SQL Server instance is case insensitive.

— Save Transaction @savepoint_variable — The name of the user-defined variable that contains the name of the valid savepoint. The variable must be declared using the CHAR, VARCHAR, NCHAR, or nvARCHAR data types. If it is longer than 32 characters, it can also be passed to a variable, but only the first 32 characters are used.

–rollback { tran | transaction } [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ]; Roll back an explicit or implicit transaction to the beginning of the transaction or to a savepoint within the transaction. You can use ROLLBACK TRANSACTION to clear all data changes made since the beginning of a TRANSACTION or to a savepoint. It also frees resources controlled by the transaction.

–commit { tran | transaction } { transaction_name | @tran_name_variable }; — Marks the end of a successful implicit or explicit transaction. If @@TranCount is 1, the COMMIT TRANSACTION makes all data modifications performed since the start of the TRANSACTION permanent part of the database, frees up the resources consumed by the TRANSACTION, and reduces @@TranCount to 0. If @@TranCount is greater than 1, the COMMIT TRANSACTION decrements @@TranCount by 1 and the TRANSACTION remains active.

— delayed_bug — Scope: SQL Server and Azure SQL Database — request option to submit this transaction with deferred persistence. If the database has been changed for delayed_bugs = DISABLED or delayed_bugs = FORCED, the request is ignored. For more information, see Topic Control Transaction Persistence.

Example:

Declare database references

use testss;

go

Begin Transaction explicitTran with mark ‘Start an explicit transaction’

begin

declare @counts int =0;

Insert into test1 (name, sex, age, every, height) values (‘ transaction test ‘and’ male ‘, ’21’, ’20’, ‘178’);

set @counts=@counts+(select @@ROWCOUNT);

save tran inserttran1; Rollback point 1, the transaction has inserted a data

Insert into test2(name) values(‘ transaction test ‘);

set @counts=@counts+(select @@ROWCOUNT);

save tran inserttran2; Rollback point 2, the transaction has inserted two data

Insert into test3(name,sex,age, Classid) VALUES (‘ class ‘,’ male ‘,’asd’,’23’);

set @counts=@counts+(select @@ROWCOUNT);

save tran inserttran3; Rollback point 3, the transaction has inserted three data

Error rollback to rollback point

if @counts=3

rollback transaction inserttran3;

else if @counts=2

rollback transaction inserttran2;

else if @counts=1

rollback transaction inserttran1;

Commit transaction or roll back transaction

if @counts<>0

commit transaction explicittran

–with(delayed_durability=on)

;

else

rollback transaction explicittran;

end

go

Example results: