欢迎各位兄弟 发布技术文章

这里的技术是共享的

You are here

首页

MySQL中InnoDB事物的隔离模式

星期五, 2020-09-25 08:40 — adminshiping1

MySQL中InnoDB事物的隔离模式

 MySQL  struggling   1840次浏览  0个评论

一、 什么是事务
事务就是一段sql 语句的批处理，但是这个批处理是一个atom（原子） ，不可分割，要么都执行，要么回滚（rollback）都不执行。

二、如何在MYSQL 中使用事务

MySQL5.5.8版本以前默认的 MyISAM 引擎是不支持事务的，使用show engines 可以看到支持的和默认的engine。

  1. <code>
  2. mysql> show  engines;
  3. +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
  4. | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
  5. +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
  6. | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
  7. | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
  8. | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
  9. | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
  10. | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
  11. | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
  12. | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
  13. | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
  14. +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
  15. 8 rows in set (0.00 sec)
  16. </code>

三，使用事物

1，Read Uncommitted（读取未提交内容）
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

A端：

  1. <code>
  2. mysql> select @@autocommit;    
  3. +--------------+
  4. | @@autocommit |
  5. +--------------+
  6. |            1 |
  7. +--------------+
  8. 1 row in set (0.00 sec)
  9. mysql> set autocommit=0;     #取消自动提交，如果没有明确启动事务，autocommit能实现自动提交，每一个操作都直接提交；
  10.  
  11. mysql> set tx_isolation='read-uncommitted';   #修改隔离性
  12. Query OK, 0 rows affected (0.00 sec)
  13.  
  14. mysql> select @@tx_isolation;    #查看隔离性
  15. +------------------+
  16. | @@tx_isolation   |                 
  17. +------------------+
  18. | READ-UNCOMMITTED |              ------》MySQL默认的事物隔离级别
  19. +------------------+
  20. 1 row in set (0.00 sec)
  21.  
  22. mysql> start transaction;    #开启事物
  23. Query OK, 0 rows affected (0.00 sec)
  24.  
  25. mysql> select * from  students;
  26. +-----+----------+------+--------+------+------+------+---------------------+
  27. | SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
  28. +-----+----------+------+--------+------+------+------+---------------------+
  29. |   1 | GuoJing  |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
  30. |   2 | YangGuo  |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
  31. |   3 | DingDian |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
  32. +-----+----------+------+--------+------+------+------+---------------------+
  33. 3 rows in set (0.00 sec)
  34.  
  35. mysql> update students set age=50 where sid<4;
  36. Query OK, 3 rows affected (0.00 sec)
  37. Rows matched: 3  Changed: 3  Warnings: 0
  38.  
  39. mysql> select * from  students;
  40. +-----+----------+------+--------+------+------+------+---------------------+
  41. | SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
  42. +-----+----------+------+--------+------+------+------+---------------------+
  43. |   1 | GuoJing  |   50 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
  44. |   2 | YangGuo  |   50 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
  45. |   3 | DingDian |   50 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
  46. +-----+----------+------+--------+------+------+------+---------------------+
  47. 3 rows in set (0.00 sec)
  48. </code>

B端：

  1. <code>
  2. mysql> select @@tx_isolation;
  3. +-----------------+
  4. | @@tx_isolation  |
  5. +-----------------+
  6. | REPEATABLE-READ |                     ------》注意：客户端B可能与A不同步，需手动设置下
  7. +-----------------+
  8. 1 row in set (0.00 sec)
  9.  
  10. mysql> set tx_isolation='read-uncommitted';    
  11. Query OK, 0 rows affected (0.00 sec)
  12.  
  13. mysql> start transaction;
  14. Query OK, 0 rows affected (0.00 sec)
  15.  
  16. mysql> select * from  students;    
  17. +-----+----------+------+--------+------+------+------+---------------------+
  18. | SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
  19. +-----+----------+------+--------+------+------+------+---------------------+
  20. |   1 | GuoJing  |   50 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
  21. |   2 | YangGuo  |   50 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
  22. |   3 | DingDian |   50 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
  23. +-----+----------+------+--------+------+------+------+---------------------+
  24. 3 rows in set (0.00 sec)
  25. </code>

A端：

  1. <code>
  2. mysql> rollback;
  3. Query OK, 0 rows affected (0.03 sec)
  4.  
  5. mysql> select * from  students;
  6. +-----+----------+------+--------+------+------+------+---------------------+
  7. | SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
  8. +-----+----------+------+--------+------+------+------+---------------------+
  9. |   1 | GuoJing  |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
  10. |   2 | YangGuo  |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
  11. |   3 | DingDian |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
  12. +-----+----------+------+--------+------+------+------+---------------------+
  13. 3 rows in set (0.00 sec)
  14.  
  15. mysql> commit;
  16. Query OK, 0 rows affected (0.00 sec)
  17. </code>

B端：

  1. <code>
  2. mysql> select * from  students;     ------》A端执行rollback后执行词句
  3. +-----+----------+------+--------+------+------+------+---------------------+
  4. | SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
  5. +-----+----------+------+--------+------+------+------+---------------------+
  6. |   1 | GuoJing  |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
  7. |   2 | YangGuo  |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
  8. |   3 | DingDian |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
  9. +-----+----------+------+--------+------+------+------+---------------------+
  10. 3 rows in set (0.00 sec)
  11.  
  12. mysql> commit;
  13. Query OK, 0 rows affected (0.00 sec)
  14. </code>

可以看出，当设置为’read-uncommitted’（读未提交时），没有提交也能看到别人修改的数据。

2，Read Committed（读取提交内容）
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

A端：

  1. <code>
  2. mysql> set tx_isolation='read-committed';
  3. Query OK, 0 rows affected (0.00 sec)
  4.  
  5. mysql> start transaction;
  6. Query OK, 0 rows affected (0.00 sec)
  7.  
  8. mysql> select * from  students;
  9. +-----+----------+------+--------+------+------+------+---------------------+
  10. | SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
  11. +-----+----------+------+--------+------+------+------+---------------------+
  12. |   1 | GuoJing  |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
  13. |   2 | YangGuo  |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
  14. |   3 | DingDian |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
  15. +-----+----------+------+--------+------+------+------+---------------------+
  16. 3 rows in set (0.00 sec)
  17.  
  18. mysql> update students set age=50 where sid<4;
  19. Query OK, 3 rows affected (0.06 sec)
  20. Rows matched: 3  Changed: 3  Warnings: 0
  21.  
  22. mysql> select * from  students;
  23. +-----+----------+------+--------+------+------+------+---------------------+
  24. | SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
  25. +-----+----------+------+--------+------+------+------+---------------------+
  26. |   1 | GuoJing  |   50 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
  27. |   2 | YangGuo  |   50 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
  28. |   3 | DingDian |   50 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
  29. +-----+----------+------+--------+------+------+------+---------------------+
  30. 3 rows in set (0.00 sec)
  31. </code>

B端：

  1. <code>
  2. mysql> start transaction;
  3. mysql> set tx_isolation='read-committed';
  4. Query OK, 0 rows affected (0.00 sec)
  5.  
  6. mysql> select * from  students;
  7. +-----+----------+------+--------+------+------+------+---------------------+
  8. | SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
  9. +-----+----------+------+--------+------+------+------+---------------------+
  10. |   1 | GuoJing  |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
  11. |   2 | YangGuo  |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
  12. |   3 | DingDian |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
  13. +-----+----------+------+--------+------+------+------+---------------------+
  14. 3 rows in set (0.00 sec)
  15. </code>

A端：

  1. <code>
  2. mysql> commit;
  3. Query OK, 0 rows affected (0.00 sec)
  4. </code>

B端:

  1. <code>
  2. mysql> select * from  students;
  3. +-----+----------+------+--------+------+------+------+---------------------+
  4. | SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
  5. +-----+----------+------+--------+------+------+------+---------------------+
  6. |   1 | GuoJing  |   50 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
  7. |   2 | YangGuo  |   50 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
  8. |   3 | DingDian |   50 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
  9. +-----+----------+------+--------+------+------+------+---------------------+
  10. 3 rows in set (0.00 sec)
  11.  
  12. mysql> commit;
  13. Query OK, 0 rows affected (0.00 sec)
  14. </code>

可以看出，只有A端提交后，B端才能看到其修改的内容。

3，Repeatable Read（可重读）
这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

A端：

  1. <code>
  2. mysql> set tx_isolation='repeatable-read';
  3. Query OK, 0 rows affected (0.00 sec)
  4.  
  5. mysql> start transaction;
  6. Query OK, 0 rows affected (0.00 sec)
  7.  
  8. mysql> select * from  students;
  9. +-----+----------+------+--------+------+------+------+---------------------+
  10. | SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
  11. +-----+----------+------+--------+------+------+------+---------------------+
  12. |   1 | GuoJing  |   50 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
  13. |   2 | YangGuo  |   50 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
  14. |   3 | DingDian |   50 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
  15. +-----+----------+------+--------+------+------+------+---------------------+
  16. 3 rows in set (0.00 sec)
  17.  
  18. mysql> update students set age=0 where sid<4；
  19. Query OK, 3 rows affected (0.00 sec)
  20. Rows matched: 3  Changed: 3  Warnings: 0
  21.  
  22. mysql> select * from  students;
  23. +-----+----------+------+--------+------+------+------+---------------------+
  24. | SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
  25. +-----+----------+------+--------+------+------+------+---------------------+
  26. |   1 | GuoJing  |    0 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
  27. |   2 | YangGuo  |    0 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
  28. |   3 | DingDian |    0 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
  29. +-----+----------+------+--------+------+------+------+---------------------+
  30. 3 rows in set (0.00 sec)
  31. </code>

B端：

  1. <code>
  2. mysql> start transaction;
  3. Query OK, 0 rows affected (0.00 sec)
  4.  
  5. mysql> select * from  students;
  6. +-----+----------+------+--------+------+------+------+---------------------+
  7. | SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
  8. +-----+----------+------+--------+------+------+------+---------------------+
  9. |   1 | GuoJing  |   50 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
  10. |   2 | YangGuo  |   50 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
  11. |   3 | DingDian |   50 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
  12. +-----+----------+------+--------+------+------+------+---------------------+
  13. 3 rows in set (0.00 sec)
  14. </code>

A端：

  1. <code>
  2. mysql> commit;
  3. Query OK, 0 rows affected (0.00 sec)
  4. </code>

B端：

  1. <code>
  2. mysql> select * from  students;
  3. +-----+----------+------+--------+------+------+------+---------------------+
  4. | SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
  5. +-----+----------+------+--------+------+------+------+---------------------+
  6. |   1 | GuoJing  |   50 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
  7. |   2 | YangGuo  |   50 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
  8. |   3 | DingDian |   50 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
  9. +-----+----------+------+--------+------+------+------+---------------------+
  10. 3 rows in set (0.00 sec)
  11.  
  12. mysql> commit;
  13. Query OK, 0 rows affected (0.00 sec)
  14.  
  15. mysql> select * from  students;
  16. +-----+----------+------+--------+------+------+------+---------------------+
  17. | SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
  18. +-----+----------+------+--------+------+------+------+---------------------+
  19. |   1 | GuoJing  |    0 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
  20. |   2 | YangGuo  |    0 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
  21. |   3 | DingDian |    0 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
  22. +-----+----------+------+--------+------+------+------+---------------------+
  23. 3 rows in set (0.00 sec)
  24. </code>

在B客户端上提交后可以看到新数据。
也就是说在可重复读隔离级别只能读取已经提交的数据，并且在一个事务内，读取的数据就是事务开始时的数据。

4，Serializable（可串行化）
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

A端：

  1. <code>
  2. mysql> set tx_isolation='serializable';
  3. Query OK, 0 rows affected (0.00 sec)
  4.  
  5. mysql> start transaction;
  6. Query OK, 0 rows affected (0.02 sec)
  7.  
  8. mysql> select * from  students;
  9. +-----+----------+------+--------+------+------+------+---------------------+
  10. | SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
  11. +-----+----------+------+--------+------+------+------+---------------------+
  12. |   1 | GuoJing  |   50 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
  13. |   2 | YangGuo  |   50 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
  14. |   3 | DingDian |   50 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
  15. +-----+----------+------+--------+------+------+------+---------------------+
  16. 3 rows in set (0.00 sec)
  17.  
  18. mysql> update students set age=0 where sid<4;
  19. Query OK, 3 rows affected (0.00 sec)
  20. Rows matched: 3  Changed: 3  Warnings: 0
  21.  
  22. mysql> select * from  students;
  23. +-----+----------+------+--------+------+------+------+---------------------+
  24. | SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
  25. +-----+----------+------+--------+------+------+------+---------------------+
  26. |   1 | GuoJing  |    0 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
  27. |   2 | YangGuo  |    0 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
  28. |   3 | DingDian |    0 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
  29. +-----+----------+------+--------+------+------+------+---------------------+
  30. 3 rows in set (0.00 sec)
  31. </code>

B端：

  1. <code>
  2. mysql> set tx_isolation='serializable';
  3. Query OK, 0 rows affected (0.00 sec)
  4.  
  5. mysql> start transaction;
  6. Query OK, 0 rows affected (0.00 sec)
  7.  
  8. mysql> select * from students;
  9.                           -------------》》光标会卡到此处，等待A端提交
  10. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction   #超过一定时间会出现此错误
  11.  
  12. mysql> select * from students;   #再次执行
  13. </code>

A端：

  1. <code>
  2. mysql> commit;      #在A端提交
  3. Query OK, 0 rows affected (0.00 sec)  
  4. </code>

B端：

  1. <code>
  2. mysql> select * from students;
  3. +-----+----------+------+--------+------+------+------+---------------------+
  4. | SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
  5. +-----+----------+------+--------+------+------+------+---------------------+
  6. |   1 | GuoJing  |    0 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
  7. |   2 | YangGuo  |    0 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
  8. |   3 | DingDian |    0 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
  9. +-----+----------+------+--------+------+------+------+---------------------+
  10. 3 rows in set (7.01 sec)        --------》此时可以看到查询到的结果已经改变，花费7.01秒！！！然而只是等待时间
  11.  
  12. mysql> commit;
  13. Query OK, 0 rows affected (0.00 sec)
  14. </code>

可以看到在A客户端操作表时会锁定该数据，如果B客户端想要操作就需要等待A客户端释放。

最后思考：事物隔离级别越高并发能力越低，正确的使用InnoDB 隔离模式，能够让您的应用程序得到最佳性能。你得到的好处可能不同，在某些情况下，也可能没什么区别。


来自  http://www.tianfeiyu.com/?p=674

普通分类: 
mysql
Powered by Drupal

友情链接

校园好文网

 www.shipingzhong.cn  个人技术网_前端_后台    备案号:苏ICP备18010659号-2