MySQL事务——《MySQL8 CookBook》笔记

MySQL事务

事务是一组应该一起成功或者失败的SQL语句。

事务的属性（ACID）

• 原子性（Atomicity）

  一组SQL语句的执行要么全部成功，要么全部失败，不会出现部分更新。

• 一致性（Consistency）

  事务只能以允许的方式改变受其影响的数据。

• 隔离性（Isolation）

  同时发生的事务(并发事务) 不应该导致数据库处于不一致的状态中。系统中的每个事务都应该像唯一事务一样执行。任何事务都不应影响其他事务的存在。

• 持久性（Durability）

  无论数据库或者系统是否发生故障，数据都会永久保存在磁盘上，并且不会丢失。

使用

在数据库中执行一组SQL语句时，开启事务的方法

1. 开启事务

   START TRANSACTION;

   或者

   BEGIN;

2. SQL执行成功后

   COMMIT;

3. 如果在SQL中执行错误时（不执行COMMIT语句）

   ROLLBACK

注意： 数据库中的DDL语句，如CREATE、DROP语句，以及表或存储例程的CREATE、DROP、ALTER语句，都是无法回滚的。

保存点

使用保存点可以回滚到事务中的某些点，而无需终止事务。可以使用SAVEPOINT标识符为事务设置名称。（eg：SAVEPOINT TRANSFER_TO_B;） 并使用ROLLBACK TO 标识语句将事务回滚到指定的保存点而不中止事务。（eg：ROLLBACK TO TRANSFER_TO_B;）

当SQL执行完毕之后依然通过COMMIT来提交事务。

隔离级别

当两个及其以上的事务同时发生时，隔离级别定义了一个事务与其他事务在资源或者数据修改方面的隔离程度。隔离级别总共有4种，要更改隔离级别，需要设置 tx_isolation 变量，该变量是动态的并具有会话级别的作用范围。

1. 读去未提交（read uncommitted）

   当前事务可以读取由另一个未提交的事务写入的数据，这也称为脏读（dirty read）。

2. 读提交 （read committed）

   当前事务只能读取另一个事务提交的数据，这也称为不可重复读读取 (non-repeatable read)

3. 可重复读 （repeatable read）

   这是MySQL的默认事务隔离级别，它确保同一个事务的多个实例在并发读取数据时，会看到同样的数据行。不过会导致另一个问题——幻读 （Phantom Read）。简单来说，幻读是指当用户读取某一范围时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影”行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC， Multiversion Concurrency Control） 机制解决了该问题。

   引用： mysql四种隔离级别

   MVCC: 当事务开始并执行第一次读去数据时，将创建读取视图并保持打开状态，直到事务结束。为了在事务结束之前提供相同的结果集，InnoDB使用版本控制和UNDO信息。假设事务1选择了几行，另一个事务删除了这些行并提交了数据。如果事务1处于打开状态，它应该能够看到自己在开始时选择的行。已被删除的行保留在UNDO日志空间中以履行事务1.一旦事务1操作完成，那些行便被标记为从UNDO日志中删除。

4. 序列化 （serializable）

   通过把选定的所有行锁起来，序列化可以提供最高级别的隔离。

使用：

以读提交为例

SET @@transaction_isolation=‘READ-COMMITTED’

锁

内部锁

MySQL在自身服务器内部执行内部锁，以管理多个会话对表内容的争用。

内部锁分类：

1. 行级锁

   行级锁是细粒度的。只有被访问的行会被锁定。这允许通过多个会话同时进行写访问，使其适用于多用户、高度并发和OLTP的 应用程序。只有InnoDB支持行级锁。

2. 表级锁

   MySQL对MyISAM、MEMORY和MERGE表使用表级锁，一次只允许一个会话更新这些表。这种锁定级别是得这些存储引擎更使用于只读的或以读取操作为主的或单用户的应用程序。

外部锁

MySQL为客户会话提供选项来显示地获取表锁，以阻止其他会话访问表。

1. READ: 当一个表锁定为READ时，多个会话可以从表中读取数据而不需要获取锁。此外，多个会话可以在同一个表上获得锁，因此READ锁也称为共享锁。当READ锁被保持时，不可将数据写入表中。
2. WRITE： 当一个表被锁定为WRITE时，除持有该锁的会话之外，其他任何会话都不能读取或向表中写入数据。因此WRITE也被称为排他锁。

使用方法：

-- 锁定
LOCK TABLES table_name [READ | WRITE]-- 解锁
UNLOCK TABLES;

锁队列

除共享锁（一个表可以有多个共享锁）之外，没有两个锁可以一起加在一个表上。如果一个表中已经有一个共享锁，此时有一个排他锁要进来，那么它将被保留在队列中，直到共享锁被释放。当排他锁在队列中时，所有后续的共享锁也会被阻塞并保留在队列中。