添加链接 注册    登录
link管理
链接快照平台
  • 输入网页链接，自动生成快照
  • 标签化管理网页链接
相关文章推荐
很拉风的灭火器  ·  WHERE 子句 (Microsoft ...·  4 天前    · 
沉稳的麻辣香锅  ·  ALL、DISTINCT、DISTINCTR ...·  4 天前    · 
有腹肌的李子  ·  Solved: Notebook to ...·  2 天前    · 
胡子拉碴的刺猬  ·  SQL Syntax in Query ...·  2 天前    · 
至今单身的橙子  ·  Select objects by ...·  昨天    · 
奋斗的企鹅  ·  拯救者Y700超控灵动版首销，售价从3399 ...·  1 月前    · 
兴奋的西装  ·  iPhone XS ...·  5 月前    · 
精明的开心果  ·  Axure元件库图标不显示的原因及解决方法- ...·  6 月前    · 
考研的绿豆  ·  checkbox值未在checkbox单击时 ...·  6 月前    · 
含蓄的瀑布  ·  Backup fails with ...·  6 月前    · 
link管理  ›  Mysql的事务隔离级别 | Cheuk的博客
隔离级别 事务隔离级别 mysql select
https://diiyw.com/2019/10/31/mysql-transaction-isolation/
慷慨大方的炒饭
7 月前

  • 脏读 一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，读取到了不想得到的数据了。

  • 幻读 第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

  • 不可重复读 数据库访问中，在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

    InnoDB默认是可重复读的（REPEATABLE READ），查询当前使用的隔离级别 

    1
    2
    3
    4
    5
    6
    7
    		 
    mysql> SELECT @@tx_isolation;
    +-----------------+
    | @@tx_isolation  |
    +-----------------+
    | REPEATABLE-READ |
    +-----------------+
    1 row in set (0.01 sec)

    test 数据库测试新建一张表 person 测试 

    1
    2
    3
    4
    5
    6
    7
    		 
    CREATE TABLE `person` (
      `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
      `city` varchar(255) DEFAULT NULL,
      `age` mediumint(9) DEFAULT NULL,
      `name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

    脏读

    我们开启两个客服端A、B备用

    A 开启事务插入一条数据，不需要提交事务 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    		 
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into person (city)values('china');
    Query OK, 1 row affected (0.01 sec)

    mysql> select * from person;
    +----+-------+------+------+
    | id | city  | age  | name |
    +----+-------+------+------+
    |  1 | china | NULL | NULL |
    +----+-------+------+------+
    1 row in set (0.00 sec)

    B 中并不会查询到A中事务提交的数据，不会出现脏读。 

    1
    2
    		 
    mysql> select * from person;
    Empty set (0.00 sec)

    B 客户端更改为 读未提交 （Read Uncommitted）那么就能读取到A事务中插入的数据。 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    		 
    mysql> set session transaction isolation level read uncommitted;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select * from person;
    +----+-------+------+------+
    | id | city  | age  | name |
    +----+-------+------+------+
    |  1 | china | NULL | NULL |
    +----+-------+------+------+
    1 row in set (0.00 sec)

    查询 A 中隔离级别 

    1
    2
    3
    4
    5
    6
    7
    		 
    mysql> select @@session.tx_isolation;
    +------------------------+
    | @@session.tx_isolation |
    +------------------------+
    | REPEATABLE-READ        |
    +------------------------+
    1 row in set (0.00 sec)

    虽然 A 中的隔离级别是 Repeatable read 但是 B 指定 Read Uncommitted 级别照样读取到了其中的事务数据。

    到此 A 的隔离级别为 Repeatable read ,修改 B 的隔离级别为 Read committed 

    1
    2
    		 
    mysql> set session transaction isolation level read committed;
    Query OK, 0 rows affected (0.00 sec)

    不可重复度

    之前的例子中手动回滚了事务，此时person表是空的。我们 A 和 B 都开启一个事务，A中提交事务，观察 B 查询数据。

    B 开启事务查询数据 

    1
    2
    3
    4
    5
    		 
    mysql> begin;
    Query OK, 0 rows affected (0.01 sec)

    mysql> select * from person;
    Empty set (0.00 sec)

    A 开启事务插入一条记录 

    1
    2
    3
    4
    5
    6
    7
    8
    		 
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into person (city)values('china');
    Query OK, 1 row affected (0.01 sec)

    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)

    B 事务中查询记录 

    1
    2
    3
    4
    5
    6
    7
    		 
    mysql> select * from person;
    +----+-------+------+------+
    | id | city  | age  | name |
    +----+-------+------+------+
    |  2 | china | NULL | NULL |
    +----+-------+------+------+
    1 row in set (0.01 sec)

    B 事务中两次查询到数据不一致。不可重复读就是事务中数据不能进行重复读取，不然会导致数据不一致。

    可重复读

    同上面操作我们修改 B 的隔离级别为 Repeatable read 

    1
    		 
    set session transaction isolation level repeatable read

    此时 A 和 B 的隔离级别是一致的，重复上面的操作

    B 开启事务查询数据 

    1
    2
    3
    4
    5
    6
    7
    		 
    mysql> select * from person;
    +----+-------+------+------+
    | id | city  | age  | name |
    +----+-------+------+------+
    |  2 | china | NULL | NULL |
    +----+-------+------+------+
    1 row in set (0.00 sec)

    A 开启事务插入一条记录 

    1
    2
    3
    4
    5
    6
    7
    8
    		 
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into person (city)values('china');
    Query OK, 1 row affected (0.01 sec)

    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)

    B 事务中查询记录 

    1
    2
    3
    4
    5
    6
    7
    		 
    mysql> select * from person;
    +----+-------+------+------+
    | id | city  | age  | name |
    +----+-------+------+------+
    |  2 | china | NULL | NULL |
    +----+-------+------+------+
    1 row in set (0.00 sec)

    B 事务中两次查询到数据是一致的。

    幻读

    A 和 B 事务中进行操作，步骤接替进行看效果 

    1
    2
    		 
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    		 
    1
    2
    		 
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    		 
    1
    2
    3
    4
    5
    6
    7
    8
    		 
    mysql> select * from person;
    +----+-------+------+------+
    | id | city  | age  | name |
    +----+-------+------+------+
    |  2 | china | NULL | NULL |
    |  3 | china | NULL | NULL |
    +----+-------+------+------+
    2 rows in set (0.01 sec)
    		 
    1
    2
    3
    4
    5
    6
    7
    8
    		 
    mysql> select * from person;
    +----+-------+------+------+
    | id | city  | age  | name |
    +----+-------+------+------+
    |  2 | china | NULL | NULL |
    |  3 | china | NULL | NULL |
    +----+-------+------+------+
    2 rows in set (0.00 sec)
    		 
    1
    2
    		 
    mysql> insert into person (id,city)values(1,'xm');
    Query OK, 1 row affected (0.00 sec)
    		 
    1
    2
    3
    4
    5
    6
    7
    8
    		 
    mysql> select * from person;
    +----+-------+------+------+
    | id | city  | age  | name |
    +----+-------+------+------+
    |  2 | china | NULL | NULL |
    |  3 | china | NULL | NULL |
    +----+-------+------+------+
    2 rows in set (0.00 sec)
    		 
    1
    2
    		 
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    		 
    1
    2
    3
    4
    5
    6
    7
    8
    		 
    mysql> select * from person;
    +----+-------+------+------+
    | id | city  | age  | name |
    +----+-------+------+------+
    |  2 | china | NULL | NULL |
    |  3 | china | NULL | NULL |
    +----+-------+------+------+
    2 rows in set (0.00 sec)
    		 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    		 
    mysql> select * from person;
    +----+-------+------+------+
    | id | city  | age  | name |
    +----+-------+------+------+
    |  1 | xm    | NULL | NULL |
    |  2 | china | NULL | NULL |
    |  3 | china | NULL | NULL |
    +----+-------+------+------+
    3 rows in set (0.00 sec)
    		 
    1
    2
    		 
    mysql> insert into person(id,city)values(1,"xm2");
    ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

    仔细对比可以发现，即使 A 中提交了新数据，但是 B 中并不会找到新增加的数据，所以天真的以为可以添加一条新数据，这时候mysql报出来主键重复了，就像是产生幻觉一样。

    在 B 查询语句可以知道数据库只有两条记录，如果我们在B中更新记录 

    1
    2
    3
    		 
    mysql> update person set city='b';
    Query OK, 3 rows affected (0.01 sec)
    Rows matched: 3  Changed: 3  Warnings: 0

    咦！怎么有三条记录更新，不是两条吗。这同样产生了幻觉。

    * 注意 * 当隔离级别是可重复读，且禁用 innodb_locks_unsafe_for_binlog 的情况下，在搜索和扫描 index 的时候使用的 next-key locks 可以避免幻读。

    当然如果使用update语句，mysql会加锁会保证数据一致。 

    1
    2
    3
    4
    5
    6
    		 
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    mysql> update person set city='a' where id=1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    B 等待 A 释放锁或者超时 

    1
    2
    3
    4
    5
    		 
    mysql> begin;
    Query OK, 0 rows affected (0.01 sec)

    mysql> update person set city='b' where id=1;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    •  
    推荐文章
    很拉风的灭火器  ·  WHERE 子句 (Microsoft Access SQL) | Microsoft Learn
    4 天前
    沉稳的麻辣香锅  ·  ALL、DISTINCT、DISTINCTROW、top 谓词 (Microsoft Access SQL) | Microsoft Learn
    4 天前
    有腹肌的李子  ·  Solved: Notebook to read complex JSON array - Microsoft Fabric Community
    2 天前
    胡子拉碴的刺猬  ·  SQL Syntax in Query Service | Adobe Experience Platform
    2 天前
    至今单身的橙子  ·  Select objects by key/value - Scripting - McNeel Forum
    昨天
    奋斗的企鹅  ·  拯救者Y700超控灵动版首销，售价从3399元起跳_腾讯新闻
    1 月前
    兴奋的西装  ·  iPhone XS 订阅的第三方日历，同步日程… - Apple 社区
    5 月前
    精明的开心果  ·  Axure元件库图标不显示的原因及解决方法-即时设计
    6 月前
    考研的绿豆  ·  checkbox值未在checkbox单击时进行选择_单击按钮时添加CheckBox_单击时更改asp:CheckBox的文本 - 腾讯云开发者社区 - 腾讯云
    6 月前
    含蓄的瀑布  ·  Backup fails with InnoDB: Operating system error number 0 - Percona XtraBackup - Percona Community F
    6 月前
    Link管理   ·   51好读   ·   Sov5搜索   ·   小百科
    link管理 - 链接快照平台