Introduction to things and isolation levels

features of the transaction:
atomicity (atomicity)All operations in a transaction are either executed entirely or not.
Consistency (consistency)The database is in a consistent state before and after the transaction executes.
Isolation (Isolation)Multiple transaction executions do not feel that other transactions are executing.
persistence (Durability)Once a transaction is committed, all modifications will be permanently saved, even if the system failure is not lost.
Isolation Level:

read do not submit--readuncommittedDirty reads, possibly reading dirty data. In the following example, T1 reads data 100, updates it to 200, and then T2 reads data 200 and updates it to 600. After T1 rollback or commit, the T2 read and overwrite data are invalid, and the dirty data is read.



Read Submit--readcommittedYou can avoid dirty reads, but they will cause non-repeatable reads, and each time you read the data may be different. As shown below: T1 reads 100 with a row lock and then updates it to 200, at the same time T2 want to read the row data, but T1 does not release the lock, T2 can only wait. T1 rollback Live Commit after releasing the row lock, T2 get lock, can read the data. However, the T2 session is not repeatable read, it is possible to read 200, and then T1 modified to 300,T2 read again, read the data is 300, how to avoid non-repeatable reading will be used to the next isolation level.



Repeatable Read--repeatablereadYou can avoid non-repeatable reads, but there are phantom reads, and more records are read the second time. As follows: T1 locks the table, reads data 100 for the first time, then T2 locks the data table, adds data 300, and T2 submits the request. After that, T1 the table with a lock, and the second read of data 100 and 300, which is the Phantom read.



Serializable --serializablePerformance is low, but can avoid all of the above mentioned problems.





Here is a summary chart of the isolation levels of things, which shows the individual isolation levels and the corresponding issues: