[Mysql]--Transaction, transaction isolation level, dirty read, non-repeatable read, phantom read analysis

La la la, let's talk about affairs today~

If you like me, like me~

What is a transaction?

A transaction in a database is a batch of operations performed on a database

In the same transaction, these operations either all execute or all fail.

a. A transaction is a atomic operation.is the smallest execution unit.(Can consist of one or more SQL statements)

b. In the same transaction, all statements are successfully executed, and the entire transaction successful.

One transaction failed, and the entire transaction failed.

Limit: Only InnoDB and BDB type database tables support transactions.

Characteristics/Principles of Transactions (ACID)

Atomicity (Atomicity):

The entire transaction process is like atomic operations, either all succeed or all fail.

This atomicity is seen from the final result, and from the final result the process is indivisible.

Consistency (Consist):

The integrity of the database not corrupted before the transaction started and after the transaction ended.u>.

For example, one person transfers money to another person, and one person has already deducted the money, but the other person has not received it. This is inconsistent.

Isolated (Isolated):

The execution of transactions is non-interfering, and it is impossible for one transaction to see the data at a certain moment in the middle of other transactions when they are running.

The ability of the database to allow multiple concurrent transactions to read, write and modify its data at the same time. Isolation can prevent data inconsistency due to cross execution when multiple transactions are executed concurrently.

Transaction isolation is divided into: Read uncommitted (Read uncommitted), Read committedspan> (Read committed), repeatable read (Readpeatble read), serialization(Serialzable)

Durability (Durability):

Once a transaction is committed, its change to the data in the database is Permanent.

Means that after the transaction is completed, the changes made by the transaction to the database are persisted in the database and will not be rolled back.

Transaction isolation level

InnoDB's default isolation level is repeatable read

Read uncommitted (Read uncommitted):

Simply put: all transactions can see the data of uncommitted transactions.

Concurrency will occur: dirty read, non-repeatable read, phantom read.

Read committed (Read committed):

The transaction can only be seen after the transaction has been successfully committed.

Concurrency will occur: non-repeatable read, phantom read.

Repeatable read (Readpeatble read):

Multiple queries within the same transaction returned different data.

Concurrency will occur: phantom reading.

Serialzable (Serialzable):

Forced ordering, adding a shared lock on each read row.The most expensive but most reliable transaction isolation level.

Concurrency will occur: it will lead to a large number of timeouts and lock competition.

Problems caused by different isolation levels:

Dirty read (Dirty read):

A transaction is accessing the data and making changes, but before committing, another transaction also accesses the data and uses it, which causes dirty reads.

Summary: read the unmodified records.

Unrepeatable read (Unrepeatableread):

refers to reading the same data multiple times within a transaction, and before the end, another transaction also accesses the data, then the first transaction is between two data reads, because the third transactionChange, the first transaction is read twice and it is not the same.

Summary: Because it was modified by others, I read twice is differentspan>.

Phantom read (Phantom read):

One is modifying and one is adding and deleting.The first person felt that he had hallucinations, and the data kept changing.

Summary: Others have added or deleted the table, and their own reading and modification are inconsistent.

Please give me a thumbs up after reading it~~