MySQL transaction isolation level

The problems caused by transaction concurrency are the problems that need to be solved by the database. The transaction processing technology is very mature, and the four isolation levels plus a snapshot is a common solution for all databases, various data libraries are slightly different in details. MySQL supports multiple storage engines. Each storage engine has its own characteristics. MyISAM is fast, but does not support transaction processing. The granularity can be controlled too coarse during concurrency. InnoDB is a very good storage engine, which has been acquired by Oracle. Oracle has been crazy over the past few years. InnoDB and Berkeley DB have been included in the bag, and this time MySQL has not been spared. MySQL for Linux uses MyISAM by default, while InnoDB for Windows. In the following discussion, InnoDB tables are used. The default isolation level of MySQL is repeatable.
Mysql> show variables like '% iso % '; + --------------- + percent + | Variable_name | Value | + --------------- + percent + | tx_isolation | REPEATABLE-READ | + ----------------- + --------------- + 1 row in set (0.00 sec) dirty reads, can not be repeated read, phantom read is a major problem caused by transaction concurrency, and Repeatable read is used to solve the problem of non-repeated read. However, MySQL's Repeatable read level seems magical, and it can avoid phantom read. If Snapshot technology is used, all the above problems can be solved at one time without knowing how InnoDB works. First, the behavior at the isolation level can be re-viewed. 1. Create a table as shown in the following figure. Use the InnoDB engine: mysql> show create table innodb \ G **************************** 1. row *************************** Table: innodbCreate Table: create table 'innodb' ('name' char (20) default null, 'age' int (11) default null) ENGINE = innodb default charset = latin11 row in set (0.00 sec) 2. enable two clients and disable automatic submission for them respectively. Mysql> show variables like 'autocommit '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | autocommit | ON | + --------------- + ------- + 1 row in set (0.00 sec) mysql> set autocommit = 0->; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'autocommit '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | autocommit | OFF | + --------------- +- ------ + 1 row in set (0.00 sec) 3. insert a test record to use mysql> insert into innodb values ('fzj', 25); Query OK, 1 row affected (0.00 sec) mysql> select * from innodb; + ------ + | name | age | + ------ + | fzj | 25 | + ------ + 1 row in set (0.00 sec) 4. Start the test, the two clients are differentiated by red and blue respectively. Give them the names A and B respectively. 1> solve the problem of unrepeatable reading mysql> start transaction; Query OK, 0 rows affected (0.05 sec)
Mysql> update innodb set age = age + 5 where name = 'fzj'; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
Mysql> select * from innodb; + ------ + | name | age | + ------ + | fzj | 30 | + ------ + 1 row in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec)
Mysql> select * from innodb where name = 'fzj '; + ------ + | name | age | + ------ + | fzj | 25 | + ------ + 1 row in set (0.00 sec) can be seen here, transactions B avoid dirty reads. Mysql> commit->; Query OK, 0 rows affected (0.04 sec)
Mysql> select * from innodb where name = 'fzj '; + ------ + | name | age | + ------ + | fzj | 25 | + ------ + 1 row in set (0.00 sec) Even if transaction A is committed, the query results of transaction B are still unaffected, which is a typical feature of the Repeatable read isolation level. But what if transaction B needs to update the same record? Based on this number, transaction A will be lost. Mysql> update innodb set age = age + 5 where name = 'fzj'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
Mysql> select * from innodb where name = 'fzj '; + ------ + | name | age | + ------ + | fzj | 35 | + ------ + 1 row in set (0.00 sec) it can be seen that the update of transaction A is not lost. If transaction B updates the same record before transaction A is committed, transaction B will be blocked, and the update must add A row lock to the corresponding row. This exclusive lock is dedicated. 2> solve phantom read problem mysql> start transaction; Query OK, 0 rows affected (0.00 sec)
Mysql> insert into innodb values ('ecy', 25); Query OK, 1 row affected (0.00 sec)
Mysql> start transaction; Query OK, 0 rows affected (0.04 sec)
Mysql> select count (*) from innodb; + ---------- + | count (*) | + ---------- + | 1 | + ---------- + 1 row in set (0.02 sec) mysql> commit->; Query OK, 0 rows affected (0.05 sec)
Mysql> select count (*) from innodb; + ---------- + | count (*) | + ---------- + | 1 | + ---------- + 1 row in set (0.00 sec) it can be seen that transaction A successfully inserts A record because it does not compete with transaction B, so transaction A insertion record and transaction B update record can be performed simultaneously, however, the commit of transaction A does not lead to Phantom reads of transaction B. MySQL's Repeatable read isolation level can indeed avoid Phantom reads.
3> the serializable isolation level is the highest isolation level. The query statement in a transaction will add an exclusive lock to the corresponding row until the transaction ends. Mysql> set session transaction isolation level serializable; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update innodb set age = age + 6 where name = 'fzj'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
Mysql> set session transaction isolation level serializable; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from innodb where name = 'fzj'; transaction B will be blocked at this time, because transaction A needs to be in line with the new 'fzj', so an exclusive lock is applied to this line, if transaction B adds a shared lock to it, it will fail. After transaction A is used to commit, transaction B will execute (I am surprised to find that in transaction B, even if the query name is 'ecy, transaction B will also be blocked. Is table locking used ??). Compared with the REPEATABLE-READ isolation level, we can find that the Repeatable read isolation level does not add a shared lock to the query statement, if needed, you can use select explicitly... lock in share mode and select... for update, add the shared lock and exclusive lock respectively.