Database Transaction Level

1. readuncommitted (dirty read) (read data being committed) readuncommitted (also known as reading uncommitted content) allows tasks to read uncommitted data changes in the database. Test Script: Create a table CREATETABLE [dbo]. [testTb] ([ID] [int] NULL, [Name] [char] (20) NULL) 2. create transaction A: insert data begin

1. read uncommitted (dirty read) (read data being committed) read uncommitted (also known as read uncommitted content) allows tasks to read uncommitted data changes in the database. Test Script: create table [dbo]. [testTb] ([ID] [int] NULL, [Name] [char] (20) NULL) 2. create transaction A: insert data begin

1. read uncommitted (dirty read) (read data being committed)

Read uncommitted (also known as reading uncommitted content) allows tasks to read uncommitted data changes in the database.

Test script:

1. Create a table

Create table [dbo]. [testTb] (
[ID] [int] NULL,
[Name] [char] (20) NULL
)

2. Create transaction A: insert data

Begin tran
Insert into testTb values (5, 'E ')

Waitfor delay '00: 00: 10'

Commit;

3. Create transaction B: Read data

Set transaction isolation level read uncommitted
Begin tran

Select * from testTb

Commit;

4. Run transaction A to run transaction B immediately. At this time, transaction A has not been committed, and transaction B can read the data being committed by transaction.

2. read committed (submit read) (cannot read data being committed)

Level Read Committed (also known as reading submitted content) can prevent dirty reads. This level of query reads only submitted data changes. If the transaction needs to read data modified by another unfinished transaction, the transaction will wait until the first transaction is completed (committed or rolled back ).

Create table [dbo]. [testTb] (
[ID] [int] NULL,
[Name] [char] (20) NULL
)

2. Create transaction A: insert data

Begin tran
Insert into testTb values (5, 'E ')

Waitfor delay '00: 00: 10'

Commit;

3. Create transaction B: Read data

Set transaction isolation level read committed
Begin tran

Select * from testTb
Commit

4. Run transaction A to run transaction B immediately. At this time, transaction A has not been committed, and transaction B must wait until transaction A completes before it can read data.

3. repeatable read (repeatable read) (READ data cannot be modified)

The specified statement cannot read the rows modified but not committed by other transactions. It is specified that no other transaction can modify the data read by the current transaction before the current transaction is completed.

Create table [dbo]. [testTb] (
[ID] [int] NULL,
[Name] [char] (20) NULL
)

2. Create transaction A: update data

Begin tran
Update testTb set Name = 'cv 'where ID = '8'

Waitfor delay '00: 00: 10'

Commit;

3. Create transaction B: Read data

Set transaction isolation level Repeatable read

Begin tran

Select * from testTb
Commit

4. Run transaction A to run transaction B immediately. At this time, transaction A has not been committed, and transaction B must wait until transaction A completes before it can read data.

4. SERIALIZABLE (sequential read) (Reading data cannot be inserted or modified)

• The statement cannot read data modified by other transactions but not committed.
• No other transaction can modify the data read by the current transaction before the current transaction is completed.
• Before the current transaction is completed, other transactions cannot use the key value read by any statement in the current transaction to Insert a new row.

Create table [dbo]. [testTb] (
[ID] [int] NULL,
[Name] [char] (20) NULL
)

2. Create transaction A: update data and insert data

Begin tran
Insert into testTb values (9, 'D ')

Update testTb set Name = 'cv 'where ID = '8'

Waitfor delay '00: 00: 0'

Commit;

3. Create transaction B: Read data

Set transaction isolation level serializable
Begin tran

Select * from testTb

Commit;

4. Run transaction A to run transaction B immediately. At this time, transaction A has not been committed, and transaction B must wait until transaction A completes before it can read data.