MySQL锁机制

MySQL锁机制

MySQL锁类型

共享锁、排他锁

共享锁：又叫做读锁，当用户要进行数据的读取时，对数据加上共享锁。共享锁就是让多个线程同时获取一个锁。

排他锁：又叫做写锁，当用户要进行数据的写入时，对数据加上排他锁。排它锁也称作独占锁，一个锁在某一时刻只能被一个线程占有，其它线程必须等待锁被释放之后才可能获取到锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

总结：
1）读锁（共享锁S）会阻塞写 (排他锁X)，但是不会堵塞读 (S)。而写锁则会把读 (S) 和写 (X) 都堵塞。
2）对于InnoDB 在 RR（MySQL默认隔离级别）而言，对于 update、delete 和 insert 语句， 会自动给涉及数据集加排它锁（X）；对于普通 select 语句，innodb 不会加任何锁。如果想在 select 操作的时候加上 S 锁 或者 X 锁，需要我们手动加锁。
-- 加共享锁（S）
select * from table_name where ... lock in share mode
-- 加排它锁（X)
select * from table_name where ... for update
3）用 select … in share mode 获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行 update 或者 delete 操作。但是如果当前事务也需要对该记录进行更新操作，则有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用 select … for update 方式获得排他锁。

行级锁、表级锁、页级锁

行级锁：行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。
特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。InnoDB是基于索引来完成行锁，例: select * from tab_with_index where id = 1 for update，for update可以根据条件来完成行锁锁定，并且id是有索引键的列，如果id不是索引键那么InnoDB将完成表锁。

表级锁：表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
特点：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

页级锁：页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折中的页级，一次锁定相邻的一组记录。使用页级锁定的主要是BerkeleyDB存储引擎。
特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

总结：
1）三种锁对比
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
2）InnoDB中的行锁的实现依赖于索引，一旦某个加锁操作没有使用到索引，那么该锁就会退化为表锁。
3）在MyISAM存储引擎下，当执行SELECT查询语句时，如果同时有其他进程对表进行写操作（例如UPDATE或DELETE），则MySQL会对整个表进行锁定，这被称为表级锁定。这意味着其他的读请求必须等待写锁释放才能继续执行。因此，在高并发环境中，MyISAM存储引擎下的SELECT查询可能会面临锁表问题，从而导致性能下降和响应时间延长。为了避免出现这种情况，可以考虑使用InnoDB存储引擎，它支持行级锁定，可以避免表级锁定的问题，提高系统的并发性能。
4）不同的存储引擎
MyISAM存储引擎，它只支持表锁，并发写的时候，性能差。
InnoDB存储引擎，即支持表锁，也支持行锁，默认为行级锁。
BDB存储引擎，它支持表锁和页锁。

乐观锁、悲观锁

乐观锁：乐观锁认为一个用户读数据的时候，别人不会去写自己所读的数据；悲观锁就刚好相反，觉得自己读数据库的时候，别人可能刚好在写自己刚读的数据，其实就是持一种比较保守的态度；时间戳就是不加锁，通过时间戳来控制并发出现的问题（每行记录都标记了最后修改和读取它的事务的timestamp。当事务的timestamp小于记录的timestamp时（不能读到”未来的”数据），需要回滚后重新执行）。一般会使用版本号机制或CAS算法实现。乐观锁适用于读多写少，核心SQL：
update table set x=x+1, version=version+1 where id=#{id} and version=#{version};

悲观锁：悲观锁就是在读取数据的时候，为了不让别人修改自己读取的数据，就会先对自己读取的数据加锁，只有自己把数据读完了，才允许别人修改那部分数据，或者反过来说，就是自己修改某条数据的时候，不允许别人读取该数据，只有等自己的整个事务提交了，才释放自己加上的锁，才允许其他用户访问那部分数据。悲观锁适用于写多读少，核心SQL：
select status from t_goods where id=1 for update;

总结：
悲观锁优点：悲观锁利用数据库中的锁机制来实现数据变化的顺序执行，这是最有效的办法。
缺点：一个事务用悲观锁对数据加锁之后，其他事务将不能对加锁的数据进行除了查询以外的所有操作，如果该事务执行时间很长，那么其他事务将一直等待，那势必影响我们系统的吞吐量。

乐观锁优点：乐观锁不在数据库上加锁，任何事务都可以对数据进行操作，在更新时才进行校验，这样就避免了悲观锁造成的吞吐量下降的劣势。
缺点：乐观锁因为是通过我们人为实现的，它仅仅适用于我们自己业务中，如果有外来事务插入，那么就可能发生错误。

悲观锁：因为悲观锁会影响系统吞吐的性能，所以适合应用在写为居多的场景下。
乐观锁：因为乐观锁就是为了避免悲观锁的弊端出现的，所以适合应用在读为居多的场景下。

意向锁、插入意向锁

意向锁：是一种不与行级锁冲突的表级锁。未来的某个时刻，事务可能要加共享或者排它锁时，先提前声明一个意向。InnoDB是支持表锁和行锁共存的，如果一个事务A获取到某一行的排他锁，并未提交，这时候事务B请求获取同一个表的表共享锁。因为共享锁和排他锁是互斥的，因此事务B想对这个表加共享锁时，需要保证没有其他事务持有这个表的表排他锁，同时还要保证没有其他事务持有表中任意一行的排他锁。然后问题来了，你要保证没有其他事务持有表中任意一行的排他锁的话，去遍历每一行？这样显然是一个效率很差的做法。为了解决这个问题，InnoDb的设计大叔提出了意向锁。

意向锁分为两类：
意向共享锁：简称IS锁，当事务准备在某些记录上加S锁时，需要现在表级别加一个IS锁。
意向排他锁：简称IX锁，当事务准备在某条记录上加上X锁时，需要现在表级别加一个IX锁。
比如：
select ... lock in share mode，要给表设置IS锁。
select ... for update，要给表设置IX锁。

意向锁又是如何解决这个效率低的问题呢：
如果一个事务A获取到某一行的排他锁，并未提交，这时候表上就有意向排他锁和这一行的排他锁。这时候事务B想要获取这个表的共享锁，此时因为检测到事务A持有了表的意向排他锁，因此事务A必然持有某些行的排他锁，也就是说事务B对表的加锁请求需要阻塞等待，不再需要去检测表的每一行数据是否存在排他锁啦。意向锁仅仅表明意向的锁，意向锁之间不会互斥，是可以并行的。

插入意向锁：是插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插入方式的信号。
它解决的问题：多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。假设有索引值4、7，几个不同的事务准备插入5、6，每个锁都在获得插入行的独占锁之前用插入意向锁各自锁住了4、7之间的间隙，但是不阻塞对方因为插入行不冲突。

记录锁、间隙锁、临键锁、自增锁

记录锁（Record Lock）：是最简单的行锁，仅仅锁住一行。如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE，如果C1字段是主键或者是唯一索引的话，这个SQL会加一个记录锁。记录锁永远都是加在索引上的，即使一个表没有索引，InnoDB也会隐式的创建一个索引，并使用这个索引实施记录锁。它会阻塞其他事务对这行记录的插入、更新、删除。

间隙锁（Gap Lock）：为了解决幻读问题，InnoDB引入了间隙锁。间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。它锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

临键锁（Next-key）：锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。说得更具体一点就是，临键锁会封锁索引记录本身，以及索引记录之前的区间，即它的锁区间是前开后闭，比如(5,10]。如果一个会话占有了索引记录R的共享/排他锁，其他会话不能立刻在R之前的区间插入新的索引记录。

自增锁：是一种特殊的表级别锁。它是专门针对AUTO_INCREMENT类型的列，对于这种列，如果表中新增数据时就会去持有自增锁。简言之，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

MySQL锁表操作

锁表是如何发生的

如果有多个并发请求存取数据，在数据就可能会产生多个事务同时操作同一行数据。如果并发操作不加控制，不加锁的话，就可能写入了不正确的数据，或者导致读取了不正确的数据，破坏了数据的一致性。因此需要考虑加锁。在MySQL中，锁表的原因是一个程序执行了对表的insert、update或者delete操作还未commite时，另一个程序也对同一个表进行相同的操作，则此时会发生资源正忙的异常，也就是锁表。

1、锁表发生在insert、update 、delete中

2、锁表的原理是数据库使用独占式封锁机制，当执行上面的语句时，对表进行锁住，直到发生commite或者回滚或者退出数据库用户。
第一种：A程序执行了对tableA的insert，并还未commite时，B程序也对tableA进行insert则此时会发生资源正忙的异常，就是锁表。
第二种：锁表常发生于并发而不是并行（并行时，一个线程操作数据库时，另一个线程是不能操作数据库的，cpu和io分配原则）。

3、减少锁表的概率：
减少insert 、update 、delete语句执行到commite之间的时间。
批量执行改为单个执行、优化sql自身的非执行速度，如果异常则对事物进行回滚。

4、锁表案例
1）使用update，假设kid是表table的一个索引字段且值不唯一：
如果kid有多个值为12的记录那么：
update table set name=’feie’ where kid=12; #会锁表
如果kid有唯一的值为1的记录那么：
update table set name=’feie’ where kid=1; #不会锁
总结：用索引字段做为条件进行修改时，是否锁表取决于这个索引字段能否确定记录唯一，当索引值对应记录不唯一，会进行锁表，相反则行锁。

2）使用delete，如果有两个delete，kid1与kid2是索引字段
语句1：delete from table where kid1=1 and kid2=2;
语句2：delete from table where kid1=1 and kid2=3;
这样的两个delete是不会锁表的
语句1：delete from table where kid1=1 and kid2=2;
语句2：delete from table where kid1=1;
这样的两个delete会锁表
总结：同一个表，如果进行删除操作时，尽量让删除条件统一，否则会相互影响造成锁表。

alter table锁表分析

常见的一种锁表场景就是有事务操作处于Waiting for table metadata lock状态（比如修改表结构时插入数据）。MySQL在进行alter table等DDL操作时，有时会出现Waiting for table metadata lock的等待场景。一旦alter table的操作停滞在Waiting for table metadata lock状态，后续对该表的任何操作（包括读）都无法进行，因为它们也会在Opening tables的阶段进入到Waiting for table metadata lock的锁等待队列。如果核心表出现了锁等待队列，就会造成灾难性的后果。

场景一：长事务运行，阻塞DDL，继而阻塞所有同表的后续操作
通过show processlist可以看到表上有正在进行的操作（包括读），此时alter table语句无法获取到metadata独占锁，会进行等待。

场景二：为提交事务，阻塞DDL，继而阻塞所有同表的后续操作
通过show processlist看不到表上有任何操作，但实际上存在有未提交的事务，可以在information_schema.innodb_trx中查看到。在事务没有完成之前，表上的锁不会释放，alter table同样获取不到metadata的独占锁。处理方法：通过select * from information_schema.innodb_trx\G, 找到未提交事物的sid，然后kill掉，让其回滚。

场景三：显式事务失败操作获得锁，未释放
通过show processlist看不到表上有任何操作，在information_schema.innodb_trx中也没有任何进行中的事务。很可能是因为在一个显式的事务中，对表进行了一个失败的操作（比如查询了一个不存在的字段），这时事务没有开始，但是失败语句获取到的锁依然有效，没有释放。从performance_schema.events_statements_current表中可以查到失败的语句。处理方法：通过performance_schema.events_statements_current找到其sid，kill 掉该session，也可以kill掉DDL所在的session。

总之，alter table的语句是很危险的（核心是未提交事务或者长事务导致的），在操作之前要确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter table的维护任务，在无人监管的时候运行，最好通过lock_wait_timeout设置好超时时间，避免长时间的metedata锁等待。

truncate锁表分析

认识truncate（不和其他语句一起执行时）：truncate只作用于表，主要用于删除一张表中的所有数据，其作用与不带任何条件的delete一样，且都不会破坏表结构。但是，truncate不走事务，不会锁表；不会产生日志，不写入日志文件；truncate执行完之后，立马释放磁盘空间。truncate会清空表中的所有行，但表结构及其约束、索引不会被改变，但是会使表和索引所占用的空间会恢复到初始大小。最后一点，它还会重置表的自增值。这样的效果，让我们联想起，它是不是像是把一张表drop之后，又把表重新create了一遍，答案确实如此。

delete简述（有事务支持）：删除表全部数据或者部分数据，删除的时候是一行一行删除的，所以删除表全部数据时速度比较慢。语句后面可以用where过滤待删除的行，比较灵活语句执行之后会返回本次删除的行数。

问题产生：表之间数据同步时，逻辑是先truncate再insert重建（清空数据库表重新生成数据）。锁表之后会导致锁库，导致整个系统无法使用。没清空重建完数据库表就开始插入数据，清空没有事务支持。

锁表问题分析：cpu不高很平稳，慢查询正常，但连接数很高。这种很可能是锁表。进去一看processlist果然，那个truncate卡在那里，然后一堆线程在wating for meta data lock...，kill后故障恢复，数据表改由delete清空可正常运行。

原因分析：锁表或数据库繁忙的一个显著表现就是connection飙升，这是由于连接池的行为，查询无法返回就新开连接重查。严重时可以耗尽connection limit。所以truncate应慎重，它属于ddl，会lock table meta data，甚至可能由锁表升级为锁库。由于经验，删除数据truncate肯定是最快的，并且会回收空间，这是最好的选择。其实不然，经过刷新进程知道，当现象为system lock时，进程会同时出现大量等待。当system lock出现频率比较高时等待就越多，延迟也就越高。system lock为系统锁，整个库的进程都得等待。

系统锁产生原因：truncate为ddl语句，会改变元数据，会lock table meta data，空间直接释放，数据丢失不易找回，该现状会由锁表升级为锁库。影响极其严重。因此，truncate虽然快并且好用，但是在系统层面还是得慎用，特别是使用频次较高的时候。

MySQL解锁操作

杀死进程解锁

-- 锁表解决方法（频繁的操作会让数据库表只读不可修改）
-- 显示完整的进程列表
show full processlist;
-- 查看有是哪些事务占据了表资源（可省略，杀死等待的进程即可）
select * from information_schema.innodb_trx; 
-- 杀死进程
kill 436;

-- 参数说明
第一列id，一个标识，你要kill一个语句的时候使用。
第二列user列，显示当前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。
第三列host列，显示这个语句是从哪个ip的哪个端口上发出的，可以用来追踪出问题语句的用户。
第四列db列，显示这个进程目前连接的是哪个数据库。
第五列command列，显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。
第六列time列，此这个状态持续的时间，单位是秒。
第七列state列，显示使用当前连接的sql语句的状态，很重要的列，注意，state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成。
第八列info列，显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，是一个判断问题语句的重要依据。

锁表引发的问题与解决方案

发现问题：用户反馈某功能页面报502错误（锁表会导致系统功能无法使用），于是第一时间看服务是否正常，数据库是否正常。在控制台看到数据库CPU飙升，堆积大量未提交事务，部分事务已经阻塞了很长时间，基本定位是数据库层出现问题了。查看阻塞事务列表，发现其中有锁表现象，本想利用控制台直接结束掉阻塞的事务，但控制台账号权限有限，于是通过客户端登录对应账号将锁表事务kill掉，才避免了情况恶化。

解决问题：
1）首先最简单粗暴的方式就是：重启MySQL。注意：这样操作有数据丢失的风险。
2）查看哪些表正在被使用，重点排查筛选出来的数据库表
show open tables where in_use > 0;
3）查看正在运行的进程，该命令只显示当前用户正在运行的进程，当然，如果是root用户是能看到所有的。
show processlist;
杀死进程
kill 1009;
4）下面是一些更加准确的定位方式
-- 查看当前运行的所有事务
SELECT * FROM information_schema.INNODB_TRX;
-- 查看当前出现的锁
SELECT * FROM information_schema.INNODB_LOCKs;
-- 查询锁等待的对应关系
SELECT * FROM information_schema.INNODB_LOCK_waits;

如何杀死特定用户的所有MySQL进程

要杀死特定用户的所有进程，请使用CONCAT创建包含线程和语句列表的文件，在本例中，我们以root用户身份输入。要指定另一个用户，请将root替换为所需的用户名。
SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE user='root' INTO OUTFILE '/tmp/process_list.txt';
此查询创建了一个process_list.txt的文件，你可以根据自己的喜好编辑名字，打开文件并检查它们是否是你想要杀死的进程，准备好后，输入下面命令执行：
SOURCE /tmp/process_list.txt;
你可以向查询添加条件以缩小输出文件中的进程列表。例如，将time > 1000添加到命令中，只包含时间值大于1000的进程。
SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE user='root' and time>1000 INTO OUTFILE '/tmp/process_list.txt';

show processlist命令

show processlist命令通过查看mysql的官网，可以发现，其主要是查询数据库中哪些线程正在执行，针对比较慢的线程（time的数值比较大的线程）我们可以将其kill掉。此外，show full processlist返回的结果是实时变化的。有三种方式可以执行show processlist，可以通过命令行、SQL语句、Navicat客户端等。

show processlist参数：
id：ID标识，要kill一个语句的时候很有用（kill id）
use：当前连接用户
host：显示这个连接从哪个ip的哪个端口上发出
db：数据库名
command：连接状态，一般是休眠（sleep），查询（query），连接（connect）
time：连接持续时间，单位是秒
state：显示当前sql语句的状态
info：显示这个sql语句

state状态说明：
官网：https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html
Checking table：正在检查数据表（这是自动的）。
Closing tables：正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out：复制从服务器正在连接主服务器。
Copying to tmp table on disk：由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table：正在创建临时表以存放部分查询结果。
deleting from main table：服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables：服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables：正在执行FLUSH TABLES，等待其他线程关闭数据表。
Killed：发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked：被其他查询锁住了。
Sending data：正在处理SELECT查询的记录，同时正在把结果发送给客户端。
Sorting for group：正在为GROUP BY做排序。
Sorting for order：正在为ORDER BY做排序。
Opening tables：这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
Removing duplicates：正在执行一个SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table：获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting：修复指令正在排序以创建索引。
Repair with keycache：修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
Searching rows for update：正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。
Sleeping：正在等待客户端发送新请求。
System lock：正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加–skip-external-locking参数来禁止外部系统锁。
Upgrading lock：INSERT DELAYED正在尝试取得一个锁表以插入新记录。
Updating：正在搜索匹配的记录，并且修改它们。
User Lock：正在等待GET_LOCK()。
Waiting for tables：该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
waiting for handler insert：INSERT DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。

注意：进入mysql/bin目录下输入mysqladmin processlist也可以查看正在执行线程。如果有SUPER权限，则可以看到全部的线程，否则，只能看到自己发起的线程（这是指，当前对应的MySQL帐户运行的线程）。show processlist只列出前100条，如果想全列出则使用show full processlist。

show status命令

show status命令，查看数据库使用状态，参数含义如下：
Aborted_clients：由于客户没有正确关闭连接已经死掉，已经放弃的连接数量；
Aborted_connects：尝试已经失败的MySQL服务器的连接的次数；
Connections：试图连接MySQL服务器的次数；
Created_tmp_tables：当执行语句时，已经被创造了的隐含临时表的数量；
Delayed_insert_threads：正在使用的延迟插入处理器线程的数量；
Delayed_writes：用INSERT DELAYED写入的行数；
Delayed_errors：用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数；
Flush_commands：执行FLUSH命令的次数；
Handler_delete：请求从一张表中删除行的次数；
Handler_read_first：请求读入表中第一行的次数；
Handler_read_key：请求数字基于键读行；
Handler_read_next：请求读入基于一个键的一行的次数；
Handler_read_rnd：请求读入基于一个固定位置的一行的次数；
Handler_update：请求更新表中一行的次数；
Handler_write：请求向表中插入一行的次数；
Key_blocks_used：用于关键字缓存的块的数量；
Key_reads：请求从缓存读入一个键值的次数；
Key_write_requests：从磁盘物理读入一个键值的次数；
Key_writes：请求将一个关键字块写入缓存次数；
Max_used_connections：将一个键值块物理写入磁盘的次数；
Not_flushed_key_blocks：同时使用的连接的最大数目；
Not_flushed_delayed_rows：在INSERT DELAY队列中等待写入的行的数量；
Open_tables：打开表的数量；
Open_files：打开文件的数量；
Open_streams：打开流的数量(主要用于日志记载）；
Opened_tables：已经打开的表的数量；
Questions：发往服务器的查询的数量；
Slow_queries：要花超过long_query_time时间的查询数量；
Threads_connected：当前打开的连接的数量；
Threads_running：不在睡眠的线程数量；
Uptime：服务器工作了多少秒；

MySQL加锁操作

并发情况下，如何做到安全的修改同一行数据

要安全的修改同一行数据，就要保证一个线程在修改时其它线程无法更新这行记录。其实一般有悲观锁和乐观锁两种思想。
1）悲观锁思想：当前线程要进来修改数据时，别的线程都得拒之门外。比如，可以使用select...for update，select * from User where name=‘jay’ for update;以上这条sql语句会锁定User表中所有符合检索条件name=‘jay’的记录。本次事务提交之前，别的线程都无法修改这些记录。
2）乐观锁思想：就是有线程过来，先放过去修改，如果看到别的线程没修改过，就可以修改成功，如果别的线程修改过，就修改失败或者重试。实现方式：乐观锁一般会使用版本号机制或CAS算法实现。
3）分布式锁思想：数据库分布式锁、Redis分布式锁、Zookeeper分布式锁

InnoDB三种行锁的算法

MySQL上的行级锁是通过给索引上的索引项加锁来实现，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。同时，当两条不同行的记录使用了相同的索引键时，也是会发生锁冲突的。比如这条SQL：select * from t where id = 666 for update。可以根据条件来完成行锁锁定，并且id是有索引键的列，如果id不是索引键那么InnoDB将实行表锁。

InnoDB行锁的3种算法：
Record Lock：单个索引记录上的锁。
Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。
Next-Key Lock：它等于Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身。

如果查询条件的是唯一索引或者主键时，Next-Key Lock会降为Record Lock。如果是普通索引，将对下一个键值加上gap lock，其实就是对下一个键值的范围为加锁。gap lock间隙锁，就是为了解决幻读问题而设计出来的。间隙锁是RR隔离级别的，如果你想关闭间隙锁，你可以修改隔离级别。也可以修改这个数据库参数innodb_locks_unsafe_for_binlog为1。

一条SQL是如何加锁的

1）查询条件是主键，RC隔离级别
在RC（读已提交）的隔离级别下，对查询条件是主键id的场景，会加一个排他锁（X锁），或者说加一个X型的记录锁。

2）查询条件是唯一索引，RC隔离级别
id是唯一索引，name是主键的场景下，我们给定SQL：delete from t2 where id = 6。在RC隔离级别下，该SQL需要加两个X锁，一个对应于id唯一索引上的id = 6的记录，另一把锁对应于聚簇索引上的[name=’b’,id=6]的记录。

为什么主键索引上的记录也要加锁呢？
如果并发的一个SQL，是通过主键索引来更新：update t2 set id = 666 where name = 'b';此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

3）查询条件是普通索引，RC隔离级别
若id列是普通索引，那么对应的所有满足SQL查询条件的记录，都会加上锁。同时，这些记录对应主键索引，也会上锁。

4）查询条件上没有索引，RC隔离级别
若id列上没有索引，MySQL会走聚簇索引进行全表扫描过滤。每条记录都会加上X锁。但是，为了效率考虑，MySQL在这方面进行了改进，在扫描过程中，若记录不满足过滤条件，会进行解锁操作。同时优化违背了2PL原则。

5）查询条件是主键，RR隔离级别
给定SQL：delete from t1 where id = 6，如果id是主键的话，在RR隔离级别下，跟RC隔离级别，加锁是一样的，也都是在id = 6这条记录上加上X锁。

6）查询条件是唯一索引，RR隔离级别
给定SQL：delete from t1 where id = 6，如果id是唯一索引的话，在RR隔离级别下，跟RC隔离级别，加锁也是一样的，加了两个X锁，id唯一索引满足条件的记录上一个，对应的主键索引上的记录一个。

7）查询条件是普通索引，RR隔离级别
如果查询条件是普通的二级索引，在RR（可重复读的隔离级别下），除了会加X锁，还会加间隙Gap锁。Gap锁的提出，是为了解决幻读问题引入的，它是一种加在两个索引之间的锁（如id=10加锁，则下一个15之间会加间隙锁，则添加id=12会等待锁释放）。

8）查询条件上没有索引，RR隔离级别
如果查询条件列没有索引，主键索引的所有记录，都将加上X锁，每条记录间也都加上间隙Gap锁。大家可以想象一下，任何加锁并发的SQL，都是不能执行的，全表都是锁死的状态。如果表的数据量大，那效率就更低。在这种情况下，MySQL做了一些优化，即semi-consistent read，对于不满足条件的记录，MySQL提前释放锁，同时Gap锁也会释放。而semi-consistent read是如何触发的呢：要么在Read Committed隔离级别下；要么在Repeatable Read隔离级别下，设置了innodb_locks_unsafe_for_binlog参数。但是semi-consistent read本身也会带来其他的问题，不建议使用。

9）Serializable隔离级别
在Serializable串行化的隔离级别下，对于写的语句，比如update account set balance= balance-10 where name=‘Jay’，跟RC和RR隔离级别是一样的。不一样的地方是，在查询语句，如select balance from account where name = ‘Jay’，在RC和RR是不会加锁的，但是在Serializable串行化的隔离级别，即会加锁。

RR隔离级别下的加锁规则

对于RC隔离级别，加的排他锁（X锁），是比较好理解的，哪里更新就锁哪里嘛。但是RR隔离级别，间隙锁是怎么加的呢？对InnoDb的锁来说，面试的时候问的比较多，就是Record lock、Gap lock、Next-key lock。接下来我们来学习，RR隔离级别，到底一个锁是怎么加上去的。首先MySQL的版本，是5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。加锁规则一共包括：两个原则、两个优化和一个bug：
原则1：加锁的基本单位都是next-key lock。next-key lock（临键锁）是前开后闭区间。
原则2：查找过程中访问到的对象才会加锁。
优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁（Record lock）。
优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁（Gap lock）。
一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

参考链接：https://mp.weixin.qq.com/s?__biz=MzkyMzU5Mzk1NQ==&mid=2247506461&idx=1&sn=ff71cb615ecea4df64b8d42701a05243&source=41#wechat_redirect

如何查看事务加锁情况

使用infomation_schema数据库中的表获取锁信息

infomation_schema数据库中，有几个表跟锁紧密关联的：
1）INNODB_TRX：该表存储了InnoDB当前正在执行的事务信息，包括事务id、事务状态（比如事务是在运行还是在等待获取某个所）等。我们一般关注这几个参数：
trx_tables_locked：该事务当前加了多少个表级锁。
trx_rows_locked：表示当前加了多少个行级锁。
trx_lock_structs：表示该事务生成了多少个内存中的锁结构。

2）INNODB_LOCKS：该表记录了一些锁信息，包括两个方面：
1、如果一个事务想要获取某个锁，但未获取到，则记录该锁信息。
2、如果一个事务获取到了某个锁，但是这个锁阻塞了别的事务，则记录该锁信息。

3）INNODB_LOCK_WAITS：表明每个阻塞的事务是因为获取不到哪个事务持有的锁而阻塞。
requesting_trx_id：表示因为获取不到锁而被阻塞的事务的事务id
blocking_trx_id：表示因为获取到别的事务需要的锁而导致其被阻塞的事务Id。

如何查看一个SQL加了什么锁：执行完原生SQL，再执行SELECT * FROM performance_schema.data_lock。

show engine innodb status

INNODB_LOCKS和INNODB_LOCK_WAITS在MySQL 8.0已被移除，其实就是不鼓励我们用这两个表来获取表信息。而我们还可以用show engine innodb status获取当前系统各个事务的加锁信息。在看死锁日志的时候，我们一般先把这个变量innodb_status_output_locks打开，它是MySQL 5.6.16 引入的：set global innodb_status_output_locks=on。

日志关键词：
lock_mode X locks gap before rec 表示X型的gap锁。
lock_mode X locks rec but not gap 表示X型的记录锁（Record Lock）。
lock mode X 一般表示X型临键锁（next-key锁）。

锁日志一般关注点是以下几个地方：
-- 表示它在等这个锁
TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED

-- 表示一个锁结构，这个锁结构的Space ID是267，page number是4，n_bits属性为80，对应的索引是c，这个锁结构中存放的锁类型是X型的插入意向Gap锁。
RECORD LOCKS space id 267 page no 4 n bits 80 index c of table `test2`.`t5` trx id 1644853 lock_mode X locks gap before rec insert intention waiting

-- 对应加锁记录的详细信息，8000000a代表的值就是10，a的16进制是10。
0: len 4; hex 8000000a; asc ;;

-- 表示一个插入意向表锁
TABLE LOCK table `test2`.`t5` trx id 1644853 lock mode IX

这个日志例子，其实理解起来，就是事务A持有了索引c的间隙锁（~，10），而事务B想获得这个gap锁，而获取不到，就一直在等待这个插入意向锁。

如何排查死锁问题

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

如何防止死锁？
1）尽量约定固定的顺序访问表，因为交叉访问更容易造成事务等待回路。
2）尽量避免大事务，建议拆成多个小事务。因为大事务占用的锁资源越多，越容易出现死锁。
3）降低数据库隔离级别，比如RR降低为RC，因为RR隔离级别，存在GAP锁，死锁概率大很多。
4）死锁与索引是密不可分的，合理优化你的索引，死锁概率降低。
5）如果业务处理不好可以用分布式事务锁或者使用乐观锁。

如何排查死锁问题？
1）查看最近一次死锁日志，命令show engine innodb status;
2）找出死锁Sql，找到关键词TRANSACTION
3）分析sql加锁情况，查看正在执行的SQL
4）模拟死锁案发
5）分析死锁日志
6）分析死锁结果，看SQL持有什么锁，又在等待什么锁