A deep understanding of oracle transaction isolation and an in-depth understanding of oracle transactions

A deep understanding of oracle transaction isolation and an in-depth understanding of oracle transactions
In Oracle relational database, let's look at the following question:
A transaction: select <cols> from T where id> 10 and id <10000;
Transaction B: update T set id = 45000 where id = 4501
The two transactions are executed in the following order:
Transaction A: | -------------------------------- | commit
Transaction B: | ------------- | commit
That is to say, transaction A starts execution first, and transaction B starts execution later after A period of time. However, transaction B finishes execution first and commits the transaction commit. Transaction A is completed after A period of time. So the question is, in this case, can transaction A obtain the correct result, will there be interference between two transactions, and how can it interfere?
This is a typical Isolation Problem of relational database transactions. In addition, different databases (storage engines) may have different performances.

Based on the above description, Taking oracle as an example, its default database isolation level is read-committed (read-committed), and transaction A holds A read lock (instant shared read lock ), B holds a row of write locks.
The official definition of Read Committed is implemented through "instant shared Read lock" and "exclusive write lock. Transactions that read data allow other transactions to continue to access this row of data, but uncommitted write transactions will prohibit other transactions from accessing this row.
According to the definition of Read committed, it seems that according to the above condition, both transactions A and B can be correctly completed and committed by commit.

However, relational database vendors often do not fully implement their products in accordance with the specifications. They will always add something unique to them. Next, let's analyze in detail how oracle handles the SQL statement execution. The internal process of SQL statement execution is quite complex. What is explicit and easy to understand is to run the execution plan first, then execute the SQL optimization and other policies, and then perform lock processing and context switching based on the keywords. For example, a read lock will be applied to the select statement.

When a DML statement is executed, Oracle adds an sn for each row, for example, select <cols> from T where id> 10 and id <10000; this statement, nearly pieces of data are queried. When scanning is performed, an sn is added to the row that meets the condition (the actual operation may be associated with a sn value in the memory ), this sn number is actually used as an optimistic lock.
The following may occur: The select statement of transaction A has not been executed. When it reaches 2000, B started an update T set id = 45000 where id = 4501 transaction. Because the write lock level in oracle is higher than the read lock level, the update Statement of transaction B obtains the write lock at this time, after successful execution and commit, the write lock is handed over.
When the first select statement is executed to 4501, if transaction B has been committed, transaction A will continue to execute and commit will be successful. Otherwise, when transaction A is executed to 4501 rows, transaction B has not been committed, so the lock on the data in the 4501 conflict, then the entire A transaction will fail.

Here we insert a statement. For DML select statements, only read consistency exists. If it fails, it only reports an error and does not roll back.

However, there is a missing Point in the above description, that is, the so-called MVCC (Multi-Version Concurrency Control)-based Multi-Version Concurrency Control Protocol (Note: Compared with MVCC, lock-Based Concurrency Control ). The biggest benefit of MVCC is that read does not lock, and read/write does not conflict. In OLTP applications with more reads and less writes, read/write conflicts are very important, greatly increasing the system's concurrency performance. At present, almost all RDBMS support MVCC.

In MVCC concurrency control, read operations can be divided into two types: snapshot read and current read ). Snapshot reading reads the visible version of the record (which may be a previous version) without locking. The current read reads the latest version of the record, and all records returned from the current read will be locked (one is the optimistic lock of the sn serial number method mentioned above ), ensure that other transactions do not concurrently modify this record.

In oracle, undo is a so-called snapshot. If the undo statement is large enough, the select statement of transaction A returns the data before the update statement is executed. If the undo statement is not large enough, the select statement of transaction A directly reports an error and no return value, because it is an implicit commit, so it does not roll back.
This is oracle's classic error ORA-01555 snapshot is too old.

Back to the original question at the beginning, when the select statement of transaction A is executed, it is not explicitly indicated whether it is A snapshot read or the current read. Therefore, for the sake of rigor, our final result is:
1. if transaction A executes snapshot reads, if undo is large enough, transactions A and B can be correctly committed by commit, and the select statement of transaction A returns data before the statement where update is not executed; if the undo is not large enough, B transaction can be correctly commit, the select statement of A transaction will directly report an error and no return value. In fact, most of the database read/write transactions are in this situation;
2. if transaction A executes the current read, when the select read operation of transaction A does not conflict with the update write operation of transaction B (the row where transaction A is not read and written at the same time ), both transactions can be correctly executed. On the contrary, transaction A may fail. Not as long as transaction A is executed first, the two transactions will be successfully committed by commit.