对数据库事务中不同隔离级别的一些澄清？ [英] Some clarifications on different Isolation level in database transaction?

问题描述

以下是维基百科的隔离文章中关于可重复阅读的声明

在此隔离级别中，基于锁的并发控制DBMS实现保持读取和写入锁定数据）
，直到事务结束。但是，范围锁不是管理的，因此可能会发生幻影读取现象（见下文）。

In this isolation level, a lock-based concurrency control DBMS implementation keeps read and write locks (acquired on selected data) until the end of the transaction. However, range-locks are not managed, so the phantom reads phenomenon can occur (see below).

如果我们以同一链接的REPEATABLE READS隔离级别的不可重复读取为例，根据我的理解trnsaction 1 begin
当第一次查询被触发时， SELECT * FROM users WHERE id = 1。 DBMS将保持用户表上的锁，直到和除非事务结束。
这里结束我的意思是当连接被回滚或提交时，不是在 SELECT * FROM users WHERE id = 1 完成。直到那时候
交易2会等待吗？

If we take the example of Non-repeatable reads with REPEATABLE READS Isolation level at the same link , as per my understanding trnsaction 1 begin when first query is fired i.e SELECT * FROM users WHERE id = 1. DBMS will keep the lock on the users table until and unless transaction gets end. here By end I mean is when connection gets rolledback or commited not on the completion of SELECT * FROM users WHERE id = 1. Till that time Transaction 2 will wait Right?

问题2：现在如果我们考虑隔离级别（在同一链接）

Question 2 :- Now if we consider the isolation level and thier behaviour as given below (at the same link)

Isolation level     Dirty reads   Non-repeatable   Phantoms
Read Uncommitted    may occur     may occur        may occur
Read Committed      -             may occur        may occur
Repeatable Read     -             may occur        -
Serializable        -             -                -

根据我的理解最可靠的是Serializable然后Repeatable Read然后Read Committed，但我还看到了使用Read Committed的应用。是因为
的串行化和可重复读的性能比Read Committed差，因为在序列化中它是顺序的，如果事务的
必须等待另一个事务释放锁定。 正确？为了获得最好的效果，我们可以使用隔离
level作为Read Committed with SELECT FOR UPDATE ）。如果我们想要读取，如果读取提交
隔离级别，则不知道如何实现幻像读取

As per my understanding Most reliable is Serializable then Repeatable Read and then Read Committed but still i have seen aplications using Read Committed. Is that because of performance of Serializable and Repeatable Read is bad in comparison to Read Committed because in serializable it will be sequential and in case of transaction has to wait for release of lock by another transaction. Right? So to get best of all three we can use isolation level as Read Committed with SELECT FOR UPDATE (to achieve repeatable read).Not sure how we can achieve phantom read if we want , in case of read commited isolation level?

推荐答案

Oracle不支持 REPEATABLE READ 隔离级别。但是，SQL Server确实会在事务选择的所有行上放置锁，直到它结束（即：它被提交或回滚）。所以你是正确的，这将确实使其他事务等待（如果他们正在更新锁定的数据），并且可能不利于并发。

Oracle does not support the REPEATABLE READ isolation level. However, SQL Server does - and it does place locks on all rows selected by the transaction until it ends (ie: it's committed or rolled back). So you are correct, this will indeed make other transactions wait (if they are updating the locked data) and can be detrimental to concurrency.

至于问题2：隔离级别越高，并发事务将执行的性能越差，因为它们必须等待更多的锁释放。我不知道你是什么意思，通过使用 SELECT FOR UPDATE 获得最好的所有三个，因为 SELECT FOR UPDATE 将在所有选定的行上放置行锁。

As for question 2: Yes, the higher the isolation level, the worse your concurrent transactions will perform because they have to wait for more locks to be released. I am not sure what you mean by "getting the best of all three" by using SELECT FOR UPDATE because SELECT FOR UPDATE will place row locks on all selected rows.

最后，这里是Oracle手册中关于幻像读取的引用：

And finally, here's a quote from Oracle's manual on phantom reads:

当一个事务重新运行查询，返回满足搜索条件的一组行并发现另一个提交的事务已插入满足条件的其他行时，会发生幻影读取。

[phantom reads occur when] a transaction reruns a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.

例如，事务查询员工数。五分钟后，它执行相同的查询，但现在的数字增加了一个，因为另一个用户插入一个新的雇用的记录。更多的数据满足查询条件比以前，但不像在模糊读取以前读取的数据不变。

For example, a transaction queries the number of employees. Five minutes later it performs the same query, but now the number has increased by one because another user inserted a record for a new hire. More data satisfies the query criteria than before, but unlike in a fuzzy read the previously read data is unchanged.

---

参考：

• 数据并发和一致性（Oracle）


• SET TRANSACTION LEVEL （SQL Server）

• Data Concurrency and Consistency (Oracle)
• SET TRANSACTION LEVEL (SQL Server)

这篇关于对数据库事务中不同隔离级别的一些澄清？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！