SQL Server transaction Processing

server| Transaction Processing Transaction definition: A transaction is a single unit of work. If a transaction succeeds, all data changes made in that transaction are commit to become a permanent part of the database. If the transaction encounters an error and must be canceled or rolled back, all data changes have been cleared. Transaction Three mode of operation:
Auto COMMIT Transaction
Each individual statement is a transaction.
An explicit transaction
Each transaction starts explicitly with the BEGIN TRANSACTION statement,
Ends explicitly with a COMMIT or ROLLBACK statement.
Hidden transactions
The previous transaction completes an implicit start of the newly-transacted transaction, but each transaction is still in a COMMIT or ROLLBACK statement explicitly completed. syntax for transaction operations: BEGIN TRANSACTION
BEGIN Distributed TRANSACTION
COMMIT TRANSACTION
COMMIT WORK
ROLLBACK WORK
SAVE TRANSACTION
BEGIN TRANSACTION

BEGIN TRANSACTION
Marks the starting point for an explicit local transaction.

The BEGIN transaction the @ @TRANCOUNT plus 1.

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

Grammar
BEGIN TRAN [saction] [transaction_name | @tran_name_variable [with MARK [' description ']]]

Example:
BEGIN TRAN T1
UPDATE table1 ...
--nest Transaction M2
BEGIN TRAN M2 with MARK
UPDATE table2 ...
SELECT * FROM table1
COMMIT TRAN M2
UPDATE Table3 ...
COMMIT TRAN T1

BEGIN Distributed TRANSACTION
Specifies the start of a Transact-SQL Distributed transaction managed by the Microsoft Distributed Transaction Coordinator (MS DTC).

Grammar
BEGIN distributed TRAN [saction]
[transaction_name | @tran_name_variable]

Parameters
Transaction_name
is a user-defined transaction name used to track distributed transactions in the MS DTC utility. Transaction_name must conform to the rules for identifiers, but use only the first 32 characters

@tran_name_variable
is a user-defined variable name that contains a transaction name that is used to track distributed transactions in the MS DTC utility. The variable must be declared with a char, varchar, nchar, or nvarchar data type.

Comments
The server executing the BEGIN Distributed TRANSACTION statement is the transaction creator and controls the completion of the transaction

When a subsequent COMMIT TRANSACTION or ROLLBACK TRANSACTION statement is issued by the connection,
The master server requests MS DTC to manage the completion of distributed transactions between the servers involved.
There are two ways to register a remote SQL server in a distributed transaction:

A registered connection in a distributed transaction executes a remote stored procedure call that references a remote server.
A registered connection in a distributed transaction executes a distributed query that references a remote server.

Example
This example updates the author's last name on the local and remote databases. The local and remote databases will either commit or roll back the transaction at the same time.

Description
MS DTC must be installed on the current SQL Server.

Use pubs
Go
BEGIN Distributed TRANSACTION
UPDATE Authors
SET au_lname = ' McDonald ' WHERE au_id = ' 409-56-7008 '
EXECUTE link_server_t. Pubs.dbo.changeauth_lname ' 409-56-7008 ', ' McDonald '
COMMIT TRAN
Go:
If you need to connect to remote DB, be sure to fix the linkserver RPC option to True if you are connecting linkserver.

SET Xact_abort
Specifies that when a Transact-SQL statement produces a run-time error, Microsoft? SQL Server? Whether the current transaction is automatically rolled back.

(It can be simpler to understand that if there is any SQL error in the middle, all the SQL rolls back.) Especially suitable for Procedure intermediate call Procedure, if the first Procedure Ok, there is an error in the called Procedure if set xact_ Abort=false, the part of the error is rolled back, the other parts are submitted, and of course the external procedure is also submitted. ）.

---in distributed trans must be careful to set the following parameters (Xact_abort)

Grammar SET Xact_abort {on | OFF}

Note When SET xact_abort is on, if a Transact-SQL statement produces a run-time error, the entire transaction terminates and is rolled back. When off, only Transact-SQL statements that produce errors are rolled back, and transactions continue to be processed. Compilation errors, such as syntax errors, are not affected by SET Xact_abort.

for most OLE DB providers, including SQL Server, data-modification statements in implicit or explicit transactions must have Xact_abort set to on.

Set XACT_ABORT settings are set at execution or runtime, not at parse time.

The following example causes a foreign key error to occur in a transaction that contains other Transact-SQL statements. An error occurred in the first statement set, but the other statements were executed successfully and the transaction succeeded
Submit. In the second statement set, set Xact_abort to ON. This causes the statement error to terminate the batch process and rollback the transaction.

CREATE TABLE T1 (a int PRIMARY KEY)
CREATE TABLE T2 (a int REFERENCES T1 (a))
Go
INSERT into T1 VALUES (1)
INSERT into T1 VALUES (3)
INSERT into T1 VALUES (4)
INSERT into T1 VALUES (6)
Go
SET Xact_abort off
Go
BEGIN TRAN
INSERT into T2 VALUES (1)
INSERT into T2 VALUES (2)/* Foreign Key Error * *
INSERT into T2 VALUES (3)
COMMIT TRAN
Go

SET Xact_abort on
Go

BEGIN TRAN
INSERT into T2 VALUES (4)
INSERT into T2 VALUES (5)/* Foreign Key Error * *
INSERT into T2 VALUES (6)
COMMIT TRAN
Go

SAVE TRANSACTION
Set the save point within a transaction.

Grammar SAVE TRAN [saction] {savepoint_name | @savepoint_variable}
parameter savepoint_name
is the name assigned to the save point. The savepoint name must conform to the rules for identifiers, but only the first 32 characters are used.
@savepoint_variable
is the name of a user-defined variable that contains a valid savepoint name.
The variable must be declared with a char, varchar, nchar, or nvarchar data type. notes
A user can set a savepoint or tag within a transaction. A savepoint defines where a transaction can be returned if it is conditionally canceled as part of a transaction. If a transaction is rolled back to the savepoint, the transaction must be continued (if necessary, with more Transact-SQL statements and COMMIT TRANSACTION statements), or the transaction must be completely canceled (by rolling the transaction back to its starting point). To cancel the entire transaction, use the ROLLBACK TRANSACTION transaction_name format. This will undo all the statements and procedures for the transaction.

note:1: SAVE TRANSACTION is not supported in distributed transactions that are explicitly started by the BEGIN distributed TRANSACTION or upgraded from a local transaction.

2: When the transaction starts, it will always control the resources used in the transaction until the transaction completes (that is, the lock). When a part of a transaction is rolled back to the savepoint, the resource continues to be controlled until the transaction completes (or the entire transaction is rolled back).

Example:BEGIN TRANSACTION
Save Transaction A
Insert into demo values (' BB ', ' B term ')
Rollback TRANSACTION A
CREATE TABLE Demo2 (name varchar, age int)
Insert into Demo2 (name,age) VALUES (' Lis ', 1)
Commit TRANSACTION

ROLLBACK TRANSACTION

Rolls back an explicit or implicit transaction to the start of a transaction or to a savepoint within a transaction.
Grammar
ROLLBACK [TRAN [saction]
[transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable]]

Parameters
Transaction_name
is the name assigned to the transaction on the BEGIN TRANSACTION. Transaction_name must conform to the rules for identifiers, but use only the first 32 characters of the transaction name. Nesting
Transaction, transaction_name must be the name from the farthest BEGIN transaction statement.
@tran_name_variable
is the name of a user-defined variable that contains a valid transaction name. The variable must be declared with a char, varchar, nchar, or nvarchar data type.
Savepoint_name
Is the savepoint_name from the SAVE TRANSACTION statement. Savepoint_name must conform to the rules for identifiers. Use savepoint_name when a conditional rollback affects only part of a transaction.
@savepoint_variable
is the name of a user-defined variable that contains a valid savepoint name. The variable must be declared with a char, varchar, nchar, or nvarchar data type.

Notes ROLLBACK TRANSACTION clears all data modifications made from the start of the transaction or to a savepoint. ROLLBACK also releases resources that are controlled by the transaction.
ROLLBACK transaction, without Savepoint_name and transaction_name, is rolled back to the start of the transaction. When a transaction is nested, the statement rolls back all the inner transactions to the farthest BEGIN TRANSACTION statement. In both cases, the ROLLBACK TRANSACTION will reduce the @ @TRANCOUNT system function to 0. ROLLBACK
TRANSACTION Savepoint_name does not reduce the @ @TRANCOUNT.

Note:
The ROLLBACK TRANSACTION statement does not release any locks if specified savepoint_name.
In a distributed transaction that is explicitly started by the BEGIN distributed TRANSACTION or upgraded from a local transaction, ROLLBACK TRANSACTION cannot
Reference Savepoint_name. The transaction cannot be rolled back after the COMMIT TRANSACTION statement has been executed .

Duplicate savepoint names are allowed within a transaction, but ROLLBACK TRANSACTION if a duplicate savepoint name is used, roll back to the most recent save TRANSACTION that uses the savepoint name.

In the stored procedure, the ROLLBACK transaction statement without savepoint_name and transaction_name rolls back all statements to the farthest begintransaction. In a stored procedure, the ROLLBACK TRANSACTION statement causes the value of @ @TRANCOUNT to be different from the @ @TRANCOUNT value when the trigger completes, and generates a message. This information does not affect subsequent processing.

if the ROLLBACK TRANSACTION is emitted in the trigger: all data modifications made to that point in the current transaction are rolled back, including the modifications made by the trigger.
The trigger continues to execute all remaining statements after the ROLLBACK statement. If any of the statements in these statements modify the data, the modifications are not rolled back. Executing the remaining statements does not fire nested triggers. In batch processing, all statements that are located after the statement that fired the trigger are not executed. Each time the trigger is entered, the @ @TRANCOUNT increases by 1, even in autocommit mode. (The system regards a trigger as an implicit nested transaction.) ）

In a stored procedure, the ROLLBACK TRANSACTION statement does not affect subsequent statements in the batch that invokes the procedure;
Subsequent statements in the batch are executed. In the trigger, the ROLLBACK TRANSACTION statement terminates the batch containing the statement that fired the trigger;
Subsequent statements in the batch are not executed.

The ROLLBACK TRANSACTION statement does not generate information that is displayed to the user. If you need a warning in a stored procedure or trigger, use either the RAISERROR or PRINT statement. RAISERROR is the preferred statement used to indicate the error.

The effect of ROLLBACK on cursors is defined by the following three rules:
When CURSOR_CLOSE_ON_COMMIT is set to ON, ROLLBACK closes but does not release all open cursors.
When CURSOR_CLOSE_ON_COMMIT is set to OFF, ROLLBACK does not affect any open synchronous static or insensitive cursors that do not affect fully populated asynchronous static cursors. Any other type of open cursor will be closed but not released.
For errors that cause the batch to terminate and generate an internal rollback, all cursors declared within the batch containing the error statement are released.
All cursors are disposed, regardless of the type of the cursor or the CURSOR_CLOSE_ON_COMMIT setting, including cursors declared within the stored procedure that was invoked by the error batch. Cursors declared within the batch before the error batch are subject to rules 1 and 2. Deadlock errors belong to this type of error. ROLLBACK statements emitted in triggers automatically generate such errors as well.

Permissions
ROLLBACK TRANSACTION permissions are granted to any valid user by default.
Example:

BEGIN TRANSACTION
Save Transaction A
Insert into demo values (' BB ', ' B term ')
Rollback TRANSACTION A

--select * into Demo2 from demo1

CREATE TABLE Demo2 (name varchar, age int)
Insert into Demo2 (name,age) VALUES (' Lis ', 1)
ROLLBACK TRANSACTION

COMMIT TRANSACTION
Flags the end of a successful hidden or user-defined transaction. If @ @TRANCOUNT for 1,commit TRANSACTION Since the start of the business all data modifications that have been performed since are a permanent part of the database, releasing the connection resource, and reduce the @ @TRANCOUNT to 0. If @ @TRANCOUNT greater than 1, the COMMIT The TRANSACTION makes the @ @TRANCOUNT descending by 1.
A COMMIT TRANSACTION command is issued only if the logic of all data referenced by the transaction is correct.
COMMIT WORK
Marks the end of a transaction.
Grammar
COMMIT [WORK] Notes
This statement has the same functionality as a commit TRANSACTION, but a commit TRANSACTION accepts a user-defined transaction name. This designation or the COMMIT syntax for the optional keyword work is not specified and SQL-92 compatible Example :
BEGIN TRANSACTION A
Insert into demo values (' BB ', ' B term ')
Commit TRANSACTION A Hidden Transactions
When a connection is operating in an implicit transaction mode, SQL Server automatically starts a new transaction after committing or rolling back the current transaction. No need to describe the beginning of a transaction, just submit or Roll back each transaction. The implicit transaction pattern generates a continuous chain of transactions. after the implicit transaction mode is set to open for a connection, a transaction is started automatically when SQL Server executes any of the following statements for the first time:

	ALTER TABLE	INSERT
	CREATE	OPEN
	DELETE	REVOKE
	DROP	SELECT
	FETCH	TRUNCATE TABLE
	GRANT	UPDATE

the transaction remains in effect until a COMMIT or ROLLBACK statement is issued. After the first transaction is committed or rolled back, the next time the connection executes these statements SQL Server will automatically start a new transaction when any of the statements in the SQL Server will continue to generate an implicit transaction chain, until the hidden transaction mode is closed Example:
BEGIN TRANSACTION
Save Transaction A Insert into demo values (' BB ', ' B term ')
Rollback TRANSACTION A
CREATE TABLE Demo2 (name varchar, age int)
Insert into Demo2 (name,age) VALUES (' Lis ', 1)
ROLLBACK TRANSACTION
--SQL Server has implicitly created a trans when the CREATE table Demo2, knowing that the commit or rollback Nested transactions: The 1:trans is nested, merging the internal Trans into the outer and forming a Trans. BEGIN Tran T1 ----in the The "
Insert into Demo2 (name,age) VALUES (' Lis ', 1)
---Second Trans BEGIN TRANSACTION T2
Insert into demo values (' BB ', ' B term ')
Commit TRANSACTION T2
----in the The "
Insert into Demo2 (name,age) VALUES (' Lis ', 2)
ROLLBACK TRANSACTION T1 Note: naming multiple transactions with a single transaction name in a series of nested transactions has no effect on the transaction. The system registers only the first (most external) transaction name. rolling back to any other name (except for valid save names) will cause an error . In fact, any statements that were executed before the rollback were not rolled back when the error occurred. This statement is rolled back only when the outer transaction is rolled back. Example: internal transaction rollback SQL Server error. BEGIN Tran T1
Insert into Demo2 (name,age) VALUES (' Lis ', 1)
---Second Trans --server:msg 6401, level, State 1, line 6
---cannot roll back t2. No transaction or savepoint of that name is found.
BEGIN TRANSACTION T2
Insert into demo values (' BB ', ' B term ')
rollback Transaction t2

----in the The "
Insert into Demo2 (name,age) VALUES (' Lis ', 2)
Commit TRANSACTION T1 Example: internal transaction commit SQL Server does not report an error. BEGIN Tran T1
Insert into Demo2 (name,age) VALUES (' Lis ', 1)
---Second Trans no error
BEGIN TRANSACTION T2
Insert into demo values (' BB ', ' B term ')
Commit Transaction t2

----in the The "
Insert into Demo2 (name,age) VALUES (' Lis ', 2)
Commit TRANSACTION T1 SQL Server Isolation level:

1: Set Timeout parameters

Set Lock_timeout 5000

A lock timeout of 5 seconds will automatically unlock

Set Lock_timeout 0

Production immediately unlock, return error defaults to-1, infinite wait

2:

(SET TRANSACTION Isolation Level
{READ Committed
| READ UNCOMMITTED
| Repeatable READ | SERIALIZABLE})

READ committed

Specifies that the shared lock is controlled when reading data to avoid dirty reads, but the data can be changed before the transaction ends, resulting in a non repeatable read or

Phantom Data. This option is the default value for SQL Server.

Avoid dirty reads and cannot modify existing data in other sessions in a transaction. Shared locks.

READ UNCOMMITTED

Performs a dirty read or level 0 isolation lock, which means that no shared locks are issued and exclusive locks are not accepted. When you set this option, you can log

according to the execution of uncommitted read or dirty read; You can change the values in the data before the transaction ends, or the rows can appear in the dataset or from the data

set disappears. This option has the same effect as setting NOLOCK on all the tables in all sentences within a transaction. This is in four isolation levels

Limit the minimum level.

Repeatable READ

Lock all data used in a query to prevent other users from updating the data, but other users can insert new phantom rows into the data

set, and phantom rows are included in the in subsequent reads of the previous transaction. Because concurrency is lower than the default isolation level, you should only make the

With this option.

SERIALIZABLE

Place a range lock on the dataset to prevent other users from updating the dataset or inserting rows into the dataset before the transaction completes. This

is four isolation-level limits the largest level of the system. Because the concurrency level is low, this option should be used only if necessary. This option

function in all SELECT statements within a transaction. Sets the same HOLDLOCK on all tables.