Database transactions (III)

Transaction isolation level 
Although the database provides users with a DML Lock operation method, it is very troublesome to directly use lock management. Therefore, the database provides users with an automatic lock mechanism. As long as the User specifies the transaction isolation level of the session, the database will analyze the SQL statements in the transaction, and then automatically add the appropriate lock for the data resources of the transaction operation. In addition, the database will maintain these locks. When there are too many locks on a resource, the database will automatically upgrade the lock to improve the system running performance, this process is completely transparent to users.
ANSI/iso SQL 92 defines four levels of transaction isolation levels. In the same data environment, the same input is used to perform the same work, depending on different isolation levels, it can lead to different results. Different transaction isolation levels have different capabilities to solve data concurrency problems.
Table 1 solutions to concurrency problems at the transaction isolation level

Isolation level	Dirty read	Non-repeated read	Phantom read	Category 1 lost updates	Type 2 lost updates
READ UNCOMMITED	Allow	Allow	Allow	Not Allowed	Allow
READ COMMITTED	Not Allowed	Allow	Allow	Not Allowed	Allow
REPEATABLE READ	Not Allowed	Not Allowed	Allow	Not Allowed	Not Allowed
SERIALIZABLE	Not Allowed	Not Allowed	Not Allowed	Not Allowed	Not Allowed

The isolation level of transactions is opposite to the database concurrency. Generally, read uncommited isolation-level databases have the highest concurrency and throughput, while SERIALIZABLE isolation-level databases have the lowest concurrency.

SQL 92 defines READ UNCOMMITED to provide non-blocking READ capability. Although Oracle also supports READ UNCOMMITED, it does not support dirty READ, because Oracle uses a multi-version mechanism to completely solve the problem of reading dirty data during non-blocking reading and ensure read consistency, the read committed isolation level of Oracle has already met the repeatable read isolation level of SQL 92.

In SQL 92, REPEATABLE READ is recommended to ensure data READ consistency. However, you can select an appropriate isolation level based on application requirements.