I. Concept

A database transaction is a mechanism, a sequence of operations, that contains a set of database operation commands.A transaction submits or revokes operation requests to the system as a whole, and these operations are either all executed or not executed, and are an inseparable unit of work.A transaction consists of all database operations performed between the beginning of the transaction and the end of the transaction.

Main example: bank transfer
The funds transferred from the first account must be correctly deposited into the second account. If the second link is not completed, the entire process should be cancelled, otherwise the funds will be lost.The problem.The entire transaction process can be regarded as one thing. If it succeeds, it will all succeed. If it fails, it needs to be undone. This can avoid the problem of data inconsistency when there is a problem in the middle of the operation.

Second, the characteristics of the transaction

1, atomic

A transaction is a complete operation.The elements of a transaction are indivisible (atomic), and all elements must be committed or rolled back as a whole.If any element in the transaction fails, the entire transaction fails.Either all succeed or all fail, there can be no intermediate states.
Taking the bank transfer transaction as an example, if the transaction is submitted, the data of the two accounts will be updated.If, for some reason, the transaction terminates before the two accounts have been successfully updated, the balances of both accounts will not be updated, and the modifications to any account balances will be reversed, and the transaction cannot be partially committed.

2, Consistency

Data must be in a consistent state.That is, before the transaction begins, the data stored in the database is in a consistent state.During an ongoing transaction. Data may be in an inconsistent state, such as data may be partially modified.However, when the transaction completes successfully, the data must again be returned to a known consistent state.Modifications made to data through transactions cannot corrupt the data, or transactions cannot leave the data store in an unstable state.
For example, bank transfer, account A is transferred to account B, no matter how many times the transfer is made, the total amount of accounts A and B cannot be changed.

3, Isolation

All concurrent transactions that modify data are isolated from each other, which means that a transaction must be independent and it should not depend on or affect other transactions in any way.A transaction that modifies data can access the data before another transaction using the same data begins, or after another transaction using the same data ends.

4, Persistence

Transaction durability means that after a transaction completes successfully, the changes it makes to the database are permanent, even if the system fails.That is to say, once the transaction is committed, any changes made by the transaction to the data will be permanently retained in the database and guaranteed by redo log and binlog together.
The ACID principle of transactions guarantees that a transaction is either committed successfully or rolled back if it fails, either.Therefore, it is recoverable for modification of transactions.That is, when a transaction fails, its modifications to the data will be restored to the state before the transaction was executed.

Third, transaction isolation level

1, uncommitted read

No locks will be added when reading data, and no detection will be performed, and uncommitted data may be read.

2, uncommitted read

Only read the submitted data and wait for other things to release the exclusive lock. The shared lock of the read data will be released immediately after the read operation is completed.This isolation level is the default isolation level of SQL Server.

3, repeatable

Like committed read, but the shared lock is held until the transaction ends.

4, repeatable

Similar to repeatable read, but locks not only lock the queried data, but also the queried range, which prevents new data from being inserted into the queried range.

4. Types of mutual influence between transactions

1, lost edits

Two transactions read the same record at the same time, A first modifies the record, and B also modifies the record (B does not know that A has modified it). After B submits the data, the modification result of B overwrites the modification result of A.

2, dirty read

Transaction B modifies a piece of data and does not commit it, transaction A reads the data, then transaction B rolls back, and finally A reads it again. The data read twice is inconsistent, which is called dirty reading.

3, unrepeatable

After transaction A reads a piece of data, transaction B modifies the data, and transaction A reads the data again. The data read twice is also inconsistent, which is called non-repeatable read.

4, hallucinations

Phantom reading refers to a phenomenon that occurs when transactions are not executed independently. For example, the first transaction modifies the data in a table, and this modification involves all data rows in the table.At the same time, the second transaction also modifies the data in this table by inserting a new row of data into the table.Then, the user who operates the first transaction will find that there are no modified data rows in the table in the future, as if an illusion has occurred.