MySQL Transaction and Isolation level

MySQL transaction and Isolation Level 1. Brief introduction

MySQL transaction is mainly used to deal with large-scale and high-complexity data. For example, in the Personnel Management system, you delete a person, you need to delete the basic information of the person, but also to delete the information related to the person, such as mailbox, articles and so on, so that these database operation statements constitute a transaction!

• In MySQL, transactions are supported only by databases or tables that use the INNODB database engine.
• Transactions can be used to maintain the integrity of the database, to ensure that batches of SQL statements are either fully executed or not executed at all.
• Transactions are used to manage insert,update,delete statements

2. Basic elements of a transaction acid

In general, transactions are required to meet 4 conditions (ACID): atomicity (Atomicity, or indivisibility), consistency (Consistency), Isolation (Isolation, Also known as independence), persistence (Durability).

• atomicity: All operations in a transaction (transaction) are either complete or not complete and do not end up in the middle of a session. When an error occurs during execution, the transaction is rolled back (Rollback) to the state before the transaction begins, as if the transaction had never been executed.
• Consistency: The integrity of the database is not compromised until the transaction begins and after the transaction has ended. This means that the data being written must fully conform to all of the preset rules, which include the accuracy of the data, the concatenation, and the subsequent database's ability to perform the scheduled work spontaneously. For example, a to B transfer, not a deduction of money, B but did not receive.
• Isolation: The ability of a database to read and write and modify its data at the same time for multiple concurrent transactions, which prevents inconsistencies in data resulting from cross-execution when multiple transactions are executing concurrently. Transaction isolation is divided into different levels, including read UNCOMMITTED, Read Committed, REPEATABLE READ (repeatable Read), and serialization (Serializable).
• Persistence: After the transaction is complete, all updates to the database are saved to the database and cannot be rolled back.

3. Concurrency Issues for transactions

1. Dirty read: allow to read uncommitted dirty data, such as: Transaction A read the transaction b updated data, and then B rollback operation, then a read to the data is dirty data;
2. non-repeatable READ: If you read some records at a point in time, or if you want to re-read the same data at T2 Point in time, the records may have been changed or disappeared, for example: Transaction a reads the same data more than once, and transaction B updates and commits the data during transaction a T1. Cause transaction A to read the same data multiple times, the result is inconsistent.
3. Phantom reading: system administrator A changes the scores of all students in the database from the specific score to the ABCDE level, but system Administrator B inserts a record of a specific score at this time, and when system administrator a changes to the end, it turns out that there is a record that hasn't changed, as if there was a hallucination. This is called Phantom Reading.

Non-REPEATABLE read and Phantom reads are easy to confuse, non-repeatable reading focuses on modification, and Phantom reading focuses on new or deleted. To solve the problem of non-repeatable reading, just lock the line that satisfies the condition, and solve the Phantom read need lock table.

4.4 Isolation Levels for transactions

To solve the concurrency problem of the above transaction, the SQL standard proposes 4 isolation levels, the following are the problem correspondence that each isolation level resolves:

Transaction ISOLATION Level	Dirty Read	non-repeatable read	Phantom Reading
read-uncommitted	N	N	N
read-committed	Y	N	N
Repeatable-read (default)	Y	Y	N
Serializable	Y	Y	Y

The default isolation level for MySQL is repeatable.

To view the system-level and session-level isolation levels:

select @@global.tx_isolation,@@tx_isolation; +-----------------------+-----------------+| @@global.tx_isolation | @@tx_isolation  |+-----------------------+-----------------+| REPEATABLE-READ       | REPEATABLE-READ |+-----------------------+-----------------+1rowinset2 warnings (0.01 sec)

Here are some examples to illustrate these four isolation levels:

1. read-uncommitted

Change the isolation level to read-uncommitted:

setsession tx_isolation=‘read-uncommitted‘;Query0rows1 warning (0.01select @@tx_isolation;+------------------+| @@tx_isolation   |+------------------+| READ-UNCOMMITTED |+------------------+1rowinset1 warning (0.00 sec)

First, prepare some test data:

selectfromuser;+----+----------+------+id | name     | age  |+----+----------+------+|  1 | zhangsan |   25 ||  2 | lisi     |   26 ||  3 | wangwu   |   27 ||  4 | nike     |   28 ||  5 | lucy     |   29 |+----+----------+------+5rowsinset (0.00 sec)

Client A:

Mysql>Start Transaction;QueryOk0 rowsAffected (0.00SEC) mysql>Select* from User;+----+----------+------+|ID| name | Age |+----+----------+------+|1| Zhangsan | -||2| Lisi | -||3| Wangwu | -||4| Nike | -||5| Lucy | in|+----+----------+------+5 rows inch Set(0.00Sec

Client B:

starttransaction;Query0rows affected (0.00updateuserset age=52where name=‘zhangsan‘;Query1row affected (0.00 sec)Rows1  1  0

Client A:

selectfromuser;+----+----------+------+id | name     | age  |+----+----------+------+|  1 | zhangsan |   52 ||  2 | lisi     |   26 ||  3 | wangwu   |   27 ||  4 | nike     |   28 ||  5 | lucy     |   29 |+----+----------+------+5rowsinset (0.00 sec)

As you can see, client B's transactions have not yet been committed, and the updated data is visible within client A's transactions.

Client B:

rollback;Query0rows affected (0.02 sec)

Client A:

selectfromuser;+----+----------+------+id | name     | age  |+----+----------+------+|  1 | zhangsan |   25 ||  2 | lisi     |   26 ||  3 | wangwu   |   27 ||  4 | nike     |   28 ||  5 | lucy     |   29 |+----+----------+------+5rowsinset (0.00 sec)

Because of the transaction rollback of client B, the data read by client A becomes the raw data, so the last time client a reads the data into dirty data. In concurrent transactions, this problem of reading data is called dirty reading.

2. read-commited

To solve the above problem, you can change the isolation level of the database to read-commited.

Client A:

setsession tx_isolation=‘read-committed‘;Query0rows1 warning (0.00 sec)

Follow the above steps to test, found that the dirty read problem has been resolved, before transaction B does not commit, transaction A will not read the dirty data.

The following is an example of a non-repeatable read problem.

Client A:

Mysql>Start Transaction;QueryOk0 rowsAffected (0.00SEC) mysql>Select* from User;+----+----------+------+|ID| name | Age |+----+----------+------+|1| Zhangsan | -||2| Lisi | -||3| Wangwu | -||4| Nike | -||5| Lucy | in|+----+----------+------+5 rows inch Set(0.00Sec

Client B:

starttransaction;Query0rows affected (0.00updateuserset age=52where name=‘zhangsan‘;Query1row affected (0.01 sec)Rows1  1  0commit;Query0rows affected (0.01 sec)

Client A:

Mysql>Select* from User;+----+----------+------+|ID| name | Age |+----+----------+------+|1| Zhangsan | the||2| Lisi | -||3| Wangwu | -||4| Nike | -||5| Lucy | in|+----+----------+------+5 rows inch Set(0.00SEC) mysql>rollback;QueryOk0 rowsAffected (0.00Sec

You can see that the data read by client A is different before and after client B's transaction commits. That is, repeated reads of the same data have different results.

Personal understanding, dirty reading is also a category of non-repeatable reading, but dirty read before transaction B is not committed causes two read data, non-repeatable read after transaction B commits, resulting in two read results. There is the dirty read is called dirty data, because this data is not really stored in the database, this is an intermediate state of the transaction. The non-repeatable read two reads different data actually already exists in the database.

3. Repeatable-read

To resolve a non-repeatable read problem, you can change the isolation level of the database to Repeatable-read.

Client A:

setsession tx_isolation=‘repeatable-read‘;Query0rows1 warning (0.00select @@tx_isolation;+-----------------+| @@tx_isolation  |+-----------------+| REPEATABLE-READ |+-----------------+1rowinset1 warning (0.00 sec)

Follow the above steps to test, found that the non-repeatable read problem has been resolved, after transaction B has no commit, the data read by transaction A does not change, close the transaction to reopen a transaction to read the updated data.

The following shows the problem of Phantom reading.

Client A:

Mysql>Start Transaction;QueryOk0 rowsAffected (0.00SEC) mysql>Select* from User;+----+----------+------+|ID| name | Age |+----+----------+------+|1| Zhangsan | -||2| Lisi | -||3| Wangwu | -||4| Nike | -||5| Lucy | in|+----+----------+------+5 rows inch Set(0.00Sec

Client B:

starttransaction;Query0rows affected (0.00insertintouservalues(6,‘shell‘,30);Query1row affected (0.01commit;Query0rows affected (0.00 sec)

Client A:

Mysql>Insert  into User Values(6,' Shell ', -); ERROR1062(23000): Duplicateentry ' 6 '  for Key ' PRIMARY 'Mysql>Select* from User;+----+----------+------+|ID| name | Age |+----+----------+------+|1| Zhangsan | -||2| Lisi | -||3| Wangwu | -||4| Nike | -||5| Lucy | in|+----+----------+------+5 rows inch Set(0.00Sec

As you can see, for client A, the name does not have the ID 6 of the data, but still insert failed, and then query or not ah, feel the illusion, this is the Phantom reading problem. The difference between phantom reading and non-repeatable reading is that the focus of non-repetition is the updated read, the focus of the magic reading is to insert and delete these operations, solve the non-repeatable read, only need to lock the corresponding data row. Resolving Phantom reads requires locking the entire table.

What if two transaction B did not commit before committing the insert? Let's take a look:

Client A:

insertintouservalues(6,‘shell‘,30);

You can see that if you insert the same ID as transaction B, the operation of transaction a will be blocked until the transaction B commits the commit, and the error will be:

Client A:

insertintouservalues(8,‘svn‘,321062 (23000entry‘8‘forkey‘PRIMARY‘

If client A does not conflict with the data transaction B that is inserted into it, the success is immediately returned:

Client A:

insertintouservalues(9,‘svn‘,32);Query1row affected (0.00 sec)

4. Serializable

To solve the problem of phantom reading, you can change the isolation level of the database to serializable.

Client A:

setsession tx_isolation=‘serializable‘;Query0rows1 warning (0.00 sec)

Following the steps above, it is found that the problem of phantom reading has been resolved, when transaction B tries to insert a transaction that is blocked, that is, transaction a locks the entire table. The operation of transaction B will not return the result until transaction a commits commit.

In this case, only one transaction is allowed to execute, and the other transaction must wait for the transaction to finish before executing. No concurrency, just a simple serial.

5. Summary

1. The default transaction isolation level in MySQL is repeatable read and does not lock the fetched rows;
2. When the transaction isolation level is read Committed, the write data will only lock the corresponding row;
3. When the transaction isolation level is repeatable, if there is an index (including the primary key index), when the index is listed as conditional update data, there will be a gap lock gap lock, Row lock, the next key lock problem, thereby locking some rows, if there is no index, update data will lock the entire table;
4. When the transaction isolation level is serialized, the read-write data locks the entire table;
5. The higher the isolation level, the more guaranteed the integrity and consistency of the data, but the greater the impact on concurrency, the more the fish and the paw can not be combined. For most applications, it is preferable to set the isolation level of the database system to read Committed, which avoids dirty reads and has good concurrency performance. Although it leads to non-repeatable reads, Phantom reads, these concurrency problems can be controlled by applications using pessimistic or optimistic locks on individual occasions where such problems may occur.

Reference:

Http://www.runoob.com/mysql/mysql-transaction.html

Https://www.cnblogs.com/huanongying/p/7021555.html

7063639

MySQL Transaction and Isolation level