MySQL transaction, concurrency problem, lock mechanism--phantom read, non-repeatable read

1. What is a transaction

A transaction is a combination of one or more database operation statements with acid,4 characteristics.

Atomicity: Either all succeeds or all is undone

Isolation: Transactions are independent from one another and are not interfering with each other

Consistency: The database consistency constraint is not compromised after the database has changed state correctly

Persistence: The commit result of the transaction, which persists in the database

2. What will happen to the concurrency of the transaction

1) The first category of missing updates: In the absence of transaction isolation, two transactions update one row of data at the same time, but the second transaction fails to exit halfway, resulting in two modifications to the data that are invalidated .

For example:

Zhang San's salary is 5000, transaction a gets a salary of 5000, transaction B gets a salary of 5000, sinks in 100, submits the database, and the salary becomes 5100,

Then

Transaction A has an exception, rolled back, and the recovery Zhang San's salary is 5000, which causes the update of transaction B to be lost.

2) Dirty read: Dirty read refers to when a transaction is accessing the data, and the data is modified, and this modification has not been committed to the database, then another transaction also accesses the data, and then used this data.
For example:
Zhang San's salary was 5000 and transaction a changed his salary to 8000, but transaction a was not yet submitted.
Meanwhile
Transaction B is reading Zhang San's salary and reading to Zhang San's salary is 8000.
Then
Transaction A has an exception and the transaction is rolled back. Zhang San's wages were rolled back to 5000.
At last
Transaction B reads 8000 of the Zhang San payroll data as dirty data, and transaction B does a dirty read.

3) Non-repeatable read: Refers to the same data that is read multiple times within a transaction. When this transaction is not finished, another transaction accesses the same data. Then, between the two read data in the first transaction, the data that the first transaction two reads may be different because of the modification of the second transaction. This occurs when the data that is read two times within a transaction is not the same and is therefore called non-repeatable read.
For example:
In transaction A, the salary read to Zhang San is 5000, the operation is not completed, and the transaction is not committed.
Meanwhile
Transaction B changed the salary of Zhang San to 8000 and submitted the transaction.
Then
In transaction A, the salary of the Zhang San is read again, at which time the salary becomes 8000. The result of two reads in a transaction does not cause a non-repeatable read.

4) Second category missing update: Special case of non-repeatable reading.
There are two concurrent transactions that read the same row of data at the same time, and one of them modifies the commit, and the other commits the modification. This will cause the first write operation to fail.

For example:

In transaction A, the deposit to Zhang San is 5000, the operation is not completed, and the transaction is not committed.
Meanwhile
Transaction B, which stores 1000, changes the deposit of Zhang San to 6000 and commits the transaction.
Then
In transaction A, store 500, change the deposit of Zhang San to 5500, and commit the transaction so that the update of transaction a overwrites the update of transaction B.

5) Phantom reading: a phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies data in a table, which involves all rows of data in the table. At the same time, the second transaction modifies the data in the table by inserting a new row of data into the table. Then the user who will be working on the first transaction in the future finds that there are no modified rows of data in the table, as if the illusion had occurred.
For example:
The current salary is 5000 employees have 10 people, transaction a read all wages for 5000 of the number of 10 people.
At this time
Transaction B Inserts a record with a salary of 5000.
This is, transaction a reads the employee with a payroll of 5000 again, and records 11 people. This creates a phantom read.

Remind:
The key to non-repeatable reading is the modification, the same condition, the data you read, read it again and find that the value is different.
The focus of the magic reading is to add or delete , the same conditions, the 1th and 2nd read out the number of records are different

3. Transaction isolation level, what concurrency issues are resolved, and what concurrency problems exist

(1) read_uncommitted
This is the lowest isolation level of a transaction, which allows another transaction to see the uncommitted data for this transaction.
Resolves the first category of missing updates, but there are dirty reads, non-repeatable reads, a second category of missing updates, and Phantom reads.
(2) read_committed
Ensure that a transaction modified data is committed before it can be read by another transaction, that is, another transaction cannot read uncommitted data for that transaction.
Resolves the first category of missing updates and dirty reads, but the problem of non-repeatable reads, the second type of missing updates, and the Phantom Read problem
(3) Repeatable_read
Ensure that the data obtained two times before and after a transaction is consistent

Resolves the first category of missing updates, dirty reads, non-repeatable reads, and the second category of missing updates, but with a phantom read.
(4) SERIALIZABLE
Transactions are processed for sequential execution.
Solve all problems

Remind:

MySQL default transaction isolation level is Repeatable_read

4. Lock mechanism of INNODB engine

(The reason why InnoDB is mainly about locks is because InnoDB supports transactions, supports row and table locks, MyISAM does not support transactions, only table locks are supported)

Shared Lock (S): Allows a transaction to read one line, preventing other transactions from acquiring an exclusive lock on the same data set.
Exclusive Lock (X): A transaction that allows an exclusive lock to update data, preventing other transactions from acquiring shared read and exclusive write locks of the same data set.
Intent shared Lock (IS): The transaction intends to add a row of shared locks to the data row, and the transaction must obtain the IS lock of the table before sharing it with a data row.
Intent exclusive Lock (ix): The transaction intends to add an exclusive lock to the data row, and the transaction must obtain an IX lock on the table before adding an exclusive lock to the data row.

Description

1) shared and exclusive locks are row locks, intent locks are table locks, in the application we will only use the shared and exclusive locks, intent lock is MySQL internal use, do not require user intervention.

2) for update, Delete, and INSERT statements, InnoDB automatically adds an exclusive lock (X) to the data set involved, and for the normal SELECT statement, InnoDB does not add any locks, and the transaction can be displayed to the recordset with shared or exclusive locks.
Shared Lock (S): SELECT * FROM table_name WHERE ... LOCK in SHARE MODE.
Exclusive Lock (X): SELECT * FROM table_name WHERE ... For UPDATE.

3) InnoDB row locks are implemented by locking the index entries on the index, so innodb this type of row lock implementation means that only the data is retrieved by indexing criteria, InnoDB uses row-level locks, otherwise innodb will use a table lock!.

Http://www.cnblogs.com/fidelQuan/p/4549068.html

MySQL transaction, concurrency problem, lock mechanism-phantom read, non-repeatable read (GO)