(1) database transactions and isolation-Concept

Four major features of database transactions: acid (atomic, consistency, isolation, durability)
Here, we mainly consider consistency and isolation. To improve the transaction processing efficiency, we usually execute multiple transactions concurrently, which is a very important 'concurrency control' in the database '. To put it simply, concurrent transaction execution has the following problems:
•Write lost)
:
For example, transaction a changes the value of X to 10, and then transaction a changes the value of Y to 20. At this time, a re-reads X and finds that the updated data seems to be missing.
•Dirty read ):
For example, the uncommitted (still cached) data of transaction a is read by transaction B. If transaction a fails to roll back, the data read by transaction B is incorrect;
•Non-Repeatable read)
:
For example, transaction a reads the total value of data at two places. In the first read, the total value is 100, and then transaction B changes the total data to 200. When transaction a reads the data again, it turns out that the total value is 200, transaction a data is disordered.
•Phantom read)
:
Similar to non-repeatable read, it is also a problem of multiple read inconsistencies in the same transaction. However, non-repeatable read is inconsistent because the dataset to be retrieved is changed (such as the total data ), however, the inconsistency of the data to be read by phantom read is not the change of the data set to be read, but the change of its conditional data set. For example, select account. ID where account. name = "ppgogo *": the first read contains six qualified IDs, transaction B changes the account name from "DD" to "ppgo1", and seven data records are obtained.
Some of these four problems can be tolerated, and some problems will greatly reduce concurrency. Therefore, in order to meet different requirements, SQL 92 sets four isolation levels. The higher the isolation level, the better the data consistency, and the lower the database concurrency.
•Serializable (full serialization, level 3) provides strict transaction isolation:
It requires the transaction to be serialized. The transaction can only be executed one by one, but cannot be executed concurrently. If transaction serialization is not possible only through the "Row-Level Lock", other mechanisms must be used to ensure that the newly inserted data is not accessed by the transaction that just performs the query operation.
•Repeatable read (Repeatable read, level 2 ):
Duplicate and dirty reading are prohibited, but phantom data may sometimes appear. This can be achieved through the "shared read lock" and "exclusive write lock. The transaction that reads data will prohibit the write transaction (but allow the read transaction), and the write transaction will prohibit any other transactions.
•Read committed (Authorized read, level 1 ):
Repeated reads are allowed, but dirty reads are not allowed. This can be achieved 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.
•Read uncommitted (Unauthorized Read, level 0 ):
Dirty reads are allowed, but update loss is not allowed. If a transaction has started writing data, the write operation on the other data is not allowed at the same time, but other transactions are allowed to read this row of data. This isolation level can be achieved through the exclusive write lock.