Examples of four types of transaction concurrency problems in SQL Server)

Zhu 'er (2006.3 reposted by the author)

This article will use instances to reproduce four types of concurrency issues in database access, hoping that beginners can have a better understanding of transaction concurrency.
First, let's take a look at the parallel problem and transaction isolation level.
In the database,Assuming that the transaction is not locked and multiple users access the same database at the same time, the problem may occur when their transactions use the same data at the same time.. Concurrency problems include:

• Update Missing or overwritten.
• Unconfirmed correlation (dirty read ).
• Inconsistent analysis (non-repeated read ).
• Phantom reading.

Let's take a moment to explain these four types of problems:
1. Update loss
When two or more transactions select the same row and update the row based on the originally selected value, the update will be lost. Every transaction does not know the existence of other transactions. The last update will overwrite the updates made by other firms, which will lead to data loss.
2. Unconfirmed correlation (dirty read)
When the second transaction selects another row being updated, unconfirmed correlation issues will occur. The data being read by the second transaction has not been confirmed and may be changed by the transaction that updates this row.
3. Inconsistent analysis (non-repeated read)
When the second transaction accesses the same row multiple times and reads different data each time, an inconsistent analysis problem occurs. The inconsistent analysis is similar to the unconfirmed correlation because other transactions are also changing the data being read by the second transaction. However, in an inconsistent analysis, the data read by the second transaction is committed by a transaction that has been changed. Furthermore, the inconsistent analysis involves reading the same row multiple times (twice or more) and the information is changed by other transactions each time. Therefore, the row is read non-repeatedly.
4. Phantom reading
A phantom reading problem occurs when a row is inserted or deleted and the row belongs to the row being read by a transaction. The row range for the first read of the transaction shows that one row no longer exists in the second read or subsequent read because the row has been deleted by other transactions. Similarly, due to the insert operation of other transactions, the second or subsequent read of the transaction shows that a row does not exist in the original read.
The above four problems will cause data inconsistency. We setThe level at which the transaction is prepared 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. Low isolation levels can increase concurrency, but the cost is to reduce data correctness. On the contrary, high isolation levels can ensure data correctness, but may have a negative impact on concurrency. The isolation level required by the application determines the locking behavior used by SQL Server.
SQL-92 defines four isolation levels that SQL Server supports:

• Read uncommitted --- uncommitted read (the lowest level of transaction isolation, only ensure that the data is not READ physically corrupt ).
• Read committed --- submit READ (SQL Server default level ).
• Repeatable read --- repeatable read.
• SERIALIZABLE --- SERIALIZABLE read (the highest level of transaction isolation, full isolation between transactions ).

The following table (1) lists four isolation levels that allow different types of behavior.

Isolation level	Dirty read	Cannot be read repeatedly	Phantom
Uncommitted read	Yes	Yes	Yes
Submit read	No	Yes	Yes
Repeatable read	No	No	Yes
Serializable read	No	No	No

To reproduce the above four types of problems, we must make some preparations:
1. Use the following script to create a test table.
-- Create a test Database
Create database test
GO
-- Create a test table
USE test
GO
Create table account TABLE
(
Account CHAR (4 ),
Balance INT
)
GO
INSERT account table
SELECT 'A', 100
UNION ALL
SELECT 'B', 200
2. enable two query analyzer programs to start two connections and simulate two parallel transactions. Connect 1 and connect 2.
Test officially started:
(1) Reproduction of lost updates
Let's take a look at the following example:
-- Execute the following statement in the first connection
BEGIN TRAN
UPDATE account table SET balance = 101 WHERE account = 'A'
Waitfor delay '00: 00: 10' -- wait 10 seconds
COMMIT TRAN
-- Run the following statement using the second connection immediately.
BEGIN TRAN
UPDATE account table SET balance = 102 WHERE account = 'A'
COMMIT TRAN
We will find that the transaction in the second connection cannot be executed immediately, and it can only be executed after the transaction in the first connection is completed.
In this way, the "Update loss" problem is avoided. Otherwise, the "Update loss" problem will occur.
The loss of updates is the most serious one. Table 1 shows that no matter which transaction isolation level is used, the loss of updates is not allowed. Therefore, such problems cannot be reproduced.
(2) Reproduction of unconfirmed correlations (dirty reads)
Table 1 shows that dirty READ is allowed when the isolation level of a transaction is read uncommitted.
-- Execute the following statement in the first connection
BEGIN TRAN
UPDATE account table SET balance = 103 WHERE account = 'A'
Waitfor delay '00: 00: 10' -- wait 10 seconds
UPDATE account table SET balance = 104 WHERE account = 'A'
COMMIT TRAN
-- Run the following statement using the second connection immediately.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
SELECT balance FROM account table WHERE account = 'A'
COMMIT TRAN
We will find that the second join statement will return immediately and the result is 103, but unfortunately it reads dirty data.
If we set the transaction isolation level of the second connection To read committed, repeatable read, or SERIALIZABLE, we can avoid "Dirty READ.
(3) Reproduction of inconsistent analysis (non-repeated read)
As shown in table 1, when the isolation level of a transaction is read uncommitted or read committed, this problem can be solved.
Test the following example (assume that account A has A balance of 100 ):
-- Execute the following statement in the first connection
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- Or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
SELECT balance FROM account table WHERE account = 'A'
Waitfor delay '00: 00: 10' -- wait 10 seconds
SELECT balance FROM account table WHERE account = 'A'
COMMIT TRAN
-- Run the following statement using the second connection immediately.
BEGIN TRAN
UPDATE account table SET balance = 10 WHERE account = 'A'
COMMIT TRAN
We will find that the balance of account A returned twice in the first connection is different. The first connection is 100, and the second return is 10. This is A typical "non-repeated read" problem.
This type of problem can be prevented if you set the transaction isolation level of connection 1 to repeatable read or SERIALIZABLE.
(3) Reproduction of inconsistent analysis (non-repeated read)
As shown in table 1, when the isolation level of a transaction is read uncommitted or read committed, this problem can be solved.
Let's take A look at the following example (assume that account A has A balance of 100 ):
-- Execute the following statement in the first connection
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- Or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
SELECT balance FROM account table WHERE account = 'A'
Waitfor delay '00: 00: 10' -- wait 10 seconds
SELECT balance FROM account table WHERE account = 'A'
COMMIT TRAN
-- Run the following statement using the second connection immediately.
BEGIN TRAN
UPDATE account table SET balance = 10 WHERE account = 'A'
COMMIT TRAN
We will find that the balance of account A returned twice in the first connection is different. The first connection is 100, and the second return is 10. This is A typical "non-repeated read" problem.
This type of problem can be prevented if you set the transaction isolation level of connection 1 to repeatable read or SERIALIZABLE.
(4) Reproduction of phantom reading
Table 1 shows that when the transaction isolation level is read uncommitted, read committed, or repeatable read, this problem can be reproduced.
Let's take A look at the following example (assume that account A has A balance of 100 ):
-- Execute the following statement in the first connection
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- Or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Or SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM account table
Waitfor delay '00: 00: 10' -- wait 10 seconds
SELECT * FROM account table
COMMIT TRAN
-- Run the following statement using the second connection immediately.
BEGIN TRAN
Insert into account table VALUES ('C', '20140901 ')
COMMIT TRAN
We will find that the result set returned by the same query statement is different in the same transaction in the first connection. The result returned by the second query contains an account C, this is a typical "phantom reading" problem. This type of problem can be prevented only when the transaction isolation level of connection 1 is set to SERIALIZABLE.
Conclusion: in order to avoid the problems caused by transaction concurrency, a higher transaction isolation level can be adopted, but the transaction concurrency will be reduced. In turn, if a higher concurrency is pursued, a lower transaction isolation level will be used, it is easy to cause concurrency problems. Therefore, the default isolation level of SQL Server is relatively low "READ COMMITTED ". In actual application, the isolation level depends on the specific situation. You can also use the explicit locking method to control the transaction isolation level. For specific methods, please pay attention to the relevant articles of the author.