A long time ago, to review and learn:

InnoDB is REPEATABLE READ by default according to SQL:1992 transaction isolation level. MySQL/InnoDB provides all four transaction isolation levels described by the SQL standard. You can set the default isolation level for all connections using the — transaction-Isolation option on the command line or in the options file. For example, you can set this option in the [mysqld] section of the my.inf file as follows:

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}Copy the code

Users can use the SET TRANSACTION statement to change the isolation level for a single session or for all new connections. Its syntax is as follows:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}Copy the code

Note: The default behavior (without session and global) is to set the isolation level for the next (unstarted) transaction. If you use the GLOBAL keyword, the statement sets the default transaction level globally for all new connections created from that point onwards (except those that do not exist). You need SUPER permission to do this. Use the SESSION keyword to set the default transaction level for future transactions to be performed on the current connection. Any client is free to change the session isolation level (even in the middle of a transaction) or set the isolation level for the next transaction.

You can query global and session transaction isolation levels with the following statements:

SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;Copy the code

—- theoretical knowledge in the above manuals; = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = isolation level Dirty Read (Dirty Read) NonRepeatable Read (Phantom Read) = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

Read uncommitted Possible Possible

Read committed impossible Possible

Repeatable read impossible impossible

Serializable impossible Impossible

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

· Read Uncommitted:Dirty reads are allowed, that is, data that may be read from other sessions that have not committed transaction modifications

· Read Committed:Only committed data can be read. Most databases, such as Oracle, default to this level (no repeat reads)

· Repeated Read:Repeatable. Queries within the same transaction are consistent at the start of the transaction, InnoDB default level. In the SQL standard, this isolation level eliminates non-repeatable reads, but phantom reads still exist

· Serializable:Fully serialized reads require table-level shared locks for each read and block each other

~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

Use examples to illustrate each level:

1) dirty reads: Dirty reads are when a transaction is accessing data and making changes to the data that have not yet been committed to the database, and then another transaction accesses the data and consumes it.

session 1: mysql> select @@global.tx_isolation; +-----------------------+ | @@global.tx_isolation | +-----------------------+ | REPEATABLE-READ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > select @ @ session. Tx_isolation; +-----------------------+ | @@session.tx_isolation | +-----------------------+ | REPEATABLE-READ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > start transaction; Query OK, 0 rows affected (0.00 SEC) mysql> insert into TTD values(1); Query OK, 1 row affected (0.05sec) mysql> select * from TTD; + -- -- -- -- -- -- + | | + -- -- -- -- -- - id + | | + -- -- -- -- -- - 1 + 1 row in the set (0.00 SEC) session 2: mysql > select @ @ session. Tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > select. @ @ global tx_isolation; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | @ @ global. Tx_isolation | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | the REPEATABLE - READ | -- -- -- -- -- -- -- -- the isolation level (in addition to READ Uncommitted) + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > select * from providing; Empty set (0.00 SEC) -------- mysql> Set session Transaction ISOLATION level read uncommitted; Query OK, 0 rows affected (0.00 SEC) mysql> select @@session.tx_isolation; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | @ @ session. Tx_isolation | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | READ UNCOMMITTED - | -- -- -- -- -- -- -- -- the isolation level + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > select * from providing; + -- -- -- -- -- -- + | | + -- -- -- -- -- - id + | 1 | -- -- -- -- -- -- -- - the REPEATABLE READ level appear dirty READ + -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

Conclusion: Session 2 reads UNCOMMITTED transaction changes in Session 1 in read-uncommitted mode.

② Not repeatable to read:Read the same data multiple times within a transaction. The same data is accessed by another transaction while the transaction is still active. Then, between the two reads in the first transaction, the data read in the first transaction may not be the same because of the modification in the second transaction. This happens when the data read twice in a transaction is not the same and is therefore called a non-repeatable read.

session 1: mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-COMMITTED | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > start transaction; Query OK, 0 rows affected (0.00 SEC) mysql> select * from TTD; + -- -- -- -- -- -- + | | + -- -- -- -- -- - id + | | + -- -- -- -- -- - 1 + 1 row in the set (0.00 SEC) session 2: mysql > select @ @ session. Tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > start transaction; Query OK, 0 rows affected (0.00 SEC) mysql> select * from TTD; + -- -- -- -- -- -- + | | + -- -- -- -- -- - id + | | + -- -- -- -- -- - 1 + 1 row in the set (0.00 SEC) mysql > insert into providing values (2); Query OK, 1 row affected (0.00 SEC) mysql> select * from TTD; + -- -- -- -- -- -- + | | + -- -- -- -- -- - id + 1 | | | | + -- -- -- -- -- - 2 + 2 rows in the set (0.00 SEC) mysql > commit; Query OK, 0 rows affected (0.02sec); session 1: mysql> select * from ttd; + -- -- -- -- -- -- + | | + -- -- -- -- -- - id + | 1 | -- -- -- -- -- -- -- -- and the result of the first, the READ - COMMITTED level appeared not repeat reading | | + -- -- -- -- -- - 2 + 2 rows in the set (0.00 SEC)Copy the code

③ Repeatable reading:

session 1: mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > start transaction; Query OK, 0 rows affected (0.00 SEC) mysql> select * from TTD; + -- -- -- -- -- -- + | | + -- -- -- -- -- - id + 1 | | | | + -- -- -- -- -- - 2 + 2 rows in the set (0.00 SEC) session 2: mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > start transaction; Query OK, 0 rows affected (0.00 SEC) mysql> insert into TTD values(3); Query OK, 1 row affected (0.00 SEC) mysql> commit; Query OK, 0 rows affected (0.03 SEC); session 1: mysql> select * from ttd; + -- -- -- -- -- -- + | | + -- -- -- -- -- - id + | 1 | -- -- -- -- -- -- -- -- and the result of the first, the level of the REPEATABLE - READ a repeat READ | | + -- -- -- -- -- - 2 + 2 rows in the set (0.00 SEC) (commit Select * from TTD; select * from TTD;Copy the code

(4) phantom read:The first transaction makes a change to the data in a table that involves all rows in the table. At the same time, the second transaction also modifies the data in the table by inserting a new row into the table. Then, as if in an illusion, the user operating on the first transaction will discover that there are unmodified rows in the table.

mysql>CREATE TABLE `t_bitfly` ( `id` bigint(20) NOT NULL default '0', `value` varchar(32) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB mysql> select @@global.tx_isolation, @@tx_isolation; +-----------------------+-----------------+ | @@global.tx_isolation | @@tx_isolation | +-----------------------+-----------------+ | REPEATABLE-READ | REPEATABLE-READ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + experiment 1: t Session A Session B | | START TRANSACTION; START TRANSACTION; | | SELECT * FROM t_bitfly; | empty set | INSERT INTO t_bitfly | VALUES (1, 'a'); | | SELECT * FROM t_bitfly; | empty set | COMMIT; | | SELECT * FROM t_bitfly; | empty set | | INSERT INTO t_bitfly VALUES (1, 'a'); | ERROR 1062 (23000): | Duplicate entry '1' for key 1 v (shit, just clearly tell I don't have the record) so there is a phantom read, thought the list without the data, but data already exists, silly submitted, only to find that the data conflict. Experiment 2: t Session A Session B | | START TRANSACTION; START TRANSACTION; | | SELECT * FROM t_bitfly; | +------+-------+ | | id | value | | +------+-------+ | | 1 | a | | +------+-------+ | INSERT INTO t_bitfly | VALUES (2, 'b'); | | SELECT * FROM t_bitfly; | +------+-------+ | | id | value | | +------+-------+ | | 1 | a | | +------+-------+ | COMMIT; | | SELECT * FROM t_bitfly; | +------+-------+ | | id | value | | +------+-------+ | | 1 | a | | +------+-------+ | | UPDATE t_bitfly SET value='z';  | Rows matched: 2 Changed: 2 Warnings: 0 | how (extra line) | | SELECT * FROM t_bitfly; | +------+-------+ | | id | value | | +------+-------+ | | 1 | z | | | 2 | z | | +------+-------+Copy the code

The first time a row is read in this transaction, an update is made, and the data committed in another transaction appears. It can also be viewed as a kind of illusory reading. When the isolation level is repeatable and Innodb_locks_unSAFE_for_binlog is disabled, next-key locks are used when searching and scanning indexes to avoid phantom locks.

Select * from t_bitfly where id is the primary key.

Experiment 3: t Session A Session B | | START TRANSACTION; START TRANSACTION; | | SELECT * FROM t_bitfly | WHERE idCopy the code

If id<=1, lock (id<=1); if id<=1, lock (id<=1); if id<=1, lock (id<=1); if id<=1, lock (id<=1); < span=””>

Experiment 4: read and submit t consistency Session A Session B | | START TRANSACTION; START TRANSACTION; | | SELECT * FROM t_bitfly; | +----+-------+ | | id | value | | +----+-------+ | | 1 | a | | +----+-------+ | INSERT INTO t_bitfly | VALUES (2, 'b'); | COMMIT; | | SELECT * FROM t_bitfly; | +----+-------+ | | id | value | | +----+-------+ | | 1 | a | | +----+-------+ | | SELECT * FROM t_bitfly LOCK IN SHARE  MODE; | +----+-------+ | | id | value | | +----+-------+ | | 1 | a | | | 2 | b | | +----+-------+ | | SELECT * FROM t_bitfly FOR UPDATE; | +----+-------+ | | id | value | | +----+-------+ | | 1 | a | | | 2 | b | | +----+-------+ | | SELECT * FROM t_bitfly; | +----+-------+ | | id | value | | +----+-------+ | | 1 | a | | +----+-------+Copy the code

If normal reads are used, consistent results are obtained. If normal reads are used, consistent results are obtainedA locked read reads the results of the “latest” “committed” read.

Repeatable reads and committed reads are inherently contradictory. In the same transaction, if repeatable reads are guaranteed, other transactions will not be committed, which violates the commit read. If committed reads are guaranteed, the results of two successive reads will be inconsistent, violating repeatable reads.

It can be said that InnoDB provides a mechanism to use lock reads to query the latest data (commit reads) at the default isolation level of repeatable reads. MySQL InnoDB’s repeatable read does not guarantee to avoid phantom read. The mechanism used for this locking is next-key locks.

Conclusion:

The four levels are progressively enhanced, each addressing one problem. The higher the transaction level, the worse the performance. Most environments read COMMITTED can be used. Remember the characteristics of the four isolation levels (the example above);

Databases use locks to support better concurrency and to provide data integrity and consistency. InnoDB is a storage engine that supports row locks. The types of locks are shared (S), exclusive (X), intentional shared (IX), and intentional exclusive (IX). To provide better concurrency,InnoDB provides unlocked reads: a snapshot of a row is read without waiting for the lock to be released on the access row.This method is implemented through a feature of InnoDB: MVCC.

InnoDB has three algorithms for row locking:

1, Record the Lock: Locks on a single row record.

2, the Gap of the Lock: gap lock, which locks a range but not the record itself.

3, the Next – the Key Lock: 1+2 locks a range, and locks the record itself. For the query of rows, this method is used, the main purpose is to solve the illusion problem.

Test a:

root@localhost : test 10:56:10>create table t(a int,key idx_a(a))engine =innodb; Query OK, 0 rows affected (0.20 SEC) root@localhost: test 10:56:13>insert into T values(1),(3),(5),(8),(11); Query OK, 5 rows Affected (0.00 SEC) Records: 5 Duplicates: 0 Warnings: 0 root@localhost: test 10:56:15>select * from t; + -- -- -- -- -- -- + | | + -- -- -- -- -- - a + | 1 | | 3 | | | 5 8 | | | | + -- -- -- -- -- - 11 + 5 rows in the set (0.00 SEC) section a: root @ localhost: test 10:56:27>start transaction; Query OK, 0 rows affected (0.00 SEC) root@localhost: test 10:56:29>select * from t where a = 8 for update; + -- -- -- -- -- -- + | | + -- -- -- -- -- - a + 8 | | + -- -- -- -- -- - + 1 row set (0.00 SEC) in section B: root @ localhost: test 10:54:50 > begin; Query OK, 0 rows affected (0.00 SEC) root@localhost: test 10:56:51>select * from t; + -- -- -- -- -- -- + | | + -- -- -- -- -- - a + | 1 | | 3 | | | 5 8 | | | | + -- -- -- -- -- - 11 + 5 rows in the set (0.00 SEC) root @ localhost: test 10:56:54>insert into t values(2); Query OK, 1 row affected (0.00 SEC) root@localhost: test 10:57:01>insert into t values(4); Query OK, 1 row affected (0.00 SEC) root@localhost: test 10:57:01> Insert into t values(4); Query OK, 1 row affected (0.00 SEC) ++++++++++ root@localhost: test 10:57:04> Insert into T values(6); root@localhost : test 10:57:11>insert into t values(7); root@localhost : test 10:57:15>insert into t values(9); root@localhost : test 10:57:33>insert into t values(10); root@localhost: test 10:57:39>insert into t values(12); Query OK, 1 row affected (0.00 SEC)Copy the code

Question:

Why do insert statements on section B have lock waits? InnoDB is A row lock. InnoDB locks rows with A =8 in section A and should not be affected. According to?

Analysis:

Because InnoDB uses next-key Lock algorithm for all rows, Locking is not a single value, but a range, with indexes of 1,3,5,8,11. The next-key Locking interval is:

Minus infinity,1,1,3], (3, 5], (5, 8], (8, 11]+ up), (11,

In particular, the InnoDB storage engine also applies a gap lock to the next key in the secondary index. As the above analysis, that can be explained.

root@localhost : test 10:56:29>select * from t where a = 8 for update; + -- -- -- -- -- -- + | | + -- -- -- -- -- - a + 8 | | + -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

SQL statement lock range (5,8), next key range (8,11), insert value between 5 and 11 will be locked, require waiting. Insert 5,6,7,8,9,10 will be locked. Inserting values outside this range is fine.

Continue: What happens when the insert timeout fails?

Timeout parameter: innodb_lock_WAIT_timeout. Default is 50 seconds. Parameter: innodb_rollback_on_timeout The default value is OFF.

section A: root@localhost : test 04:48:51>start transaction; Query OK, 0 rows affected (0.00 SEC) root@localhost: test 04:48:53>select * from t where a = 8 for update; + -- -- -- -- -- -- + | | + -- -- -- -- -- - a + 8 | | + -- -- -- -- -- - + 1 row set (0.01 SEC) in section B: root @ localhost: test 04:49:04>start transaction; Query OK, 0 rows affected (0.00 SEC) root@localhost: test 04:49:07>insert into t values(12); Query OK, 1 row affected (0.00 SEC) root@localhost: test 04:49:13>insert into t values(10); Query OK, 1 row affected (0.00 SEC) root@localhost: test 04:49:13> Insert into T values(10); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction root@localhost : test 04:50:06>select * from t; + -- -- -- -- -- -- + | | + -- -- -- -- -- - a + | 1 | | 3 | | 5 | | | 8 11 | | | | + -- -- -- -- -- - 12 + 6 rows in the set (0.00 SEC)Copy the code

Innodb_rollback_on_timeout: innodb_rollback_on_timeout: INNOdb_rollback_on_timeout: INNOdb_rollback_on_timeout: Innodb_rollback_on_timeout: Innodb_rollback_on_timeout

By default, InnoDB storage engine does not roll back exceptions raised by timeout, except for deadlocks.

Since InnoDB has three algorithms, when will Record Lock be used? Using the example above, change the secondary index to a unique index.

Test 2:

root@localhost : test 04:58:49>create table t(a int primary key)engine =innodb; Query OK, 0 rows affected (0.19 SEC) root@localhost: test 04:59:02> INSERT into T values(1),(3),(5),(8),(11); Query OK, 5 rows Affected (0.00 SEC) Records: 5 Duplicates: 0 Warnings: 0 root@localhost: test 04:59:10>select * from t; + - + - + + | a | | 1 | | 3 | | | 5 8 | | | | + 11 - + 5 rows in the set (0.00 SEC) section a: root @ localhost: test 04:59:30>start transaction; Query OK, 0 rows affected (0.00 SEC) root@localhost: test 04:59:33>select * from t where a = 8 for update; + + | -- - | a + - + 8 | | + -- -- -- + 1 row set (0.00 SEC) in section B: root @ localhost: test 04:58:41 > start transaction; Query OK, 0 rows affected (0.00 SEC) root@localhost: test 04:59:45>insert into t values(6); Query OK, 1 row affected (0.00 SEC) root@localhost: test 05:00:05>insert into t values(7); Query OK, 1 row affected (0.00 SEC) root@localhost: test 05:00:08>insert into t values(9); Query OK, 1 row affected (0.00 SEC) root@localhost: test 05:00:10>insert into t values(10); Query OK, 1 row affected (0.00 SEC)Copy the code

Question:

Why does the insert statement in section B work as it did in test 1?

Analysis:

Because InnoDB uses next-key Lock for all rows, it locks not a single value, but a range, which is the same as the first test. However, when the index of the query has a unique attribute, next-key Lock optimizes and demotes it to a Record Lock, which locks only the index itself, not the range.

How do I make test one not block? canExplicitly close Gap Lock:

1: Changes the transaction isolation level to Read Committed. SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2: Change the innodb_locks_unsafe_for_binlog parameter to 1.

Conclusion:

This article has only done some illustrative tests on next-key locks, but there are many other aspects of locking that you can learn from the relevant resources.