Shulou(Shulou.com)06/01 Report--

Transfer to: http://www.bkjia.com/oracle/925189.html

In-depth understanding of transaction isolation of oracle

In the Oracle relational database, let's first look at the following question:

A transaction: select from T where id > 10 and id

< 10000; B事务：update T set id = 45000 where id = 4501 两个事务按下面的顺序执行： A事务：|--------------------------------|commit B事务： |-------------|commit 也就是A事务先开始执行，过一段时间B事务再开始执行，但是B事务先执行完并commit提交了，A事务又过了一段时间才完成。那么问题来了，在这种情况下，问A事务能不能取得正确的结果，两个事务之间会不会有干扰，怎么干扰？ 这是一个典型的关系型数据库事务的隔离性问题，而且，针对不同的数据库（存储引擎），可能会有不同的表现。 根据上面的描述，以oracle为例，它的缺省数据库隔离级别是读已提交（read-committed），事务A持有一个读锁（瞬间共享读锁），B持有一个排它写锁。 Read Committed读已提交的官方定义是，通过"瞬间共享读锁"和"排他写锁"实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。 按照读已提交的定义，似乎按上题的条件，A，B两个事务都能够正确完成并commit提交。 但关系数据库厂商，它们的产品往往不会完完全全的按照规范来实现，总会附加一些自己特有的东西在里面。那么我们接下来详细分析一下，oracle是怎样处理的，SQL语句执行的内部过程相当复杂，大概比较显式和通俗易懂的是，先运行执行计划，然后执行SQL优化等策略，接着可能根据关键字，进行加锁处理，上下文切换等操作，比如select语句就会加一个读锁。 在执行DML语句时，Oracle会给每一行增加一个sn序列号，比如select from T where id >

10 and id < 10000; this statement queries nearly 1w pieces of data, and when performing a scan, a sn will be added to the qualified row (in practice, it may be associated with a certain sn value in memory), and this sn sequence number is actually used as an optimistic lock.

Then it is possible that the select statement of transaction A has not yet been executed, and when the execution reaches 2000, B starts a transaction with update T set id = 45000 where id = 4501. Because in oracle, the write lock is higher than the read lock, so the update statement of transaction B acquires the write lock, completes the execution successfully, and commit, and surrenders the write lock.

When the first select statement is executed to 4501, if the B transaction has been commit, then the A transaction will continue to execute and successfully commit, on the contrary, when the A transaction executes to 4501 rows, the B transaction has not yet commit, then the lock of the two data conflicts at 4501, and the whole A transaction will make an error.

To insert a word here, for the select statement of DML, it only has read consistency, so if you fail, you will only give up with an error and will not be rolled back.

However, there is one knowledge missing point in the above description, which is the so-called MVCC (Multi-Version Concurrency Control)-based concurrency control protocol based on multiple versions. (note: as opposed to MVCC, it is lock-based concurrency control, Lock-Based Concurrency Control). The biggest advantage of MVCC is that there is no lock on reading and no conflict between reading and writing. In OLTP applications with more reading and less writing, non-conflict between read and write is very important, which greatly increases the concurrency performance of the system. At present, almost all RDBMS supports MVCC.

In MVCC concurrency control, read operations can be divided into two categories: snapshot read (snapshot read) and current read (current read). Snapshot reading, read the visible version of the record (it may be the historical version), without locking. The current read is the latest version of the record, and the record returned by the current read will be locked (one is the optimistic lock of the sn sequence number mentioned above) to ensure that other transactions will not modify the record concurrently.

In oracle, undo is the so-called snapshot. If the undo is large enough, the select of the A transaction returns the data before the statement of the update is executed; if the undo is not large enough, the select of the A transaction will directly report an error without a return value, because it is an implicit commit, so the rollback will not be rolled back.

This is oracle's classic error ORA-01555 snapshot is too old.

Going back to the original topic at the beginning, when executing the select statement of transaction A, it did not specify whether it was a snapshot read or a current read. So, for the sake of rigour, our end result is:

1. If the A transaction performs a snapshot read, if the undo is large enough, the select of the A transaction can commit commit correctly, and the select of the A transaction returns the data before the update statement is executed; if the undo is not large enough, the select of the commit,A transaction will directly report an error and no return value. In fact, the vast majority of read and write transactions in the database are in this case.

two。 If the A transaction performs the current read, then when the select read operation of the A transaction and the update write operation of the B transaction do not conflict (the 4501 line will not be read and written at the same time), both transactions will execute correctly; on the contrary, the A transaction may go wrong. It is not true that as long as the A transaction is executed first, the two transactions will be able to successfully commit the commit.

Snapshot reading: select

Current read: select * from xx for update; update; delete

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.