MYSQL 事务以及锁(一)

学习笔记是学习了 极客时间 - 《MySQL实战45讲》整理的笔记。

事务隔离

事务的隔离性

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

事务的隔离级别

• 读未提交（read uncommitted）：一个事务还没提交时，它做的变更就能被别的事务看到。

• 读提交（read committed）：一个事务提交之后，它做的变更才会被其他事务看到。(oracle默认事务，如果从oracle应用迁移到mysql，必须更改其事务级别)

  可重复读（repeatable read）：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据
  是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。(数据校对逻辑场景适用较多)

• 串行化（serializable ）：顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

MYSQL设置其隔离级别的参数是 transaction-isolation

mysql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+

实例讲解

两个事务对同一条数据进行操作。

transaction1.png

根据不同的事务级别，得到的V1,V2,V3的结果也不同。

• 读未提交：V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。
• 读提交: V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A看到。所以， V3 的值也是 2。
• 可重复读:V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
• 串行化: 事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

内部逻辑

执行事务时，数据库会创建一个视图，访问的时候以视图的逻辑结果为准。

• 可重复读:在事务启动时创建视图,整个事务存在期间都用这个视图。
• 读提交:视图是在每个 SQL 语句开始执行的时候创建的。
• 读未提交：直接返回记录上的最新值，没有视图。
• 串行化：加锁的方式来避免并行访问。

隔离实现（回滚日志）

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

多事务下的日志

假设一个值从 1 被按顺序改成了 2、3、4，在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view，在回滚日志里面就会有类似下面的记录。

transaction2.png

如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

删除日志

当没有事务再需要用到这些回滚日志时（当系统里没有比这个回滚日志更早的 read-view 的时候），回滚日志会被删除。

长事务

避免长事务的几个基本配置

• autocommit 参数: 建议设置为 1
  • set autocommit=0:这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。
  • set autocommit=1: 通过显式语句的方式来启动事务。(begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。)
  • information_schema 库的 innodb_trx表中查询长事务。

MYSQL中的锁

全局锁

定义

MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后的语句都会被阻塞(增删改操作，建表，修改表等)。

使用场景

全库逻辑备份

• 非InnoDB引擎：全局锁后进行备份，（不建议使用set global readonly=true 方式来设置全库只读，global 变量的方式影响面太大，并且发生异常时，会一直保持只读状态）
• 官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction
  的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。

表级锁

表锁

语法

lock tables … read/write
#lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
#如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。

元数据锁（MDL）

MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

注意的问题

transaction3.png

尽管读锁不会阻塞，但是写锁之后的读锁，必须等待写锁释放后，才可以进行。

如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新session 再请求的话，这个库的线程很快就会爆满。

行锁（InnoDB引擎）

定义

行锁就是针对数据表中行记录的锁。这很好理解，比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

业务中的行锁优化

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

举例

事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。

transaction4.png

两种解决策略

• 超时设置:通过参数innodb_lock_wait_timeout 来设置(默认50S)。
• 死锁检测:发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。 死锁检测要耗费大量的 CPU 资源