MySQL中InnoDB事物的隔离模式

MySQL struggling 1284次浏览 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,您需要填写昵称和邮箱!

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