sql server 并发_SQL Server和并发控制

sql server 并发

什么是交易？ (What is a Transaction?)

The standard definition of Transaction state that “Every Query batch that runs in a SQL server is a Transaction.”, this means any query you run on a SQL server will be considered as a Transaction it could either be a simple SELECT query or any UPDATE or ALTER query.

事务状态的标准定义是“在SQL Server中运行的每个查询批处理都是事务。”，这意味着您在SQL Server上运行的任何查询都将被视为事务，它可以是简单的SELECT查询或任何UPDATE。或ALTER查询。

If you run a query without mentioning the BEGIN TRAN keyword then it would be considered as an Implicit transition.

如果您在运行查询时未提及BEGIN TRAN关键字，则将其视为隐式过渡。

If you run a query which starts with BEGIN TRAN and ends with COMMIT or ROLLBACK then it would be considered as Explicit Transaction.

如果您运行以BEGIN TRAN开头并以COMMIT或ROLLBACK结尾的查询，则该查询将被视为显式事务。

交易性质 (Transactional Properties)

A Database Management System would be considered a Relational Database Management System if it follows the transactional properties (A.C.I.D).

如果数据库管理系统遵循事务属性(ACID)，则将其视为关系数据库管理系统。

• A: Atomicity

  答：团体

• C: Consistency

  C：坚持

• I: Isolation

  我：我这样

• D: Durability

  D：耐久性

1.原子性 (1. Atomicity)

A transaction work should be atomic in nature, which means if the user performs a transition, either the transaction should complete and perform all the asked operations or it should fail and don’t do anything. Atomicity deals with the transaction process and a Relational Database Management System transaction does not leave the transaction process in between.

事务工作本质上应该是原子的，这意味着如果用户执行过渡，则事务应该完成并执行所有要求的操作，或者事务应该失败并且不执行任何操作。 原子性处理事务处理，关系数据库管理系统事务不会使事务处理介于两者之间。

2.一致性 (2. Consistency)

After the transaction is completed the database should not be left in an inconsistent state which means, the data on which transaction is applied must be logically correct and should lead to an error.

事务完成之后，数据库不应处于不一致的状态，这意味着，应用了事务的数据在逻辑上必须正确，并会导致错误。

3.隔离 (3. Isolation)

If two transactions are applied on a similar database then both the transaction should be isolated from each other, and the user must see the end result. It can also be defined as a transaction that should see the data only after or before the concurrent transaction process is completed, which means if a one transaction process is in between the other transaction process should wait until the first transaction is completed.

如果在同一个数据库上应用了两个事务，则这两个事务应相互隔离，并且用户必须看到最终结果。 也可以将其定义为仅在并发事务处理完成之后或之前才查看数据的事务，这意味着一个事务处理是否在另一个事务处理之间，应该等待直到第一个事务完成为止。

For instance, if A performs a transaction process on data d1,and before the transaction process gets completed Balso performs another transaction process on the same data d1.Here,the isolation property will isolate the transaction process of Aand B,and the transaction process of Bwill only start after the transaction process of Agets completed.

例如，如果A对数据d1执行事务处理，并且在事务处理完成之前B也对同一数据dl执行另一事务处理。 在这里，隔离属性将隔离A和B的交易过程，而A的交易过程被完成后，B的交易过程才会开始。

4.耐久性 (4. Durability)

Even if the system fails the transaction should be persistent, which means, if the system fails during a transaction process, the transaction should be dropped too without affecting the data.

即使系统失败，事务也应该是持久的，这意味着，如果系统在事务处理过程中失败，那么事务也应该被删除而不影响数据。

The SQL Server takes care of the Atomicity, Consistency, and Durability of the system, and the user has to care of the Isolation property of the transaction.

SQL Server负责系统的原子性，一致性和持久性，并且用户必须注意事务的Isolation属性。

什么是SQL Server中的并发？ (What is Concurrency in SQL Server?)

Concurrency is a situation that arises in a database due to the transaction process. Concurrency occurs when two or more than two users are trying to access the same data or information. DBMS concurrency considered a problem because accessing data simultaneously by two different users can lead to inconsistent results or invalid behavior.

并发是由于事务处理而在数据库中出现的一种情况。 当两个或两个以上的用户试图访问相同的数据或信息时，就会发生并发。 DBMS并发性被认为是一个问题，因为两个不同的用户同时访问数据可能会导致结果不一致或无效行为。

并发问题类型 (Concurrency Problem Types)

The concurrency problem mostly arises when both the users try to write on the data or when one is writing and the other is reading. Apart from this logic, there are some common types of concurrency problems:

当两个用户都试图在数据上进行写操作时，或者当一个用户正在写数据而另一个用户正在读数据时，并发问题最容易出现。 除了这种逻辑之外，还有一些常见的并发问题类型：

• Dirty Reads
  脏读
• Lost Updates
  丢失的更新
• Non-repeatable Reads
  不可重复读
• Phantom Reads
  幻影读

1.脏读 (1. Dirty Read)

This problem occurs when another process reads the uncommitted data, for instance, if one process has changed data but not committed it yet, another process tries to read the same data, and this led to the inconsistent state. ****

当另一个进程读取未提交的数据时会发生此问题，例如，如果一个进程更改了数据但尚未提交，则另一个进程尝试读取相同的数据，这导致状态不一致。 ****

2.丢失的更新 (2. Lost Updates)

This problem occurs when two processes try to manipulate the same data simultaneously. This problem can lead to data loss or the second process might overwrite the first process manipulation.

当两个进程试图同时操作相同的数据时，会发生此问题。 此问题可能导致数据丢失，或者第二个进程可能会覆盖第一个进程的操作。

3.不可重复读 (3. Non-repeatable Reads)

This problem occurs when one process is reading the data and another process is writing the data. In non-repeatable reads, the reading value might get the change because another process writes different data.

当一个进程正在读取数据而另一进程正在写入数据时，会发生此问题。 在不可重复的读取中，读取值可能会发生变化，因为另一个进程写入了不同的数据。

4.幻影阅读 (4. Phantom Reads)

If two same queries executed by two users show different output then it would be a Phantom Read problem. For instance, If user A select a query to read some data, at the same time the user B insert some new data but the user A only get able to read the old data at the first attempt, but when User A re-query the same statement then he/she gets a different set of data.

如果两个用户执行的两个相同查询显示不同的输出，则可能是幻影读取问题。 例如，如果用户A选择查询以读取一些数据，则用户B同时插入了一些新数据，但用户A第一次尝试只能读取旧数据，但是当用户A重新查询相同的陈述，那么他/她将获得不同的数据集。

解决并发问题 (Solve Concurrency problem)

To overcome these Concurrency problems, SQL server provides 5 different levels of transaction Isolation, and these 5 Isolation Level work on two major Concurrency models:

为了克服这些并发问题， SQL Server提供了5个不同级别的事务隔离，并且这5个隔离级别可在两种主要的并发模型上工作：

并发模型 (Concurrency models)

1.悲观模型 (1. Pessimistic Model)

In the Pessimistic model of managing concurrent data access, the readers can block writers and the writers can block readers.

在管理并发数据访问的悲观模型中，读取器可以阻止写入器，而写入器可以阻止读取器。

2. Optimistic Mode

2.乐观模式

In the Optimistic model of managing concurrent data access, the readers cannot block writers and the writers cannot block readers, but the writer can block the writer.

在管理并发数据访问的乐观模型中，读取器不能阻止写入器，写入器不能阻止读取器，但是写入器可以阻止写入器。

Note readers:users performing the SELECT operations.

注意读者：执行SELECT操作的用户。

Note writer:users performing INSERT, ALTER, UPDATE, SET, etc. operations.

注释作者：执行INSERT，ALTER，UPDATE，SET等操作的用户。

隔离度 (Isolation Level)

When we link our main application with the SQL server database then the application link with the database with 5 different Isolation levels and these levels are:

当我们将主应用程序与SQL Server数据库链接时，应用程序与数据库的链接具有5种不同的隔离级别，这些级别是：

• Read Uncommitted
  读未提交
• Read Committed
  阅读已提交
• Repeatable Read
  可重复读
• Serializable
  可序列化
• Snapshot
  快照

Out of these 5 Isolation levels Read Uncommitted, Read Committed, Repeatable Read, and Serializable comes under pessimistic concurrency and snapshot comes under Optimistic concurrency.

在这5种隔离级别中，“未提交”，“已提交读”，“可重复读”和“可序列化”属于悲观并发，快照属于乐观并发。

1.阅读未提交 (1. Read Uncommitted)

It is the first level of Isolation, and it comes under the pessimistic Model of concurrency. In Read Uncommitted, one transaction is allowed to read the data which is about to change by the commit of another process. Read Uncommitted allows the dirty read problem.

这是隔离的第一个级别，它属于并发的悲观模型。 在“读未提交”中，允许一个事务读取将通过另一进程的提交更改的数据。 读取未提交允许脏读取问题。

2.阅读已提交 (2. Read Committed)

It is the second level of Isolation and falls under the pessimistic model of concurrency. In the Read Committed isolation level, we are only allowed to read data that is committed, which means it eliminates the dirty read problem. In this level, if you are reading data then the concurrent transactions which can delete or write data, have to hold until you finish your reading. ****

它是隔离的第二层，属于并发的悲观模型。 在“读取已提交”隔离级别中，只允许读取已提交的数据，这意味着它消除了脏读问题。 在此级别上，如果您正在读取数据，那么可以删除或写入数据的并发事务必须保留，直到完成读取为止。 ****

3. Repeatable Read

3.可重复读

The Repeatable Read Isolation level is similar to the Read Committed Level, and it eliminates the Non-Repeatable Read Problem. In this level, the transaction has to wait till another transaction Update query, as well as Read Query, is executed. But here if the Insert transaction does not wait for anyone, and this leads to the Phantom read Problem.

可重复读取隔离级别类似于已提交读取的级别，它消除了不可重复读取问题。 在此级别中，事务必须等待直到执行另一个事务更新查询以及“读取查询”。 但是在这里，如果Insert事务不等待任何人，这将导致Phantom读取问题。

4.可序列化 (4. Serializable)

It is the highest-level Isolation of the pessimistic model, and by implementing this level of Isolation we can prevent the Phantom Read problem. In this level of Isolation, we can ask any transaction to wait until the current other transaction gets completed or executed.

它是悲观模型的最高级别隔离，通过实现此级别的隔离，我们可以防止幻影读取问题。 在此级别的隔离中，我们可以要求任何事务等待，直到当前其他事务完成或执行为止。

5.快照 (5. Snapshot)

Snapshot follows the optimistic model of concurrency, and this level of isolation takes a snapshot of the current data and uses it as a copy for the different transactions. Here each transaction has its own copy of data, so if a user tries to perform a transaction like an update, or insert, it asks him to re-verify all the operation before the process gets started executing.

快照遵循乐观的并发模型，这种隔离级别可获取当前数据的快照并将其用作不同事务的副本。 这里，每个事务都有其自己的数据副本，因此，如果用户尝试执行更新或插入之类的事务，它将要求他在流程开始执行之前重新验证所有操作。

结论 (Conclusion)

Concurrency occurs when two transactions occur currently on the same set of data, and currency always leads to the data inconsistency and abnormal behavior of the transaction. RDBMS transactions have 4 properties which are known as ACID. There are four major common concurrency problems which can be solved by using two models of concurrency: pessimistic and optimistic. We can bind our application with SQL Server by 5 types of Isolation levels.

当当前在同一组数据上发生两个事务时，就会发生并发，并且货币总是会导致数据不一致和事务的异常行为。 RDBMS事务具有4个称为ACID的属性。 通过使用两种并发模型可以解决四个主要的常见并发问题：悲观和乐观。 我们可以通过5种类型的隔离级别将应用程序与SQL Server绑定。

翻译自: https://habr.com/en/post/494584/

sql server 并发