MySQL事务和锁

MySQL事务和锁

ACID 特性

• 原子性

  事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行

  每一个写事务，都会修改BufferPool，从而产生相应的Redo/Undo日志，在Buffer Pool 中的页被刷到
  磁盘之前，这些日志信息都会先写入到日志文件中，如果 Buffer Pool 中的脏页没有刷成功，此时数据
  库挂了，那在数据库再次启动之后，可以通过 Redo 日志将其恢复出来，以保证脏页写的数据不会丢
  失。如果脏页刷新成功，此时数据库挂了，就需要通过Undo来实现了

• 持久性：

  指的是一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，
  后续的操作或故障不应该对其有任何影响，不会丢失。

• 隔离性：

  指的是一个事务的执行不能被其他事务干扰，即一个事务内部的操作使用的数据对其他的并发事务是隔离的。

  InnoDB 支持的隔离性有 4 种，隔离性从低到高分别为：读未提交、读提交、可重复读、可串行化。
  锁和多版本控制（MVCC）技术就是用于保障隔离性的。

• 一致性：

  指的是事务开始之前和事务结束之后，数据库的完整性限制未被破坏，也可以理解为数据的完整性。
  数据的完整性是通过原子性、隔离性、持久性来保证的，而这3个特性又是通过 Redo/Undo 来保证的

事务控制的演进

1、并发事务

事务并发处理可能会带来一些问题，比如：更新丢失、脏读、不可重复读、幻读 等。

• 更新丢失

  当两个或多个事务更新同一行记录，会产生更新丢失现象。可以分为回滚覆盖和提交覆盖。

  • 回滚覆盖：一个事务回滚操作，把其他事务已提交的数据给覆盖了。
  • 提交覆盖：一个事务提交操作，把其他事务已提交的数据给覆盖了。

• 脏读

一个事务读取到了另一个事务修改但未提交的数据。

• 不可重复读

一个事务中多次读取同一行记录不一致，后面读取的跟前面读取的不一致。

• 幻读
  一个事务中多次按相同条件查询，结果不一致。后续查询的结果和面前查询结果不同，多了或少了几行记录。

2、排队

最简单的方法，就是完全顺序执行所有事务的数据库操作，不需要加锁，简单的说就是全局排队。序列
化执行所有的事务单元，数据库某个时刻只处理一个事务操作，特点是强一致性，处理性能低。

3、排他锁

引入锁之后就可以支持并发处理事务，如果事务之间涉及到相同的数据项时，会使用排他锁，或叫互斥
锁，先进入的事务独占数据项以后，其他事务被阻塞，等待前面的事务释放锁。

注意，在整个事务1结束之前，锁是不会被释放的，所以，事务2必须等到事务1结束之后开始

4、读写锁

读和写操作分为：读读、写写、读写、写读。

读写锁就是进一步细化锁的颗粒度，区分读操作和写操作，让读和读之间不加锁，这样下面的两个事务
就可以同时被执行了。

读写锁，可以让读和读并行，而读和写、写和读、写和写这几种之间还是要加排他锁

5、MVCC

多版本控制MVCC，也就是Copy on Write的思想。MVCC除了支持读和读并行，还支持读和写、写和读的并行，但为了保证一致性，写和写是无法并行的。 不过目前MVCC只在 Read Commited 和 Repeatable Read 两种隔离级别下工作

如何生成的多版本？每次事务修改操作之前，都会在Undo日志中记录修改之前的数据状态和事务号，该备份记录可以用于其他事务的读取，也可以进行必要时的数据回滚。

比如上图，在事务1开始写操作的时候会copy一个记录的副本，其他事务读操作会读取这个记录副本，因此不会影响其他事务对此记录的读取，实现写和读并行。

为了更直观地理解 MVCC 的实现原理，举一个记录更新的案例来讲解 MVCC 中多版本的实现。

假设 F1～F6 是表中字段的名字，1～6 是其对应的数据。后面三个隐含字段分别对应该行的隐含ID、事务号和回滚指针，如下图所示。

具体的更新过程如下：
假如一条数据是刚 INSERT 的，DB_ROW_ID 为 1，其他两个字段为空。当事务 1 更改该行的数据值时，会进行如下操作，如下图所示。

接下来事务2操作，过程与事务 1 相同，此时 Undo log 中会有两行记录，并且通过回滚指针连在一起，通过当前记录的回滚指针回溯到该行创建时的初始内容，如下图所示。

MVCC已经实现了读读、读写、写读并发处理，如果想进一步解决写写冲突，可以采用下面两种方案：

• 乐观锁
• 悲观锁

事务隔离级别

前面提到的“更新丢失”、”脏读”、“不可重复读”和“幻读”等并发事务问题，其实都是数据库一致性问题，为了解决这些问题，MySQL数据库是通过事务隔离级别来解决的，数据库系统提供了以下 4 种事务隔离级别供用户选择。

查看当前数据库的事务隔离级别: show variables like 'tx_isolation'
设置事务隔离级别：set tx_isolation='REPEATABLE-READ'

隔离级别案例

读未提交-脏读

（1）打开一个客户端A，并设置当前事务模式为read uncommitted，set tx_isolation='read-uncommitted'
查询表数据

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表accou

（3）这时，虽然客户端B的事务还没提交，但是客户端A就可以查询到B已经更新的数据：

读已提交-不可重复读

（1）打开一个客户端A，并设置当前事务模式为read committed，set tx_isolation='read-committed'
查询表记录

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account：

（3）这时，客户端B的事务还没提交，客户端A不能查询到B已经更新的数据，解决了脏读问题：

（4）客户端B的事务提交

（5）客户端A执行与上一步相同的查询，结果 与上一步不一致，即产生了不可重复读的问题

可重复读

打开一个客户端A，并设置当前事务模式为repeatable read，set tx_isolation='repeatable-read'
查询表记录

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account并提交

（3）在客户端A查询表account的所有记录，与步骤（1）查询结果一致，没有出现不可重复读的问题

幻读演示：

（5）重新打开客户端B，插入一条新数据后提交

（6）在客户端A查询表account的所有记录，没有查出新增数据，所以没有出现幻读

(7)验证幻读

在客户端A执行update account set balance=888 where id = 4能更新成功，再次查询能查到客户端B新增的数据

锁机制和实战

锁分类

• 从对数据操作的粒度分，分为表锁和行锁

• 从操作的类型可分为读锁和写锁

  • 读锁：共享锁，针对同一份数据，多个读操作可以同时进行而不会互相影响。
  • 写锁：排他锁，当前写操作没有完成前，它会阻断其他写锁和读锁。

• 从操作的性能可分为乐观锁和悲观锁。

  • 乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突
    检测，如果发现冲突了，则提示错误信息。
  • 悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，
    再修改的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。

表锁

表级锁每次操作都锁住整张表，并发度最低。常用命令如下：

手动增加表锁

lock table 表名称 read|write,表名称2 read|write;  

查看表上加过的锁

show open tables;

删除表锁

unlock tables;

表级读锁：当前表追加read锁，当前连接和其他的连接都可以读操作；但是当前连接增删改操作会报错，其他连接增删改会被阻塞。
表级写锁：当前表追加write锁，当前连接可以对表做增删改查操作，其他连接对该表所有操作都被阻塞（包括查询）。

行锁

在InnoDB引擎中，我们可以使用行锁和表锁，其中行锁又分为共享锁（行级锁-读锁 ）和排他锁（行级锁-写锁 ）。InnoDB的行锁是针对索引加的锁 ， 无索引行锁会升级为表锁

1）select … from 语句：InnoDB引擎采用MVCC机制实现非阻塞读，所以对于普通的select语句，InnoDB不加锁

2）select … from lock in share mode语句：追加了共享锁

3）select … from for update语句：追加了排他锁

4）update … where 语句：追加了排他锁

5）delete … where 语句：追加了排他锁

6）insert语句：InnoDB会在将要插入的那一行设置一个排他锁。

共享锁（行级锁-读锁）

共享锁又称为读锁 ，事务使用了共享锁（读锁），只能读取，不能修改，修改操作被阻塞

使用共享锁的方法是 select ... lock in share mode，只适用查询语句

排他锁（行级锁-写锁）

排他锁又称为写锁 , 事务使用了排他锁（写锁），当前事务可以读取和修改，其他事务不能修改，也不能获取记录
锁（select… for update）。如果查询没有使用到索引，将会锁住整个表记录

使用排他锁的方法是在SQL末尾加上for update，innodb引擎默认会在update，delete语句加上 for update

悲观锁

持一种悲观态度，在数据处理过程，均将数据处于锁定状态

前面提到的行锁、表锁、读锁、写锁、共享锁、排他锁等，这些都属于悲观锁范畴

乐观锁

乐观锁是相对于悲观锁而言的，它不是数据库提供的功能，需要开发者自己去实现。在数据库操作时，
想法很乐观，认为这次的操作不会导致冲突，因此在数据库操作时并不做任何的特殊处理，即不加锁，
而是在进行事务提交时再去判断是否有冲突了。

乐观锁实现原理

• 使用版本字段（version）

  先给数据表增加一个版本(version) 字段，每操作一次，将那条记录的版本号加 1。
  在更新提交时检查当前数据库中数据的version和自己更新前取到的version是否一致

• 使用时间戳（Timestamp）

  与使用version版本字段相似，同样需要给在数据表增加一个字段，字段类型使用timestamp时间戳。
  也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳是否一致

死锁与解决方案

两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁。

MySQL提供了几个与锁有关的参数和命令，可以辅助我们优化锁操作，减少死锁发生。

• 查看死锁日志
  通过show engine innodb status\G命令查看近期死锁日志信息。

• 查看锁状态变量

  show status like'innodb_row_lock%' 命令检查状态变量，分析系统中的行锁的争夺情况
  Innodb_row_lock_current_waits ：当前正在等待锁的数量
  Innodb_row_lock_time：从系统启动到现在锁定总时间长度
  Innodb_row_lock_time_avg： 每次等待锁的平均时间
  Innodb_row_lock_time_max：从系统启动到现在等待最长的一次锁的时间
  。

• 查看死锁日志
  通过show engine innodb status\G命令查看近期死锁日志信息。

• 查看锁状态变量

  show status like'innodb_row_lock%' 命令检查状态变量，分析系统中的行锁的争夺情况
  Innodb_row_lock_current_waits ：当前正在等待锁的数量
  Innodb_row_lock_time：从系统启动到现在锁定总时间长度
  Innodb_row_lock_time_avg： 每次等待锁的平均时间
  Innodb_row_lock_time_max：从系统启动到现在等待最长的一次锁的时间
  Innodb_row_lock_waits：系统启动后到现在总共等待的次数如果等待次数高，而且每次等待时间长，需要分析系统中为什么会有如此多的等待，然后着手定制优化。