MySQL事务

事务和并发

事务是代表单个工作单元的一组SQL语句，所有这些语句都应该成功完成，否则事务会运行失败。

举个银行转账的例子，假设你要取￥10转给你的盆友，那么首先这10￥会从你账户转出，接着再转到你盆友的账户。所以这个例子中我们有两个操作，这两个操作一起组成一个工作单元，转钱出去的操作成功和收钱进来的操作要一起成功，否则只要有一方操作失败，都需要退回去，还原第一个操作。

数据库在交易底层的逻辑就是这样，我们在需要对数据库进行多次更改的情况下使用事务，并希望所有这些更改作为一个单元一起成功或失败。

如果没有事务，那么就可能会出现钱转出去了但是对方没收到的情况，导致数据库处于不一致的状态，因此我们会使用事务来避免产生这种场景。

事务的属性

  1. 原子性（Atomicity）
    • 意思是事务就像原子一样，它们牢不可破，每个事务都是一个工作单元，不管它包含多少语句，要么所有这些语句都成功执行并且事务被提交，要么事务被退回去，所有更改被撤销。
  2. 一致性（Consistency）
    • 意味着通过使用事务，数据库将始终保持一致的状态，不会出现数据不一致的情况，比如钱出去了对方没收到等场景。
  3. 隔离性（Isolation）
    • 意思是这些事务互相隔离，或者当有同样的数据被更改时，各自受到保护，所以事务不会相互干扰，如果多个事务想更新同一行，受影响的行会被锁定，使得一次只有一个事务可以拿到锁的钥匙，执行更新，其他事务必须等待持锁的事务完成更新。
  4. 持久性（Durability）
    • 意思是一旦事务被提交，事务产生的更改是永久的，即使你断电或者系统崩溃，也不会丢失更改的内容（可以想想这是怎么实现的哦~）。

对于以上四种属性，我们统称为ACID。

创建事务

1
2
3
4
5
6
7
8
9
10
USE sql_store;

START TRANSACTION;

INSERT INTO orders (customer_id, order_date, status)
VALUES(1, '2021-04-19', 1);

INSERT INTO order_items VALUES(LAST_INSERT_ID(), 1, 1, 1);

COMMIT;

在有些时候，我们可能需要进行一些错误检查，并手动退回事务，此时需要用ROLLBACK语句。

Mysql会装好我们写在事务里的每一条语句，如果语句没有错误它就会自动提交。它由一个自动提交系统管控。通过执行下面这条语句可以查看：

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

通过设置其为打开，mysql会自动将我们的语句放入事务中进行提交。

并发和锁定

实际业务场景中会存在多个用户同时访问数据库的情况，当一个用户正在修改其他用户正在检索或修改的数据时，并发可能会成为一个问题。接下来讲解mysql如何处理这种“并发”的问题。

并发问题之Lost Update

当两个事务尝试更新相同的数据并且没有上锁时，就会发生。较晚提交的事务会覆盖较早提交的事务的更新内容。

比如现在A事务和B事务同时对一行数据进行修改，A事务修改”NY->VA”，B事务修改”10->20”。如果没有上锁，且B事务较晚提交，A事务的更新就会被覆盖掉，只有B事务的更新成功。

对于这种情况，mysql采用锁的机制约束事务，使其一行一行的更新。

并发问题之Dirty Reads

脏读就是当一个事务读取了尚未被提交的数据。

比如，事务A把顾客的积分从10改成20，但是在A提交更新之前，事务B已经读取了这名顾客，并基于这个顾客的数据做了某个决定。如果此时，事务A在事务B完成之前退回（rollback）了，事务B拿到的数据则仍然是事务A修改后的数据20，B读取了未提交的数据，因此这个数据是脏的。

对此，我们要对事务建立隔离级别，这样事务修改的数据不会立马被其他事务读取（想想如何实现?），除非它提交了更新。

标准的SQL定义了4个事务隔离级别，其中一个就是“读已提交（READ COMMITED）”，当我们对事务应用这个隔离级别时，那个事务只能读取已提交的数据，这样就解决了脏读的问题。

那么，在事务完成以后数据才有修改的情景怎么处理？这个问题其实并不重要，重要的是我们读取的任何数据至少是在读取的时候都是已经提交了的事务。

并发问题之Non-repeating Reads（Inconsistant Read）

当我们在事务中添加更多隔离时，我们可以保证事务只能读取已经提交的数据。但是，如果在事务过程中，你读取了某个数据两次，并得到了不同的结果怎么处理？

比如我们有一个事务，写了个比较复杂的查询，在主查询中读取了数据是10，在子查询读取的时候数据更新成了0，子查询读取的是0，这种情况就叫做不可重复读或不一致读。

对于这种情况，我们可以将要读取的数据的事务与其他事务隔离，确保数据的更改对当前事务不可见。SQL标准定义了另一个隔离级别，叫做“可重复读”。

在可重复读的级别上，我们读取的数据是可重复和一致的。即使其他事务对数据进行了更改，我们事务读取到的也是首次读取就创建的快照（相当于备份）。

并发问题之Phantom Read（幻读）

比如事务A，我们要查询积分超过10的所有顾客，给其发放优惠卷。此时事务B为另外一位谷歌更新了积分，且还没被查询返回，所以现在这个顾客是能够得到优惠卷的，但是我们的事务A并没有查询到这个顾客。所以，事务A完成后，仍然有一个符合条件的客户没有收到优惠卷。这就是幻读。

因为这类数据我们在查询的时候没有看到，查询完数据才突然出现，我们无法在先前的查询看到，因为他们是在执行查询后才添加、更新和删除的。

对于这种场景，主要看业务需求来具体解决，如果给所有符合条件的客户发放优惠卷很重要，那么我们可以确保在查询时没有任何能影响查询结果的事务在运行。为此，我们另外的一个隔离级别就叫做“序列化”。

序列化能够保证当有其他事务执行对数据产生影响时，我们的事务能够知晓变动，并等待该影响数据的事务完成后才能继续完成。

所以，序列化是我们可以应用于一个事务的最高隔离级别。它为我们操作提供了最大的确保性，但是也会极大的影响我们的执行的效率，因为序列化级别需要在存储和CPU方面调取额外的资源用以管理需要等待的事务。

总结

更高的隔离级别会存在越重的性能和可扩展性问题，因为它会用到更多隔离事务的锁。

越低的隔离级别会更容易实现并发，有着更高的性能问题，但是也会造成更多的并发问题。

并发问题及对应的隔离等级

在mysql重，默认的事务隔离级别是可重复读取，它比可序列化更快，并且防止除了幻读外的大多数并发问题。

查看事务隔离级别：

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE "transaction_isolation";
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 1 warning (0.00 sec)

设置下一个事务的隔离级别：（下一个事务执行完毕该隔离级别失效）

1
2
SET TRANSACTION ISOLATION SERIALIZABLE
-- 其他隔离级别：/REPEATABLE/COMMITTED/UNCOMMITTED

为当前会话或连接所有以后的事务设定隔离级别:(当前连接断掉该隔离级别就失效，不会影响数据库其他事务的隔离级别)

1
SET SESSION TRANSACTION ISOLATION SERIALIZABLE

我们还可以为所有会话重的所有新事务设置全局的隔离级别

1
SET GLOBAL TRANSACTION ISOLATION SERIALIZABLE

死锁

死锁就是当不同的事务因为握住了各自完成事务所需要的“锁”，而无法完成的而情况。所以两个事务都在等待对方释放锁，并永远没办法释放锁，这就是死锁问题。

死锁问题无法避免，但是可以尽量减少其发生。出现死锁后，mysql会rollback，这时我们可以提醒重新尝试更新。

如果你经常在两个事务重检测到死锁，查看他们的代码，这些事务可能是存储过程的一部分，看一下事务里的语句顺序，如果这些事务以相反的顺序更新记录就很可能出现死锁。

所以，我们可以在更新多条记录的时候可以遵照同样的顺序进行更新。

此外，还可以尽量简化事务，缩小事务的运行时长。如果你的事务要基于非常大的表运行，这些事务可能需要很长时间来运行，就会有冲突的风险，可以尝试把这些事务安排在非高峰时段运行。由此避开大量的活跃用户。

容易发生死锁的SQL事务代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 事务A
USE sql_store;

START TRANSACTION;
UPDATE orders SET status = 1 WHERE order_id = 1;
UPDATE customers SET state = "VA" WHERE customer_id = 1;
COMMIT;

-- 事务B
USE sql_store;

START TRANSACTION;
UPDATE customers SET state = "VA" WHERE customer_id = 1;
UPDATE orders SET status = 1 WHERE order_id = 1;
COMMIT;