Re-understanding of MYSQL database transactions

The concept of transaction: a series of operations performed by a single logical unit of work, either completely executed or not executed at all. A simple understanding is that every data operation is performed in the logical unit of work of a transaction.

The four attributes of transactions: ACID.

A: atomic, atomicity. Atomicity emphasizes indivisibility and abstracts the process of executing the database into an entity object. Then this object either exists or does not exist. When executed, either the entire process is completed or not executed.

This advantage ensures the integrity and correctness of the data. The most real example is that the same bank card suddenly breaks during the transfer process, and the transaction cannot be completed and is rolled back. Finally, ensure that the data is in its original state, so that the balance in your card will not be reduced.

C: Consistent. The understanding of consistency and atomicity is basically the same, and they are both to ensure the integrity of the data. For example, a transaction is composed of four operations: addition, deletion, modification, and query. Only when all four operations are executed successfully can it remain successful. Status, as long as an operation fails to execute, it is a failure status, ensuring the unity of the status.

I: Insulation,Isolation. Isolation exists for concurrent transactions. Any transactions are isolated from each other and do not affect each other. In the case of high isolation, when a transaction accesses another transaction, it either accesses the state before the transaction is executed or the state after the execution is completed. It does not access the state between the execution of the first transaction, but this leads to data concurrency. To reduce the transaction isolation level, all transactions must be weighed based on actual requirements when designing the database.

Transaction isolation can be divided from shallower to deeper: READ UNCOMMITTED (uncommitted read)---READ COMMITTED (committed read)---REPEATABLE READ (repeatable read)- --SERIALIZABLE (Serialization)

(1) SERIALIZABLE (Serialization)

Add range locks (such as table locks, page locks, etc. , I haven’t done any in-depth research on range lock) until transaction A ends. This prevents other transaction B from insert, update and other operations within this range.
Problems such as phantom reads, dirty reads, and non-repeatable reads will not occur.

(2) REPEATABLE READ (repeatable read)

For the records read out, add a shared lock until the end of transaction A . Other transaction B's attempts to modify this record will wait until transaction A ends.

Possible problems: When executing a range query, phantom reads may occur.

(3) READ COMMITTED (commit read)

When reading data in transaction A, add a shared lock to the record, but the read The end is released immediately. Other transaction B's attempts to modify this record will wait until the reading process in A ends, without the end of the entire transaction A. Therefore, the reading results of the same record in different stages of transaction A may be different. ,

Possible problems: non-repeatable reading.

(4) READ UNCOMMITTED (uncommitted read)

No shared locks are added. Therefore, other transaction B can modify the same record during the reading process of transaction A, which may cause the data read by A to be damaged or incomplete or incorrect data.

In addition, the data modified in transaction B (uncommitted) can be read in transaction A. For example, transaction B modified the R record, but it was not submitted. At this time, when the R record is read in Transaction A, the data modified by B is read.​ ​

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​

​ ​ ​ Possible problems: dirty reading.

D: Duration, persistence. After the transaction execution is completed, the impact on the data is persistent, even if the system fails fatally after this.

The above is the re-understanding of MYSQL database transactions. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!

############