SQL “读已提交”和“可重复读”之间的区别

Difference between "read commited" and "repeatable read"

提问by Fore

I think the above isolation levels are so alike. Could someone please describe with some nice examples what the main difference is ?

我认为上述隔离级别是如此相似。有人可以用一些很好的例子来描述主要区别是什么吗？

回答by Remus Rusanu

Read committed is an isolation level that guarantees that any data read was committedat the moment is read. It simply restricts the reader from seeing any intermediate, uncommitted, 'dirty' read. It makes no promise whatsoever that if the transaction re-issues the read, will find the Samedata, data is free to change after it was read.

已提交读是一种隔离级别，可确保读取此时已提交的任何数据读取。它只是限制读者看到任何中间的、未提交的、“脏”读。它不保证如果事务重新发出读取，将找到相同的数据，数据在读取后可以自由更改。

Repeatable read is a higher isolation level, that in addition to the guarantees of the read committed level, it also guarantees that any data read cannot change, if the transaction reads the same data again, it will find the previously read data in place, unchanged, and available to read.

可重复读是一种更高的隔离级别，即除了保证读提交级别外，还保证读到的任何数据都不会发生变化，如果事务再次读取相同的数据，会在原地找到之前读过的数据，不变，可供阅读。

The next isolation level, serializable, makes an even stronger guarantee: in addition to everything repeatable read guarantees, it also guarantees that no newdatacan be seen by a subsequent read.

接下来的隔离级别，序列化，使得一个更强大的保障：除了所有重复的读取担保，这也保证了没有新的数据可以通过后续读取待观察。

Say you have a table T with a column C with one row in it, say it has the value '1'. And consider you have a simple task like the following:

假设您有一个表 T，其中列 C 中有一行，假设它的值为“1”。并考虑您有一个简单的任务，如下所示：

BEGIN TRANSACTION;
SELECT * FROM T;
WAITFOR DELAY '00:01:00'
SELECT * FROM T;
COMMIT;

That is a simple task that issue two reads from table T, with a delay of 1 minute between them.

这是一个简单的任务，从表 T 发出两次读取，它们之间有 1 分钟的延迟。

• under READ COMMITTED, the second SELECT may return anydata. A concurrent transaction may update the record, delete it, insert new records. The second select will always see the newdata.
• under REPEATABLE READ the second SELECT is guaranteed to display at least the rows that were returned from the first SELECT unchanged. New rows may be added by a concurrent transaction in that one minute, but the existing rows cannot be deleted nor changed.
• under SERIALIZABLE reads the second select is guaranteed to see exactlythe same rows as the first. No row can change, nor deleted, nor new rows could be inserted by a concurrent transaction.

• 在 READ COMMITTED 下，第二个 SELECT 可能返回任何数据。并发事务可以更新记录、删除记录、插入新记录。第二个选择将始终看到新数据。
• 在 REPEATABLE READ 下，第二个 SELECT 保证至少显示从第一个 SELECT 返回的行不变。一分钟内并发事务可能会添加新行，但不能删除或更改现有行。
• 在 SERIALIZABLE 读取下，第二个选择保证看到与第一个完全相同的行。并发事务不能更改或删除任何行，也不能插入新行。

If you follow the logic above you can quickly realize that SERIALIZABLE transactions, while they may make life easy for you, are always completely blockingevery possible concurrent operation, since they require that nobody can modify, delete nor insert any row. The default transaction isolation level of the .Net System.Transactionsscope is serializable, and this usually explains the abysmal performance that results.

如果你遵循上面的逻辑，你会很快意识到 SERIALIZABLE 事务，虽然它们可能让你的生活更轻松，但总是完全阻塞每一个可能的并发操作，因为它们要求没有人可以修改、删除或插入任何行。.NetSystem.Transactions范围的默认事务隔离级别是可序列化的，这通常解释了导致的糟糕性能。

And finally, there is also the SNAPSHOT isolation level. SNAPSHOT isolation level makes the same guarantees as serializable, but not by requiring that no concurrent transaction can modify the data. Instead, it forces every reader to see its own version of the world (it's own 'snapshot'). This makes it very easy to program against as well as very scalable as it does not block concurrent updates. However, that benefit comes with a price: extra server resource consumption.

最后，还有 SNAPSHOT 隔离级别。SNAPSHOT 隔离级别提供与可序列化相同的保证，但不要求没有并发事务可以修改数据。相反，它迫使每个读者看到自己的世界版本（它自己的“快照”）。这使得编程变得非常容易并且非常可扩展，因为它不会阻止并发更新。然而，这种好处是有代价的：额外的服务器资源消耗。

Supplemental reads:

补充内容：

• Isolation Levels in the Database Engine
• Concurrency Effects
• Choosing Row Versioning-based Isolation Levels

• 数据库引擎中的隔离级别
• 并发效应
• 选择基于行版本控制的隔离级别

回答by Hazel_arun

Repeatable Read

可重复读取

The state of the database is maintained from the start of the transaction. If you retrieve a value in session1, then update that value in session2, retrieving it again in session1 will return the same results. Reads are repeatable.

从事务开始就维护数据库的状态。如果您在 session1 中检索一个值，然后在 session2 中更新该值，在 session1 中再次检索它会返回相同的结果。读取是可重复的。

session1> BEGIN;
session1> SELECT firstname FROM names WHERE id = 7;
Aaron

session2> BEGIN;
session2> SELECT firstname FROM names WHERE id = 7;
Aaron
session2> UPDATE names SET firstname = 'Bob' WHERE id = 7;
session2> SELECT firstname FROM names WHERE id = 7;
Bob
session2> COMMIT;

session1> SELECT firstname FROM names WHERE id = 7;
Aaron

Read Committed

读已提交

Within the context of a transaction, you will always retrieve the most recently committed value. If you retrieve a value in session1, update it in session2, then retrieve it in session1again, you will get the value as modified in session2. It reads the last committed row.

在事务的上下文中，您将始终检索最近提交的值。如果您在 session1 中检索一个值，在 session2 中更新它，然后再次在 session1 中检索它，您将获得在 session2 中修改的值。它读取最后提交的行。

session1> BEGIN;
session1> SELECT firstname FROM names WHERE id = 7;
Aaron

session2> BEGIN;
session2> SELECT firstname FROM names WHERE id = 7;
Aaron
session2> UPDATE names SET firstname = 'Bob' WHERE id = 7;
session2> SELECT firstname FROM names WHERE id = 7;
Bob
session2> COMMIT;

session1> SELECT firstname FROM names WHERE id = 7;
Bob

Makes sense?

说得通？

回答by Mo Zaatar

Simply the answer according to my reading and understanding to this thread and @remus-rusanu answer is based on this simple scenario:

根据我对这个线程的阅读和理解，答案很简单，@remus-rusanu 的答案基于这个简单的场景：

There are two processes A and B. Process B is reading Table X Process A is writing in table X Process B is reading again Table X.

有两个进程 A 和 B。 进程 B 正在读取表 X 进程 A 正在写入表 X 进程 B 正在再次读取表 X。

• ReadUncommitted: Process B can read uncommitted data from process A and it could see different rows based on B writing. No lock at all
• ReadCommitted: Process B can read ONLY committed data from process A and it could see different rows based on COMMITTED only B writing. could we call it Simple Lock?
• RepeatableRead: Process B will read the same data (rows) whatever Process A is doing. But process A can change other rows. Rows level Block
• Serialisable: Process B will read the same rows as before and Process A cannot read or write in the table. Table-level Block
• Snapshot: every process has its own copy and they are working on it. Each one has its own view

• ReadUncommitted：进程 B 可以从进程 A 读取未提交的数据，并且可以根据 B 的写入看到不同的行。根本没有锁
• ReadCommitted：进程 B 只能从进程 A 读取已提交的数据，并且它可以看到基于仅提交的 B 写入的不同行。我们可以称之为简单锁吗？
• RepeatableRead：无论进程 A 正在做什么，进程 B 都将读取相同的数据（行）。但是进程 A 可以更改其他行。行级块
• Serialisable：进程 B 将读取与以前相同的行，进程 A 无法在表中读取或写入。表级块
• 快照：每个进程都有自己的副本，他们正在处理它。每个人都有自己的看法

回答by Chris Gillum

Old question which has an accepted answer already, but I like to think of these two isolation levels in terms of how they change the locking behavior in SQL Server. This might be helpful for those who are debugging deadlocks like I was.

老问题已经有一个公认的答案，但我喜欢从它们如何改变 SQL Server 中的锁定行为的角度来考虑这两个隔离级别。这可能对那些像我一样调试死锁的人有帮助。

READ COMMITTED (default)

读已提交（默认）

Shared locks are taken in the SELECT and then released when the SELECT statement completes. This is how the system can guarantee that there are no dirty reads of uncommitted data. Other transactions can still change the underlying rows after your SELECT completes and before your transaction completes.

共享锁在 SELECT 中获取，然后在 SELECT 语句完成时释放。这就是系统如何保证没有未提交数据的脏读。在您的 SELECT 完成之后和您的交易完成之前，其他交易仍然可以更改基础行。

REPEATABLE READ

可重复阅读

Shared locks are taken in the SELECT and then released only after the transaction completes. This is how the system can guarantee that the values you read will not change during the transaction (because they remain locked until the transaction finishes).

共享锁在 SELECT 中获取，然后仅在事务完成后释放。这就是系统如何保证您读取的值在事务期间不会更改（因为它们在事务完成之前保持锁定状态）。

回答by vkrishna17

Trying to explain this doubt with simple diagrams.

试图用简单的图表来解释这个疑问。

Read Committed:Here in this isolation level, Transaction T1 will be reading the updated value of the X committed by Transaction T2.

已提交读：在此隔离级别中，事务 T1 将读取事务 T2 提交的 X 的更新值。

Repeatable Read:In this isolation level, Transaction T1 will not consider the changes committed by the Transaction T2.

可重复读：在这个隔离级别，事务 T1 不会考虑事务 T2 提交的更改。

回答by Ivan Pavi?i?

I think this picture can also be useful, it helps me as a reference when I want to quickly remember the differences between isolation levels (thanks to kudvenkaton youtube)

我觉得这张图也很有用，当我想快速记住隔离级别之间的差异时，它可以帮助我作为参考（感谢youtube上的kudvenkat）

回答by u861799

Please note that, the repeatablein repeatable read regards to a tuple, but not to the entire table. In ANSC isolation levels, phantom readanomaly can occur, which means read a table with the same where clause twice may return different return different result sets. Literally, it's not repeatable.

请注意的是，重复在重复读关于一个元组，而不是整个表。在 ANSC 隔离级别中，可能会出现幻读异常，这意味着读取具有相同 where 子句的表两次可能会返回不同的返回不同结果集。从字面上看，它是不可重复的。

回答by Sanjeev Dhiman

My observation on initial accepted solution.

我对最初接受的解决方案的观察。

Under RR (default mysql) - If a tx is open and a SELECT has been fired, another tx can NOT delete any row belonging to previous READ result set until previous tx is committed (in fact delete statement in the new tx will just hang), however the next tx can delete all rowsfrom the table without any trouble. Btw, a next READ in previous tx will still see the old data until it is committed.

在 RR（默认 mysql）下 - 如果一个 tx 是打开的并且一个 SELECT 已经被触发，另一个 tx 不能删除属于前一个 READ 结果集的任何行，直到前一个 tx 被提交（实际上新 tx 中的 delete 语句将挂起） ，但是下一个 tx 可以毫无困难地从表中删除所有行。顺便说一句，在前一个 tx 中的下一个 READ 仍然会看到旧数据，直到它被提交。