Deep understanding of Oracle's transactional isolation

In the Oracle relational database, let's take a look at the following question:
A transaction: Select <cols> from T where ID > Ten and ID < 10000;
B Transaction: Update T Set id = 45000 WHERE id = 4501
Two transactions are performed in the following order:
A transaction: |--------------------------------|commit
B Business: |-------------|commit
That is, a transaction begins execution, and after a period of time the B transaction begins execution, but the B transaction executes and commits, and a transaction takes a while to complete. So the question comes, in this case, ask a transaction can get the correct result, there will be two transactions between the interference, how to interfere?
This is a typical isolation problem for relational database transactions, and there may be different performance for different databases (storage engines).

According to the above description, in Oracle, for example, its default database isolation level is read Committed (read-committed), transaction a holds a read lock (instantaneous shared read lock), B holds an exclusive write lock.
Read committed reads the official definition of the commit is achieved through "instantaneous shared read lock" and "exclusive write lock". Transactions that read data allow other transactions to continue to access the row's data, but uncommitted write transactions will prevent other transactions from accessing the row.
By reading the submitted definition, it seems as if the condition of the above question, a, b two transactions can be completed correctly and commit.

But relational database vendors, their products are often not completely in accordance with the specification to achieve, always attach some of their own unique things inside. Then we will analyze in detail how Oracle is handled, the internal process of SQL statement execution is quite complex, it is probably more explicit and easy to understand that the first run the execution plan, and then execute the SQL optimization and other policies, and then may be based on the keyword, lock processing, context switching operations, For example, the SELECT statement adds a read lock.

When executing a DML statement, Oracle adds an SN sequence number to each row, such as select <cols> from T where ID > and ID < 10000; this statement, which queries out nearly 1w of data, when performing a scan, sends Now the qualifying line will be added with an SN (the actual operation may be associated with an SN value in memory), the SN serial number is actually used as optimistic lock.
Then there is the possibility that the SELECT statement for transaction A has not finished executing, and when it executes to 2000, B begins an update T set id = 45000 WHERE id = 4501 transaction, because in Oracle, the level of write lock is higher than the read lock, So the UPDATE statement of the B transaction gets a write lock, executes successfully and commits, and surrenders the write lock.
Yourselves the start of the SELECT statement to 4501, if the B transaction has been commit, then a transaction will continue to execute, a successful commit, conversely, when a transaction executes to 4501 rows, b transaction is not commit, then the lock of the two in 4501 this data conflict, At this point the whole a transaction will go wrong.

In this case, for the DML SELECT statement, only read consistency, so the failure is simply an error to give up, not rollback.

However, the above description has a knowledge missing point, that is, the so-called MVCC (multi-version Concurrency control)---Based on multiple versions of the Concurrency Control Protocol (note: In contrast to MVCC, is a lock-based concurrency control, lock-based Concurrency Control). MVCC The biggest advantage is: Read no lock, read and write do not conflict. In an OLTP application that reads and writes less, read-write conflicts are very important, greatly increasing the concurrency of the system, and almost all of the RDBMS at this stage support MVCC.

In MVCC concurrency control, read operations can be divided into two categories: snapshot read (snapshot read) and current read. Snapshot reads, read the visible version of the record (possibly a historical version), without locking. The current read, read is the latest version of the record, and the current read returned records, will be added to the lock (one is the above mentioned SN serial number mode optimistic lock), to ensure that other transactions will no longer concurrently modify this record.

In Oracle, Undo is the so-called snapshot. If Undo is large enough, the select of a transaction returns the data before the statement that did not execute the update, and if Undo is not large enough, the select of a transaction will report a direct error without a return value because it is implicitly committed, so it does not rollback rollback.
This is Oracle's classic error ORA-01555 snapshot is too old.

Back to the original topic in the beginning, when executing the SELECT statement for transaction A, it was not explicitly stated whether the snapshot read or the current read. Therefore, for the sake of rigor, our final result is:
1. If a transaction performs a snapshot read, if Undo is large enough, a, B transaction can commit correctly, and the select of a transaction returns the data before the statement that did not execute the update; The select of a transaction will direct an error without a return value, in fact, the vast majority of the database read and write transactions belong to this situation;
2. If a transaction is performing the current read, two transactions can be executed correctly when the a transaction's select read operation is not in conflict with the update write operation of the B transaction (it does not read and write to the 4501 line at the same time), and the a transaction is likely to be faulted. It is not a transaction that is executed first, and two transactions must be committed successfully.

Deep understanding of Oracle's transactional isolation