1.1. Properties of transactions
The transaction has an ACID attribute
That is, Atomic Atomic, Consistent Consistency, ISOLATED Isolation, Durable Permanent
Atomicity
It is the transaction should be used as a work unit, the transaction is completed, all work is either saved in the database, or completely rolled, all do not retain
consistency
After the transaction is completed or revoked, it should be in a consistent state.
Isolation
Multiple transactions are carried out simultaneously, and they should not interfere with each other. When a transaction should be prevented from being handled by other transactions, unreasonable access and incomplete reading data.
Permanent
After the transaction is submitted, the work done is preserved.
1.2. Problems generated by transaction concurrency processing
Lost update
When two or more transactions choose the same row, then update this line based on the original selected value, there will be lost updates, and each transaction does not know the existence of other transactions. The last update will rewrite the update made by other transactions, which will result in data loss.
Dirty
An undefined correlation problem occurs when the second transaction selects other transactions being updated.
The data that the second transaction is reading has not been confirmed and may be changed by the transaction of the update this line.
Not repeatable
An inconsistent analysis problem occurs when the second transaction is accessed multiple times and each time you read different data.
Inconsistent analysis is similar to unrecognized correlation, because other transactions are also changing the data being read by the second transaction.
However, in inconsistent analysis, the data read by the second transaction is submitted by a transaction that has been changed. Moreover, inconsistent analysis involves multiple (twice or more) reading the same row, and each information is changed by other transaction; thus the line is not read.
Phantom read
Insert or delete actions are performed on a row, and the row belongs to the range of a line being read in a transaction, an phantom read problem occurs.
The first line of transactions read is displayed in one of the lines that are not repurchable in the second reading or subsequent reading because the row has been deleted by other transactions. Similarly, due to the insertion operation of other transactions, the second or subsequent read display of the transaction has no existing readings.
1.3. Transaction Type
Automatically handle transactions
The system defaults each T-SQL command is transaction processing by the system automatically start and submit
Implicit business
When there is a lot of DDL and DML commands, it will automatically start and keep it to the user to clearly submit, switch implicit transactions can be used to set the implicit transaction mode for the connection. When set to ON, Set Implicit_Transactions will connect to the connection. For hidden transaction mode. When set to OFF, return the connection to automatic submission transaction mode
User definition transaction
The start and end command of the transaction is controlled by the user: Begin TRAN commit trrough rollback tran command
Distributed transaction
Transactions across multiple servers are called distributed transactions, and SQL Server can support processing distributed transactions by DTC Microsoft Distributed Transaction Coordinator, you can use the Begin Distributed Transaction command to launch a distributed transaction process.
1.4. Isolation level of transaction processing
Use Set Transaction Isolation Level to control the default transaction lock behavior of all statements sent by the connection
From low to high, it is:
Read uncommitted
Execute dirty or 0-level isolation lock, which means that it does not make a shared lock, nor does it accept the lock. When this option is set, the execution of the data may not be submitted to read or dirty; the value within the data can be changed before the end of the transaction, and the line can also appear in the data set or disappear from the data set. The role of this option is the same as all tables in all statements in transactions. This is the minimum level in the four isolation levels.
Example
Set Table1 (A, B, C)
A b CA1 B1 C1
A2 B2 C2
A3 B3 C3
Newly built two connections
Perform the following statement in the first connection
SELECT * from Table1
Begin TRAN
Update Table1 Set C = 'C'
SELECT * from Table1
Waitfor delay '00: 00: 10' - waiting for 10 seconds
Rollback TRAN
SELECT * from Table1
Perform the following statement in the second connection
Set Transaction Isolation Level Read Uncommitted
Print 'Dirty Read'
SELECT * from Table1
IF @@ rowcount> 0
Begin
WAITFOR DELAY '00: 00: 10 '
Print 'does not repeat read'
SELECT * from Table1
EN
The result of the second connection
Dirty
A b c
A1 B1 C
A2 B2 C
A3 B3 C
'Don't repeat it'
A b c
A1 B1 C1
A2 B2 C2
A3 B3 C3
Read committed
Specifies that the shared lock is controlled when reading data is to avoid dirty reading, but the data can be changed before the end of the transaction, resulting in an unusable read or phantom data. This option is the default value of SQL Server.
Perform the following statement in the first connection
Set Transaction Isolation Level Read Committed
Begin TRAN
Print 'initial'
SELECT * from Table1
Waitfor delay '00: 00: 10' - waiting for 10 seconds
Print 'does not repeat read'
SELECT * from Table1
Rollback TRAN
Perform the following statement in the second connection
Set Transaction Isolation Level Read Committed
Update Table1 Set C = 'C'
The result of the first connection
initial
A b c
A1 B1 C1
A2 B2 C2
A3 B3 C3
Do not repeat
A b c
A1 B1 C
A2 B2 C
A3 B3 C
REPEATABLE READ
Lock all the data used in the query to prevent other users from updating data, but other users can insert new phantom rows into the dataset, and the phantom rows include in subsequent reading in the current transaction. Because it is concurrent than the default isolation level, this option should only be used if necessary.
Perform the following statement in the first connection
Set Transaction Isolation Level RepeAtable Read
Begin TRAN
Print 'initial'
SELECT * from Table1
Waitfor delay '00: 00: 10' - waiting for 10 seconds
Print 'phantom read'
SELECT * from Table1
Rollback TRAN
Perform the following statement in the second connection
Set Transaction Isolation Level RepeAtable Read
INSERT TABLE1 SELECT 'A4', 'B4', 'C4'
The result of the first connection
initial
A b C
A1 B1 C1
A2 B2 C2
A3 B3 C3
Phantom read
A b C
A1 B1 C1
A2 B2 C2
A3 B3 C3A4 B4 C4
Serializable
Place a range lock on the data set to prevent other users from updating the dataset before the transaction is completed or insert the row into the data set. This is the maximum level in the four isolation levels. Because concurrent levels are lower, this option should only be used if necessary. The role of this option is set to Holdlock on all tables in all SELECT statements in the transaction.
Perform the following statement in the first connection
Set Transaction Isolation Level Serializable
Begin TRAN
Print 'initial'
SELECT * from Table1
Waitfor delay '00: 00: 10' - waiting for 10 seconds
Print 'has no change'
SELECT * from Table1
Rollback TRAN
Perform the following statement in the second connection
Set Transaction Isolation Level Serializable
INSERT TABLE1 SELECT 'A4', 'B4', 'C4'
The result of the first connection
initial
A b C
A1 B1 C1
A2 B2 C2
A3 B3 C3
no change
A b C
A1 B1 C1
A2 B2 C2
A3 B3 C3
1.5. Effects of transaction processing nested syntax and @@ TRANCOUNT
L Ø begin TRAN @@ TRANCOUNT 1
l Ø commit trrough @@TRANCOUNT-1
L Ø rollback tran makes @@ TRANCOUNT return 0
l Ø Save TRAN does not affect @@ TRANCOUNT
Example
SELECT 'transaction before', @@TRANCOUNT - value is 0
Begin TRAN
SELECT 'The first transaction', @@TRANCOUNT - value is 1
SELECT * from Table1
Begin TRAN
SELECT 'second transaction', @@TRANCOUNT - value 2
Delete Table1
Commit TRAN
SELECT 'submits the second transaction', @@TRANCOUNT - value is 1
Rollback TRAN
SELECT 'Rolling the first transaction', @@TRANCOUNT - value is 0
Example
SELECT 'transaction before', @@TRANCOUNT - value is 0
Begin TRAN
SELECT 'The first transaction', @@TRANCOUNT - value is 1
SELECT * from Table1
Save TRAN T1
SELECT 'After saving the first transaction', @@ TRANCOUNT - value is 1
Begin TRAN
SELECT 'second transaction', @@TRANCOUNT - value 2
Delete Table1
Rollback TRAN T1
SELECT 'Roll back to save points T1', @@TRANCOUNT - Note The value here is 2
IF @@ TRANCOUNT> 0
Rollback TRAN
SELECT 'processing end', @@ TRANCOUNT - 0SET XACT_ABORT
When the control statement generates an error, whether it is automatically returned to the current transaction.
such as
SET XACT_ABORT ON
Begin TRAN
SELECT * FROM a non-existing table
Roll Backtran
Print 'is handled at' - the execution result does not come to this step
Go
SELECT @@TRANCOUNT - value 1 generates isolated transaction
1.6. Transaction debug statement
DBCC OpenTran
If there is an oldest active transaction and the oldest distribution and non-distributed replication transactions in the specified database, the information is displayed.
Example
The next example gains transaction information for the current database and the PUBS database.
- Display Transaction Information Only for The Current Database. - DISPLY THE CURRENT Database.
DBCC OpenTran
Go
- Display Transaction Information for the Pubs Database. - DISPLAY.
DBCC OpenTran ('Pubs')
Go
1.7. Examples of business nested
1) If the inner transaction is wrong, cancel all transactions
Begin TRAN T1
Update Tablename Set ColName = '37775' Where ID = '140'
Begin TRAN T2
Update Tablename Set ColName = '37775' where id = '140' - Id does not exist
IF (@@ rowcount = 0)
Being
Rollbakc TRAN
End
Else
Begin
Commit TRAN T2
Commit TRAN T1
End
2) If there is an error in the transaction processing, it will roll back
Begin TRAN
Update Tablename Set ColName = '37775' Where id = 170
IF (@@ rowcount = 0)
Rollback TRAN
Update Tablename Set ColName = '37775' Where ID = 870 - This ID does not exist
IF (@@ rowcount = 0)
Rollback TRAN
3) Problems about loop processing transactions require loop processing, if one condition is not satisfied, cancel all loops
Declare @ID INT
Set @ id = 1
While (@ID <280)
- Note the actual ID of the actual ID is 260, so this loop is executed to the @ ID = 261 Update statement does not work.
Begin
Begin TRAN
Update Tablename Set ColName = '37775' Where id = @ id
IF (@@ rowcount = 0)
Rollback TRAN
Set @ id = @ ID 1
End
