mysql（六）锁机制

这里的锁机制，在专栏中讲的不是很仔细，后面自己再自行补充。

 

我们之前说过并发事务下会有脏写的问题，那么这个脏写是靠什么来防止的？

——————其实靠的就是锁机制。依据锁机制可以讲多个事务更新同一行数据时串行化，这样可以避免同时更新一行数据。

（如果单独执行一行sql，mysql会自动为其加上事务）

举例：
现在有一行数据，此时没有任何事务用它。此时事务A进来更新这行数据。

此时事务A会创建一个锁，里面包含自己的trx_id和等待状态（false是非等待，即获取到相应的资源了），然后将这把锁跟这行数据关联起来。

此时这行数据就被事务A加上了锁，如果事务B进来要更新这行数据，这时发现该行数据已经被事务A加锁了。此时事务A会先生成一个锁数据结构，

里面有其事务id、等待状态设为true（代表现在在等待）

直到事务A执行完提交了会释放了锁，此时才轮到事务B去加锁关联这行数据。（然后将等待状态改为false）

一、共享锁和独占锁（行锁）

1、独占锁

上面的案例加的就是Exclude独占锁，当一行数据被一个事务加了独占锁后，其他事务要更新这行数据都是要加独占锁的（读取不用），但只能生成独占锁在后面等待。

为什么读取不用加锁？——————————读取时默认开启的是MVCC机制进行读取的。所以mvcc机制也解决了读和写两个操作的加锁互斥。

2、共享锁

你要是非要在读取的时候加锁，那也可以。mysql支持一种共享锁，就是s锁。（一般查询是不用加锁的）

在查询语句后加上  lock in share mode就行。（这个意思是查询的时候对一行数据加共享锁）

也可以加互斥锁（独占锁）后面加上for update即可（一旦加了独占锁，你事务提交之前别人不能去更新这行数据）

但是共享锁和独占锁是互斥的，加了其中一把锁就不能加另一种锁。

共享锁+共享锁不互斥；独占锁+独占锁互斥。

二、表锁和行锁

1、行锁

上面讲的锁都是指行锁。

如果你此时去读取别的事务在更新的数据，有两种可能：

• 第一种可能是基于mvcc机制进行事务隔离，读取快照版本，这是比较常见的；

• 第二种可能是查询的同时基于特殊语法去加独占锁或者共享锁。

如果你查询的时候加独占锁，那么跟其他更新数据的事务加的独占锁都是互斥的；如果你查询的时候加共享锁，那么跟其他查询加的共享锁是不互斥的，但是跟其他事务更新数据就加的独占锁是互斥的，跟其他查询加的独占锁也是互斥的。

不是太建议在数据库粒度去通过行锁实现复杂的业务锁机制，而更加建议通过redis、zookeeper来用分布式锁实现复杂业务下的锁机制，其实更为合适一些。

比较正常的情况而言，其实还是多个事务并发运行更新一条数据，默认加独占锁互斥，同时其他事务读取基于mvcc机制进行快照版本读，实现事务隔离。

2、表级锁

1）、

在数据库里，你不光可以通过查询中的特殊语法加行锁，比如lock in share mode、for update等等，还可以通过一些方式在表级别去加锁。

有些人可能会以为当你执行增删改的时候默认加行锁，然后执行DDL语句的时候，比如alter table之类的语句，会默认在表级别加表锁。这么说也不太正确，但是也有一定的道理，因为确实你执行DDL的时候，会阻塞所有增删改操作；执行增删改的时候，会阻塞DDL操作。

但这是通过MySQL通用的元数据锁实现的，也就是Metadata Locks，但这还不是表锁的概念。因为表锁其实是InnoDB存储引擎的概念，InnoDB存储引擎提供了自己的表级锁，跟这里DDL语句用的元数据锁还不是一个概念。

只不过DDL语句和增删改操作，确实是互斥的，大家要知道这一点。

2）、

在mysql中其实表锁是一个很鸡肋的东西，基本不会使用到。

表锁分为两种：表锁和表锁的意向锁

（1）表锁

可以通过以下语法来加：
lock  tables XXX read；  表级共享锁（读锁）

lock  tables XXX write；  表级独占锁（写锁）

一般是没有人使用的。所以说非常鸡肋。

（2）表锁的意向锁

在两种情况下会加这个意向锁。第一种是事务在表里执行增删改操作时，在行会加独占锁，此时就会在表级加一个意向独占锁。

第二种情况是事务在表里执行查询操作，此时会在表级加一个意向共享锁。

但是这两个锁是不会有互斥的情况的（意向和意向之间不互斥），所以可以把他们看出透明的即可。

但是手动加的表锁和意向锁有部分是会互斥的。

三、线上数据库不确定性的性能抖动优化

注意这里的案例的性能抖动不是之前充放电的情况。

1、前面我们说过，如果buffer pool的内存不够了，此时又要查询比较多的数据，导致现在要将buffer pool的脏页根据lru链将不常用的缓存页刷盘，耗时较长。这个时候就会出现查询这个数据从平常的几十毫秒变长为几秒级别。

2、还有一个脏页的刷新时机前面没有讲到，例如redo log写入磁盘日志文件时，我们知道文件写满后会重新写到之前的文件覆盖那些旧内容。而在日志文件写满了后就会触发一次buffer pool脏页的刷盘操作。（因为之前的redo log文件写满后要被覆盖了，如果此时不保证数据刷盘，redo log日志内容被覆盖了就可能出现数据丢失的现象）此次的刷盘也会导致性能抖动

那么根据上面的两种情况，我们该如何来优化？

其实上面的问题其根本原因有两个：
第一个，可能buffer pool的缓存页都满了，此时你执行一个SQL查询很多数据，一下子要把很多缓存页flush到磁盘上去，刷磁盘太慢了，就会导致你的查询语句执行的很慢。

因为你必须等很多缓存页都flush到磁盘了，你才能执行查询从磁盘把你需要的数据页加载到buffer pool的缓存页里来。

第二个，可能你执行更新语句的时候，redo log在磁盘上的所有文件都写满了，此时需要回到第一个redo log文件覆盖写，覆盖写的时候可能就涉及到第一个redo log文件里有很多redo log日志对应的更新操作改动了缓存页，那些缓存页还没flush到磁盘，此时就必须把那些缓存页flush到磁盘，才能执行后续的更新语句，那你这么一等待，必然会导致更新执行的很慢了。

所以上述两个场景导致的大量缓存页flush到磁盘，就会导致莫名其妙的SQL语句性能抖动了

那么我们怎么尽可能优化mysql的一些参数，尽量的减少这种缓存页flush到磁盘带来的性能抖动问题。

而要尽量避免缓存页flush到磁盘带来的性能抖动问题。核心有两点：
1、尽量减少缓存页flush到磁盘的频率；   2、尽量提升缓存页flush到磁盘的速度

而想要减少缓存页刷盘频率这是很困难的，除非你給数据库分配大内存，给buffer pool分配的内存大点。

所以我们主要是对第二个方案的实现优化，就是尽量的去提升缓存页flush到磁盘的速度。主要可以做以下的几点优化

（1）、尽量用SSD固态硬盘，这样可以提高刷盘（随机IO）的性能。

（2）、光用固态硬盘是不行的，你还得设置一个很关键的参数，就是数据库的 innodb_io_capacity，这个参数告诉数据库采用多大的IO速率把缓存页flush到磁盘中去。（举个例子，如果你SSD能承受的是每秒随机IO次数是600次，而你数据库将 innodb_io_capacity设置为300，此时每次flush到磁盘时，每秒最多只能执行300次随机IO，这是你就没有把SSD的性能发挥出来）

所以一般建议先对机器的SSD硬盘的最大随机IO速率做一个测试，可以用fio工具来测试。

查出来后直接把这个值设置给 innodb_io_capacity就行了，尽量的让其发挥最大的功效。

（3）还有一个参数就是 innodb_flush_neighbors,这个参数就是在flush刷盘的时候，可能会控制把缓存页的临近的其他缓存页也刷到磁盘，这样有时候会导致flush的缓存也太多了。

如果实际上你用的是SSD固态硬盘，并没有必要让他同时刷临近的缓存页。可以把他设置为 0 ，禁止刷临近缓存页，这样就能把每次flush的缓存页数量降低到最少了。

其实上面的优化就是尽可能用SSD且设置innodb_io_capacity来尽量的提高其IOPS，同时设置innodb_flush_neighbors为0，禁止刷临近缓存页，尽量减少每次flush缓存页的数量。

这里的锁机制，在专栏中讲的不是很仔细，所以等专栏总结完后再对其进行补充分析

后面开始看索引的部分