SQL Server transaction Processing

Transaction definition:

A transaction is a single unit of work. If a transaction succeeds, all data changes made in the transaction are

Commits and becomes a permanent part of the database. If the transaction encounters an error and must be canceled or rolled back, all

Data changes are cleared.

Transaction three operating modes:
Automatically commit transactions
Each individual statement is a transaction.
An explicit transaction
Each transaction is explicitly started with a BEGIN TRANSACTION statement.
Explicitly ends with a COMMIT or ROLLBACK statement.
Implicit transactions
The previous transaction completes the start of the transaction implicitly, but each transaction still has a COMMIT or ROLLBACK statement

Explicit completion.

Syntax for transactional 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.

BEGIN transaction The @ @TRANCOUNT plus 1.

BEGIN TRANSACTION stands for 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 execution until it is complete without error and commits a permanent change to the database with COMMIT TRANSACTION, or errors 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 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 only the first 32 characters are used

@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 the completion of the control transaction

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

The enlisted connection in a distributed transaction executes a remote stored procedure call that references a remote server.
The enlisted connection in the 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. Local and remote databases will either commit at the same time 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
GONote:
If you need to connect to a remote db, be sure to fix the linkserver RPC option to True if it is a linkserver mode connection.

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 SQL is rolled back.) Especially for Procedure intermediate call Procedure, if the first Procedure Ok, the Procedure in the middle of the call has an error if set xact_ Abort=false, the error is partially rolled back, other parts are submitted, and of course external procedure are also submitted. ）.

--- in Distributed Trans It is important to note that the following parameters must be set (Xact_abort)

Grammar SET Xact_abort {on | OFF}

Note When SET Xact_abort is on, the entire transaction terminates and rolls back if the Transact-SQL statement produces a run-time error. When OFF, only the Transact-SQL statement that generated the error is rolled back, and the transaction continues processing. 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 be violated in a transaction that contains other Transact-SQL statements. An error was generated in the first statement set, but the other statements were executed successfully and the transaction succeeded
Submit. In the second set of statements Xact_abort sets to ON. This results in a statement error that terminates the batch and causes the transaction to be rolled back.

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 a savepoint 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 the location where a transaction can be returned if a part of the transaction is conditionally canceled. If you roll back a transaction to a savepoint, you must continue to complete the transaction (using more Transact-SQL statements and COMMIT TRANSACTION statements, if necessary), or you must cancel the transaction completely (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 initiated by the BEGIN distributed TRANSACTION or upgraded from a local transaction.

2 : When a transaction begins, the resources used in the transaction are kept in control until the transaction is complete (that is, locked). When a portion of a transaction is rolled back to the savepoint, the resource continues to be controlled until the transaction completes (or rolls back all transactions).

Example:BEGIN TRANSACTION
Save Transaction A
Insert into demo values (' BB ', ' B term ')
Rollback TRANSACTION A
CREATE TABLE Demo2 (name varchar (ten), 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]

parameter
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 of the  begin transaction  statement from the farthest.
@tran_name_variable
is the name of a user-defined variable that contains a valid transaction name. The variable must be declared with the  char, varchar, nchar , or  nvarchar  data type.
Savepoint_name
is  savepoint_name from the  save transaction  statement. savepoint_name  must conform to the rules for identifiers. Make   with Savepoint_name when 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 the  char, varchar, nchar , or  nvarchar  data type.

Notes ROLLBACK TRANSACTION clears all data modifications made from the beginning of the transaction or to a savepoint. ROLLBACK also releases resources controlled by the transaction.
ROLLBACK transaction without savepoint_name and Transaction_name is rolled back to the beginning of the transaction. When a transaction is nested, the statement rolls back all the inner transactions to the furthest begin TRANSACTION statement. In both cases, the ROLLBACK TRANSACTION reduced the @ @TRANCOUNT system function to 0. ROLLBACK
TRANSACTION Savepoint_name does not reduce @ @TRANCOUNT.

Note:
The ROLLBACK TRANSACTION statement does not release any locks if Savepoint_name is specified.
In a distributed transaction that is explicitly initiated by the BEGIN distributed TRANSACTION or promoted from a local transaction, ROLLBACK TRANSACTION cannot
Reference Savepoint_name. in the execution COMMIT TRANSACTION The transaction cannot be rolled back after the statement .

A duplicate savepoint name is allowed within a transaction, but ROLLBACK TRANSACTION uses a duplicate savepoint name to roll back only to the most recent save TRANSACTION that uses the save point name.

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

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

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 processing of the statement containing the firing 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 the RAISERROR or PRINT statement. RAISERROR is the preferred statement used to indicate errors.

The effect of ROLLBACK on cursors is defined by the following three rules:
When CURSOR_CLOSE_ON_COMMIT is set to ON, ROLLBACK closes without releasing 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. Closes but does not release any other types of open cursors.
For errors that result in terminating the batch and generating an internal rollback, all cursors declared within the batch containing the error statement are disposed.
Regardless of the type of cursor or the setting of the CURSOR_CLOSE_ON_COMMIT, all cursors are freed, including cursors declared within the stored procedure called by the error batch. Cursors declared within batches prior to the error batch are governed by rules 1 and 2. A deadlock error belongs to this type of error. This type of error is also generated automatically by the ROLLBACK statement emitted in the trigger.

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 (ten), age int)
Insert into Demo2 (name,age) VALUES (' Lis ', 1)
ROLLBACK TRANSACTION

COMMIT TRANSACTION
Flags the end of a successful implicit transaction or user-defined transaction. If @ @TRANCOUNT is 1,commit

TRANSACTION makes all data modifications that have been performed since the beginning of the transaction become a permanent part of the database, releasing the connection

Resources, and reduces the @ @TRANCOUNT to 0. If @ @TRANCOUNT is greater than 1, the commit

TRANSACTION the @ @TRANCOUNT by 1.

The COMMIT TRANSACTION command is issued only if the logic of all data referenced by the firm is correct.
COMMIT work
Flag the end of the transaction.
Grammar
COMMIT [Work]

Comments
This statement has the same functionality as commit TRANSACTION, but commit TRANSACTION accepts user-defined transactions

Name. This COMMIT syntax that specifies or does not specify the Optional keyword work is compatible with SQL-92

Example :
BEGIN TRANSACTION A
Insert into demo values (' BB ', ' B term ')
Commit TRANSACTION A

Implicit transactions
When a connection operates 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, simply commit 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 automatically started when SQL Server executes any of the following statements for the first time:

insert

 	alter TABLE
 	create	open
td>	delete	revoke
 	drop		grant	update

The transaction will remain in effect until the COMMIT or ROLLBACK statement is issued. The next time a connection executes these statements after the first transaction is committed or rolled back

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 implicit 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 (ten), age int)
Insert into Demo2 (name,age) VALUES (' Lis ', 1)
ROLLBACK TRANSACTION
--When creating table Demo2, SQL Server has implicitly created a trans, knowing commit or rollback

Nested transaction processing :

The 1:trans is nested, merging the internal trans to the outside and forming a trans.

BEGIN Tran T1

----in the first trans.
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 first trans.
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

Any other name (except for a valid save Roll Call) will cause an error.

In fact, any statements executed before the rollback are not rolled back when the error occurs. 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 A, 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 first trans.
Insert into Demo2 (name,age) VALUES (' Lis ', 2)
Commit TRANSACTION T1

Example: An internal transaction commits SQL Server without 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 first trans.
Insert into Demo2 (name,age) VALUES (' Lis ', 2)
Commit TRANSACTION T1

SQL Server Isolation Level:

1: Set timeout parameter

Set Lock_timeout 5000

Lock timeout 5 seconds will be automatically unlocked

Set Lock_timeout 0

Immediate unlock, return error default is-1, infinite wait

2:

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

READ COMMITTED

Specifies that shared locks are controlled when data is read to avoid dirty reads, but data can be changed before the end of the transaction, resulting in non-repeatable reads or

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

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

READ UNCOMMITTED

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

Read or dirty read; You can change the values within the data before the transaction ends, and the rows can appear in the dataset or from the data

Set disappears. This option works the same as setting NOLOCK on all the tables in all statements within a transaction. This is a four isolation level.

Limit the minimum level.

Repeatable READ

Lock all data used in the 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 subsequent reads of the current transaction. Because concurrency is lower than the default isolation level, you should only make

Use 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 the maximum limit in the four isolation levels. Because the concurrency level is low, you should use this option only when necessary. This option

Is the same as setting HOLDLOCK on all tables in all SELECT statements within a transaction.

SQL Server transaction Processing