Instance rendition of four types of transactional concurrency problems in SQL Server

This article will use an instance to reproduce four kinds of concurrency problems in database access, hoping to allow beginners to have a further understanding of the parallelism of the transaction.
First, let's take a look at the two concepts of parallel issues and transaction isolation levels.
In the database, it is assumed that if there are no locks and multiple users access a database at the same time, problems may occur when their transactions use the same data at the same time. Concurrency issues include:

• Missing or overwriting updates.
• Unconfirmed correlation (dirty read).
• Inconsistent analysis (non-repeatable read).
• Phantom read.

Let's take a little time to explain these four types of questions:
1. Missing updates
A missing update issue occurs when two or more transactions select the same row and then update the row based on the value originally selected. Every transaction is unaware of the existence of other transactions. The last update overrides updates made by other transactions, which results in data loss.


2. Unconfirmed correlation (Dirty Read)
An unacknowledged dependency problem occurs when the second transaction chooses the row that the other transaction is updating. The data that the second transaction is reading is not yet confirmed and may be changed by the transaction that updated the row.

3. Inconsistent analysis (non-repeatable reading)
An inconsistent parsing problem occurs when the second transaction accesses the same row multiple times and each time a different data is read. Inconsistent parsing is similar to an unconfirmed dependency because other transactions are also changing the data that the second transaction is reading. However, in an inconsistent analysis, the data read by the second transaction is committed by a transaction that has changed. Also, inconsistent analysis involves reading the same line multiple times (two or more), and each time the information is changed by another transaction, and the row is read non-repeatable.

4. Phantom Reading
A phantom read problem occurs when an INSERT or delete operation is performed on a row that belongs to the range of rows that a transaction is reading. The row range for the first read of a transaction shows that one row has ceased to exist in the second or subsequent read because the row has been deleted by another transaction. Similarly, because of the insert operation of another transaction, the second or subsequent read of the transaction shows a row that does not exist in the original read.

All four of these problems can cause inconsistencies in the data. The level at which we prepare transactions to accept inconsistent data is called the isolation level. The isolation level is the degree to which a transaction must be isolated from other transactions. A lower isolation level can increase concurrency, but at the cost of reducing data correctness. Conversely, a higher isolation level ensures that the data is correct, but can negatively affect concurrency. The isolation level required by the application determines the locking behavior that SQL Server uses.

　

The following table (1) lists four types of isolation

1 --sql- The following four isolation levels are defined, and SQL Server supports all of these isolation levels:23 READ UNCOMMITTED---  READ UNCOMMITTED (the lowest level of transaction isolation, guaranteed not to read physically corrupted data).  4 Read COMMITTED--- commit reads (SQL Server default level).  5 REPEATABLE READ--- repeatable read.  6 SERIALIZABLE---Serializable (the highest level of transaction isolation, complete isolation between transactions).

Levels allow different types of behavior.

Isolation level	Dirty Read	Non-REPEATABLE READ	Phantom image
Non-committed read	Is	Is	Is
Submit Read	Whether	Is	Is
REPEATABLE READ	Whether	Whether	Is
Serializable read	Whether	Whether	Whether

In order to reproduce the above four types of problems, we must do some preparatory work:
1. Use the following script to create a test table.

1--Create a test with a database test2 CREATE DATABASE Test3 GO4--Create a test table5 Use test6 GO7 CREATE Table Account table8 (9Account Number CHAR (4),Ten Balance INT One ) A GO - INSERT Account Table -SELECT'A', - the UNION All -SELECT'B', $

2, please open two Query Analyzer program, is intended to open two connections, simulate two parallel transactions. The following abbreviations are connected one and connected two.
The test begins formally:
(1) Reproduction of lost updates

Let's look at the following example:

1--execute the following statement in the first connection2 3 BEGIN TRAN4UPDATE Account table SET balance =101WHERE Account Number ='A' 5WAITFOR DELAY'00:00:10'--wait 10 seconds6 COMMIT TRAN7 8--then immediately use the second connection to execute the following statement9 Ten BEGIN TRAN OneUPDATE Account table SET balance =102WHERE Account Number ='A'  ACOMMIT TRAN

We will find that the transaction in the second connection cannot be executed immediately, and must wait for the first connected transaction to complete before it can be executed.
This avoids the problem of "missing updates", otherwise there is a "lost update" issue.

The problem of missing updates is one of the most serious problems, as the table shows that no matter what level of transaction isolation is used, the problem of missing updates is not allowed, so such problems cannot be reproduced.

(2) Reproduction of unconfirmed correlations (dirty reads)
As seen in table 1, dirty reads are allowed when the isolation level of a transaction is uncommitted (read UNCOMMITTED).
--Executes the following statement in the first connection

1 BEGIN TRAN2UPDATE Account table SET balance =103WHERE Account Number ='A' 3WAITFOR DELAY'00:00:10'--wait 10 seconds4UPDATE Account table SET balance =104WHERE Account Number ='A'5 COMMIT TRAN6 7--then immediately use the second connection to execute the following statement8 SET TRANSACTION Isolation level READ UNCOMMITTED9 BEGIN TRANTenSELECT balance from Account table WHERE account Number ='A'  OneCOMMIT TRAN

We will find that the second connected statement returns immediately, and the result is 103, but unfortunately it reads dirty data.
"Dirty reads" can be avoided if we set the transaction isolation level of the second connection to read COMMITTED, repeatable read, or serializable.

(3) Reproduction of inconsistent analysis (non-repetitive reading)
As seen in table 1, this problem can be present when the isolation level of a transaction is uncommitted (read UNCOMMITTED) or Read committed.
Please test this example (assuming that the balance of account A is 100):
--Executes the following statement in the first connection

1 SET TRANSACTION Isolation level READ COMMITTED2--or SET TRANSACTION isolation level READ UNCOMMITTED3 BEGIN TRAN4SELECT balance from Account table WHERE account Number ='A'5WAITFOR DELAY'00:00:10'--wait 10 seconds6SELECT balance from Account table WHERE account Number ='A'7 COMMIT TRAN8 9--then immediately use the second connection to execute the following statementTen BEGIN TRAN OneUPDATE Account table SET balance =TenWHERE Account Number ='A' ACOMMIT TRAN

We will find that the balance of two return account A in the first connection is not the same, the first time is 100, the second return is 10, this is the typical "non-repeatable read" problem.
If you set the transaction isolation level of connection one to repeatable READ or serializable, you can prevent such problems.


(3) Reproduction of inconsistent analysis (non-repetitive reading)
As seen in table 1, this problem can be present when the isolation level of a transaction is uncommitted (read UNCOMMITTED) or Read committed.
Let's look at the following example (assuming the balance of account A is 100):
--Executes the following statement in the first connection

1 SET TRANSACTION Isolation level READ COMMITTED2--or SET TRANSACTION isolation level READ UNCOMMITTED3 BEGIN TRAN4SELECT balance from Account table WHERE account Number ='A'5WAITFOR DELAY'00:00:10'--wait 10 seconds6SELECT balance from Account table WHERE account Number ='A'7 COMMIT TRAN8 9--then immediately use the second connection to execute the following statementTen BEGIN TRAN OneUPDATE Account table SET balance =TenWHERE Account Number ='A' ACOMMIT TRAN

We will find that the balance of two return account A in the first connection is not the same, the first time is 100, the second return is 10, this is the typical "non-repeatable read" problem.
If you set the transaction isolation level of connection one to repeatable READ or serializable, you can prevent such problems.



(4) Reproduction of Phantom reading
As indicated in table 1, this problem can be reproduced when the isolation level of the transaction is read UNCOMMITTED or read committed or repeatable read.
Let's look at the following example (assuming the balance of account A is 100):
--Executes the following statement in the first connection

1 SET TRANSACTION Isolation level READ COMMITTED2--or SET TRANSACTION isolation level READ UNCOMMITTED3--or SET TRANSACTION isolation level repeatable READ4 BEGIN TRAN5SELECT *From Account table6WAITFOR DELAY'00:00:10'--wait 10 seconds7SELECT *From Account table8 COMMIT TRAN9 Ten--then immediately use the second connection to execute the following statement One BEGIN TRAN AINSERT into account table VALUES ('C',' -') -COMMIT TRAN

We will find that the first connection in the same transaction, the same query statement returned two times the result set is not the same, the second return of the result set more than a number of account C account, this is a typical "phantom reading" problem. This type of problem can only be prevented if the transaction isolation level of connection one is set to serializable.
Summary: In order to avoid the problems caused by transaction concurrency, high transaction isolation level can be adopted, but it will reduce the parallelism of transaction, in turn, if the pursuit of high parallelism and lower transaction isolation level, it is easy to bring concurrency problems. Therefore, SQL Server has a relatively low "READ COMMITTED" with the default isolation level. In the actual application, the level of isolation depending on the specific situation, you can also adopt an explicit "lock" method to control the transaction isolation level, the specific method please pay attention to the author's related articles.

Transferred from "Zhu Er" (2006.3 reprint please specify the author)

Instance rendition of four types of transactional concurrency problems in SQL Server (GO)