MySQL中的事务

为什么需要事务

现在的软件基本上都是多用户、多程序、多线程的，对同一个表可能同时有很多人在用，为保持数据的一致性，所以提出了事务的概念。

一个事务一般包含多个操作，这些操作必须当成一个整体来执行，要么都成功，要么都失败，不允许部分成功和部分失败。假如要从A的账户给B的账户转账1000元，那么A的账户减少1000元，B的账户就要增加1000元，这两个update语句必须作为一个整体来执行，不然可能会导致A的账户扣钱了，但是B的账户没有加钱这种情况，这样就会导致数据不一致。

什么存储引擎支持事务

查看数据库中的哪些存储引擎支持事务？只有InnoDB支持事务。

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

查看mysql当前默认的存储引擎？

mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+

查看某张表的存储引擎？

mysql> show create table emp;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp   | CREATE TABLE `emp` (
  `empno` int(4) NOT NULL,
  `ename` varchar(10) DEFAULT NULL,
  `job` varchar(9) DEFAULT NULL,
  `mgr` int(4) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` float(7,2) DEFAULT NULL,
  `comm` float(7,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL,
  PRIMARY KEY (`empno`),
  KEY `fk_deptno` (`deptno`),
  CONSTRAINT `fk_deptno` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

修改表的存储引擎？

mysql> alter table testmyisam engine=innodb;

事务的特性

事务应该具有4个特性：

原子性
一致性
隔离性
持久性

这四个特性通常称为ACID特性。

原子性

原子性（Atomicity）：一个事务必须被视为一个不可分割的最小单元，整个事务中的所有操作要么全部提交成功，要么全部失败，对于一个事务来说，不可能只执行其中的一部分操作。

例如上面的转账例子中，A的账户减少1000元和B的账户增加1000元这两个操作要么全部成功，要么全部失败，不能出现A的账户扣钱了，但是B的账户没有加钱这种情况。

一致性

一致性（Consistency）：指事务将数据库从一种一致性转换到另外一种一致性状态，在事务开始之前和事务结束之后数据库中数据的完整性没有被破坏。

例如上面的转账例子中，A的账户减少了1000元，那么B的账户就要增加1000元，不能出现B的账户增加1500的情况，一致性与原子性是密切相关的。

持久性

持久性（Durability）也称永久性（permanence），指一个事务一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，已经提交的修改数据也不会丢失。

隔离性

隔离性（Isolation）：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

事务的隔离级别：

事务隔离级别

脏读

不可重复读

幻读

读未提交

是

不可重复读

否

是

可重复读

否

是

串行化

否

未提交读

未提交读（READ UNCOMMITED）也称为读未提交：事务中的修改，即使没有提交，对其它事务也是可见的。

脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。

mysql的默认隔离级别为REPEATABLE-READ，可以使用以下命令查看：

mysql> show variables like '%tx_isolation%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

脏读的演示：

session1

session2

set session transaction isolation level read uncommitted;

begin;

update account set balance=balance-50 where id=1;

select balance from account where id=1; – 950

rollback;

select balance from account where id=1; – 1000

session2中读取到了session1中已经修改但是还没提交的数据，这就是脏读。

已提交读

已提交读（READ COMMITED）：一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。

不可重复读：事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据做了更新并提交，导致事务A多次读取同一数据时结果不一致。

不可重复读的演示：

session1

session2

set session transaction isolation level read committed;

begin;

update account set balance=balance-50 where id=1;

select balance from account where id=1; – 1000

commit;

select balance from account where id=1; – 950

可重复读

可重复读（REPEATABLE READ）：保证在同一个事务中多次读取同样数据的结果是一样的。

幻读：事务A将数据库中所有数据都删除的时候，但是事务B就在这个时候新插入了一条记录，当事务A删除结束后发现还有一条数据，就好像发生了幻觉一样。这就叫幻读。

在mysql中无法演示幻读，因为mysql的隔离级别REPEATABLE READ使用间隙锁已经解决了幻读。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

串行化

串行化（SERIALIZABLE）：强制让事务串行执行，就是把表锁住。

串行化的演示：

session1

session2

set session transaction isolation level serializable;

begin;

select count(*) from account; – 1

select count(*) from account; --1

insert into account values(0, 500); – 阻塞

commit;

插入成功

总结：

事务隔离级别为可重复读时，如果有索引（包括主键索引）的时候，以索引列为条件更新数据，会存在间隙锁间、行锁、页锁的问题，从而锁住一些行；如果没有索引，更新数据时会锁住整张表。
事务隔离级别为串行化时，读写数据都会锁住整张表。
隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。

事务的语法

开启事务

begin
start transaction
begin work

事务回滚

rollback

事务提交

commit

还原点

savepoint

mysql> show variables like '%autocommit%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ mysql> create table test_demo(a int, b int, c int); mysql> set autocommit=0; mysql> insert into test_demo values(5,5,5); mysql> savepoint s1; mysql> insert into test_demo values(6,6,6); mysql> savepoint s2; mysql> insert into test_demo values(7,7,7); mysql> savepoint s3; mysql> select * from test_demo; +------+------+------+ | a | b | c | +------+------+------+ | 5 | 5 | 5 | | 6 | 6 | 6 | | 7 | 7 | 7 | +------+------+------+ mysql> rollback to savepoint s2; mysql> select * from test_demo; +------+------+------+ | a | b | c | +------+------+------+ | 5 | 5 | 5 | | 6 | 6 | 6 | +------+------+------+ mysql> rollback;

更多精彩内容关注本人公众号：架构师升级之路
MySQL中的事务