数据库事务及MySQL实战

该文章将讨论以下主题：

• 数据库事务及其特点

• 数据库事务并发执行的相关问题

• 数据库锁机制及死锁

• 事务隔离级别

• MySQL实战

---

数据库事务及其特点

数据库事务是指为了完成某个业务逻辑而形成的一组和数据库相关的操作，这些操作要么全部执行成功，要么全部实行失败。数据库事务有以下四个特性，简称ACID特性。

•  原子性（Atomic）：事务的所有操作具有原子性，这些操作要么全部执行成功，要不全部执行失败。

• 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。

• 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。

• 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。银行转帐就是事务的一个典型例子。

数据库事务并发执行的相关问题

数据库事务在并发执行过程中可能出现三类读相关的问题和两类写相关的问题，后面会详细说明每种问题出现的场景和解决办法，

• 脏读（Dirty Read）
• 不可重复读（Unrepeatable Read）
• 幻象读（Phantom Read）
• 第一类丢失更新
• 第二类丢失更新

数据库锁机制及死锁

为了解决数据库事务并发执行过程中的问题，数据库本身提供了锁机制，一般分为行级锁和表级锁。在MySQL中，通过对索引加锁来解决事务并发执行过程中的问题，详情请参考另一篇文章：https://blog.csdn.net/funnyrand/article/details/45077313

1. 行共享锁：select * from ... local in share mode;
2. 行独占锁：select * from ... for update;
3. 表共享锁：lock tables table_name read;
4. 表独占锁：lock tables table_name write;

死锁一定出现在锁的循环等待中，下面是一个MySQL死锁的例子，可以发现，当MySQL检查到死锁后会给出相关提示，并自动回滚触发死锁的那个事务。

Steps	Session1	Session2
T1	mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from account for update; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   100 | |  2 |       2 |   200 | +----+---------+-------+ 2 rows in set (0.00 sec)
T2		mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> mysql> select * from users for update; +----+-------+ | id | name  | +----+-------+ |  1 | Tom   | |  2 | Jerry | +----+-------+ 2 rows in set (0.00 sec)
T3	mysql> select * from users for update; // Block here ...
T4		mysql> select * from account where id=1; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   100 | +----+---------+-------+ 1 row in set (0.00 sec) mysql> select * from account for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
T5	+----+-------+ | id | name  | +----+-------+ |  1 | Tom   | |  2 | Jerry | +----+-------+ 2 rows in set (17.20 sec) // Session1 prints results

事务隔离级别

在底层，数据库提供了锁机制来解决事务并发执行过程中的问题，但是，直接让用户操作数据库锁是一件非常麻烦和复杂的事情。因此，数据库为用户提供了自动锁机制，只要用户指定相应的事务隔离级别，数据库就能自动分析相关的SQl语句，并加上适当的锁并维护这些锁。ANSI/ISO SQL 92标准定义了四个等级的事务隔离级别，在MySQL中，不同的隔离级别所能解决的不同的问题如下表所示：

隔离级别	脏读	不可重复读	幻象读	第一类丢失更新	第二类丢失更新
读未提交 READ UNCOMMITTED	允许	允许	允许	不允许	不允许
读已提交 READ COMMITTED	不允许	允许	允许	不允许	不允许
可重复读 REPEATABLE READ	不允许	不允许	允许	不允许	不允许
序列化 SERIALIZABLE	不允许	不允许	不允许	不允许	不允许

默认的隔离级别是 “可重复读 (REPEATABLE READ)”，事务隔离级别越高，解决的问题就越多，但同时并发性能越低，所以应该根据不同的业务场景选择不同的隔离级别。

MySQL实战

本节会通过MySQL演示事务并发执行过程中的五类问题，并给出相应的解决办法。

• 准备数据库表

DROP DATABASE IF EXISTS `account_management`;
CREATE DATABASE `account_management`;
USE `account_management`;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `money` float DEFAULT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `users` (`name`) VALUES ("Tom");
INSERT INTO `users` (`name`) VALUES ("Jerry");
INSERT INTO `account` (`user_id`, `money`) VALUES(1, 100);
INSERT INTO `account` (`user_id`, `money`) VALUES(2, 200);

• 脏读（Dirty Read）
  A事务读取到了B事务尚未提交的更改数据，如果B事务回滚了，那么A事务之前读取到的数据就是脏数据，A事务的读取过程就是脏读。

  Session1	Session2
  mysql> set session transaction isolation level read uncommitted; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-UNCOMMITTED       | +------------------------+ 1 row in set (0.00 sec)	mysql> set session transaction isolation level read uncommitted; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-UNCOMMITTED       | +------------------------+ 1 row in set (0.00 sec)
  mysql> start transaction; Query OK, 0 rows affected (0.00 sec)   mysql> select * from account; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   100 | |  2 |       2 |   200 | +----+---------+-------+ 2 rows in set (0.00 sec)	mysql> start transaction; Query OK, 0 rows affected (0.00 sec)   mysql> update account set money=money+100 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0   // 更改数据后并未commit。
  mysql> select * from account; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   200 | |  2 |       2 |   200 | +----+---------+-------+ 2 rows in set (0.00 sec)   // 可以看出，当session2执行完 update account set money=money+100 where id=1; 后，session1再次查询到的id为1的money为200，但session2并未commit，所以这里的200为脏数据，如果在此数据上做某些操作，可能会发生未知错误。 // 脏读只可能出现在隔离级别为“READ UNCOMMITTED”的场景中，如果是其它隔离级别，此时查询到的id为1的money将是100。	mysql> rollback; Query OK, 0 rows affected (0.01 sec)
  mysql> select * from account; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   100 | |  2 |       2 |   200 | +----+---------+-------+ 2 rows in set (0.00 sec)   // 当session2回滚后，查询到的id为1的money为100。

• 避免Dirty Read问题
  脏读是最简单的读问题，只会在“READ UNCOMMITTED”事务隔离级别中出现，所以提高事务隔离级别即可解决此问题。

  Session1	Session2
  mysql> set session transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-COMMITTED         | +------------------------+ 1 row in set (0.00 sec)	mysql> set session transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-COMMITTED         | +------------------------+ 1 row in set (0.00 sec)
  mysql> start transaction; Query OK, 0 rows affected (0.00 sec)   mysql> select * from account; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   100 | |  2 |       2 |   200 | +----+---------+-------+ 2 rows in set (0.00 sec)	mysql> start transaction; Query OK, 0 rows affected (0.00 sec)   mysql> update account set money=money+100 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0   // 更改数据后并未commit。
  mysql> select * from account; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   100 | |  2 |       2 |   200 | +----+---------+-------+ 2 rows in set (0.00 sec)   // 由于事务隔离级别“READ COMMITTED”，所以session1第二次不会读取session2未提交的数据，这样就避免了脏读问题。

• 不可重复读（Unrepeatable Read）
  由于B事务更改并提交了某一记录的修改，导致A事务前后两次读取到了该记录的不同数据，出现不可重复读问题。

  Session1	Session2
  mysql> set session transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-COMMITTED         | +------------------------+ 1 row in set (0.00 sec)	mysql> set session transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-COMMITTED         | +------------------------+ 1 row in set (0.00 sec)
  mysql> start transaction; Query OK, 0 rows affected (0.00 sec)   mysql> select * from account where id=1; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   100 | +----+---------+-------+ 1 row in set (0.00 sec)	mysql> start transaction; Query OK, 0 rows affected (0.00 sec)   mysql> update account set money=money+100 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0   mysql> commit; Query OK, 0 rows affected (0.01 sec)   // 更改数据后commit。
  mysql> select * from account where id=1; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   200 | +----+---------+-------+ 1 row in set (0.00 sec)   // 在session1中，前后两次读取到的id为1的数据不一致，此时出现了不可重复读问题。如果事务隔离级别是“REPEATABLE READ”或“SERIALIZABLE”，那么第二次读取到的数据将是100。

• 避免Unrepeatable Read问题
  提高事务级别为“REPEATABLE READ”或“SERIALIZABLE”即可解决不可重复读问题。

  Session1	Session2
  mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ        | +------------------------+ 1 row in set (0.00 sec)	mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ        | +------------------------+ 1 row in set (0.00 sec)
  mysql> start transaction; Query OK, 0 rows affected (0.00 sec)   mysql> select * from account where id=1; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   100 | +----+---------+-------+ 1 row in set (0.00 sec)	mysql> start transaction; Query OK, 0 rows affected (0.00 sec)   mysql> update account set money=money+100 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0   mysql> commit; Query OK, 0 rows affected (0.01 sec)   // 更改数据后commit。
  mysql> select * from account where id=1; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   100 | +----+---------+-------+ 1 row in set (0.00 sec)   // 由于事务隔离级别是“REPEATABLE READ”，所以同一事务中前后两次读取到的相同数据值不变，即使其它事务修改了该值。这样就避免了不可重复读问题。

• 幻象读（Phantom Read）
  由于B事务新增了某些数据，导致A事务在统计查询时读取到了这些新增数据，出现幻象读问题。

  Session1	Session2
  mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ        | +------------------------+ 1 row in set (0.00 sec)	mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ        | +------------------------+ 1 row in set (0.00 sec)
  mysql> start transaction; Query OK, 0 rows affected (0.00 sec)   mysql> select sum(money) from account; +------------+ | sum(money) | +------------+ |        300 | +------------+ 1 row in set (0.00 sec)	mysql> start transaction; Query OK, 0 rows affected (0.00 sec)   mysql> insert into account(user_id, money) values(2, 300); Query OK, 1 row affected (0.01 sec)   mysql> commit; Query OK, 0 rows affected (0.01 sec)   // 更改数据后commit。
  mysql> select sum(money) from account; +------------+ | sum(money) | +------------+ |        600 | +------------+ 1 row in set (0.00 sec)   // session1在同一事务的前后两次统计查询时读取到了不同的数据，此时出现幻象读问题。只有事务隔离级别为“SERIALIZABLE”才能解决此问题。

• 避免Phantom Read问题
  大多数情况下，幻象读问题不是很严重，一般出现在统计读取的事务中，只有事务隔离级别为“SERIALIZABLE”才能解决此问题。

  Session1	Session2
  mysql> set session transaction isolation level serializable; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | SERIALIZABLE           | +------------------------+ 1 row in set (0.00 sec)	mysql> set session transaction isolation level serializable; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | SERIALIZABLE           | +------------------------+ 1 row in set (0.00 sec)
  mysql> start transaction; Query OK, 0 rows affected (0.00 sec)   mysql> select sum(money) from account; +------------+ | sum(money) | +------------+ |        300 | +------------+ 1 row in set (0.00 sec)	mysql> start transaction; Query OK, 0 rows affected (0.00 sec)   mysql> insert into account(user_id, money) values(2, 300);   // 此时session2 block住，无法新增数据，更无法commit。 // 如果session1在规定时间内未结束，session2将等待超时， // ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  mysql> select sum(money) from account; +------------+ | sum(money) | +------------+ |        300 | +------------+ 1 row in set (0.00 sec)   // 由于隔离级别是“SERIALIZABLE”，所以在session1未结束（commit or rollback）前，session2无法修改account表中的任何数据，这样就避免了幻象读问题。

• 第一类丢失更新
  A事务撤销时把已经提交的B事务的更新覆盖了。这是很严重的问题，在任何的事务隔离级别中都不允许，下面看看在最低的事务隔离级别中如何避免此问题。

  Session1	Session2
  mysql> set session transaction isolation level read uncommitted; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-UNCOMMITTED       | +------------------------+ 1 row in set (0.00 sec)	mysql> set session transaction isolation level read uncommitted; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-UNCOMMITTED       | +------------------------+ 1 row in set (0.00 sec)
  mysql> start transaction; Query OK, 0 rows affected (0.00 sec)   mysql> select * from account where id=1; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   100 | +----+---------+-------+ 1 row in set (0.00 sec)	mysql> start transaction; Query OK, 0 rows affected (0.00 sec)   mysql> select * from account where id=1; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   100 | +----+---------+-------+ 1 row in set (0.00 sec)   mysql> update account set money=money+100 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0   mysql> commit; Query OK, 0 rows affected (0.01 sec)   // 修改数据并commit。
  mysql> select * from account where id=1; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   200 | +----+---------+-------+ 1 row in set (0.00 sec)   mysql> update account set money=money-50 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0   mysql> rollback; Query OK, 0 rows affected (0.15 sec)   mysql> select * from account where id=1; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   200 | +----+---------+-------+ 1 row in set (0.00 sec)   // 修改数据并rollback，虽然session1 rollback，session2提交的数据并没有被覆盖，id为1的值还是200。这样就避免了第一类丢失更新问题。	mysql> select * from account where id=1; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   200 | +----+---------+-------+ 1 row in set (0.00 sec)   // session2中查询到的值并没有因为session1的rollback而变化

• 第二类丢失更新
  A事务覆盖了B事务提交的数据，造成了B事务丢失了其修改的数据。这也是很严重的问题，在任何的事务隔离级别中都不允许，下面看看在最低的事务隔离级别中如何避免此问题。

  Session1	Session2
  mysql> set session transaction isolation level read uncommitted; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-UNCOMMITTED       | +------------------------+ 1 row in set (0.00 sec)	mysql> set session transaction isolation level read uncommitted; Query OK, 0 rows affected (0.00 sec)   mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-UNCOMMITTED       | +------------------------+ 1 row in set (0.00 sec)
  	mysql> start transaction; Query OK, 0 rows affected (0.00 sec)
  mysql> start transaction; Query OK, 0 rows affected (0.00 sec)	mysql> select * from account where id=1; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   100 | +----+---------+-------+ 1 row in set (0.00 sec)
  mysql> select * from account where id=1; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   100 | +----+---------+-------+ 1 row in set (0.00 sec)	mysql> update account set money=money+100 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0   mysql> commit; Query OK, 0 rows affected (0.01 sec)   // 修改数据并commit。
  mysql> update account set money=money-50 where id=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1  Changed: 1  Warnings: 0   mysql> commit; Query OK, 0 rows affected (0.00 sec)   mysql> select * from account where id=1; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   150 | +----+---------+-------+ 1 row in set (0.00 sec)   // 修改数据并commit，虽然session1 commit，session2提交的数据并没有被覆盖，session1所做的操作都是基于session2提交的数据上。这样就避免了第二类丢失更新问题。	mysql> select * from account where id=1; +----+---------+-------+ | id | user_id | money | +----+---------+-------+ |  1 |       1 |   150 | +----+---------+-------+ 1 row in set (0.00 sec)

数据库事务是数据库非常重要的功能，后续文章会介绍在Java中如何通过原生态JDBC实现编程式事务和Spring的声明式事务，以及事务的传播，超时，读写事务等高级特性。