mysql每个类型查三条_Mysql的几个灵魂拷问（三）

今天这篇就来讲讲Mysql中比较高频的锁和事务吧。

一、Mysql锁事

1、锁的类型有哪些呢

总的来说，InnoDB共有七种类型的锁：

共享/排它锁(Shared and Exclusive Locks)

意向锁(Intention Locks)

记录锁(Record Locks)

间隙锁(Gap Locks)

临键锁(Next-key Locks)

插入意向锁(Insert Intention Locks)

自增锁(Auto-inc Locks)

要记住这么多类型的锁不容易，但是总结一下来看，可以按照几个维度将锁分做一个分类，按照锁的粒度分类，分为表级锁和行级锁，

表级锁，粒度最大的一种锁，对当前操作的整张表加锁，实现简单 ，资源消耗也比较少，加锁快，不会出现死锁 。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。

行级锁，Mysql中锁定 粒度最小的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。 InnoDB支持的行级锁。

虽然使用行级索具有粒度小、并发度高等特点，但是表级锁有时候也是非常必要的：事务更新大表中的大部分数据直接使用表级锁效率更高；事务比较复杂，使用行级索很可能引起死锁导致回滚。

锁按照是否互斥划分为 共享锁(Share Locks，记为S锁)和排他锁(eXclusive Locks，记为X锁)。

共享锁(Share Locks，记为S锁)，读取数据时加S锁，共享锁之间不互斥，也就是读读可以并行

排他锁(eXclusive Locks，记为X锁)，修改数据时加X锁，排他锁与任何锁互斥——也就是写读，写写不可以并行

2、InnoDB的行锁模式和加锁方法

InnoDB的行锁模式就是分为如下的两种：

共享锁(Share Locks，记为S锁)，读取数据时加S锁

排他锁(eXclusive Locks，记为X锁)，修改数据时加X锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁(Intention Locks)：意向共享锁(IS)，意向排他锁(IX)。这两种意向锁都是表锁。 (意向锁了解即可)

InnoDB行锁模式兼容性列表:

意向锁是InnoDB自动加的；对于UPDATE、DELETE和INSERT语句，InnoDB会自动给设计数据集加排他锁(X)；对于普通的SELECT语句，InnoDB不会加锁。 可以通过以下语句显示给记录集加共享锁或排他锁：

共享锁(S)：SELECT * FROM TABLE_NAME WHERE ... LOCK IN SHARE MODE.

排他锁(X)：SELECT * FROM TABLE_NAME WHERE ... FOR UPDATE.

以上使用完后记得要提交事务。

用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT... FOR UPDATE方式获得排他锁。

所以在使用共享锁模式下，查询完数据后不要进行更新操作，不然又可能会造成死锁；要更新数据，应该使用排他锁模式。mysql锁机制详解

InnoDB的行锁原理与使用

InnoDB行锁是通过给索引上的索引项加锁来实现的，这意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！(这个问题遇到过，由于没加索引，行锁变表锁)

实际使用案例，此处就是用的行锁来实现一个事务更新操作。

//0.开始事务

begin;

//1.查询出商品信息

select status from t_goods where id=1 for update;

//2.根据商品信息生成订单

insert into t_orders (id,goods_id) values (null,1);

//3.修改商品status为2

update t_goods set status=2;

//4.提交事务

commit;

锁与事务

依赖锁的标准事务

标准SQL事务隔离级别的实现是依赖锁的，但是Mysql在只使用锁来实现隔离级别的控制的时候，需要频繁的加锁解锁，而且很容易发生读写的冲突。为了不加锁解决读写冲突的问题，MySQL引入了MVCC机制，详细可见：微信文章深入理解 MySQL 中事务隔离级别的实现原理

二、Mysql事务

事务的基本特性

原子性指的是一个事务中的操作要么全部成功，要么全部失败。

一致性指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块钱，假设中间sql执行过程中系统崩溃A也不会损失100块，因为事务没有提交，修改也就不会保存到数据库。

隔离性指的是一个事务的修改在最终提交前，对其他事务是不可见的。

持久性指的是一旦事务提交，所做的修改就会永久保存到数据库中。

事务的隔离级别

而隔离性有4个隔离级别，分别是：

read uncommit 读未提交，可能会读到其他事务未提交的数据，也叫做脏读。

read commit 读已提交，两次读取结果不一致，叫做不可重复读。

repeatable read 可重复复读，这是mysql的默认级别，就是每次读取结果都一样，但是有可能产生幻读。

serializable 串行，一般是不会使用的，他会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题。

事务隔离级别

更新丢失(Lost Update)：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题 —— 最后的更新覆盖了其他事务所做的更新。如何避免这个问题呢，最好在一个事务对数据进行更改但还未提交时，其他事务不能访问修改同一个数据。

脏读(Dirty Reads)：一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些尚未提交的脏数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做 “脏读”。

不可重复读(Non-Repeatable Reads)：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。

幻读(Phantom Reads)：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为 “幻读”。

隔离线和问题

ACID靠什么保证

A 原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql

C 一致性一般由代码层面来保证

I 隔离性由MVCC来保证

D 持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复

快照读、当前读？

快照读，读取的是快照数据，不加锁的简单 Select 都属于快照读.

SELECT * FROM player WHERE ...

当前读就是读的是最新数据,而不是历史的数据，加锁的 SELECT，或者对数据进行增删改都会进行当前读。

SELECT FROM player FOR UPDATE;

INSERT INTO player values ...

DELETE FROM player WHERE ...

UPDATE player SET ...

什么是MVCC？

解决更新丢失可以交给应用，但是后三者问题的解决需要数据库提供事务间的隔离机制来解决。实现隔离机制的方法主要有两种：加读写锁、MVCC

MVCC 的英文全称是 Multiversion Concurrency Control ，中文意思是多版本并发控制技术。简单来说就是保存数据的历史版本。可以通过比较版本号决定数据是否显示出来。读取数据的时候不需要加锁可以保证事务的隔离效果(乐观锁)。每个行记录都有隐藏的三列：

row_id ：隐藏的行 ID，聚集索引。

trx_id: 操作这个数据事务 ID ，也就是最后一个对数据插入或者更新的事务 ID 。

roll_ptr:回滚指针，指向这个记录的 Undo Log 信息。

行记录的列

InnoDB 将行记录快照保存在 Undo Log 里。

如果一个事务要查询行记录，需要读取哪个版本的行记录呢。Read View 保存了当前事务开启时所有活跃的事务列表，可以理解为: Read View 保存了不应该让这个事务看到的其他事务 ID 列表。

trx_ids 系统当前正在活跃的事务ID集合。

low_limit_id ,活跃事务的最大的事务 ID。

up_limit_id 活跃的事务中最小的事务 ID。

creator_trx_id，创建这个 ReadView 的事务ID。

如果当前事务的 creator_trx_id 想要读取某个行记录，这个行记录ID 的trx_id

如果 trx_id < 活跃的最小事务ID(up_limit_id),也就是说这个行记录在这些活跃的事务创建前就已经提交了，那么这个行记录对当前事务是可见的。

如果trx_id > 活跃的最大事务ID(low_limit_id)，这个说明行记录在这些活跃的事务之后才创建，说明这个行记录对当前事务是不可见的。

如果 up_limit_id < trx_id

MVCC如何查询一条记录

获取事务自己的版本号，即 事务ID

获取 Read View

查询得到的数据，然后 Read View 中的事务版本号进行比较。

如果不符合 ReadView 规则， 那么就需要 UndoLog 中历史快照；

最后返回符合规则的数据

InnoDB 实现多版本控制 (MVCC)是通过 ReadView+ UndoLog 实现的，UndoLog 保存了历史快照，ReadView 规则帮助判断当前版本的数据是否可见。

MVCC能解决幻读问题吗？

MVCC并不能解决幻读问题。间隙锁是可重复读级别下才会有的锁，结合MVCC和间隙锁可以解决幻读的问题。

begin;

#假设users表为空，下面查出来的数据为空

select * from users; #没有加锁

#此时另一个事务提交了，且插入了一条id=1的数据

select * from users; #读快照，查出来的数据为空

update users set name='mysql' where id=1;#update是当前读，所以更新成功，并生成一个更新的快照

select * from users; #读快照，查出来id为1的一条记录，因为MVCC可以查到当前事务生成的快照

commit;

可以看到前后查出来的数据行不一致，发生了幻读。所以说只有MVCC是不能解决幻读问题的，解决幻读问题靠的是间隙锁。啥叫间隙锁：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。例如，来看下下面这个案例：

begin;

#假设users表为空，下面查出来的数据为空

select * from users for update; #加上共享锁

#此时另一个事务B想提交且插入了一条id=1的数据，由于有间隙锁，所以要等待

select * from users; #读快照，查出来的数据为空

update users set name='mysql' where id=1;#update是当前读，由于不存在数据，不进行更新

select * from users; #读快照，查出来的数据为空

commit;

#事务B提交成功并插入数据

注意，Mysql默认是RR(repeatable read)级别下想解决幻读问题，需要我们显式加锁，不然查询的时候还是不会加锁的。