一、 什么是事务
事务就是一段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-田飞雨 》》转载请注明源地址
