SQL Server database transaction processing (msdn online resources)

 

Transaction definition:

A transaction is a single unit of work. If a transaction succeeds, all data changes made in the transaction will be committed and become a permanent component of the database. If the transaction encounters an error and must be canceled or rolled back, all data changes are cleared.

Three transaction running modes:
Each separate statement of the automatically committed transaction is a transaction. Each explicit transaction starts with a begin transaction statement and ends with a commit or rollback statement. Implicit transactions are implicitly started when the previous transaction is completed, but each transaction is still explicitly completed using commit or rollback statements.

Syntax of the transaction operation:

Begin transaction
Begin Distributed Transaction
Commit transaction
Commit work
Rollback work
Save transaction
Begin transaction

Begin transaction
Mark the starting point of an explicit local transaction.

Begin transaction adds @ trancount to 1.

Begin transaction indicates a point where the data referenced by the connection is logically and physically consistent. If an error occurs, all data changes after begin transaction can be rolled back to return the data to a known consistent state. Each transaction continues to be executed until it completes without error and uses commit transaction to make permanent changes to the database, or encounters an error and erased all changes using the rollback transaction statement.

Syntax
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 point of a Transact-SQL Distributed Transaction managed by the Microsoft Distributed Transaction Processing Coordinator (MS DTC.

Syntax
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 comply with the identifier rules, but only use the first 32 characters

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

Note
The server that executes the begin Distributed Transaction statement is the transaction creator and controls the completion of the transaction.

When the connection sends a subsequent commit transaction or rollback transaction statement,
The master server requests the completion of ms dtc managing distributed transactions between involved servers.
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, which references a remote server.
A registered connection in a distributed transaction executes a distributed query that references a remote server.

Example
In this example, the author's surname is updated on the local and remote databases. Local and remote databases 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 = '2017-56-7008'
ExecuteLink_server_t. Pubs. DBO. changeauth_lname '2017-56-7008 ', 'mcdonald'
Commit tran
GoNote:
If you need to connect to a remote database and use the linkserver Method for connection, you must set the RPC option of the linkserver to true.

Set xact_abort
Microsoft? SQL Server? Whether to automatically roll back the current transaction.

(It is easy to understand. If any SQL error occurs in the middle, all SQL statements will be rolled back. procedure is particularly suitable for intermediate procedure calls. If the first procedure is OK, an error occurs in the called procedure. If set xact_abort = false, partial rollback with errors is performed, and other parts are submitted, of course, external procedure is also submitted .).

---In distributedTransYou must set the following parameters.(Xact_abort)

SyntaxSet xact_abort {on | off}

NoteWhen set xact_abort is on, if a running error occurs in a Transact-SQL statement, the entire transaction is terminated and rolled back. When it is off, only the wrong Transact-SQL statement is returned, and the transaction will continue to be processed. Compilation errors (such as syntax errors) are not affected by set xact_abort.

For most Ole databasesProgram(Including SQL Server), xact_abort must be set to on in implicit or explicit transaction data modification statements.

Set xact_abort is set during execution or running, rather than during analysis.

ExampleIn the following example, a foreign key violation error occurs in transactions that contain other Transact-SQL statements. Errors are generated in the first statement set, but other statements are successfully executed and the transaction is successful.
Submit. In the second statement set, set xact_abort to on. This causes a statement error to terminate the batch processing and roll back the transaction.

Create Table Table1 (A int primary key)
Create Table Table2 (A int references T1 ())
Go
Insert into Table1 values (1)
Insert into Table1 values (3)
Insert into Table1 values (4)
Insert into Table1 values (6)
Go
Set xact_abort off
Go
Begin tran
Insert into Table2 values (1)
Insert into Table2 values (2)/* foreign key Error */
Insert into Table2 values (3)
Commit tran
Go

Set xact_abort on
Go

Begin tran
Insert into Table2 values (4)
Insert into Table2 values (5)/* foreign key Error */
Insert into Table2 values (6)
Commit tran
Go

Save transaction
Set the save point in the transaction.

SyntaxSave Tran [saction] {savepoint_name | @ savepoint_variable}
ParametersSavepoint_name
Is the name assigned to the Save point. The Save point name must comply with the identifier rules, but only use the first 32 characters.
@ Savepoint_variable
Is the name of a user-defined variable that contains a valid vertex name.
The variable must be declared with the char, varchar, nchar, or nvarchar data type.Note
You can set the save point or mark in the transaction. Save the point definition. If a part of the transaction is canceled conditionally, the event can return a location. If you roll back the transaction to the Save point, you must continue to complete the transaction (if necessary, use more Transact-SQL statements and commit transaction statements, or the transaction must be completely canceled (by rolling back the transaction to its starting point. To cancel the entire transaction, use the rollback transaction transaction_name format. This will cancel all the statements and processes of the transaction.

Note:1:In distributed transactions that are explicitly started by begin distributed transaction or upgraded from local transactions, save transaction is not supported.

2:At the beginning of the transaction, the resources used in the transaction will be controlled until the transaction is completed (that is, locked ). When a part of the transaction is rolled back to the storage point, the resource will continue to be controlled until the transaction is completed (or all transactions are rolled back ).

Example:Begin transaction
Save transaction

Create Table demo1 (name varchar (20), SnO varchar (12 ))
Insert into demo1 values ('testname1', '20140901 ')
Rollback transaction
Create Table demo2 (name varchar (10), age INT)
Insert into demo2 (name, age) values ('OK', 1)
Commit transaction

Rollback transaction

Roll back an explicit or implicit transaction to the start point of the transaction or a point of storage in the transaction.
Syntax
Rollback [Tran [saction]
[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 comply with the identifier rules, but only use the first 32 characters of the transaction name. Nesting
During a transaction, transaction_name must be the name of the oldest begin transaction statement.
@ Tran_name_variable
Is the name of a user-defined variable with a valid transaction name. The variable must be declared with the char, varchar, nchar, or nvarchar data type.
Savepoint_name
Is the savepoint_name from the save transaction statement. Savepoint_name must comply with the identifier rules. Savepoint_name is used when conditional rollback only affects part of the transaction.
@ Savepoint_variable
Is the name of a user-defined variable that contains a valid vertex name. The variable must be declared with the char, varchar, nchar, or nvarchar data type.

NoteRollback transaction clears all data modifications made from the transaction start point or to a storage point. Rollback also releases resources controlled by transactions.
Rollback transaction without savepoint_name and transaction_name rolls back to the starting point of the transaction. During nested transactions, this statement rolls back all the inner transactions to the oldest begin transaction statement. In both cases, rollback transaction reduces the @ trancount system function to 0. Rollback
Transaction savepoint_name is not reduced @ trancount.

Note:
If the rollback transaction statement specifies savepoint_name, no locks are released.
In a distributed transaction that is explicitly started by begin distributed transaction or upgraded from a local transaction, rollback transaction cannot
Reference savepoint_name.In executionCommit transactionTransactions cannot be rolled back after a statement.

Duplicate save point names are allowed in the transaction. However, if the rollback transaction uses a duplicate save point name, only the latest save transaction with the name of the Save point is rolled back.

In the stored procedure, the rollback transaction statement without savepoint_name and transaction_name rolls back all statements to the farthest begintransaction. In the stored procedure, the rollback transaction statement makes the value of @ trancount when the trigger is complete different from the value of @ trancount when the stored procedure is called, and generates an information. This information does not affect subsequent processing.

If a rollback transaction is triggered in the trigger, all data changes made to the current transaction will be rolled back, including the changes made by the trigger.
The trigger continues to execute all other statements after the rollback statement is executed. If any statement in these statements modifies data, these modifications are not rolled back. Executing other statements does not trigger nested triggers. In batch processing, do not execute all statements after the statement that triggers the trigger. Every time you enter the trigger, @ trancount increases by 1, even in the automatic submission mode. (The system regards the trigger as a hidden nested transaction .)

During the stored procedure, the rollback transaction statement does not affect subsequent statements that call the procedure in batches;
The subsequent statements in the batch processing will be executed. In a trigger, the rollback transaction statement terminates the batch processing of statements containing the trigger;
Do not execute subsequent statements in the batch processing.

The rollback transaction statement does not generate information displayed to the user. If warnings are required in stored procedures or triggers, use raiserror or print statements. Raiserror is the first choice for identifying errors.

The effect of rollback on the cursor is defined by the following three rules:
When cursor_close_on_commit is set to on, rollback closes but does not release all opened cursors.
When cursor_close_on_commit is set to off, rollback does not affect any opened static or insensitive cursors and does not affect fully filled asynchronous static cursors. Will close but will not release any other type of open cursor.
For errors that cause batch processing to be terminated and internal rollback is generated, all the cursors declared in the batch containing the error statement will be released.
No matter the type of the cursor or the setting of cursor_close_on_commit, all the cursors will be released, including the cursor declared in the stored procedure called by this batch of errors. The cursor declared in the batch before the error batch processing is subject to rules 1 and 2. Deadlock errors belong to these types of errors. The rollback statement issued in the trigger also automatically generates such errors.

Permission
The rollback transaction permission is granted to any valid user by default.
Example:

Begin transaction
Save transaction
Insert into demo1 values ('testname2', '20140901 ')
Rollback transaction

-- Select * into demo2 from demo1

Create Table demo2 (name varchar (10), age INT)
Insert into demo2 (name, age) values ('OK', 1)
Rollback transaction

Use pubs
Go
Declare @ del_error int, @ ins_error int
-- Start a transaction.
Begin tran

-- Execute the delete statement.
Delete authors
Where au_id = '2017-56-7088'

-- Set a variable to the error value
-- The delete statement.
Select @ del_error = @ Error

-- Execute the insert statement.
Insert authors
Values ('2017-56-7008', 'bennet', 'abraham', '2017 658-9932 ',
'1970 Bateman st. ', 'berkele', 'CA', '123', 1)
-- Set a variable to the error value
-- The insert statement.
Select @ ins_error = @ Error

-- Test the error values.
If @ del_error = 0 and @ ins_error = 0
Begin
-- Success. Commit the transaction.
Print "The author information has been replaced"
Commit tran
End
Else
Begin
-- An error occurred. Indicate which operation (s) failed
-- And roll back the transaction.
If @ del_error <> 0
Print "An error occurred during execution of the delete
Statement ."

If @ ins_error <> 0
Print "An error occurred during execution of the insert
Statement ."

Rollback tran
End
Go

Commit transaction
Indicates the end of a successful implicit transaction or user-defined transaction. If @ trancount is 1, commit

Transaction makes all the data modifications executed since the beginning of the transaction a permanent part of the database and releases the connection.

And reduce @ trancount to 0. If @ trancount is greater than 1, commit

Transaction: decrease @ trancount by 1.

The commit transaction command is issued only when the logic of all data referenced by the firm is correct.
Commit work
Indicates the end of the transaction.
Syntax
Commit [work]

Note
This statement has the same functions as commit transaction, but commit transaction accepts user-defined transactions.

Name. The commit syntax that specifies or does not specify the optional keyword work is compatible with the SQL-92

Example:
Begin transaction
Insert into demo1 values ('testname3', '20140901 ')
Commit transaction

Implicit transactions
When the connection is operated in implicit transaction mode, SQL Server automatically starts a new transaction after committing or rolling back the current transaction. You do not need to describe the start of the transaction. You only need to submit or

Roll back each transaction. The implicit transaction mode generates a continuous transaction chain.

After the implicit transaction mode is set to open for a connection, when SQL Server executes any of the following statements for the first time, a transaction is automatically started:

	alter table	insert
	Create	open
	Delete	revoke
	drop	select
	fetch	truncate table
	grant	Update

The transaction remains valid until a commit or rollback statement is issued. After the first transaction is committed or rolled back, these statements will be executed at the next connection.

SQL Server automatically starts a new transaction. SQL Server will continuously generate a hidden transaction chain,

Until the implicit transaction mode is closed

Example:
Begin transaction
Save transaction

Insert into demo1 values ('testname1', '20140901 ')
Rollback transaction

Create Table demo2 (name varchar (10), age INT)
Insert into demo2 (name, age) values ('Lis ', 1)
Rollback transaction
-- SQL Server has implicitly created a trans when creating table demo2, knowing to submit or roll back

Nested transaction processing:

1: Trans nesting, which combines the internal trans into the external and forms a trans.

Begin Tran T1

---- In the first trans.
Insert into demo2 (name, age) values ('ok1', 1)

--- Second trans begin transaction T2
Insert into demo1 values ('testname5', '2013') Commit transaction T2

---- In the first trans.
Insert into demo2 (name, age) values ('ok12', 2)
Rollback transaction T1

Note:

Using one transaction name in a series of nested transactions to name multiple transactions has no effect on this transaction. The system registers only the first (most external) transaction name. Rollback

Any other name (except valid names) will produce an error.

In fact, no statement executed before rollback is rolled back when an error occurs. This statement will be rolled back only when the outer transaction is rolled back.

For example, an error is returned when an internal transaction is rolled back to SQL Server.

Begin Tran T1
Insert into demo2 (name, age) values ('okok ', 1)
--- Second Trans

-- Server: MSG 6401, level 16, state 1, line 6
--- Cannot roll back T2. no transaction or savepoint of that name was found.
Begin transaction T2
Insert into demo1 values ('test88', '20140901 ')
RollbackTransaction T2

---- In the first trans.
Insert into demo2 (name, age) values ('test best', 2)
Commit transaction T1

For example, SQL server does not report an error when internal transactions are committed.

Begin Tran T1
Insert into demo2 (name, age) values ('ok6', 1)
--- Second trans no error
Begin transaction T2
Insert into demo1 values ('testname1', '20140901 ')
CommitTransaction T2

---- In the first trans.
Insert into demo2 (name, age) values ('testok', 2)
Commit transaction T1

SQL ServerIsolation level:

1: Set the timeout Parameter

Set lock_timeout 5000

Locked for 5 seconds

Set lock_timeout 0

The product is unlocked immediately, and the default error is-1, which is infinite waiting.

2:

(SET transaction isolation level
{Read committed
| Read uncommitted
| Repeatable read | serializable })

Read committed

Specify to control the shared lock when reading data to avoid dirty reading, but the data can be changed before the end of the transaction, resulting in non-repeated reading or

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

Avoid dirty reads and do not modify existing data in other sessions in the transaction. Shared lock.

Read uncommitted

Execute dirty read or 0-level isolation lock, which means no shared lock is issued or the exclusive lock is not accepted. When this option is set, you can log

Uncommitted read or dirty read by Data Execution; you can change the value in the data before the transaction ends, or the row can appear in the dataset or from the data

The Set disappears. This option is used to set nolock for all tables in all statements in the transaction. These are four isolation levels.

Minimum Limit.

Repeatable read

Lock all data used in the query to prevent other users from updating data. However, 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, it should be enabled only when necessary

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 is completed. This

Is the maximum of the four isolation levels. Because the concurrency level is low, this option should be used only when necessary. This option

Is used to set holdlock for all tables in all select statements in the transaction.