[Reprinted] Introduction to Microsoft SQL Server transaction isolation-level instances

Source: http://untitled.spaces.live.com/Blog/cns! 86b82838704e0d5a! 819. Entry

 

Transaction isolation level is a basic concept in a database. Why is there a transaction isolation level? What transaction isolation levels are implemented on SQL server? The premise of transaction isolation level is a multi-user, multi-process, multi-thread concurrent system. To ensure data consistency and integrity, we introduce the concept of transaction isolation level, this problem does not exist for a single-user or single-thread application.

First, let's take a look at the problems in the high-concurrency system. In order to facilitate understanding, let's take Michael's account and deposit at China Merchants Bank as an example.

I. preparations:

1. Create a bank account table (just to illustrate the problem, without considering the table design paradigm)
Create Table DBO. bankaccount
(
Bankaccountid char (16) not null, -- bank account
Username nvarchar (32) not null, -- User
Balance decimal (19, 2) not null, -- balance
Lastupdate smalldatetime not null
)
Go

2. Prepare data
Insert into DBO. bankaccount
Values ('000000', n' Zhang san', 9555500100071120, getdate () -- account of Beijing Branch
Insert into DBO. bankaccount
Values ('000000', n' Zhang san', 9555507551227787, getdate () -- account of Shenzhen Branch
Go

3. View data
Select * From DBO. bankaccount

II. Application scenarios

Assume that John has two accounts in China Merchants Bank, one is China Merchants Bank Beijing Branch and the other is China Merchants Bank Shenzhen Branch. The two accounts have a balance of 10,000 and 20,000 respectively.

1. john made a transaction online, with a transaction amount of 100. John, the buyer, made a bank transfer of 100 to John's account of the Beijing Branch (see the left figure below ), the counter operator saves 100 (transaction 1) to Michael Jacob and then runs some operation logs (assuming it takes 10 seconds, waitfor delay '00: 00: 10 ') at this time, John checked the account balance (transaction 2) at the ATM and found that it was already 10100. So he went back to prepare for delivery, but transaction 1 timed out when writing operation logs, this is the transaction rollback, the deposit transaction is canceled, and the money is returned to John. In this way, the account balance found by John is actually data not submitted by transaction 1, zhang San mistakenly believes that he has received the transaction money.
 
A transaction reads data that has not been committed by another transaction, which is called dirty read.
Solution: Adjust the transaction isolation level to read committed, that is, change set Tran isolation level read uncommitted in the right to set Tran isolation level read committed. When we repeat the above action, we will find that transaction 2 will wait until the execution of the transaction is complete and then return the result, because at this time the transaction has rolled back its own changes, therefore, transaction 2 can return the correct result.

2. Michael Jacob queries the balance of an account twice. During the two queries, Mr. Smith completed the bank transfer, resulting in two different query results.
A transaction reads the same record successively, but the data read twice is different, which is called non-repeated read.
Solution: Adjust the transaction isolation level to Repeatable read. Use set Tran isolation level Repeatable read in. When we repeat the above action, we will find that transaction 2 will wait until the execution of the transaction is complete and then return the result.

3. zhang Sanhe's wife queries the total balance of all accounts of Zhang San's China Merchants Bank twice. During this period, Zhang Sanhe successfully opened an account at the China Merchants Bank branch in Guangzhou and saved the account to 5000, as a result, the total balance of the Two Queries is different. During this period, the balance of the two original accounts is not changed.

A transaction reads a range of records successively, but the number of records read twice is different, which is called phantom read.
Solution: Adjust the transaction isolation level to serializable. Use set Tran isolation level serializable in. When we repeat the above action, we will find that transaction 2 will wait until the execution of the transaction is complete and then return the result.

 

Iii. Summary

The transaction isolation level is controlled by the locking mechanism of the database. Different transaction isolation levels need to be applied in different application scenarios. The default transaction isolation level of SQL Server is read committed, the default isolation level can already meet the needs of most of our applications.