MySQL transaction-to-transaction isolation

Since the introduction of the InnoDB engine in MySQL, in MySQL support transactions, the transaction is a set of atomic query statements, but also a number of queries as a separate unit of work, usually by submitting a unit of work to complete the corresponding query or modification in the transaction, The acid test, the four characteristics of a transaction, must be met in a database that can support transactions:

A:atomicity, atomic (neither executed nor executed)

C:consistency, consistency (move from one consistent state to another)

I:isolaction, Isolation (all modification operations of a transaction are not visible to other transactions before committing)

D:durability, persistence (once the transaction has been committed, its modifications will be permanently valid)

And in the early default engine MyISAM is not support transactions, so if it is in the MyISAM table is not supporting things, want to know the data in the specific support of which table engine can pass "SHOW ENGINES;" View all the engines supported by MySQL in the version you are using. Here first create a table transaction_tbl for testing:

CREATE TABLE transaction_tbl (id int (4)) Engine=innodb; INSERT into Transaction_tbl value (1), insert into Transaction_tbl value (2), insert into TRANSACTION_TBL value (3), insert I NTO transaction_tbl Value (4); INSERT into Transaction_tbl value (5);

The ability to turn off auto-commit during normal use is enabled by default system

Mysql> SHOW GLOBAL VARIABLES like ' autocommit '; +---------------+-------+| variable_name | Value |+---------------+-------+| autocommit | On |+---------------+-------+1 row in Set (0.00 sec)

You need to close before using the transaction, you need to check if there is an open autocommit before using the transaction, of course, if the dependency on the transaction is relatively large recommendation can be permanently shut down the global auto-commit, but in the normal use of the process as long as the use of closed autocommit, and manually start the transaction, This will allow the transaction to be rolled back in order to get the desired effect based on the relevant transaction isolation level, autocommit can be turned off when the transaction is used, and then turned on after all transactions have ended autocommit

mysql> SET GLOBAL autocommit=off; #当然这里也可以使用布尔值的0和1Query OK, 0 rows Affected (0.00 sec)

The control statement of the transaction is also simple, as follows:

BEGIN; or start TRANSACTION; explicitly open a transaction commit, commit the transaction to end the transaction, and make all modifications to the database persistent rollback; The transaction rollback ends the user's transaction and revokes any uncommitted modifications that are in progress savepoint identifier; Allows a savepoint to be created in a transaction, with multiple savepointrelease savepoint identifier in a transaction; Deletes a savepoint for a transaction when no savepoint is specified. Execution of the SQL statement will report an exception rollback to identifier; Roll the transaction back to the Mark Point

The level of isolation in the transaction is different, the security of things is different, but the higher the security of things, the lower the concurrency, of course, according to the actual situation of choice, in MySQL, the level of isolation of the transaction is four, the security level from low to High:

Read Uncommittend: Unread uncommitted

Read Committend: Reading commit

Repeatable READ: Can be reread

SERIALIZABLE: Serializable

View the default transaction isolation level currently in use:

Mysql> SHOW GLOBAL VARIABLES like ' tx_isolation '; +---------------+-----------------+| variable_name | Value |+---------------+-----------------+| tx_isolation | Repeatable-read |+---------------+-----------------+1 row in Set (0.00 sec)

And in the use of four isolation levels, the effect is not the same, at this time the test needs to open 2 sessions more intuitive, here is a, B to represent two sessions open transactions A, B:

Mysql> SELECT * from transaction_tbl;+----+|  ID |+----+|  1 | |  2 | |  3 | |  4 | | 5 |+----+5 rows in Set (0.01 sec)

One, read uncommittend: Reads uncommitted, as the name implies that all transactions can read to other transactions uncommitted content, the isolation mode in peacetime generally do not use, because the use of read uncommittend will bring dirty read problems, the following is used transaction _tbl A simple example to illustrate the following:

mysql> SET GLOBAL tx_isolation= ' read-uncommitted '; Query OK, 0 rows Affected (0.00 sec) mysql> SHOW GLOBAL VARIABLES like ' tx_isolation '; +---------------+---------------- --+| variable_name | Value |+---------------+------------------+| tx_isolation | read-uncommitted |+---------------+------------------+1 row in Set (0.00 sec)

Transaction A has been done, but not committed:

Mysql> START TRANSACTION; Query OK, 0 rows Affected (0.00 sec) mysql> UPDATE transaction_tbl SET id = ' 6 ' WHERE id= ' 1 ';  Query OK, 1 row affected (0.02 sec) Rows matched:1 changed:1 warnings:0mysql> SELECT * from transaction_tbl;+----+|  ID |+----+|  6 | |  2 | |  3 | |  4 | | 5 |+----+5 rows in Set (0.00 sec)

But at this point, transaction B is able to see the data in transaction a

Transaction B:

Mysql> START TRANSACTION; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * from transaction_tbl;+----+|  ID |+----+|  6 | |  2 | |  3 | |  4 | | 5 |+----+5 rows in Set (0.01 sec)

Transaction A is rolled back at this time:

Mysql> ROLLBACK; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * from transaction_tbl;+----+|  ID |+----+|  1 | |  2 | |  3 | |  4 | | 5 |+----+5 rows in Set (0.00 sec)

The uncommitted data in transaction A is identified in transaction B:

Mysql> SELECT * from transaction_tbl;+----+|  ID |+----+|  1 | |  2 | |  3 | |  4 | | 5 |+----+5 rows in Set (0.00 sec)

This is the dirty read by the read uncommittend, which is not used in the general database production environment.

Second, read Committend, the same as the name to know the isolation level of things is higher than the READ UNCOMMITTED high transaction isolation level, which resolves the problem of dirty read, which is the default transaction isolation level used in most databases, but not the default transaction isolation level of MySQL, The transaction isolation level resolves the dirty read problem, but the new problem is not re-accented, and if there are exactly 2 transactions on the same table at this time, different results will be found when executing the same query in one transaction:

mysql> SET GLOBAL tx_isolation= ' read-committed ';       Query OK, 0 rows Affected (0.00 sec) mysql> SHOW GLOBAL VARIABLES like ' tx_isolation '; +---------------+----------------+| variable_name | Value |+---------------+----------------+| tx_isolation | read-committed |+---------------+----------------+1 row in Set (0.00 sec)

In transaction A:

Mysql> BEGIN; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * from transaction_tbl;+----+|  ID |+----+|  1 | |  2 | |  3 | |  4 | | 5 |+----+5 rows in Set (0.00 sec)

At this point, transaction B also opens a transaction to do an operation:

Mysql> BEGIN; Query OK, 0 rows Affected (0.00 sec) mysql> UPDATE transaction_tbl SET id = ' 6 ' WHERE id= ' 1 ';  Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0mysql> SELECT * from transaction_tbl;+----+|  ID |+----+|  6 | |  2 | |  3 | |  4 | | 5 |+----+5 rows in Set (0.01 sec)

Now look at the query in transaction a:

Mysql> SELECT * from transaction_tbl;+----+|  ID |+----+|  1 | |  2 | |  3 | |  4 | | 5 |+----+5 rows in Set (0.00 sec) #但此时事务B中COMMIT提交后, transaction a mysql> SELECT * from transaction_tbl;+----+|  ID |+----+|  6 | |  2 | |  3 | |  4 | | 5 |+----+5 rows in Set (0.00 sec)

This is the non-stressed problem caused by the read committend, so this transaction isolation level is not recommended in a general database production environment.

Third, repeatable READ, can be stressed, with the deserve transaction isolation level resolves the problem of non-stress, in repeatable READ using MVCC (multi-version concurrency control) at the start of each transaction, InnoDB will provide a snapshot of the current moment for each transaction started, To achieve this, InnoDB provides two hidden fields for each table, one for saving the creation time of the row, one for saving the row's expiration time, and the system version number stored in the MVCC, which is designed to take effect in read Committend and REPEATABLE read two transaction isolation. However, the repeatable read is the same as the above transaction isolation level, which solves the problem of non-stress and also brings a new problem to the Phantom reading, when there are exactly 2 transactions on the same table to do, before committing in one transaction, one of the transactions before the commit of another transaction before and after the results of the query is not the same:

mysql> SET GLOBAL tx_isolation= ' repeatable-read '; Query OK, 0 rows affected (0.01 sec) mysql> SHOW GLOBAL VARIABLES like ' tx_isolation '; +---------------+---------------- -+| variable_name | Value |+---------------+-----------------+| tx_isolation | Repeatable-read |+---------------+-----------------+1 row in Set (0.01 sec)

Transaction A:

Mysql> BEGIN; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * from transaction_tbl;+----+|  ID |+----+|  6 | |  2 | |  3 | |  4 | | 5 |+----+5 rows in Set (0.02 sec)

In transaction B, make the relevant operation and submit:

Mysql> BEGIN; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * from transaction_tbl;+----+|  ID |+----+|  6 | |  2 | |  3 | |  4 | | 5 |+----+5 rows in Set (0.00 sec) mysql> UPDATE transaction_tbl Set id = ' 1 ' WHERE id= ' 6 ';  Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0mysql> SELECT * from transaction_tbl;+----+|  ID |+----+|  1 | |  2 | |  3 | |  4 | | 5 |+----+5 rows in Set (0.01 sec) mysql> COMMIT; Query OK, 0 rows affected (0.02 sec)

Now look at transaction a again:

Mysql> SELECT * from transaction_tbl;+----+|  ID |+----+|  1 | |  2 | |  3 | |  4 | | 5 |+----+5 rows in Set (0.02 sec) mysql> COMMIT; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * from transaction_tbl;+----+|  ID |+----+|  6 | |  2 | |  3 | |  4 | | 5 |+----+5 rows in Set (0.00 sec)

This is repeatable read to bring the illusion of the problem, of course, in the actual production of this happens relatively few, so generally as the default transaction isolation level of MySQL.

Four, SERIALIZABLE, serializable, strong transaction sorting is also the highest level of transaction isolation, all transactions have the use of shared locks, so as to solve the corresponding magic read problem, but because of the shared lock to reduce the performance of the write, thereby reducing the performance of MySQL:

mysql> SET GLOBAL tx_isolation= ' SERIALIZABLE ';  Query OK, 0 rows Affected (0.00 sec) mysql> SHOW GLOBAL VARIABLES like ' tx_isolation '; +---------------+--------------+| variable_name | Value |+---------------+--------------+| tx_isolation | SERIALIZABLE |+---------------+--------------+1 row in Set (0.00 sec)

Inserting a piece of data in transaction A does not commit:

Mysql> BEGIN; Query OK, 0 rows Affected (0.00 sec) mysql> INSERT into Transaction_tbl VALUE (' 7 '); Query OK, 1 row Affected (0.00 sec) mysql> SELECT * from transaction_tbl;+----+|  ID |+----+|  1 | |  2 | |  3 | |  4 | |  5 | | 7 |+----+6 rows in Set (0.00 sec)

At this point in transaction B, the commit cannot be written until transaction A is committed.

mysql> show global variables like  ' tx_ Isolation '; +---------------+--------------+| variable_name | value         |+---------------+--------------+| tx_isolation  | serializable  |+---------------+--------------+1 row in set  (0.00 sec) mysql> begin; query ok, 0 rows affected  (0.00 sec) mysql> select * from  transaction_tbl;+----+| id |+----+|  1 | |   2 | |   3 | |   4 | |   5 |+----+5 rows in set  (0.00 sec) mysql> update  transaction_tbl set id =  ' 6 '  where id= ' 1 '; error 1205  (HY000): lock wait timeout exceeded; try restarting  Transaction 

It is only possible to commit in transaction B after commit commits in transaction a, but it is important to note that after MySQL 5.7 has undergone a lot of code refactoring optimizations, transaction A and B are at the transaction isolation level of repeatable read by default for the first three isolation levels. And in the ordinary use of transactions often used in a large number of stored procedures, and different databases (including the different versions of MySQL, there are differences), the syntax is very different, migration difficulties, change the database, need to rewrite, so the excessive business logic written in the stored procedure is not good maintenance, not conducive to hierarchical management, easy chaos, General stored procedures for individual performance requirements of the business, the other need is not very large, in peacetime use needs to be based on the actual situation.

This article from "Jim's Technical Essay" blog, declined to reprint!

MySQL transaction-to-transaction isolation