Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of the four types of transaction concurrency in SQL Server. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Case representation of four kinds of transaction concurrency problems in SQL Server

First, let's take a look at the concepts of parallelism and transaction isolation levels.

In a database, assume that if there is no lock and multiple users access the same database at the same time, problems may occur when their transactions use the same data at the same time. Concurrency issues include:

Lost or overwritten updates.

Unconfirmed correlation (dirty reading).

Inconsistent analysis (non-repetitive reading).

Phantom reading.

Let's take a moment to explain these four types of problems:

1. Missing updates

The problem of missing updates occurs when two or more transactions select the same row and then update the row based on the initially selected value. Each transaction is unaware of the existence of other transactions. The final update will rewrite updates made by other transactions, which will result in data loss.

2. Unconfirmed correlation (dirty reading)

Unacknowledged correlation problems occur when the second transaction selects a row that another transaction is updating. The data being read by the second transaction has not been confirmed and may be changed by the transaction that updated this row.

3. Inconsistent analysis (non-repetition)

Inconsistent analysis problems occur when the second transaction accesses the same row multiple times and reads different data each time. Inconsistent analysis is similar to unconfirmed correlation because other transactions are also changing the data being read by the second transaction. However, in the inconsistent analysis, the data read by the second transaction is committed by the transaction that has changed. Furthermore, inconsistent analysis involves reading the same row multiple times (two or more times), and each time the information is changed by another transaction; therefore, the row is read non-repeatedly.

4. Phantom reading

Phantom reading problems occur when an insert or delete operation is performed on a row that falls within the scope of the row being read by a transaction. The row range of the first read of the transaction shows that one of the rows no longer exists in the second or subsequent read because the row has been deleted by another transaction. Similarly, due to the insert operation of other transactions, the second or subsequent read of the transaction shows that a row no longer exists in the original read.

All of the above four problems will cause data inconsistency. We refer to the level at which the transaction is prepared to accept inconsistent data as the isolation level. The isolation level is the extent to which one transaction must be isolated from other transactions. A lower isolation level can increase concurrency, but at the expense of reduced data correctness. In contrast, a higher isolation level ensures the correctness of the data, but may have a negative impact on concurrency. The isolation level required by the application determines the SQL

Locking behavior used by Server.

SQL-92 defines the following four isolation levels, all of which SQL Server supports:

READ UNCOMMITTED--- uncommitted reads (the lowest level of transaction isolation that only guarantees that physically corrupted data is not read).

READ COMMITTED--- commit read (SQL Server default level).

REPEATABLE READ--- can be read repeatedly.

SERIALIZABLE--- can be read serially (the highest level of transaction isolation, complete isolation between transactions).

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

Isolation level dirty read is not repeatable read phantom uncommitted read is submitted read is repeatable read no serial read no

In order to reproduce the above four types of problems, we must do some preparatory work:

1. Please use the following script to create a test table.

-- create a test database testCREATE DATABASE testGO-- create a test table USE testGOCREATE TABLE account table (account number CHAR (4), balance INT) GOINSERT account table SELECT 'union ALLSELECT' account 200

2. please open two query analyzer programs, which are intended to open two connections and simulate two parallel transactions. Hereinafter referred to as connection one and connection two.

The test officially begins:

(1) the reproduction of lost updates

Let's take a look at the following example:

-- execute the following statement in the first connection: BEGIN TRANUPDATE account table SET balance = 101WHERE account number ='A 'WAITFOR DELAY' 0000 WHERE 10'-wait 10 seconds COMMIT TRAN-- and then immediately use the second connection to execute the following statement BEGIN TRANUPDATE account table SET balance = 102WHERE account number ='A' COMMIT TRAN

We will find that the transaction in the second connection cannot be executed immediately and must wait for the transaction of the first connection to complete before it can be executed.

This avoids the problem of "missing updates", otherwise it will give rise to the problem of "lost updates".

The problem of missing updates is the most serious one. As can be seen from the table, no matter which transaction isolation level is used, the problem of losing updates is not allowed, so this kind of problem cannot be reproduced.

(2) the reproduction of unconfirmed correlation (dirty reading)

As you can see from Table 1, dirty reads are allowed when the isolation level of the transaction is READ UNCOMMITTED.

-- execute the following statement in the first connection: BEGIN TRANUPDATE account table SET balance = 103 WHERE account number ='A 'WAITFOR DELAY' 00 UPDATE account number = 10'--wait 10 seconds UPDATE account table SET balance = 104 WHERE account number = 'A'COMMIT TRAN-- then immediately use the second connection to execute the following statement: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDBEGIN TRANSELECT balance FROM account table WHERE account number =' A'COMMIT TRAN

We will find that the statement of the second connection returns 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 reading".

(3) the reproduction of inconsistent analysis (non-repetitive reading)

As you can see from Table 1, this problem can occur when the isolation level of the transaction is READ UNCOMMITTED or READ COMMITTED.

Please test the following example (assuming that the balance of account An is 100):

-- execute the following statement in the first connection: SET TRANSACTION ISOLATION LEVEL READ COMMITTED-- or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDBEGIN TRANSELECT balance FROM account table WHERE account number = 'A'WAITFOR DELAY' 00 WHERE 0015 10'- wait 10 seconds SELECT balance FROM account table WHERE account number = 'A'COMMIT TRAN-- then immediately use the second connection to execute the following statement BEGIN TRANUPDATE account table SET balance = 10 WHERE account number =' A'COMMIT TRAN

We will find that the balance of account A returned twice in the first connection is not the same, the first time is 100, and the second time is 10, which is a typical "non-repeating" problem.

Such problems can be prevented if the transaction isolation level of connection one is set to REPEATABLE READ or SERIALIZABLE.

(3) the reproduction of inconsistent analysis (non-repetitive reading)

As you can see from Table 1, this problem can occur when the isolation level of the transaction is READ UNCOMMITTED or READ COMMITTED.

Let's take a look at the following example (assuming the balance of account An is 100):

-- execute the following statement in the first connection: SET TRANSACTION ISOLATION LEVEL READ COMMITTED-- or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDBEGIN TRANSELECT balance FROM account table WHERE account number = 'A'WAITFOR DELAY' 00 WHERE 0015 10'- wait 10 seconds SELECT balance FROM account table WHERE account number = 'A'COMMIT TRAN-- then immediately use the second connection to execute the following statement BEGIN TRANUPDATE account table SET balance = 10 WHERE account number =' A'COMMIT TRAN

We will find that the balance of account A returned twice in the first connection is not the same, the first time is 100, and the second time is 10, which is a typical "non-repeating" problem.

Such problems can be prevented if the transaction isolation level of connection one is set to REPEATABLE READ or SERIALIZABLE.

(4) the reproduction of phantom reading.

As you can see from 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 take a look at the following example (assuming the balance of account An is 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 READBEGIN TRANSELECT * FROM account table WAITFOR DELAY '00WAITFOR DELAY * FROM account table COMMIT TRAN-- wait 10 seconds and then immediately use the second connection to execute the following statement: BEGIN TRANINSERT INTO account table VALUES (' Che Magazine 300') COMMIT TRAN

We will find that in the first connection, in the same transaction, the same query statement returns a different result set twice, and the second return result set has an account with account number C. this is a typical "phantom reading" problem. This type of problem can only be prevented by setting the transaction isolation level of connection one to SERIALIZABLE.

Conclusion: in order to avoid the problems caused by transaction concurrency, a higher transaction isolation level can be adopted, but it will reduce the transaction parallelism; on the contrary, if you pursue high parallelism and use a lower transaction isolation level, it is easy to bring concurrency problems. Therefore, the default isolation level adopted by SQL Server is a relatively low "READ COMMITTED". In practical application, the isolation level depends on the specific situation, and the explicit "locking" method can also be used to control the transaction isolation level. for specific methods, please pay attention to the author's relevant articles.

This is the end of this article on "sample Analysis of four types of transaction concurrency problems in SQL Server". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.