Four types of transaction concurrency problems in SQL Server

1. instance reproduction of four types of transaction concurrency problems in SQL Server

Zhu Er (2006.3 reproduced please note the author) http://blog.csdn.net/netcoder/article/details/633153
This articleArticleIt 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, if the transaction is not locked and multiple users access a database at the same time, the problem may occur when their transactions use the same data at the same time. Concurrency problems include:

• Update is lost 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. The 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. ApplicationProgramThe required isolation level is determined SQL Server The locking behavior used.

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. 2. How to let multiple users call the same stored procedure in sequence, rather than executing the http://topic.csdn.net/u/20080215/17/10cdbdd6-d6ee-4c0e-aa5e-d23f3f7d5a85.html at the same time The table contains a "ticket number" field that stores a sequential number (for example, 0001,0002). A storage program is designed to read the table, generate a maximum sequential number, and return the generated sequential number, enter the serial number in the table. There is no problem during operations by a single user, and everything is normal, but now it is found that this serial number is repeated when multiple users operate at the same time. 2) shared lock
execute the following statement in the first connection
Tran in TRAN
select * From Table1 holdlock-holdlock artificially locks
where B = 'b2'
waitfor delay '00: 00: 30' -- wait 30 seconds
commit Tran

Execute the following statement in the second connection 
Begin tran 
Select a, c from Table1 
Where B = 'b2' 
Update Table1 
Set a = 'A' 
Where B = 'b2' 
Commit tran 

If the preceding two statements are executed simultaneously, the SELECT query in the second connection can be executed. 
However, update can only be executed 30 seconds after the first transaction releases the shared lock and changes it to the exclusive lock. 

3) deadlock  
Add Table2 (D, E)  
D e  
D1 E1  
D2 E2  
Execute the following statement in the first connection:  
Begin tran  
Update Table1  
Set a = 'A'  
Where B = 'b2'  
Waitfor delay '00: 00: 30' 
Update Table2  
Set d = 'd5'  
Where E = 'e1'  
Commit tran  

Execute the following statement in the second connection 
Begin tran 
Update Table2 
Set d = 'd5' 
Where E = 'e1' 
Waitfor delay '00: 00: 10' 
Update Table1 
Set a = 'A' 
Where B = 'b2' 
Commit tran 

At the same time, the system detects a deadlock and terminates the process. 

Add:
table-level locking prompt supported by SQL Server2000
holdlock holds the shared lock, when the entire transaction is completed, it should be released immediately when the locked object is not needed, equal to the serializable transaction isolation level
when the nolock statement is executed, no shared lock is issued and dirty reads are allowed, equal to the read uncommitted transaction isolation level
paglock uses multiple page locks when a table lock is used
readpast allows SQL Server to skip any lock row, execute the transaction, applicable to read uncommitted transaction isolation level only skip the RID lock, not skip the page,
rowlock enforces row lock
tablockx enforces exclusive table-level locks, this lock prevents any other transactions from using this table during the transaction.
uplock forces the table to be updated while reading the table. shared lock

Application lock: 
The application lock is the client.CodeThe generated lock, instead of the lock generated by SQL Server. 
Two processes for processing application locks 
Sp_getapplock: Lock application resources 
Sp_releaseapplock unlock application resources 

Note: What is the difference between locking a database table? 

Select * from table with (holdlock) other transactions can read the table, but cannot update or delete the table. 
Select * from table with (tablockx) other transactions cannot read tables. Updates and deletions are published through wiz.