mysql事务、锁

• 事务
• • 事务特性
  • 事务隔离级别
• MVCC
• • MVCC概念
  • MVCC实现原理
• 锁
• • 锁分类
  • • 从操作的粒度可分为表级锁、行级锁和页级锁。
    • 从操作的类型可分为读锁和写锁。
    • 从操作的性能可分为乐观锁和悲观锁。
  • 行锁原理
  • 悲观锁
  • 乐观锁
  • 死锁
  • • 表锁死锁
    • 行级锁死锁
  • 死锁排查
• 参考

事务

事务特性

1. 原子性（atomicity）

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

2. 一致性（Consistency）

指的是事务开始之前和事务结束之后，数据库的完整性限制未被破坏。一致性包括两方面的内容，分别是约束一致性和数据一致性。

3. 隔离性 （Isolation）

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

4. 持久性（Durability）

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

事务隔离级别

• 未提交读 所有事务都可以看到没有提交事务的数据

• 已提交读 事务成功提交后才可以被查询到

• 可重复读 （排它锁+MVCC）

• 可串行化 强制的进行排序，在每个读读数据行上添加共享锁。会导致大量超时现象和锁竞争。

脏读 ： 读取到其他事务未提交的数据

不可重复读 ： A、B事务同时访问一条数据，A第一次查询=>B查询=>B修改=>B提交=>A第二次查询数据与第一次数据不一致

幻读 ：事务T1对一定范围内执行操作，T2对相同的范围内执行不兼容的操作，这时会发生幻读。
如：T1删除符合条件C1的所有数据，T2又插入了一些符合条件C1的数据，则在T1中再次查找符合条件C1的数据还是可以查到，这对T1来说好像是幻觉一样，这时的读取操作称为幻读。（重点在新增或删除）

丢失更新 事务T1读取了数据，并执行了一些操作，然后更新数据。事务T2也做相同的事，则T1和T2更新数据时可能会覆盖对方的更新，从而引起错误。
1.回滚覆盖：一个事务回滚操作，把其他事务已提交的数据给覆盖了。
2.提交覆盖：一个事务提交操作，把其他事务已提交的数据给覆盖了。

MVCC

多版本控制MVCC，也就是Copy on Write的思想。MVCC除了支持读和读并行，还支持读和写、写和读的并行，但为了保证一致性，写和写是无法并行的。

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

MVCC概念

MVCC（Multi Version Concurrency Control）被称为多版本控制，是指在数据库中为了实现高并发的数据访问，对数据进行多版本处理，并通过事务的可见性来保证事务能看到自己应该看到的数据本。多版本控制很巧妙地将稀缺资源的独占互斥转换为并发，大大提高了数据库的吞吐量及读写性能。 如何生成多版本？ 每次事务修改操作之前，都会在Undo日志中记录修改之前的数据状态和事务号，该备份记录可以用于其他事务的读取，也可以进行必要时的数据回滚。

MVCC实现原理

MVCC最大的好处是读不加锁，读写不冲突。在读多写少的系统应用中，读写不冲突是非常重要的，极大的提升系统的并发性能，这也是为什么现阶段几乎所有的关系型数据库都支持 MVCC 的原因，不过目前MVCC只在 Read Commited 和 Repeatable Read 两种隔离级别下工作。 在 MVCC 并发控制中，读操作可以分为两类: 快照读（Snapshot Read）与当前读 （Current Read）。

• 快照读：读取的是记录的快照版本（有可能是历史版本），不用加锁。（select）
• 当前读：读取的是记录的最新版本，并且当前读返回的记录，都会加锁，保证其他事务不会再并发修改这条记录。（select… for update 或lock in share mode，insert/delete/update）

锁

锁分类

从操作的粒度可分为表级锁、行级锁和页级锁。

• 表级锁：每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB 等存储引擎中。

• 行级锁：每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB 存储引擎中。

• 页级锁：每次锁定相邻的一组记录，锁定粒度界于表锁和行锁之间，开销和加锁时间界于表锁和行锁之间，并发度一般。应用在BDB 存储引擎中。

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

IS锁、IX锁：意向读锁、意向写锁，属于表级锁，S和X主要针对行级锁。在对表记录添加S或X锁之前，会先对表添加IS或IX锁。
S锁：事务A对记录添加了S锁，可以对记录进行读操作，不能做修改，其他事务可以对该记录追加S锁，但是不能追加X锁，需要追加X锁，需要等记录的S锁全部释放。
X锁：事务A对记录添加了X锁，可以对记录进行读和修改操作，其他事务不能对记录做读和修改操作

• 共享锁（读锁 S）

共享锁，针对同一份数据，多个读操作可以同时进行而不会互相影响。

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

• 排他锁（写锁 X）

排他锁，当前写操作没有完成前，它会阻断其他写锁和读锁

SELECT * FROM table_name WHERE ... FOR UPDATE

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

• 乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。

• 悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。

行锁原理

在InnoDB引擎中，我们可以使用行锁和表锁，其中行锁又分为共享锁和排他锁。InnoDB行锁是通过对索引数据页上的记录加锁实现的，主要实现算法有 3 种：Record Lock、Gap Lock 和 Next-key Lock。

• RecordLock锁：锁定单个行记录的锁。（记录锁，RC、RR隔离级别都支持）

• GapLock锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁，RR隔离级别支持）

• Next-key Lock 锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别支持）

在RR隔离级别，InnoDB对于记录加锁行为都是先采用Next-Key Lock，但是当SQL操作含有唯一索引时，Innodb会对Next-Key Lock进行优化，降级为RecordLock，仅锁住索引本身而非范围

1. select … from 语句：InnoDB引擎采用MVCC机制实现非阻塞读，所以对于普通的select语句，InnoDB不加锁
2. select … from lock in share mode语句：追加了共享锁，InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁
3. select … from for update语句：追加了排他锁，InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁
4. update … where 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
5. delete … where 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可降级为RecordLock锁。
6. insert语句：InnoDB会在将要插入的那一行设置一个排他的RecordLock锁。

当没有索引时，会导致全表锁定，因为InnoDB引擎 锁机制是基于索引实现的记录锁定

悲观锁

悲观锁（Pessimistic Locking），是指在数据处理过程，将数据处于锁定状态，一般使用数据库的锁机制实现。从广义上来讲，前面提到的行锁、表锁、读锁、写锁、共享锁、排他锁等，这些都属于悲观锁范畴。

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

lock table 表名称 read|write,表名称2 read|write;
//查看表上加过的锁
show open tables;
//删除表锁
unlock tables;

• 表级读锁：当前表追加read锁，当前连接和其他的连接都可以读操作；但是当前连接增删改操作会报错，其他连接增删改会被阻塞。

• 表级写锁：当前表追加write锁，当前连接可以对表做增删改查操作，其他连接对该表所有操作都被阻塞（包括查询）。 总结：表级读锁会阻塞写操作，但是不会阻塞读操作。而写锁则会把读和写操作都阻塞。

• 共享锁（行级锁-读锁） 共享锁又称为读锁，简称S锁。共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。使用共享锁的方法是在select … lock in share mode，只适用查询语句。 总结：事务使用了共享锁（读锁），只能读取，不能修改，修改操作被阻塞。

• 排他锁（行级锁-写锁） 排他锁又称为写锁，简称X锁。排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能对该行记录做其他操作，也不能获取该行的锁。 使用排他锁的方法是在SQL末尾加上for update

1. innodb引擎默认会在update，delete语句加上for update。
2. 行级锁的实现其实是依靠其对应的索引，所以如果操作没用到索引的查询，那么会锁住全表记录。
3. 总结：事务使用了排他锁（写锁），当前事务可以读取和修改，其他事务不能修改，也不能获取记录锁（select… for update）。如果查询没有使用到索引，将会锁住整个表记录。

乐观锁

乐观锁是相对于悲观锁而言的，它不是数据库提供的功能，需要开发者自己去实现。在数据库操作时，想法很乐观，认为这次的操作不会导致冲突，因此在数据库操作时并不做任何的特殊处理，即不加锁，而是在进行事务提交时再去判断是否有冲突了。 乐观锁实现的关键点：冲突的检测。 悲观锁和乐观锁都可以解决事务写写并发，在应用中可以根据并发处理能力选择区分，比如对并发率要求高的选择乐观锁；对于并发率要求低的可以选择悲观锁。

实现原理

• 使用版本字段（version） 先给数据表增加一个版本(version) 字段，每操作一次，将那条记录的版本号加 1。version是用来查看被读的记录有无变化，作用是防止记录在业务处理期间被其他事务修改。
• 使用时间戳（Timestamp） 与使用version版本字段相似，同样需要给在数据表增加一个字段，字段类型使用timestamp时间戳。也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则提交更新，否则就是版本冲突，取消操作。

除了自己手动实现乐观锁之外，许多数据库访问框架也封装了乐观锁的实现，比如hibernate框架。MyBatis框架大家可以使用OptimisticLocker插件来扩展。

死锁

表锁死锁

产生原因： 用户A访问表A（锁住了表A），然后又访问表B；另一个用户B访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。 用户A–》A表（表锁）–》B表（表锁） 用户B–》B表（表锁）–》A表（表锁）
解决方案：这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

行级锁死锁

产生原因1：如果在事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等价于表级锁），多个这样的事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或死锁。

解决方案1： SQL语句中不要使用太复杂的关联多表的查询；使用explain“执行计划"对SQL语句进行分析，对于有全表扫描和全表锁定的SQL语句，建立相应的索引进行优化。

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

解决方案2：
在同一个事务中，尽可能做到一次锁定所需要的所有资源
按照id对资源排序，然后按顺序进行处理

死锁排查

MySQL提供了几个与锁有关的参数和命令，可以辅助我们优化锁操作，减少死锁发生。
查看死锁日志 通过show engine innodb status\G命令查看近期死锁日志信息。
使用方法： 1、查看近期死锁日志信息； 2、使用explain查看下SQL执行计划

查看锁状态变量 通过**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：系统启动后到现在总共等待的次数

参考

脏读、丢失更新、不可重复读、幻读
第一类、第二类丢失更新
thinkphp悲观锁机制处理高并发
深度好文:MySQL事务与锁