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

印刷约定

Convention	Meaning
Stylized Consolas Font	Used for blocks of code, commands and script examples. Text should be interpreted exactly as presented.
Consolas Font	Used for inline code, commands or examples. Text should be interpreted exactly as presented.
<italic font in brackets>	Italic texts set in angle brackets denote a variable requiring substitution for a real value.
Italic font	Used to denote the title of a book, article, or other publication.
Note	Additional information or caveats.
About screen capture and images	Selections and arrows default color is dark red (Red 192, Green 0 and Blue 0) Selections and arrows width is limited to 2 px Selections are rectangles Arrows are used to show relations between important parts of the screen capture Screen captures won’t comprise user desktop or unnecessary white space Highlights on text in an image should be done using yellow color with straight edges. Confidential information should be properly cut off, not blurred. You can simply cut the part of or color it so that it matches the background.

惯例	含义
	用于代码块，命令和脚本示例。 文字的解释应与提出的完全相同。
Consolas字体	用于内联代码，命令或示例。 文字的解释应与提出的完全相同。
<括号中的斜体>	尖括号中设置的斜体文本表示需要替换实数值的变量。
斜体字体	用于表示书籍，文章或其他出版物的标题。
注意	其他信息或警告。
关于屏幕截图和图像	选项和箭头的默认颜色为深红色（红色192，绿色0和蓝色0） 选择和箭头宽度限制为2像素 选择是矩形 箭头用于显示屏幕截图重要部分之间的关​​系 屏幕截图不会包含用户桌面或不必要的空白 图像中文本的高亮应使用带有直边的黄色完成。 机密信息应适当切断，而不是模糊不清。 您可以简单地切割或对其进行着色，使其与背景匹配。

Context

语境

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.

事务必须是一致的，即，根据定义的规则（例如主键唯一性或外键约束），使用数据库系统写入的任何数据都必须有效。

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.

最后但并非最不重要的一点是，由于一个数据库系统中可以同时运行多个事务，因此我们可以找到读取或写入同一数据对象（行，表，索引等）的事务。 它引入了一系列问题，不同的“事务隔离（级别）”倾向于响应这些问题。 事务隔离（级别）定义了数据库系统如何以及何时将任何事务所做的更改呈现给其他用户会话。

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.

当两个事务访问相同的记录并且都更新该记录时，就会发生这种现象。 下图总结了一个简单示例中可能发生的情况。

In this