MySQL Transaction learning

1. Business

A logical set of operations in which the individual logical units that make up this set of operations either succeed together or fail together.

2. Transaction characteristics

Atomicity: Emphasizing the indivisibility of affairs.
Consistency: It is emphasized that the integrity of the data should be consistent before and after the execution of the transaction.
Isolation: The execution of a transaction should not be disturbed by other transactions.
Persistence: Once a transaction ends (commits/rolls back) The data is persisted to the database.

3. Security issues for transactions

Dirty reads: One transaction reads data that has not yet been committed by another transaction.
Non-REPEATABLE READ: One transaction reads the data of an update that has been committed by another transaction, causing inconsistencies in the results of multiple queries in the current transaction.
Virtual read/Phantom read: One transaction reads the insert data that has been committed by another transaction, resulting in inconsistent query results multiple times in the current transaction.

4. Isolation level of a transaction

MySQL Isolation level: REPEATABLE READ Oracle Isolation LEVEL: Read Committed
* READ UNCOMMITTED
Behavior: READ UNCOMMITTED, dirty read, non-repeatable read, virtual read can occur.
Cause: The read is not locked, but when the data is updated, the row-level shared lock (other transactions cannot be changed, but can be read, resulting in dirty reads), released at the end of the transaction.
* Read Committed
Behavior: Read Committed. Avoid dirty reads. But non-repeatable reads and false reads can occur.
Cause: This isolation level indicates that the read data is submitted successfully, resolves the dirty read problem, the solution is to write data plus row-level exclusive lock, so that the write process is unreadable, until the transaction is finished to release the exclusive lock, read the data to add row-level shared lock, so read the time is not written, However, once the line has been read, the shared lock is released. Although dirty reads are handled in this mode, there is no problem with missing updates and non-repeatable reads.
* REPEATABLE READ
Phenomenon: repeat read. Avoid dirty reading, not repeat read. But there is a possibility of a false reading.
Reason: In this isolation level can be repeated reading data, as the name implies, to solve the problem of non-repeatable reading, but also solve the problem of update loss. Workaround: Add row-level exclusive locks to the written data, end-of-transaction release, add row-level shared locks to the read data, and release after the transaction ends. This mode still does not deal with the Phantom reading problem
* Serializable
Phenomenon: Serialization. Avoid dirty reading, non-repetition of reading, the occurrence of virtual reading.
Cause: The transaction read data is added to the table-level shared lock, the transaction write data is added to the table-level exclusive lock, the magic reading problem has been resolved

5. Resolve

The isolation level creates a problem

READ UNCOMMITTED
Dirty Read
Read Committed
Dirty read non-repeatable read
REPEATABLE READ
Non-REPEATABLE READ
Serializable
Virtual Read/Phantom read

MySQL Transaction learning