关于数据库事务中不同隔离级别的一些澄清？
以下是维基百科的隔离文章中关于可重复读取的声明
在此隔离级别中，基于锁的并发控制 DBMS 实现会保留读锁和写锁（在选定数据上获取） 直至交易结束。但是，范围锁不受管理，因此可能会发生幻读现象（见下文）。
我的问题是事务分别何时开始和结束。
如果我们以同一链接上具有可重复读取隔离级别的不可重复读取为例，根据我的理解 trnsaction 1 begin 当第一个查询被触发时，即 SELECT * FROM users WHERE id = 1. DBMS 将保持对 users 表的锁定，直到事务结束。 这里结束我的意思是当连接回滚或提交时，而不是在SELECT * FROM users WHERE id = 1完成时。到那时 事务 2 会等待，对吗？
问题 2：- 现在，如果我们考虑下面给出的隔离级别和行为（在同一链接），
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 - - -
根据我的理解，最可靠的是可序列化，然后是可重复读取，然后是已提交读，但我仍然看到使用已提交读的应用程序。那是因为 与已提交读取相比，可串行化和可重复读取的性能较差，因为在可串行化中，它将是顺序读取的，并且以防万一 事务必须等待另一个事务释放锁。 对吗？因此，为了充分利用这三者，我们可以使用隔离 使用SELECT FOR UPDATE将级别设置为已提交读（以实现可重复读取）。如果需要，在已提交读的情况下，不确定如何实现幻读 隔离级别？
Below is the statement written from Wikipedia's Isolation article about REPEATABLE READS
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).
My question here is when does the the transaction begin and end respectively.
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?
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 - - -
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
Oracle 不支持
REPEATABLE READ隔离级别。然而，SQL Server 确实如此，并且它确实在事务选择的所有行上放置锁，直到事务结束（即：提交或回滚）。所以你是对的，这确实会让其他事务等待（如果它们正在更新锁定的数据）并且可能不利于并发。至于问题2：是的，隔离级别越高，并发事务的性能就越差，因为它们必须等待更多的锁被释放。我不确定您所说的通过使用 SELECT FOR UPDATE 来“获得三者中最好的”是什么意思，因为 SELECT FOR UPDATE 会在所有选定的行上放置行锁。
最后，引用 Oracle 幻读手册中的一段话：
参考：
设置事务级别(SQL Server)Oracle does not support the
REPEATABLE READisolation 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.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 UPDATEbecauseSELECT FOR UPDATEwill place row locks on all selected rows.And finally, here's a quote from Oracle's manual on phantom reads:
Reference:
SET TRANSACTION LEVEL(SQL Server)