“已提交读"与“已提交读"之间的区别和“可重复阅读"
我觉得上面的隔离级别太相似了.有人可以用一些很好的例子来描述主要区别是什么吗?
I think the above isolation levels are so alike. Could someone please describe with some nice examples what the main difference is ?
推荐答案
已提交读是一种隔离级别，可保证读取当前已已提交的任何数据.它只是限制读者看到任何中间的、未提交的、脏"读.它不承诺如果事务重新发出读取，将找到相同数据，数据在读取后可以自由更改.
Read committed is an isolation level that guarantees that any data read was committed at 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 Same data, 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 new data can be seen by a subsequent read.
假设您有一个表 T，其中 C 列有一行，假设它的值为1".并考虑您有一个简单的任务，如下所示:
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:
BEGIN TRANSACTION;
SELECT * FROM T;
WAITFOR DELAY '00:01:00'
SELECT * FROM T;
COMMIT;
这是一个简单的任务，从表 T 发出两次读取，它们之间有 1 分钟的延迟.
That is a simple task that issue two reads from table T, with a delay of 1 minute between them.
- 在 READ COMMITTED 下，第二个 SELECT 可能返回任何数据.并发事务可以更新记录、删除记录、插入新记录.第二个选择将始终看到新数据.
- 在 REPEATABLE READ 下，第二个 SELECT 保证至少显示从第一个 SELECT 未更改返回的行.并发事务可以在那一分钟内添加新行，但不能删除或更改现有行.
- 在 SERIALIZABLE 读取下，第二个选择保证看到完全与第一个相同的行.并发事务不能更改或删除任何行，也不能插入新行.
- under READ COMMITTED, the second SELECT may return any data. A concurrent transaction may update the record, delete it, insert new records. The second select will always see the new data.
- 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 exactly the same rows as the first. No row can change, nor deleted, nor new rows could be inserted by a concurrent transaction.
如果你遵循上面的逻辑，你会很快意识到 SERIALIZABLE 事务，虽然它们可能让你的生活变得轻松，但总是完全阻塞所有可能的并发操作，因为它们要求没有人可以修改，删除或插入任何行..Net System.Transactions 范围的默认事务隔离级别是可序列化的，这通常解释了导致的糟糕性能.
If you follow the logic above you can quickly realize that SERIALIZABLE transactions, while they may make life easy for you, are always completely blocking every possible concurrent operation, since they require that nobody can modify, delete nor insert any row. The default transaction isolation level of the .Net System.Transactions scope is serializable, and this usually explains the abysmal performance that results.
最后，还有 SNAPSHOT 隔离级别.SNAPSHOT 隔离级别提供与可序列化相同的保证，但不要求并发事务不能修改数据.相反，它迫使每个读者看到自己的世界版本(它自己的快照").这使得编程变得非常容易并且非常可扩展，因为它不会阻止并发更新.然而，这种好处是有代价的:额外的服务器资源消耗.
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.
补充阅读:
- 数据库引擎中的隔离级别
- 并发效应
- 选择基于行版本控制的隔离级别
相关文章