Attention can view more fans exclusive blog~
Propagation mechanism for transactions
Support current transactions:
- REQUIRED (default) : Supports using the current transaction and creating a new one if it does not already exist.
- SUPPORTS: SUPPORTS the use of the current transaction. If the current transaction does not exist, the transaction is not used.
- MANDATORY: Specifies that the current transaction is MANDATORY. If the current transaction does not exist, an Exception is thrown.
Current transactions are not supported:
- REQUIRES_NEW: Creates a new transaction and suspends the current one if it exists.
- NOT_SUPPORTED: No transaction is executed and suspends the current transaction if it exists.
- NEVER: No transaction is executed. An Exception is thrown if a transaction exists.
Other:
- NESTED: execute a NESTED transaction if the current transaction exists. If the current transaction does not exist, it behaves as REQUIRED.
The isolation level of the transaction
| Transaction isolation level | Dirty read | Unrepeatable read | Phantom read |
|---|---|---|---|
| Read Uncommitted (read-uncommitted) | is | is | is |
| Read-Committed | no | is | is |
| Repeatable Read | no | no | is |
| Serialization (Serializable) | no | no | no |
Concurrency issues for transactions
- Dirty read: Transaction A reads the data updated by transaction B, and then TRANSACTION B rolls back the data
- Non-repeatable read: When transaction A reads the same data for many times, transaction B updates and commits the data during the process of reading the same data for many times. As A result, when transaction A reads the same data for many times, the results are inconsistent.
- Phantom reads: system administrators, A database of all the grades of the students from the specific scores to ABCDE level, but the system administrator B at this time by inserting A specific score record, when A system administrator A change after the found there is no change to come over, A record like the illusion, this is called magic to read.
Summary: It’s easy to confuse unrepeatable reading with magic reading. Unrepeatable reading focuses on modification, while magic reading focuses on addition or deletion. To solve the problem of unrepeatable reads, you only need to lock the rows that meet the condition. To solve phantom reads, you need to lock the table
MySQL InnoDB handles magic reading MVCC
InnoDB’s RR isolation level does not have or resolves illusionary problems and is not accurate. It should be said that it does not completely solve the problem of illusory reading. There is no illusion if normal SELECT snapshot reads are always performed within the same transaction. However, if the data in this transaction is read now or updated first and then snapshot read, phantom reads will occur.
Mysql-innodb-mvcc multiversion control