MySQL中InnoDB事物的隔离模式

MySQL struggling 1443次浏览 0个评论

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

二、如何在MYSQL 中使用事务

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

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

三，使用事物

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

A端：

<code>
mysql> select @@autocommit;    
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
mysql> set autocommit=0;     #取消自动提交，如果没有明确启动事务，autocommit能实现自动提交，每一个操作都直接提交；

mysql> set tx_isolation='read-uncommitted';   #修改隔离性
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;    #查看隔离性
+------------------+
| @@tx_isolation   |                 
+------------------+
| READ-UNCOMMITTED |              ------》MySQL默认的事物隔离级别
+------------------+
1 row in set (0.00 sec)

mysql> start transaction;    #开启事物
Query OK, 0 rows affected (0.00 sec)

mysql> select * from  students;
+-----+----------+------+--------+------+------+------+---------------------+
| SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+----------+------+--------+------+------+------+---------------------+
|   1 | GuoJing  |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo  |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
+-----+----------+------+--------+------+------+------+---------------------+
3 rows in set (0.00 sec)

mysql> update students set age=50 where sid<4;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from  students;
+-----+----------+------+--------+------+------+------+---------------------+
| SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+----------+------+--------+------+------+------+---------------------+
|   1 | GuoJing  |   50 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo  |   50 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian |   50 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
+-----+----------+------+--------+------+------+------+---------------------+
3 rows in set (0.00 sec)
</code>

B端：

<code>
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |                     ------》注意：客户端B可能与A不同步，需手动设置下
+-----------------+
1 row in set (0.00 sec)

mysql> set tx_isolation='read-uncommitted';    
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from  students;    
+-----+----------+------+--------+------+------+------+---------------------+
| SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+----------+------+--------+------+------+------+---------------------+
|   1 | GuoJing  |   50 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo  |   50 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian |   50 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
+-----+----------+------+--------+------+------+------+---------------------+
3 rows in set (0.00 sec)
</code>

A端：

<code>
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from  students;
+-----+----------+------+--------+------+------+------+---------------------+
| SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+----------+------+--------+------+------+------+---------------------+
|   1 | GuoJing  |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo  |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
+-----+----------+------+--------+------+------+------+---------------------+
3 rows in set (0.00 sec)

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

B端：

<code>
mysql> select * from  students;     ------》A端执行rollback后执行词句
+-----+----------+------+--------+------+------+------+---------------------+
| SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+----------+------+--------+------+------+------+---------------------+
|   1 | GuoJing  |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo  |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
+-----+----------+------+--------+------+------+------+---------------------+
3 rows in set (0.00 sec)

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

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

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

A端：

<code>
mysql> set tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from  students;
+-----+----------+------+--------+------+------+------+---------------------+
| SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+----------+------+--------+------+------+------+---------------------+
|   1 | GuoJing  |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo  |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
+-----+----------+------+--------+------+------+------+---------------------+
3 rows in set (0.00 sec)

mysql> update students set age=50 where sid<4;
Query OK, 3 rows affected (0.06 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from  students;
+-----+----------+------+--------+------+------+------+---------------------+
| SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+----------+------+--------+------+------+------+---------------------+
|   1 | GuoJing  |   50 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo  |   50 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian |   50 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
+-----+----------+------+--------+------+------+------+---------------------+
3 rows in set (0.00 sec)
</code>

B端：

<code>
mysql> start transaction;
mysql> set tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from  students;
+-----+----------+------+--------+------+------+------+---------------------+
| SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+----------+------+--------+------+------+------+---------------------+
|   1 | GuoJing  |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo  |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
+-----+----------+------+--------+------+------+------+---------------------+
3 rows in set (0.00 sec)
</code>

A端：

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

B端:

<code>
mysql> select * from  students;
+-----+----------+------+--------+------+------+------+---------------------+
| SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+----------+------+--------+------+------+------+---------------------+
|   1 | GuoJing  |   50 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo  |   50 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian |   50 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
+-----+----------+------+--------+------+------+------+---------------------+
3 rows in set (0.00 sec)

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

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

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

A端：

<code>
mysql> set tx_isolation='repeatable-read';
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from  students;
+-----+----------+------+--------+------+------+------+---------------------+
| SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+----------+------+--------+------+------+------+---------------------+
|   1 | GuoJing  |   50 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo  |   50 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian |   50 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
+-----+----------+------+--------+------+------+------+---------------------+
3 rows in set (0.00 sec)

mysql> update students set age=0 where sid<4；
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from  students;
+-----+----------+------+--------+------+------+------+---------------------+
| SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+----------+------+--------+------+------+------+---------------------+
|   1 | GuoJing  |    0 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo  |    0 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian |    0 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
+-----+----------+------+--------+------+------+------+---------------------+
3 rows in set (0.00 sec)
</code>

B端：

<code>
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from  students;
+-----+----------+------+--------+------+------+------+---------------------+
| SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+----------+------+--------+------+------+------+---------------------+
|   1 | GuoJing  |   50 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo  |   50 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian |   50 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
+-----+----------+------+--------+------+------+------+---------------------+
3 rows in set (0.00 sec)
</code>

A端：

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

B端：

<code>
mysql> select * from  students;
+-----+----------+------+--------+------+------+------+---------------------+
| SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+----------+------+--------+------+------+------+---------------------+
|   1 | GuoJing  |   50 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo  |   50 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian |   50 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
+-----+----------+------+--------+------+------+------+---------------------+
3 rows in set (0.00 sec)

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

mysql> select * from  students;
+-----+----------+------+--------+------+------+------+---------------------+
| SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+----------+------+--------+------+------+------+---------------------+
|   1 | GuoJing  |    0 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo  |    0 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian |    0 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
+-----+----------+------+--------+------+------+------+---------------------+
3 rows in set (0.00 sec)
</code>

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

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

A端：

<code>
mysql> set tx_isolation='serializable';
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from  students;
+-----+----------+------+--------+------+------+------+---------------------+
| SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+----------+------+--------+------+------+------+---------------------+
|   1 | GuoJing  |   50 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo  |   50 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian |   50 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
+-----+----------+------+--------+------+------+------+---------------------+
3 rows in set (0.00 sec)

mysql> update students set age=0 where sid<4;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from  students;
+-----+----------+------+--------+------+------+------+---------------------+
| SID | Name     | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+----------+------+--------+------+------+------+---------------------+
|   1 | GuoJing  |    0 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo  |    0 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian |    0 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
+-----+----------+------+--------+------+------+------+---------------------+
3 rows in set (0.00 sec)
</code>

B端：

<code>
mysql> set tx_isolation='serializable';
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from students;
                          -------------》》光标会卡到此处，等待A端提交
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction   #超过一定时间会出现此错误

mysql> select * from students;   #再次执行
</code>

A端：

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

B端：

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

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

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

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

DevOps-田飞雨 》》转载请注明源地址
喜欢 (0)or分享 (0)
发表我的评论
取消评论
验证图片
*

表情 贴图 加粗 链接 删除线 居中 斜体 签到

Hi，您需要填写昵称和邮箱！

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址