一、 什么是事务
事务就是一段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 隔离模式,能够让您的应用程序得到最佳性能。你得到的好处可能不同,在某些情况下,也可能没什么区别。