Mysql事务与锁

事务

事务的四大特性（ACID）：

• 原子性(Atomic)：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性(Consistent)：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。数据一致

• 隔离性(Isolated)：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

• 持久性(Durable)：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。事务完成后所做的改动都会被持久化，即使发生灾难性的失败。通过日志和同步备份可以在故障发生后重建数据。

事务隔离级别解决方案：

LBCC（加锁）MVCC（快照）

事务隔离级别：

RU、RC、RR

image.jpeg

事务并发问题

并发问题

锁

锁总结

悲观锁：悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）

1）使用悲观锁，我们必须关闭mysql数据库的自动提交属性，采用手动提交事务的方式，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。

2）需要注意的是，在事务中，只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 同一笔数据时会等待其它事务结束后才执行，一般SELECT ... 则不受此影响。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)。

3）补充：MySQL select…for update的Row Lock与Table Lock

使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认Row-Level Lock，所以只有「明确」地指定主键（或有索引的地方），MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。

乐观锁：

乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做（一般是回滚事务）。那么我们如何实现乐观锁呢，一般来说有以下2种方式：

1）.使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

2）.乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

mysql Innodb的锁

全局锁

MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

一般会在进行 全库逻辑备份 的时候使用，这样就能确保 其他线程不能对该数据库做更新操作。

在 MVCC 中提供了获取 一致性视图 的操作使得备份变得非常简单，如果想了解 MVCC 可以参考https://juejin.im/post/5da8493ae51d4524b25add55

表锁

MDL(Meta Data Lock)元数据锁

MDL锁用来保证只有一个线程能对该表进行表结构更改。

怎么说呢？MDL分为 MDL写锁 和 MDL读锁，加锁规则是这样的

• 当线程对一个表进行 CRUD 操作的时候会加 MDL读锁
• 当线程对一个表进行 表结构更改 操作的时候会加 MDL写锁
• 写锁和读锁，写锁和写锁互斥，读锁之间不互斥

lock tables xxx read/write;

这是给一个表设置读锁和写锁的命令，如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其他表。

这种表锁是一种处理并发的方式，但是在InnoDB中常用的是行锁。

行锁

我们知道在5.5版本以前 MySQL 的默认存储引擎是 MyISAM，而 MyISAM 和 InnoDB 最大的区别就是两个

• 事务
• 行锁

其中行锁是我们今天的主题，如果不了解事务可以去补习一下。

其实行锁就是两个锁，你可以理解为 写锁(排他锁 X锁)和读锁(共享锁 S锁)

• 共享锁（S锁）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。也叫做读锁：读锁是共享的，多个客户可以同时读取同一个资源，但不允许其他客户修改。
• 排他锁（X锁)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。也叫做写锁：写锁是排他的，写锁会阻塞其他的写锁和读锁。

而行锁还会引起一个一个很头疼的问题，那就是死锁。

如果事务A对行100加了写锁，事务B对行101加了写锁，此时事务A想要修改行101而事务B又想修改行100，这样占有且等待就导致了死锁问题，而面对死锁问题就只有检测和预防了。

next-key锁

MVCC 和行锁是无法解决 幻读 问题的，这个时候 InnoDB 使用了 一个叫 GAP锁(间隙锁) 的东西，它配合 行锁 形成了 next-key锁，解决了幻读的问题。

但是因为它的加锁规则，又导致了扩大了一些加锁范围从而减少数据库并发能力。具体的加锁规则如下：

• 加锁的基本单位是next-key lock 就是行锁和GAP锁结合。
• 查找过程中访问到的对象就会加锁。
• 索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
• 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
• 唯一索引上的范围查询会访问到不满足条件的第一个值为止。

MVCC 解决幻读的思路比较复杂，这里就不做过多的验证。

总结

对于 MySQL 的索引来说，我给了很多最佳实践，其实这些最佳实践都是从原理来的，而 InnoDB 其实就是一个改进版的 B+树，还有存储索引的结构。弄懂了这些你就会得心应手起来。

而对于 MySQL 的锁，主要就是在行锁方面，InnoDB 其实就是使用了 行锁，MVCC还有next-key锁来实现事务并发控制的。

而对于MySQL中最重要的其实就是 锁和索引 了，因为内容太多这篇文章仅仅做一些介绍和简单的分析，如果想深入了解可以查看相应的文章。

锁的应用

select id for update无索引会锁表，有索引命中锁行，没命中间隙锁(区间)

show engine innodb status; -- 查看最后一次死锁日志

select * from where id = ？ for update；别的地方不能select for update，可以update

SELECT * from information_schema.INNODB_TRX;

SELECT * from information_schema.INNODB_LOCKS;

SELECT * from information_schema.INNODB_LOCK_WAITS;

image.jpeg

*所有的意向锁之间都不会冲突 我可以获取意向S锁，也可以获取意向X锁

*共享锁之间不冲突 读不冲突

*排他锁之间冲突 写冲突

*排他锁X和共享锁S之间冲突 读写冲突

delete、update、insert操作锁的情况

间隙锁的产生（insert的时候）

并发间隙锁引起死锁

index_merge

*update与delete产生的锁现象是一样的（因为本质上都是先select）

*Insert操作可能会触发Unique Key的冲突检查

*GAP锁，就是RR隔离级别，相对于RC隔离级别，不会出现幻读（多读一行或者少读一行）的关键

快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外)

例如：select * from table where ?;

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * from table where ? lock in share mode; (s锁)

select * from table where ? for update; （x锁）

insert into table values (…); （x锁）（Insert操作可能会触发Unique Key的冲突检查，也会进行一个当前读）

update table set ? where ?; （x锁）

delete from table where ?; （x锁）

在一个事务中只有执行简单的select操作才会生成快照。

俩段锁协议 => 意向锁 描述整个表是否已经有加锁 （但一个事务要锁表，为了不遍历判断记录是否有行锁，只需在加行数的时候给整个表加上一个意向锁排他锁即可。）

总结锁的类型：

image.jpeg

InnoDb的锁机制，InnoDB有三种行锁：

• Record Lock：单个行记录上的锁
• Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况(间隙锁是根据你的查询条件决定的)
• Next-Key Lock：前两个锁的加和，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题