mysql笔记：15. 事务和锁

一、事务概述

在MySQL中，事务就是针对数据库的一组操作，它可以由一条或多条SQL语句组成。在程序执行过程中，只要有一条SQL语句执行失败或发生错误，其他语句都不会执行；也就是说，事务中的语句要么都执行，要么都不执行。
数据库之所以提供事务的机制，主要有以下两个目的：

• 为数据库的一组操作提供了一个从失败中恢复到正常状态的途径，同时保证了数据库即使在异常状态下也仍能保持数据的一致性。
• 当多个应用程序并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

MySQL中的事务必须满足四个特性，即事务的ACID特性，分别是原子性、一致性、隔离性和持久性。

1. 原子性（Atomictity）
   原子性是指一个事务必须被视为一个不可分割的最小工作单元，只有事务中所有的数据库操作都执行成功，才算整个事务执行成功。事务中的所有DML操作，要么全部执行成功，要么全部执行失败，不会存在部分执行成功，部分执行失败的情况。事务中如果有任何一个SQL语句执行失败，已经执行成功的SQL语句也必须撤销，数据库的状态退回到执行事务前的状态，相当于事务没有执行过。
2. 一致性（Consistency）
   一致性是指事务在开始执行前和事务执行结束后，数据库中数据的完整性没有被破坏。数据从一个一致状态转变为另一个一致状态，并且完全符合所有的预设规则，数据不会存在一个中间的状态。
3. 隔离性（Isolation）
   隔离性是指一个事务在执行时，不会受到其他事务的影响。隔离性保证了未完成事务的所有操作与数据库系统的隔离，直到事务完成之后，才能看到事务的执行结果。当多个用户并发访问数据库时，数据库为每一个用户开启的事务不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
4. 持久性（Durability）
   持久性是指事务一旦提交，对数据库中数据的修改就是永久性的。数据不会因为系统出现故障而丢失。为了实现事务的持久性，MySQL在提交事务时采用的是预写日志的方式。即提交事务时，先写日志，再写数据。只要日志成功写入，就是事务提交成功。

事务的持久性不能做到百分之百的持久，只能从事务本身的角度来保证永久性，如果一些外部原因导致数据库发生故障（如硬盘损坏），那么所有提交的数据可能都会丢失。

二、事务基本操作

在MySQL中，用户执行的每一条SQL语句默认都会当成单独的事务自动提交。如果想要将一组SQL语句作为一个事务，需要在执行这组SQL语句之前显式地开启事务。事务开启后，后续的每一条SQL语句将不再自动提交。想要提交时，需要手动提交事务。只有事务提交后，事务中的SQL语句才会生效。如果不想提交当前事务，还可以回滚事务。
相关语句如下：

# 开启事务
START TRANSACTION;
# 也可以使用BEGIN
BEGIN;

# 提交事务
COMMIT;

# 回滚事务
ROLLBACK;

ROLLBACK语句只针对未提交的事务执行回滚，已经提交的事务是不能回滚的。
当执行COMMIT或ROLLBACK后，当前事务就会自动结束。
MySQL中事务不允许嵌套，如果执行START TRANSACTION语句之前，上一个事务还没有提交，则此时会隐式执行上一个事务的提交操作。
MySQL中的事务默认是自动提交，如果用户想要设置事务的自动提交方式，可以通过更改AUTOCOMMIT的值来实现。其值为0表示关闭事务自动提交，值为1表示开启事务自动提交。修改、查看的语句如下：

# 查看当前会话的AUTOCOMMIT
SELECT @@AUTOCOMMIT

# 关闭当前会话的事务自动提交
SET AUTOCOMMIT=0

# 开启当前会话的事务自动提交
SET AUTOCOMMIT=1

三、事务保存点

在回滚事务时，事务内的所有操作都将被撤销。如果希望只撤销事务内的部分操作，则可以借助事务的保存点实现。在事务中设置保存点后，可以将事务回滚到指定的保存点。不用时也可以删除指定的保存点。相关语法：

# 创建保存点
SAVEPOINT name;

# 回滚到指定保存点
ROLLBACK TO SAVEPOINT name;

# 删除指定的保存点
RELEASE SAVEPOINT name;

一个事务可以创建多个保存点。
一旦提交事务，事务中的保存点都会被删除。
如果事务回滚到某个保存点后，该保存点之后创建的其他保存点也会被删除。

四、事务的隔离级别

MySQL支持多线程并发访问，用户可以通过不同的线程执行不同的事务。为保证多个事务之间互不影响，就需要为事务设置适当的隔离级别。
在MySQL中，事务有4种隔离级别，分别为READ UNCOMMITTED（读未提交）、READ COMMITTED（读已提交）、REPEATABLE READ（可重复读）和SERIALIZABLE（串行化）。
不同的事务隔离级别，可能存在不同的问题：

1. READ UNCOMMITTED

READ UNCOMMITTED是事务隔离级别中最低的级别，该级别下的所有事务可以读取其他事务中未提交的数据，这种读取方式也被称为脏读（Dirty Read）。在这种级别上，可能会产生很多问题，除非用户真的知道自己在做什么，并且有很好的理由选择这样做。本隔离级别很少应用于实际应用中。

设置事务的隔离级别

MySQL的默认隔离级别是REPEATABLE READ，该级别可以避免脏读。修改方式有两种：

• 配置文件：修改mysql.ini配置文件，在最后加上事务隔离级别的配置即可全局修改事务隔离级别。

[mysqld]
transaction-isolation=REPEATABLE READ
# 这里全局修改为REPEATABLE READ级别，其实默认就是这个级别。

• set session：针对当前session，可以使用set session命令修改。

# 将当前客户端中事务的隔离级别设置为READ UNCOMMITTED;
SET SESSION TRANSATION ISOLATION LEVEL READ UNCOMMITTED;

2. READ COMMITTED

在READ COMMITTED级别下，事务只能读取其他事务已经提交的内容，可以避免脏读现象，但是会出现不可重复读和幻读的情况。不可重复读是指在事务内重复读取别的线程已经提交的数据，由于多次查询期间，其他事务作了更新操作，因此出现多次读取的结果不一致的现象。
不可重复读并不算错误，但在有些情况下去不符合实际需求。

3. REPEATABLE READ

REPEATABLE READ是MySQL默认的事务隔离级别，它可以避免脏读、不可重复读。但在理论上，该级别会出现幻读。幻读又称为虚读，是指在一个事务内两次查询中的数据条数不一致。幻读和不可重复读类似 ，都是在两次查询过程中；不同的是，幻读是由于其他事务作了插入记录的操作，导致记录数有所增加。
MySQL的存储引擎通过多版本并发控制机制解决了该问题，当事务的隔离级别为REPEATABLE READ时可以避免幻读。

4. SERIALIZABLE

SERIALIZABLE是事务的最高隔离级别，它会在每个读的数据行上加锁，从而解决脏读、幻读、重复读的问题。这个级别可能导致大量的超时和锁竞争现象，因此也是性能最低的一种隔离级别。

五、MySQL的锁

在并发环境下，为了解决并发一致性问题保证事务的隔离性，MySQL采用了锁的机制。当一个事务在进行操作时会对操作的数据进行加锁，从而限制另一个事务的操作。为保证数据库的效率和性能，加锁的粒度不宜太大。

InnoDB的锁类型

MySQL的不同存储引擎采用的锁机制不完全相同。MyISAM存储引擎和Memory存储引擎采用表级锁，而InnoDB存储引擎既支持行级锁，也支持表级锁。

1. InnoDB的行级锁

InnoDB默认采用的是行级锁，并实现了以下两种类型的行级锁。

• 共享锁（S）
  共享锁（Share Lock）也叫读锁。在同一个数据对象上可以有多把共享锁。如果一个事务在数据对象上加上了共享锁，则该事务可以读取数据但不能修改数据。其他的事务也可以在该数据对象上继续添加共享锁，也可以读取该数据，但同样也不能修改数据。
• 排他锁（X）
  排他锁（Exclusive Lock）也叫写锁。在同一个数据对象上只允许有一把排他锁，获取到数据排他锁的事务可以读取数据和修改数据。一旦数据被加上了排他锁，其他事务就不允许再对该数据添加任何类型的行级锁。

查询语句SELECT默认不会添加任何锁类型。因此当数据上有了排他锁，还是可以通过查询语句SELECT获取数据的。
使用查询语句SELECT也可以为数据加锁。如：使用select ... fro update语句可以为数据添加排他锁；使用select ... lock in share mode语句可以为数据添加共享锁。

InnoDB的行锁是通过索引实现的，这意味着只有通过索引查询数据时，InnoDB引擎才会使用行级锁。否则，InnoDB存储引擎将使用表级锁。要注意的是等级锁不是针对表中的行加锁。如果表上没有对应的索引或者使用了相同的索引，也会造成锁的冲突进而导致整张表加上排他锁。

2. InnoDB的表级锁

InnoDB为了实现同时支持行级锁和表级锁，在其内部使用了两种类型的意向锁（Intention Locks）来实现多粒度锁机制。这两种意向锁都是表级锁。

• 意向共享锁（IS）
  事务在给数据添加行级共享锁之前，必须先取得该表的意向共享锁。
• 意向排他锁（IX）
  事务在给数据添加等级排他锁之前，必须先取得该表的意向排他锁。

由于意向锁是InnoDB存储引擎自动加的，不需要用户干预，是其内部使用的锁机制，因此对于普通用户来说不需要过多地关注这两种类型锁。

如果一个事务请求的锁模式与当前数据的锁模式兼容，InnoDB引擎就将事务请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待当前的锁释放。下表展示InnoDB引擎锁与锁之间的兼容模式。

死锁

死锁是指两个或两个以上事务在执行过程中，因为互相的等待或因为争抢相同的资源而造成的互相等待现象。
死锁的避免：

• 以固定的顺序访问表和行。如两个任务批量更新的情形，简单方法是对id列表先排序后执行，这样就避免了交叉等待锁的情形。
• 大事务拆小。大事务更倾向死锁，如果业务允许，将大事务拆小。
• 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
• 降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择。
• 为表添加合理的索引。