SQL Server transaction Overview (II)

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.

READ UNCOMMITTED

Execute dirty read or 0
Level-1 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, you can change the values in the data, or the rows can appear in
The dataset disappears from or 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.

For 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	C1
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 for 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. Low concurrency
The default isolation level, so 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 of the high concurrency level
Low, so 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

 BEGIN TRAN @@TRANCOUNT＋1
COMMIT TRAN @@TRANCOUNT－1
ROLLBACK TRAN