數據庫如何解決高并發，sql server 并發_并發問題– SQL Server中的理論和實驗

sql server 并發

介紹 (Introduction)

Intended audience

目標聽眾

This document is intended for application developers and database administrators who are willing to get an overview of comm concurrency problems to which transaction isolation levels respond in the particular case of Microsoft SQL Server.

數據庫如何解決高并發， 本文檔適用于愿意概述通信并發問題的應用程序開發人員和數據庫管理員，在Microsoft SQL Server的特殊情況下，事務隔離級別會對此問題做出響應。

Typographical Conventions

印刷約定

Stylized Consolas Font

Context

語境

sqlserver高可用方案。 In any relational database system, there is the concept of transaction. A transaction is a set of logical operations that have to be performed in a user session as a single piece of work. Let’s review the properties of a transaction.

在任何關系數據庫系統中，都存在事務的概念。 事務是一組必須在用戶會話中作為單個工作執行的邏輯操作。 讓我們回顧一下事務的屬性。

Hence, a transaction must be atomic i.e. there is no halfway for it to complete: either all the logical operations occur or none of them occur.

因此，事務必須是原子的，即沒有半路完成：要么發生所有邏輯操作，要么都不發生。

A transaction has to be consistent i.e. any data written using database system must be valid according to defined rules like primary key uniqueness or foreign key constraints.

sql千萬并發？ 事務必須是一致的，即，根據定義的規則（例如主鍵唯一性或外鍵約束），使用數據庫系統寫入的任何數據都必須有效。

Once the consistency is ensured, a transaction must be permanently stored to disk. It guarantees the durability required for a transaction.

一旦確保了一致性，就必須將事務永久存儲到磁盤。 它保證了交易所需的持久性。

Last but not least, as multiple transactions could be running concurrently in a database system, we can find transactions that read from or writes to the same data object (row, table, index…). It introduces a set of problems to which different ??transaction isolation (level)?? tend to respond. A transaction isolation (level) defines how and when the database system will present changes made by any transaction to other user sessions.

最后但并非最不重要的一點是，由于一個數據庫系統中可以同時運行多個事務，因此我們可以找到讀取或寫入同一數據對象（行，表，索引等）的事務。 它引入了一系列問題，不同的“事務隔離（級別）”傾向于響應這些問題。 事務隔離（級別）定義了數據庫系統如何以及何時將任何事務所做的更改呈現給其他用戶會話。

sqlserver基本介紹、 In order to select the appropriate transaction isolation level, having a good understanding on common concurrency problems that can occur is mandatory.

為了選擇適當的事務隔離級別，必須對可能發生的常見并發問題有一個很好的了解。

This article is the first one of a series about transaction isolation level. It is divided into two parts. The first one will explain the concurrency problems with a theoretical example while the second will be more practical and we will try to experience these problems on a SQL Server instance.

本文是有關事務隔離級別的系列文章中的第一篇。 它分為兩個部分。 第一個將通過理論示例解釋并發問題，而第二個將更實際，我們將嘗試在SQL Server實例上體驗這些問題。

并發問題 (Concurrency problems)

Before diving into transaction levels details, it’s important to get used to typical concurrency problems and how we call them.

多線程并發訪問數據庫。 在深入研究交易級別細節之前，重要的是要習慣于典型的并發問題以及我們如何稱呼它們。

Lost update and dirty write

更新丟失，寫臟

This phenomenon happens when two transactions access the same record and both updates this record. The following figure summarizes what could happen in a simple example.

當兩個事務訪問相同的記錄并且都更新該記錄時，就會發生這種現象。 下圖總結了一個簡單示例中可能發生的情況。

sql server登錄？ In this example, we have 2 concurrent transactions that access a record with a (60) modifiable value. This record is identified either by its rowId or by a primary key column that won’t be presented here for simplicity.

在此示例中，我們有2個并發事務訪問具有（60）可修改值的記錄。 該記錄由其rowId或主鍵列標識，為簡單起見，此處將不顯示該記錄。

The first transaction reads this record, does some processing then updates this record and finally commits its work. The second transaction reads the record then updates it immediately and commits. Both transactions do not update this record to the same value. This leads to a loss for the update statement performed by second transaction.

第一個事務讀取該記錄，進行一些處理，然后更新該記錄，最后提交其工作。 第二個事務讀取記錄，然后立即更新并提交。 這兩個事務都不會將此記錄更新為相同的值。 這導致第二筆交易執行的更新語句丟失。

sqlserver怎么用。 As Transaction 1 overwrites a value that Transaction 2 already modified. We could have said that Transaction 1 did a ??dirty write?? if Transaction 2 didn’t commit its work.

當事務1覆蓋事務2已經修改的值時。 我們可以說，如果事務2不提交其工作，則事務1會執行“臟寫”操作。

Dirty read

臟讀

A dirty read happens when a transaction accesses a data that has been modified by another transaction but this change has not been committed or rolled back yet. Following figure shows a case of dirty read. In this example, record has two columns with a starting value of (60,40). In this context, let’s say we have an applicative constraint that says that the sum of those values must always be 100.

sqlserver可視化界面。 當事務訪問已被另一事務修改的數據但尚未提交或回滾此更改時，將發生臟讀。 下圖顯示了臟讀的情況。 在此示例中，記錄有兩列，起始值為（60,40）。 在這種情況下，假設我們有一個應用約束，該約束說這些值的總和必須始終為100。

Non-repeatable read or fuzzy read

不可重復讀取或模糊讀取

The situation of non-repeatable read is almost the same as dirty read except that both values are modified. As in previous sub-sections, we will review a graphical representation of a non-repeatable read situation. To do so, we will keep two concurrent transactions accessing two columns of the same record. One of them reads and modifies each value, one at a time, then commits while the other reads the first value, does some processing, reads the second value then commits. Keeping our constraint from previous example (the sum of both values equals 100), the presented situation leads the second transaction to manipulate inconsistent data and maybe to present it to an end-user.

sql server2012使用、 不可重復讀取的情況與臟讀取幾乎相同，不同之處在于兩個值均被修改。 與前面的小節一樣，我們將回顧不可重復讀取情況的圖形表示。 為此，我們將保持兩個并發事務訪問同一記錄的兩列。 其中一個讀取和修改每個值，一次一次，然后提交，而另一個讀取第一個值，進行一些處理，讀取第二個值，然后提交。 保持前面示例的約束（兩個值的總和等于100），所呈現的情況導致第二次事務處理不一致的數據，并可能將其呈現給最終用戶。

Phantom reads

幻影閱讀

Phantom reads are a variation of non-repeatable reads in the context of row sets. Here is an example that illustrates this:

sql server有什么用。 幻像讀取是行集上下文中不可重復讀取的一種變體。 這是說明此的示例：

Let’s say we have a transaction Transaction 1 that performs twice a SELECT query against a table T, once at its beginning and once just before its end. Let’s assume another transaction Transaction 2 starts after the first one, inserts a new row to the table T and commits before the second time Transaction 1 will run its SELECT query. The result sets that will be returned by the two occurrences of the SELECT query will differ.

假設我們有一個事務Transaction 1 ，它對表T執行兩次SELECT查詢，一次在表T的開始，一次在表T的結束。 假設另一個事務Transaction 2在第一個事務之后開始，在表T中插入新行，并在第二次事務1運行其SELECT查詢之前提交。 兩次SELECT查詢返回的結果集將有所不同。

Here is a diagram that summarizes the situation:

這是一個匯總情況的圖表：

數據庫并發控制三種方法，

Locking reads

鎖定讀取

This is not really a concurrency problem, but more likely a “design pattern”. In short, the principle is to read a value from a given record and update this record based on the returned value inside the same transaction, with the insurance that no other session will modify the value that has just been read.

這并不是真正的并發問題，而更可能是“設計模式”。 簡而言之，原理是從給定記錄中讀取一個值，并根據同一筆交易中返回的值來更新該記錄，以確保沒有其他會話會修改剛剛讀取的值。

sql并發語句怎么寫？ It’s the concept of SELECT FOR UPDATE?in Oracle or SELECT … FROM <table> WITH (UPDLOCK) in SQL Server.

它是Oracle中的SELECT FOR UPDATE或SQL Server中的SELECT…FROM <table> WITH（UPDLOCK）的概念 。

This will only work in SERIALIZABLE isolation level. We won’t discuss it anymore in this article.

這僅適用于SERIALIZABLE隔離級別。 在本文中我們將不再討論。

在SQL Server上進行實驗 (Experimentation on SQL Server)

The experimentation scripts presented here are all designed using the AdventureWorks2012 database.

sqlserver并發300， 此處介紹的實驗腳本都是使用AdventureWorks2012數據庫設計的。

If no precision is made about a transaction isolation level in the experimentation detailed explanation, the results presented are those returned using SQL Server’s default transaction isolation level, which is READ COMMITTED.

如果在實驗詳細說明中未對事務隔離級別進行精確說明，則顯示的結果是使用SQL Server的默認事務隔離級別READ COMMITTED返回的結果。

Lost update

更新失敗

Following queries depict the following scenario.

以下查詢描述了以下情況。

We are in a bank system. Your bank account has an initial balance of 1500 (currency does not matter). You will find below the T-SQL statement to set up the test.

我們在銀行系統中。 您的銀行帳戶的初始余額為1500（貨幣無關緊要）。 您將在T-SQL語句下面找到設置測試。

CREATE TABLE BankAccounts(AccountId		INT IDENTITY(1,1),BalanceAmount?? INT
);insert into BankAccounts (BalanceAmount
)
SELECT 1500
;

Your employer attempts to pay your (tiny) salary, let’s say 1600. This will consist of your first session for which you will find T-SQL statement corresponding to the action that has to be done inside the bank system.

您的雇主嘗試支付您的（微不足道的）薪水，比方說1600。這將包括您的第一次會話，在該會話中，您將找到與銀行系統內必須執行的操作相對應的T-SQL語句。

-- Session 1: Employer
DECLARE @CustomerBalance	INT ;
DECLARE @BalanceDifference	INT ;SET @BalanceDifference = 1600 ;BEGIN TRANSACTION ;-- Getting back current balance valueSELECT @CustomerBalance = BalanceAmountFROM BankAccountsWHERE AccountId = 1 ;PRINT 'Read Balance value: ' + CONVERT(VARCHAR(32),@CustomerBalance);-- adding salary amountSET @CustomerBalance = @CustomerBalance + @BalanceDifference ;-- Slowing down transaction to let tester the time-- to run query for other sessionPRINT 'New Balance value: ' + CONVERT(VARCHAR(32),@CustomerBalance);WAITFOR DELAY '00:00:10.000';-- updating in tableUPDATE BankAccountsSET BalanceAmount = @CustomerBalance WHERE AccountId = 1 ;-- display results for userSELECT BalanceAmount as BalanceAmountSession1FROM BankAccountsWHERE AccountId = 1 ;
COMMIT ;

At the same time, as you’ve returned an article to your favorite web reseller, he’s also trying to add 40 to your bank account. Following code will be run:

同時，當您將文章退還給您最喜歡的網絡經銷商時，他還試圖將40加到您的銀行帳戶中。 將運行以下代碼：

-- Session 2: Web resellerDECLARE @CustomerBalance	INT ;
DECLARE @BalanceDifference	INT ;SET @BalanceDifference = 40 ;BEGIN TRANSACTION ;-- Getting back current balance valueSELECT @CustomerBalance = BalanceAmountFROM BankAccountsWHERE AccountId = 1 ;PRINT 'Read Balance value: ' + CONVERT(VARCHAR(32),@CustomerBalance);-- adding salary amountSET @CustomerBalance = @CustomerBalance + @BalanceDifference ;PRINT 'New Balance value: ' + CONVERT(VARCHAR(32),@CustomerBalance);-- updating in tableUPDATE BankAccountsSET BalanceAmount = @CustomerBalance WHERE AccountId = 1 ;-- display results for userSELECT BalanceAmount as BalanceAmountSession2FROM BankAccountsWHERE AccountId = 1 ;
COMMIT ;

Here are the results we will get from final SELECT in each query:

這是我們將從每個查詢中的最終SELECT獲得的結果：

Unfortunately, we lost the money from web reseller…

不幸的是，我們從網絡經銷商那里虧損了……

Now, let’s clean up our test.

現在，讓我們清理一下測試。

DROP TABLE BankAccounts ;

Dirty read

臟讀

To illustrate dirty reads, we will update data in Person.Person table: we will update all records so that all rows where FirstName column value is “Aaron” will bear the same value for it’s their corresponding LastName column. This value will be “Hotchner” but won’t persist: we will rollback the transaction.

為了說明臟讀，我們將更新Person.Person表中的數據：我們將更新所有記錄，以便FirstName列值為“ Aaron”的所有行都將具有相同的值，因為它們是其對應的LastName列。 該值將為“ Hotchner”，但不會持久：我們將回滾事務。

Here is the script for the first session:

這是第一個會話的腳本：

SELECT COUNT(DISTINCT LastName) DistinctLastNameBeforeBeginTran
FROM Person.Person
WHERE FirstName = 'Aaron';BEGIN TRANSACTION;UPDATE Person.Person
SET LastName = 'Hotchner'
WHERE FirstName = 'Aaron'
;SELECT COUNT(DISTINCT LastName) DistinctLastNameInTransaction
FROM Person.Person
WHERE FirstName = 'Aaron';WAITFOR DELAY '00:00:10.000';ROLLBACK TRANSACTION;SELECT COUNT(DISTINCT LastName) DistinctLastNameAfterRollback
FROM Person.Person
WHERE FirstName = 'Aaron';

While the first session is in running its WAITFOR DELAY instruction, we can run following query in a second session:

當第一個會話正在運行其WAITFOR DELAY指令時，我們可以在第二個會話中運行以下查詢：

SELECT COUNT(DISTINCT LastName) SecondSessionResults
FROM Person.Person
WHERE FirstName = 'Aaron';

With SQL Server’s default isolation level, the second session will be waiting for the first session to complete?:

使用SQL Server的默認隔離級別，第二個會話將等待第一個會話完成：

So, we can say that, by default, SQL Server protects you from dirty reads. Let’s just change session isolation level to READ UNCOMMITTED and check that SQL Server will present ??dirty?? data to session 2 in that mode…

因此，我們可以說默認情況下，SQL Server保護您免受臟讀的侵害。 讓我們僅將會話隔離級別更改為READ UNCOMMITTED，并檢查SQL Server將以該模式向會話2提供“臟”數據。

In order to change session’s transaction isolation level, run following query:

為了更改會話的事務隔離級別，請運行以下查詢：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Then, run the code for second session from above again.

然后，再次從上方運行第二個會話的代碼。

Here are the results for both sessions:

這是兩個會話的結果：

Non-repeatable reads

不可重復讀

If you remind the explanation above about non-repeatable reads, this problem occurs when two consecutive reads of a given column value in a particular table row lead to two different values, meaning that values returned by a query are time-dependent even inside the same transaction.

如果您回想起上面關于不可重復讀取的解釋，則當特定表行中給定列值的兩次連續讀取導致兩個不同的值時，就會發生此問題，這意味著即使同一查詢內部的查詢返回的值也是時間相關的交易。

Once again, we will use two sessions for this experiment. The first session will run a query returning five first rows from Person. Person table, wait for some time then rerun the exact same query.

再次，我們將使用兩個會話進行此實驗。 第一個會話將運行查詢，以從Person返回前五行。 人員表，等待一段時間，然后重新運行完全相同的查詢。

Here is the code for the first session:

這是第一次會話的代碼：

-- ensure we use SQL Server default isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION;-- Query 1 - first run
SELECT TOP 5FirstName,MiddleName,LastName,Suffix
FROM Person.Person
ORDER BY LastName
;-- let some time for session 2
WAITFOR DELAY '00:00:10.000';-- Query 1 - second run
SELECT TOP 5FirstName,MiddleName,LastName,Suffix
FROM Person.Person
ORDER BY LastName
;COMMIT TRANSACTION;

While the first session is waiting, run following code that will update all records that have FirstName column value set to “Kim” and LastName column value set to “Abercrombie”.

在第一個會話等待時，運行以下代碼，將更新FirstName列值設置為“ Kim”和LastName列值設置為“ Abercrombie”的所有記錄。

-- ensure we use SQL Server default isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION;UPDATE Person.Person
SET Suffix = 'Clothes'
WHERE LastName = 'Abercrombie'
AND FirstName = 'Kim';COMMIT TRANSACTION;

Here are the result sets returned in the first session:

以下是在第一次會話中返回的結果集：

We can revert our changes with following query:

我們可以使用以下查詢還原更改：

UPDATE Person.Person
SET Suffix = NULL
WHERE LastName = 'Abercrombie'
AND FirstName = 'Kim';

If we are willing to prevent this behavior to happen, we could have heard about the REPEATABLE READ isolation level. Let’s try it out!

如果我們愿意防止這種行為發生，我們可能已經聽說過REPEATABLE READ隔離級別。 讓我們嘗試一下！

For the first session, we just need to change the first command from:

對于第一個會話，我們只需要將第一個命令從以下位置更改：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

to

至

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

and execute it. The code for second session can be used as is.

并執行它。 可以按原樣使用第二個會話的代碼。

With this simple change, we have a lock that is held by first session. This leads the second session to wait for the first one to complete before actually modify rows.

通過此簡單的更改，我們在第一次會話中擁有了一個鎖。 這導致第二個會話在實際修改行之前等待第一個會話完成。

Here is a screenshot that proves it:

這是證明它的屏幕截圖：

And, obviously, we do not forget to revert our changes to the query shown above.

而且，顯然，我們不會忘記將更改還原到上面顯示的查詢。

Phantom reads

幻影閱讀

For this experiment, we will create a table called dbo.Employee and let it empty:

在本實驗中，我們將創建一個名為dbo.Employee的表并將其為空：

IF (OBJECT_ID('dbo.Employee') IS NOT NULL)
BEGINDROP TABLE [dbo].[Employee];
END;CREATE TABLE [dbo].[Employee] (EmpId?????? int IDENTITY(1,1) NOT NULL,EmpName???? nvarchar(32)??????NOT NULL,CONSTRAINT pk_EmpId PRIMARY KEY CLUSTERED (EmpId)
);

In first session, we will run a SELECT query against this tablespace in time by 10 seconds:

在第一個會話中，我們將在10秒內針對此表空間運行SELECT查詢：

-- ensure we use SQL Server default isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION;-- Query 1 - first run
SELECT *
FROM dbo.Employee
;-- let some time for session 2
WAITFOR DELAY '00:00:10.000';-- Query 1 - second run
SELECT *
FROM dbo.Employee
;COMMIT TRANSACTION;

In second session, we will insert some rows in dbo.Employee table.

在第二個會話中，我們將在dbo.Employee表中插入一些行。

-- ensure we use SQL Server default isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION;INSERT INTO [dbo].[Employee] ([EmpName]) VALUES ('Oby');
INSERT INTO [dbo].[Employee] ([EmpName]) VALUES ('One');
INSERT INTO [dbo].[Employee] ([EmpName]) VALUES ('Ken');
INSERT INTO [dbo].[Employee] ([EmpName]) VALUES ('Tukee');COMMIT TRANSACTION;

And here are the results sets returned in first session:

以下是在第一次會話中返回的結果集：

As we can see, duet o concurrency (and transaction isolation level), it’s possible to get different results sets for the same query, from time to time even in the same transaction.

如我們所見，由于并發（和事務隔離級別）的雙重影響，即使在同一事務中，也可能會不時為同一查詢獲得不同的結果集。

摘要 (Summary)

So far, we’ve seen that the concurrency of transactions implies some basic issues that transaction isolation level can eventually prevent them to happen.

到目前為止，我們已經看到事務并發意味著一些基本問題，而事務隔離級別最終可以阻止它們發生。

In the next article, you will get more details about each transaction isolation level, included what common concurrency problem(s) they solve…

在下一篇文章中，您將獲得有關每個事務隔離級別的更多詳細信息，包括它們解決的常見并發問題。

參考資料 (References)

• A Critique of ANSI SQL Isolation Levels ANSI SQL隔離級別的批判
• Isolation Levels in the Database Engine 數據庫引擎中的隔離級別
• Difference between Non- repeatable read and Phantom read 不可重復讀和幻像讀之間的區別

翻譯自: https://www.sqlshack.com/concurrency-problems-theory-and-experimentation-in-sql-server/

sql server 并發