ITPub博客

mysql事务隔离级别

原创 MySQL 作者:水逸冰 时间:2018-11-08 15:55:44 0 删除 编辑

本文主要讨论 mysql 中的事务隔离级别。

 

总述

Innodb 存储引擎实现了四中事务隔离级别。

 

(一)   读未提交( READ UNCOMMITTED ),简称 RU ,一个事务可以读到另一个事务的未提交更新,也就是脏读的现象。

(二)   读已提交( READ COMMITTED , 简称 RC ,也叫不可重复读, Oracle 的默认隔离级别。一个事务中可以读到其他事务的提交 dml 动作。该隔离级别解决了脏读的问题,但是针对提交的 insert 语句等,会出现幻读的现象。

(三)   可重复读( REPEATABLE-READ )是 mysql 默认的事务隔离级别,简称 RR 。该级别解决了之前存在的幻读,脏读现象。

(四)   串行( SERIALIZABLE ),对读取的数据行加表级共享锁,更新数据时加表级排他锁,基本没什么并发能力了。

 

 

RU 实验

mysql5.7.22 默认级别

mysql> show variables like '%iso%';

+-----------------------+-----------------+

| Variable_name         | Value           |

+-----------------------+-----------------+

| transaction_isolation | REPEATABLE-READ   |

| tx_isolation          | REPEATABLE-READ |

+-----------------------+-----------------+

2 rows in set (0.00 sec)

 

两个会话在会话级别设置成读未提交级别。

 

会话级别修改

mysql> set session   tx_isolation='READ-UNCOMMITTED';

Query OK, 0 rows affected, 1 warning   (0.00 sec)

 

mysql> show variables like   '%iso%';               

+-----------------------+------------------+

| Variable_name         | Value            |

+-----------------------+------------------+

| transaction_isolation |   READ-UNCOMMITTED |

| tx_isolation          | READ-UNCOMMITTED |

+-----------------------+------------------+

2 rows in set (0.00 sec)

 

会话 a

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from test1;

+-----------------+

| a               |

+-----------------+

| 1               |

| 255.255.255.255 |

+-----------------+

2 rows in set (0.00 sec)

 

会话 b 插入一条数据

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into test1 values(2);

Query OK, 1 row affected (0.00 sec)

 

 

返回会话 a

mysql> select * from test1;

+-----------------+

| a               |

+-----------------+

| 1               |

| 2               |

| 255.255.255.255 |

+-----------------+

3 rows in set (0.00 sec)

 

 

 

 

RC 实验

设置会话级别隔离级别

mysql> set session   tx_isolation='READ-COMMITTED';

Query OK, 0 rows affected, 1 warning   (0.00 sec)

 

mysql> show variables like '%iso%';             

+-----------------------+----------------+

| Variable_name         | Value          |

+-----------------------+----------------+

| transaction_isolation | READ-COMMITTED   |

| tx_isolation          | READ-COMMITTED |

+-----------------------+----------------+

 

会话 a

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from test1;

+-----------------+

| a               |

+-----------------+

| 1               |

| 2               |

| 255.255.255.255 |

+-----------------+

3 rows in set (0.00 sec)

 

会话 b 插入一条数据,未提交

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into test1 values(3);

Query OK, 1 row affected (0.01 sec)

 

此时会话 a 看不到

mysql> select * from test1;

+-----------------+

| a               |

+-----------------+

| 1               |

| 2               |

| 255.255.255.255 |

+-----------------+

3 rows in set (0.00 sec)

 

 

会话 b 提交

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

 

返回会话 a

mysql> select * from test1;

+-----------------+

| a               |

+-----------------+

| 1               |

| 2               |

| 255.255.255.255 |

| 3               |

+-----------------+

4 rows in set (0.00 sec)

这种现象就是幻影读了。

 

 

 

 

 

RR 实验

mysql> show variables like '%tx%';

+---------------+-----------------+

| Variable_name | Value           |

+---------------+-----------------+

| tx_isolation  | REPEATABLE-READ |

| tx_read_only  | OFF             |

+---------------+-----------------+

2 rows in set (0.00 sec)

 

会话 a

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from test1;

+-----------------+

| a               |

+-----------------+

| 1               |

| 2               |

| 255.255.255.255 |

| 3               |

| 4               |

| 5               |

+-----------------+

6 rows in set (0.00 sec)

 

会话 b 插入一条数据,未提交

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into test1 values(6);

Query OK, 1 row affected (0.00 sec)

 

会话 a 没有看到会话 b 新插入的数据

mysql> select * from test1;

+-----------------+

| a                 |

+-----------------+

| 1               |

| 2               |

| 255.255.255.255 |

| 3               |

| 4               |

| 5               |

+-----------------+

6 rows in set (0.00 sec)

 

会话 b 提交

mysql> commit;

 

会话 a 还是没有看到

mysql> select * from test1;

+-----------------+

| a               |

+-----------------+

| 1               |

| 2               |

| 255.255.255.255 |

| 3               |

| 4               |

| 5               |

+-----------------+

6 rows in set (0.00 sec)

 

想要看到的话需要加 for   update

mysql> select * from test1 for update;

+-----------------+

| a               |

+-----------------+

| 1               |

| 2               |

| 255.255.255.255 |

| 3               |

| 4               |

| 5               |

| 6               |

+-----------------+

7 rows in set (0.00 sec)

 

 

串行实验

串行( SERIALIZABLE ),对读取的数据行加表级共享锁,更新数据时加表级排他锁,基本没什么并发能力了。

 

设置会话级别事务隔离级别

mysql> set session   tx_isolation='serializable';

Query OK, 0 rows affected, 1 warning   (0.00 sec)

 

 

会话 a 删除一行数据

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> delete from test1 where a='6';

Query OK, 1 row affected (0.00 sec)

 

会话 bselect 语句都会夯住

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from test1;

 

 

 

会话 c 查看锁的状态

 INFORMATION_SCHEMA.INNODB_LOCKS;

+-------------------------+-----------------+-----------+-----------+------------------+
| lock_id                 | lock_trx_id     | lock_mode | lock_type | lock_table       |
+-------------------------+-----------------+-----------+-----------+------------------+
| 421706548341472:158:4:8 | 421706548341472 | S          | RECORD    | `mingdb`.`test1` |
| 14604:158:4:8           | 14604           | X          | RECORD    | `mingdb`.`test1` |
+-------------------------+-----------------+-----------+-----------+------------------+

 

过了一段时间后,会话 b 返回

ERROR 1205 (HY000): Lock wait timeout   exceeded; try restarting transaction

这个时间是由 innodb_lock_wait_timeout 决定的

mysql> show variables like   'innodb_lock_wait_timeout';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| innodb_lock_wait_timeout | 50    |

+--------------------------+-------+

1 row in set (0.01 sec)

 

 

 

 

 

 


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31480688/viewspace-2219253/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论
精通oracle和linux,热衷于研究oracle,擅长shell和Python编程,喜欢自动化运维。

注册时间:2017-08-05

  • 博文量
    53
  • 访问量
    18233