MySQL's affairs in a detailed

MySQL's affairs in a detailedDecember 16, 2015 21:28:35Hits: 6178

We know that a business logic in an application is often done by a combination of multiple statements. Then we can simply understand the transaction as a set of SQL statements, either the collection is all successful, or the collection fails back to the state before the first sentence.

Grammar

Let's look at the syntax of the transaction first. Now the society is more impetuous, we often only care about how to solve the problem, but not to consider the nature of the problem is what.

So I decided to introduce the syntax of the transaction first:
1. Open transaction start transaction, can be shortened to begin
2. Then record a set of SQL that needs to be executed after
3. Commit a Commit
4. If all SQL executes successfully, commit and persist the results of SQL execution into the data table.
5. Rollback rollback
6. If there is a failed SQL, rollback is required to return the results of the SQL execution to the beginning of the transaction
7. Regardless of rollback or commit, the transaction will be closed! You need to turn it on again to use it.
8. Another point to note is that the transaction is only for the current connection.

Here's a demonstration:

With the first link a, after the transaction is opened, an UPDATE statement is executed.
The results are successful and the data has become modified!

We did not submit it at this time.
Again from other connection B, the discovery data is changed:

At this point, if connection a chooses commit, that is the commit operation. The data for connection B will also change.

And if connection a chooses rollback, that is the rollback operation. Then connection a queries again to find the data restore.

Basic principle

The grammar is finished, the impetuous person also does not need to continue to look down. Let's briefly explain the fundamentals of business.
Commits, the result is persisted, not committed.
If we do not open the transaction and execute only one SQL, the data will be persisted immediately, and it can be seen that the normal execution is to commit immediately.
This is because MySQL default execution of SQL statements is automatically committed.

That is, to open the transaction, is actually turned off the auto-commit function, changed to commit the manual commit!

We can do this by simply setting it up for automatic submission.
The auto-commit feature is stored within a autocommit variable of the service. Can be modified:

It is also important to note that transactions are similar to foreign KEY constraints and are supported only by the InnoDB engine.

Characteristics

The following is the characteristic of the business of acid. That is, atomicity, consistency, isolation, and persistence.

Atomicity: A transaction is indivisible.
Consistency: Ensure that data is consistent throughout the execution cycle of the transaction!
Isolated type: Interference relationship between multiple transactions! Isolation level!
Persistence: Once a transaction is committed, it can no longer be rolled back!

transactional concurrency

Transaction concurrency can cause problems, so there is a different level of transaction isolation. To understand the isolation level of a transaction, you must first understand the problems that are associated with transaction concurrency.
In general, there are three types of data read problems and data update issues.

Dirty Read

An uncommitted data dependency occurs when a transaction is being modified on one record, but not committed, and another transaction reads the dirty data and processes it further.
To give an example:

Time	Transfer Transaction A	Withdrawal transaction B
T1		Start a transaction
T2	Start a transaction
T3		Enquiry account balance of 1000 yuan
T4		Remove $500 and change the balance to $500
T5	Enquiry account balance is 500 yuan (dirty Read)
T6		Undo transaction balance restored to $1000
T7	Import $100 to change the balance to $600
T8	Commit a transaction

A reads the dirty number that has not been committed by B, resulting in a final balance of $600.

Non-REPEATABLE READ

A transaction reads inconsistent data at different times.
To give an example:

Time	withdrawal Transaction A	Transfer Transaction B
T1		Start a transaction
T2	Start a transaction
T3		Enquiry account balance of 1000 yuan
T4	Enquiry account balance of 1000 yuan
T5		Remove $100 and change the balance to $900
T6		Commit a transaction
T7	Enquiry account balance is $900 (inconsistent with T4 Read)

You can see inconsistencies in the data that was last read.

Phantom reading

The concept of phantom reading and non-repeatable reading is similar to that of different time data, except that Phantom reads are for new data, and non-repeatable reading is for changing data.
See an example:

Time	statistic amount Transaction a	Transfer Transaction B
T1		Start a transaction
T2	Start a transaction
T3	Total number of deposits is $10000
T4		Add a deposit account with a deposit of $100
T5		Commit a transaction
T6	Re-statistics of total deposits of $10100 (phantom reading)

Update lost

Two transactions update the same data, which overrides the first-person update.

Time	withdrawal Transaction A	Transfer Transaction B
T1	Start a transaction
T2		Start a transaction
T3	Enquiry account balance of 1000 yuan
T4		Enquiry account balance of 1000 yuan
T5		Import $100 to change the balance to $1100
T6		Commit a transaction
T7	Remove $100 to change the balance to $900
T8	Revoking a transaction
T9	Balance restored to $1000 (missing update)

Isolation level

The problem with transaction concurrency has been described very carefully before. The isolation level of a transaction is designed to address concurrency problems, and different levels can guarantee different consistency.

To address the problems associated with the concurrency transaction mentioned above, the SQL standard proposes 4 levels of transaction isolation. Different isolation levels can have different results in the same environment.
Here's a look at the comparison of four isolation levels:

Isolation Level	Read Data Consistency	Dirty Read	non-repeatable read	Phantom Reading
Uncommitted read (READ UNCOMMITTED)	Lowest level, only guaranteed not to read physically corrupted data	Is	Is	Is
Read Committed	Statement-level	Whether	Is	Is
REPEATABLE READ (Repeatable Read)	Transaction-level	Whether	Whether	Is
Serializable (Serializable)	Highest level, transaction level	Whether	Whether	Whether

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced. 50332443

MySQL's affairs in detail