Transaction isolation level

To simulate the concurrent environment, SQL Server opens two query windows (transaction 1 and transaction 2 respectively). concurrent users use transaction 1 and transaction 2 for short.

Test Table script:
Create Table [Customer] (
[Custid] [int] not null,
[Fname] [nvarchar] (20 ),
[Lname] [nvarchar] (20 ),
[Address] [nvarchar] (50 ),
[City] [nvarchar] (20 ),
[State] [nchar] (2) default ('CA '),
[Zip] [nchar] (5) not null,
[Phone] [nchar] (10)
)
Insert into customer values (1, 'gary ', 'mckee', '2017 main', 'palm springs', 'CA', 111 5551212)
Insert into customer values (2, 'Tom ', 'Smith', '2014 geogia ', 'fresno' 'jp', 609 5551212)
Insert into customer values (3, 'jams ', 'bond', 'st geogie 21', 'Washington ', 'ny', 20331,440 5551864)



SQL Server transaction isolation test:
1. Read uncommitted: reads uncommitted data from other transactions.
Open transaction 1 and run:
Begin tran
Select * from customer
Update customer set state = 'tn 'Where custid = 3
Go to transaction 2 and run:
SET transaction isolation level read uncommitted
Begin tran
Select * from customer
At this time, we can see that transaction 1 has been updated but has not yet been committed (State value of record 3 Tn)
2. Read committed: Only data submitted by other transactions can be read (with modifications)
Open transaction 1 and run:
Begin tran
Select * from customer
Update customer set state = 'tn 'Where custid = 3
Go to transaction 2 and run:
SET transaction isolation level read committed
Begin tran
Select * from customer
At this time, we will find that transaction 2 has been waiting and is not over.
3. Repeatable read: ensure that the transaction at the isolation level is consistent when reading data, data will not be modified or deleted by other transactions (because other transactions will be blocked if any modification or deletion operation is performed)
Start transaction 1, modify the transaction level to be repeated, and execute:
SET transaction isolation level Repeatable read
Begin tran
Select * from customer where State = 'CA'
Get one record. At this time, transaction 2 runs:
SET transaction isolation level Repeatable read
Begin tran
Update customer set state = 'jp 'where State = 'CA'
Commit
Transaction 2 remains waiting and does not end. Return transaction 1, run:
Select * from customer where State = 'CA' -- two reads with consistent results
Commit
After transaction 1 is successfully completed, transaction 2 is returned and transaction 2 is completed. The lock mechanism is used to block the modification of other transactions and maintain the read consistency during the transaction.
4. serializable: All Tables Used by transactions at the isolation level will be locked. Other transactions cannot be added, modified, or deleted.
Start transaction 1, modify the transaction level to the serialization level, and execute:
SET transaction isolation level serializable
Begin tran
Select * from customer
Start transaction 2 and execute:
Begin tran
Update customer set state = 'jp 'where State = 'CA'
Transaction 2 is always waiting
5. snapshot: Snapshot isolation
Note: To use Snapshot isolation, you must first set the current database to be able to perform Snapshot isolation.
For example:
Alter database netbardb
Set allow_snapshot_isolation on
Transactions running under Snapshot isolation will read data,
Then the data is modified by another transaction. The Snapshot transaction does not block the update operations performed by other transactions,
It ignores data changes and continues to read data from versionized rows.
Start transaction 1, modify the transaction level to the snapshot level, and execute:
SET transaction isolation level Snapshot
Begin tran
Select * from customer
Start transaction 2 and execute:
Begin tran
Update customer set state = 'tn 'Where custid = 3
One row was modified.
Return to transaction 1 and execute
Select * from customer
It is found that the status of the queried custid = 3 is still 'ny 'and not 'tn'



Common SQL Server transactions:
1. Update loss
The default isolation level of sqlserver is read committed. At this level, updates may be lost.
SQL Server
Start transaction 1 and run:
SET transaction isolation level read committed
Begin tran
Select * from customer -- three records are displayed.
Now switch to transaction 2. At this time, transaction 1 has not ended. Run in transaction 2:
SET transaction isolation level read committed
Begin tran
Select * from customer -- three records are displayed, which is the same as that in transaction 1.
Now assume that transaction 1 continues to run, modify the data and submit it:
Update customer set state = 'tk 'Where custid = 3
Commit
Back to transaction 2, transaction 2 modifies data based on the previously queried results:
Update customer set zip = 99999 where State = 'ny'
Commit
As a result, because transaction 1 has modified the where condition data of transaction 2, transaction 2 has not successfully modified the data (in fact, it should be an update failure caused by phantom read. However, if the number of records meeting the condition is large, the update of transaction 2 may update a smaller number of records than expected, or it may be counted as "missing" part of the update that should have been completed. I think that as long as I understand what actually happened, I don't have to look into words too much ). There may be other situations when updates are lost, such as transaction 2.
Update customer set state = 'ko 'Where custid = 3
After both transactions are completed, the result of transaction 2 is reflected in the database, but the update of transaction 1 is lost, and transaction 2 does not know that it overwrites the update of transaction 1.

2. Dirty read demonstration
The default isolation level of sqlserver is read committed)
Open transaction 1 and run:
Begin tran
Select * from customer
Update customer set state = 'tn 'Where custid = 3
Go to transaction 2 and run:
SET transaction isolation level read uncommitted
Begin tran
Select * from customer
At this time, we can see that transaction 1 has been updated but has not yet been committed (State value TN of record 3 ). If transaction 1 finds that the data processing is incorrect, it goes to transaction 1 and rolls back:
Rollback
At this time, if transaction 2 is further processed based on the read data, it will cause errors. The data it reads is not updated to the database and is "dirty ".

3. Repeatable reading
SQL Server has no dirty read problem by default, but there is a non-repeated read problem.
Open transaction 1 and run:
SET transaction isolation level read committed
Begin tran
Select * from customer where State = 'CA'
You can get one record, which is run in transaction 2:
SET transaction isolation level read committed
Begin tran
Update customer set state = 'jp 'where State = 'CA'
Commit
Transaction 2 inserts a record and submits it. Back to transaction 1, transaction 1 continues to run. At this time, it re-queries the same data and makes further modifications, but it is found that the data read has changed.
Select * from customer where State = 'CA'
-- Two reads are inconsistent, and subsequent data processing should be canceled. Otherwise incorrect
Update customer set City = 'garden 'where State = 'CA'
Commit
Failed to obtain records. That is to say, two identical queries in the same transaction obtain different results, resulting in read duplication.

4. Phantom reading
When the isolation level is set to Repeatable read, the problems in the preceding example can be solved. It is implemented internally by keeping the read lock during the transaction.
Start transaction 1, modify the transaction level to be repeated, and execute:
SET transaction isolation level Repeatable read
Begin tran
Select * from customer where State = 'CA'
Get one record as in the previous example. At this time, transaction 2 runs:
SET transaction isolation level Repeatable read
Begin tran
Update customer set state = 'jp 'where State = 'CA'
Commit
Transaction 2 remains waiting and does not end. Return transaction 1, run:
Select * from customer where State = 'CA' -- two reads with consistent results
Update customer set City = 'garden 'where State = 'CA'
Commit
After transaction 2 is successfully completed, transaction 1 is returned and transaction 1 is completed. The lock mechanism is used to block the modification of other transactions and maintain the read consistency during the transaction. However, if you insert data, the following problems still occur:
Start transaction 1, modify the transaction level to be repeated, and execute:
SET transaction isolation level Repeatable read
Begin tran
Select * from customer where State = 'CA'
Get one record. At this time, transaction 2 runs:
SET transaction isolation level Repeatable read
Begin tran
Insert into customer values (4, 'hellow', 'World', 'Paradise 001', 'garden ', 'CA', 00000,111 9995555)
Commit
Transaction 2 was committed immediately and ended normally. Return transaction 1, run:
Select * from customer where State = 'CA'
Two records are displayed. This phenomenon is called phantom reading.

Transaction isolation level