my11_mysql事务隔离
概述
************************************************
Mysql有四个事务隔离级别，默认隔离级别为RR，开启一个事务可以使用 START TRANSACTION。
START TRANSACTION
[transaction_characteristic [, transaction_characteristic] ...]
transaction_characteristic:
WITH CONSISTENT SNAPSHOT
| READ WRITE
| READ ONLY
START TRANSACTION默认为READ ONLY方式，但若在其中执行DML语句时，自动转化为READ WRITE方式。开启事务除非显式commit或者遇到DDL语句等，否则事务将一直停留
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from information_schema.innodb_trx\G; Empty set (0.20 sec) ERROR: No query specified mysql> mysql> mysql> insert into sbtest1(k,c,pad) select k,c,pad from sbtest1 limit 100000; Query OK, 100000 rows affected (6.89 sec) Records: 100000 Duplicates: 0 Warnings: 0 mysql> select * from information_schema.innodb_trx\G; *************************** 1. row *************************** trx_id: 10044 trx_state: RUNNING trx_started: 2018-08-06 11:25:58 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 101373 trx_mysql_thread_id: 11 trx_query: select * from information_schema.innodb_trx trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 2 trx_lock_structs: 1373 trx_lock_memory_bytes: 139472 trx_rows_locked: 101370 trx_rows_modified: 100000 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 1 row in set (0.00 sec) ERROR: No query specified mysql> commit; Query OK, 0 rows affected (0.47 sec) mysql> select * from information_schema.innodb_trx\G; Empty set (0.00 sec) ERROR: No query specified
测试方法
***********************************************************
开启两个会话A与B，在会话B设置相应的事务隔离级别后，在会话A中插入数据（插入大量数据，而不是一条数据，延长DML事务的时间以观察效果），在会话B中查询SQL。大概分以下三种情况：
1. 会话A插入数据的时间点在会话B执行查询SQL之前
2. 会话A插入数据的时间点在会话B执行查询SQL之后，但查询SQL尚未结束时
2. 会话A正在插入数据一直未结束，会话B不断地执行查询SQL
查看当前事务隔离级别
**********************************************************************
mysql版本
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.11 | +-----------+ 1 row in set (0.04 sec)
现线上运行数据库的事务隔离级别通常为RR级别，MVCC（Multiversion Concurrency Control，多版本并发控制）解决了幻读的问题。
mysql> show variables like '%isolation%'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.01 sec) mysql> select @@global.transaction_isolation; +--------------------------------+ | @@global.transaction_isolation | +--------------------------------+ | REPEATABLE-READ | +--------------------------------+ 1 row in set (0.00 sec) mysql> select @@session.transaction_isolation; +---------------------------------+ | @@session.transaction_isolation | +---------------------------------+ | REPEATABLE-READ | +---------------------------------+ 1 row in set (0.00 sec) mysql> select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ | +-------------------------+ 1 row in set (0.00 sec)
read uncommitted事务隔离级别
*****************************************************
会话A插入120万条记录数据
mysql> insert into sbtest1(k,c,pad) select k,c,pad from sbtest1 limit 1200000;
会话B设置事务隔离级别为read uncommitted
set session transaction isolation level read uncommitted;
start transaction;
select count(*) from sbtest1;
在会话A数据还没有commit还在不断的插入的过程中，会话B查询范围内的数据在不断地增加，也就是会话B可以看到会话A未提交的数据
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 14471333 |
+----------+
1 row in set (27.78 sec)
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 14863456 |
+----------+
1 row in set (22.86 sec)
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 15057947 |
+----------+
1 row in set (8.69 sec)
read committed事务隔离级别
*****************************************************
会话B设置事务隔离级别为read committed
set session transaction isolation level read committed;
start transaction;
mysql> select count(*) from sbtest1 where id > 10000000;
+----------+
| count(*) |
+----------+
| 9919550 |
+----------+
1 row in set (14.01 sec)
在会话B第二次查询之后，在会话A中插入1.2万条记录数据，用时10秒
mysql> insert into sbtest1(k,c,pad) select k,c,pad from sbtest1 limit 12000;
Query OK, 12000 rows affected (10.43 sec)
Records: 12000 Duplicates: 0 Warnings: 0
可以看到会话B查询的结果仍与之前一样，并没有因为会话A插入数据而改变，但会话B的查询时间变长了（由原来的14秒左右变为了41秒左右）
mysql> select count(*) from sbtest1 where id > 10000000;
+----------+
| count(*) |
+----------+
| 9919550 |
+----------+
1 row in set (41.61 sec)
RU离职级别存在脏读，而RC隔离级别中，每次select都会开启一个“一致性快照读”，所以会话A的事务还没有提交的话，读取的结果是一致的，因为快照中的事务列表没有变化。
同时，也正因为RC中是“每次select”都会开启一个“一致性快照读”，如果两次select之间有事务的变化，那么后面的select由于是一个新的快照读，所以可以查询到事务的变化。因此，
在会话B中进行第三次查询，即在会话Acommit之后查询，可以看到数据增加了1.2万条记录
mysql> select count(*) from sbtest1 where id > 10000000;
+----------+
| count(*) |
+----------+
| 9931550 |
+----------+
1 row in set (12.69 sec)
这种在同一个事务里，两次查询结果不一致的现象就是不可重复读
repeatable read事务隔离级别
****************************************************************************
RR事务隔离级别是Mysql数据库的默认事务隔离级别
set session transaction isolation level repeatable read;
RR级别的事务中，会话A中不断插入数据，会话B的结果不变；但会话A数据插入对会话B的查询依然有影响，无数据插入查询用时约0.3秒，插入10万记录，查询用时在4-10秒间波动。
mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%isolation'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | | tx_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 2 rows in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 1500000 | +----------+ 1 row in set (0.28 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 1500000 | +----------+ 1 row in set (10.99 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 1500000 | +----------+ 1 row in set (4.05 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 1500000 | +----------+ 1 row in set (4.04 sec)
那RR又是怎么做到了可重复读呢？
前面说到RC是每次select都开启一个“一致快照读”，而RR是在事务中的第一次select时开启一致性快照读，后面的select不再开启，同一事务中所有select读取的是同一个一致性快照的事务，
所以,每次select得到的结果是一样的。这么做相对RC是有代价的，直接代价就是加锁的时间更长（RC是每次select结束时就自动释放了，RR是等你手工commit结束事务才释放），另外，在内部的实现上，需要更多的锁来保证这个功能。
