SQL Server transaction Overview

Transaction attributes
Transactions have ACID properties
That is, atomic atomicity, consistent consistency, isolated isolation, durable permanent

Atomicity

That is, the transaction should be taken as a unit of work, and the transaction is processed completely. All the work is either saved in the database or completely
Rollback, all are not retained

Consistency
After the transaction is completed or canceled, it should be in the same state.

Isolation

When multiple transactions are performed at the same time, they should not interfere with each other. When a transaction is prevented from processing data that other transactions also need to modify,
Unreasonable access and incomplete data reading

Permanent
After the transaction is committed, the work is permanently saved.

Problems arising from concurrent Transaction Processing

Update loss

When two or more transactions select the same row and update the row based on the originally selected value, the update will be lost,
Every transaction does not know the existence of other transactions. The last update will overwrite the updates made by other firms, which will lead to data loss.

Dirty read
When the second transaction selects another row being updated, unconfirmed correlation issues will occur.
The data being read by the second transaction has not been confirmed and may be changed by the transaction that updates this row.

Non-repeated read

When the second transaction accesses the same row multiple times and reads different data each time, an inconsistent analysis problem occurs.
The inconsistent analysis is similar to the unconfirmed correlation because other transactions are also changing the data being read by the second transaction.
However, in an inconsistent analysis, the data read by the second transaction is committed by a transaction that has been changed. Furthermore, the inconsistent analysis involves reading the same row multiple times (twice or more) and the information is changed by other transactions each time. Therefore, the row is read non-repeatedly.

Phantom read

A phantom reading problem occurs when a row is inserted or deleted and the row belongs to the row being read by a transaction.
The row range for the first read of the transaction shows that one row no longer exists in the second read or subsequent read because the row has been deleted by other transactions. Similarly, due to the insert operation of other transactions, the second or subsequent read of the transaction shows that a row does not exist in the original read.

Three transaction processing types

Automatic Transaction Processing

By default, each T-SQL command is a transaction that is automatically started and committed by the system.

Implicit transactions

When a large number of DDL and DML commands are executed, the system starts automatically until the user explicitly commits them. You can use set implicit_transactions to switch between implicit transactions.
Set the implicit transaction mode for the connection. When set to on, set implicit_transactions sets the connection to the implicit transaction mode. When it is set to off, the connection is returned to the automatic commit transaction mode.

User-Defined transactions

User-controlled start and end commands of transactions include: Begin Tran commit Tran rollback Tran command

Distributed transactions
Transactions that span multiple servers are called distributed transactions. SQL Server can be operated by DTC Microsoft Distributed Transaction Coordinator.
To support distributed transactions. You can use the begin Distributed Transaction command to start a distributed transaction.

 

Iv. isolation level of Transaction Processing

Use the SET transaction isolation level to control the default transaction lock behavior of all statements sent by the connection.

From low to high is

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, uncommitted or dirty reads can be performed on the data. Before the transaction ends, the values in the data can be changed, and the rows can also appear in the dataset or disappear from the dataset. This option is used to set nolock for all tables in all statements in the transaction. This is the minimum limit among the four isolation levels.

Example

Set Table1 (A, B, C)
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3

Create two connections
Execute 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' -- wait 10 seconds
Rollback tran
Select * From Table1

Execute 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 'no-repetition read'
Select * From Table1
End

Result of the second connection

Dirty read
A B C
A1 B1 C
A2 B2 C
A3 B3 C

'No repeated read'
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3

 

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 read or phantom data. This option is the default value of SQL Server.

Execute 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' -- wait 10 seconds
Print 'no-repetition read'
Select * From Table1
Rollback tran

Execute the following statement in the second connection
SET transaction isolation level read committed

Update Table1 set C = 'C'

Result of the first connection

Initial
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3

No repeated read
A B C
A1 B1 C
A2 B2 C
A3 B3 C

 

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 dataset and the phantom rows are included in subsequent reads of the current transaction. Because concurrency is lower than the default isolation level, this option should be used only when necessary.

Execute 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' -- wait 10 seconds
Print 'phantom read'
Select * From Table1
Rollback tran

Execute the following statement in the second connection
SET transaction isolation level Repeatable read
Insert Table1 select 'a4 ', 'b4', 'c4'

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 C3
A4 B4 C4

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 most restrictive level among 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.

Execute the following statement in the first connection:
SET transaction isolation level serializable
Begin tran
Print 'initial'
Select * From Table1
Waitfor delay '00: 00: 10' -- wait 10 seconds
Print 'unchanged'
Select * From Table1
Rollback tran

Execute the following statement in the second connection
SET transaction isolation level serializable
Insert Table1 select 'a4 ', 'b4', 'c4'

Result of the first connection

Initial
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3

No changes
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3

Five. The nested Syntax of transaction processing and its impact on @ trancount

Tran in TRAN @ trancount + 1
commit Tran TRANCOUNT-1
rollback tr