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 specifiedmysql>
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: 0mysql> 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 specifiedmysql> 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秒间波动。