MySQL锁机制与优化实践

数据库乐观和悲观锁

乐观锁

比如在数据库中设置一个版本字段，每操作一次，都会将这行对应的版本号+1，这样下次更新都会拿到最新的版本号更新，如果一个事务拿到了版本号但是更新前其他人已经将版本号升级了，那么当前事务就会更新不到这条数据。也就达到了隔离的效果

悲观锁

直接加锁，加了锁以后其他进来访问就访问不了

锁分类

读锁

select * from 表名 where id=1 lock in share mode

读锁不互斥，同一个数据多个读操作不会互斥，可以同时进行

写锁

select * from 表名 where id=1 for update

写锁互斥，操作同一个数据，其中一个给这个数据加上了写锁，那么其他的操作就只能等待，等这个操作的写锁取消了才能再进行操作。

意向锁

主要是为了针对表锁，当想要给表加锁可能还要遍历当前表每一行数据看看有没有加行锁，但是意向锁的作用就是如果当前表加了行锁就会给表加一个标识代表当前表有行锁，这时候如果要加表锁就会发现表中有行锁。

间隙锁

上图中表可以看到主键id是断断续续的，那么现在有一个场景

当前隔离级别是MySQL默认隔离界别(可重复读)。

会给主键中缺少的数据加一个锁，比如10-15中间没数据，15-20中间没数据。

所以会加一个 (10,15)、(15,20)加锁。比如id > 1 and id <= 16 for update会加一个行锁在 1-16之间，但是又因为有间隙锁 15-20.所以在 1-20中间都会加锁，这时候其他事务想要插入或者修改1-20之间的数据都是要等待上个锁释放掉

临建锁

案例：有一个事务A 读取 id > 1 and id <= 16 for update;

事务A没提交事务并且加了行锁，这时事务B进来写了一个插入语句，并且id = 16

因为有间隙锁那么就会给 10-15、15-20这中间加锁，但是又因为上边加了行锁在 1-16之间。但是临建锁会因为16在 间隙锁15-20之间取大的也就是20进行加锁。这时即使查询是 1<id<=16但是因为间隙锁的缘故也会导致 1-20之间的数据都加了锁

示例2

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
	BEGIN;
	UPDATE account 
SET `name` = 12 WHERE id < 13 and id > 1;

上边这个sql这是没有提交，因为在可重复度级别下会自动给update语句加一个行锁，但是由于间隙锁和临建锁的缘故，再去插入 id为1-13之间空缺的数据肯定不能成功的，必须等上边update语句提交或者回滚。

表锁

锁住整张表，其他操作都不能进来操作这张表

页数

锁住一页，但是这里的一页并不是我们分页查询的一页。

而是在索引树中一块存储的页，如下图

上边这才是一页，类似于Java中的分段锁一样

行锁

锁住一行数据，比如给 id = 1 的数据加了写锁，这是其他操作来修改id=2的数据也是可以成功的。也就是给当前行加锁并不影响其他人操作其他行数据。

但是这个锁必须要使用索引，如果加锁的字段不是索引，就会导致这个行锁升级为表锁。是不是主键索引都可以，但是只能走索引，不然就是表锁,如果走了不是主键索引而是普通索引，那么锁就是锁的就是普通索引.

比如有普通索引 ind_name(name)；这时修改 update table set age = 10 where name = 'lilei';

而上边修改事务没提交，其他事务来新增 数据 其中name就是lilei，按照道理来说可以成功，但是这个 新增操作却 需要锁等待，因为当前锁是锁的name=lilei 只要是操作name = lilei的数据的人都会锁等待

锁问题分析

‐‐ 查看事务
2 select * from INFORMATION_SCHEMA.INNODB_TRX;
3 ‐‐ 查看锁，8.0之后需要换成这张表performance_schema.data_locks
4 select * from INFORMATION_SCHEMA.INNODB_LOCKS;
5 ‐‐ 查看锁等待，8.0之后需要换成这张表performance_schema.data_lock_waits
6 select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
7
8 ‐‐ 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
9 kill trx_mysql_thread_id
10
11 ‐‐ 查看锁等待详细信息
12 show engine innodb status;

上边操作可以查看当前锁的竞争情况，也可以查询锁的数量和状态和执行的sql语句，这样如果需要优化也可以看这个进行优化。

比如死锁，简单的死锁MySQL会自己进行处理，kill掉死锁的事务。复杂的MySQL处理不了我们可以通过上边锁情况进行自己手动处理。

锁的优化

1. 事务尽量小一点 不然锁会一直等待
2. 尽可能让所有数据检索都都走索引，这样不会表锁
3. 降低事务隔离级别

MVCC多版本并发控制机制

可重复读

mvcc的undo回滚日志

表中的每一条数据当被事务进行了修改，那么都会有一个这条数据的undo回滚日志。其中包含了表中的字段还有额外的两个字段 trx_id 操作的事务id。roll_polnter 是记录了当前操作回滚的undolog日志地址，比如当前事务操作了 insert 插入一个id=1的数据，那么undolog就会对应一个delete id = 1的操作，如果需要回滚就会执行这个反操作delete。

mvcc的可见性算法

有一个read-view。开启事务并不会立即记录read-view ，而是开始了事务执行任意查询语句会将当前MySQL全部的事务都记录下来。比如事务A开启之后，MySQL此时存在 事务id分别为： 100，200，300，400，500，600，还有一个700已经提交了。 这时候就会记录成一个数组 (100，200，300，400，500，600 ),700 .

结构为： (未提交的事务id),当前存在的事务最大id(不管提每提交)

这样构建的read-view为 (100，200，300，400，500，600),700。具体如何用看下一个怎么完成的隔离性。

版本链比对规则：

1. 如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的；

2. 如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的

trx_id 就是当前自己的事务是可见的）；

3. 如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况

a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自己的

事务是可见的)；

b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。

那么事务中是怎么完成隔离性的呢

例如上图：事务100、200、300、select1同时开启事务，事务100 和 事务200分别test表中 id = 1、id = 6的age赋值，这时候事务300开始修改account中id=1的balance加500，并且事务100、200都没提交，300提交了事务。

这时候其他事务 select1开始查询：

1. 由于事务开启并且第一次开始查询语句的时候 事务 100、200都没有提交所以read-view为: (100,200),300
2. 在开启事务 第一次查询语句才算是真正的开启事务，比如select1虽然和 100、200、300一起开启的事务，但是select1并没有任何操作，而这时id = 1的balance=0，事务id=300的将account中id=1的balance修改为加500时并且提交了，这时候select1再去执行查询才算是真正的开启了select1的事务，读取到的就是500.
3. select1第一次开始查询undolog版本链id=1最新数据可以得到事务id = 300，此时并不在数组(100，200)的范围，代表是可读的。所以直接返回 balance = 500的数据
4. 这时候事务 100 将id = 1的balance 第一次加了300第二次又加了200并且提交 这时候balance = 1000并且提交
5. select1再次查询id = 1的数据发现balance =500而不是1000。是因为查询undo日志链最新的数据得到事务id = 100、balance = 10000在read-view的数组(100,200)中，不可见所以就会继续给上找，发现还是事务id=100、balance = 800 而事务id = 100还是在数组(100，200)之间，继续给上边找发现是 事务id = 300、balance = 500 不在数组之间，是可见的，返回balance = 500.

1. 如果 这时候select1 再去修改id = 1的数据balance加100.那么select1再次查询会发现之前查询一直都是500这时候竟然变为了1100 。 原因就是事务对于查询是读取历史版本，而对于修改是修改当前提交数据，也就是虽然读取的是历史数据balance=500。但是修改却是最新提交数据1000进行处理的。并且将修改的数据放到内存中，下次这个事务读取这个数据就直接给内存中拿，也就是balance=1100了。