会话
事务
行锁锁定的对象查询 --自动释放
mdl锁定对象的查询
阻塞查询
====================================================================================================================================================================
1.会话与事务id
information_schema.processlist: ID
perforamance_schema.threads： THREAD_ID, PROCESSLIST_ID, THREAD_OS_ID
information_schema.innodb_trx: trx_mysql_thread_id, trx_id
performation_schema.metadata_locks: OWNER_THREAD_ID
performation_schema.data_locks:THREAD_ID --等效于threads的thread_id
解释：
processlist表id： 一般在mysql层使用，跟前台登录会话相关联，是真正的线程ID。
thread_id: 仅在perforamance_schema中使用，是其内部自动增长的计数器，前台和后台线程均有这个id，且thread_id和thread_os_id相关联。
processlistid和前台用户直接相关，每创建一个登录会话，就会新增一个processlistid。
在performance_schema.threads表中，后台线程（非会话进程）没有processlist表ID的值，这个值是NULL，这是因为后台线程不是登录用户所创建的。
但是前后台线程都会有thread_os_id和thread_id的值，因为所有线程最终都要依赖OS线程执行，thread_id和thread_os_id有一定的对应关系。
thread_os_id: 系统内部分配的线程ID,与ps -ef出来的mysql线程号相同，top -H -p mysqld_pid，当然，OS不会为每一个会话创建os_id。
trx_mysql_thread_id: 这个参数的值和processlis表id相同，即执行事务的前台线程id，也即会话ID。
trx_mysql_thread_id: 即show processlist结果中的ID，也就是performance_schema.thread表中的processlist_id;
trx_id:事务id
OWNER_THREAD_ID:等效于thread_id,不是processlist表id
因此：一般情况下thread_id和thread_os_id不需要关注，只是用来性能库和后台使用。
一个事务的trx_id会关联一个trx_mysql_thread_id，而trx_mysql_thread_id的值又等同于processlist表id(也是session id)，通过processlistid去performance_schema.threads表可找到thread_id和thread_os_id 。
当需要kill掉一个事务时，不能直接kill thread_os_id，这样会导致数据库重启，且重启后事务会继续执行，因为事务具有原子性；
如果直接执行kill processlistid或kill trx_mysql_thread_id或kill session_id，这也是我们常用的操作，这会使事务对应的trx_mysql_thread_id置为0，事务会rollback。
一个ip被限制连接：
同一个ip在短时间内产生太多(超过mysql数据库max_connection_errors的最大值)中断的数据库连接而导致的阻塞，此时需要清理数据库缓存
解决：
最简单的方法是root登录后，直接使用：mysql> flush hosts; -----Host is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’；
未来将会移除这个用法，替换为：TRUNCATE TABLE performance_schema.host_cache;
==========================================================================================================================================================================
2.mysql中的所有锁
全局锁： ------锁定所有库，server层的锁，一般备份时候用。查不到
flush table with read lock;
unlock tables;
lock instance for backup; ------8.0新增特性
unlock instance;
mysql 8中新增了一个轻量级的备份锁，它允许在online备份的时候进行DML操作，同时可防止快照不一致。这个锁禁止的操作很少，它禁止的操作包括：文件的创建、删除、改名，账户的管理和 REPAIR TABLE 、TRUNCATE TABLE、 OPTIMIZE TABLE。
使用这些语句需要BACKUP_ADMIN权限：
grant BACKUP_ADMIN on *.* to zhao@'%';
长查询对flush tables with read lock是有影响的，整个系统会hung住，这时其它的数据库查询也不能用，包括use database。可以看到大家都在等这个锁释放。而lock instance for backup则没有这个问题。
页级锁： ----BDB引擎支持
=======
表级锁(table)：X,S -------innodb与myisam引擎都支持。
读锁会阻断其他客户写，写锁也阻断其他客户读写；同时自己不能访问其他表只能访问锁定的表，其他用户可以。
lock table t1 read/write;
unlock tables;
DDL语句产生的锁。
加字段，拆分区，建索引等
MDL -- 元数据锁，无需显式使用，自动加; 当对一张表进行增删改查的时候，加MDL表级别读锁(共享)；当对表结构进行变更操作的时候，加MDL表写锁.------说明正在执行查询时不能改表结构
意向锁（table）：IX、IS ----在表上加，表锁
1、概念
意向锁是表锁，为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存
2、作用
当有事务A有行锁时，MySQL会自动为该表添加意向锁，事务B如果想申请整个表的写锁，那么不需要遍历每一行判断是否存在行锁，而直接判断是否存在意向锁，增强性能。
行级锁(record):X、S共享 -------innodb引擎支持，行级锁并不是直接锁记录，而是锁的索引。
for update/lock in share mode;
间隙锁(record)：REC_NOT_GAP、GAP ------REC_NOT_GAP无间隙锁，GAP间隙锁
参数：
innodb_locks_unsafe_for_binlog=OFF(开启gap lock) --该参数只能在数据库启动时进行设置，隔离级别可以在启动后进行动态设置 --RR模式能开，rc模式没有这个参数
===
MDL锁： -----DDL或lock table t1 read/write 与 DML与DDL之间产生的锁。
元数据锁，无需显式使用，当执行DDL或DML语句时会自动加;
当对一张表进行增删改查的时候，加MDL共享读写锁； --SHARED_READ、SHARED_WRITE
当对表结构进行变更或lock table操作的时候，加MDL独占写锁。 --SHARED_READ_ONLY、SHARED_NO_READ_WRITE ------说明正在执行查询时不能改表结构
发生mdl锁，怎么定位源头？
DDL会话先被授予了SHARED_UPGRADABLE，然后被授予INTENTION_EXCLUSIVE，然后想被授予EXCLUSIVE的锁，但是拿不到，所以处于PENDING状态。所以只要找到GRANTED的会话是哪个。
==========
行锁等待参数： ---只管行锁（增删改查产生的锁），管不了表锁lock table/DDL语句等。
innodb_lock_wait_timeout=30 (秒，global或session,超过这个时间还未分配到资源则会返回应用失败)
=========
行锁监控指标：
锁等待个数： show status like 'Innodb_row_lock_waits';
平均每次锁等待时间： show status like 'Innodb_row_lock_time_avg';
查看是否存在表锁： show open TABLES where in_use>0； ---有数据代表存在锁表，空为无表锁
show engine innodb status\G;
===========================================================================================================================================================================================
常用查询：
=======================
1.连接（会话）
连接断开时间参数： ---线程池与on-thread-per-connect都生效
wait_timeout=600 (s) mysql客户端连接
interactive_timeout=600 s jdbc连接
说得直白一点，通过mysql命令行终端连接数据库是交互式连接，通过jdbc等连接数据库是非交互式连接。
对于任何连接来讲都是wait_timeout生效的。
interactive_timeout就是在连接的时候判断，
如果是交互式连接就用interactive_timeout替换掉wait_timeout。
然后我们利用performance_schema.variables_by_thread来观察会话级别的参数，如下：
select b.processlist_id,a.VARIABLE_NAME,a.VARIABLE_VALUE,b.name,b.type
from performance_schema.variables_by_thread a ,performance_schema.threads b
where a.THREAD_ID=b.THREAD_ID and VARIABLE_NAME in('wait_timeout','interactive_timeout') and TYPE='FOREGROUND';
如果会话参数中interactive_timeout == wait_timeout且为28700 那么为交互式。
如果会话参数中interactive_timeout != wait_timeout一个为28800一个为28700 则为非交互式。
查看：
show status like'%thread%'; ----线程池与one-thread-per-connect都用这个
Threads_connected:连接数
Threads_running：正在运行连接数
show variables like'%connection%'; ---还是这个控制最大连接数，在这个基础上线程池还能控制
max_connections:20000
show processlist;
select * from information_schema.processlist where COMMAND!='Sleep'; ---Time列，当前状态持续时间
2.查sql 执行次数 与 并发 数量
sql执行次数查不了
并发，mysql没并行的hint
=======================
锁查询：
1.查被锁定的表、分区表对象: --表锁、表意向锁、行锁等锁定的对象都包含在里面。 正常增删改查产生的锁，有MDL共享读写锁。
select THREAD_ID,
OBJECT_SCHEMA,
OBJECT_NAME,
PARTITION_NAME,
SUBPARTITION_NAME,
LOCK_TYPE,
LOCK_MODE,
LOCK_STATUS,
LOCK_DATA
from performance_schema.data_locks
order by OBJECT_SCHEMA,OBJECT_NAME,THREAD_ID;
2.查行锁阻塞的源头： ---正常增删改查(DML与DML)差生的锁。复杂环境很不好定位阻塞源头。 --生产环境很常见
一般不用管，innodb_lock_wait_timeout=30等待超时sql就停了。
select REQUESTING_THREAD_ID be_blocked,BLOCKING_THREAD_ID blocking from performance_schema.data_lock_waits order by blocking; ---一般看出现最多的thread_id(blocking)，就是源头。
3.查表锁阻塞的源头： -----生产环境不常见
DDL导致执行DML语句阻塞的源头分析。 （DDL阻塞DDl的源头很好找）
sql不会自己报错停止，data_lock_waits也查不到阻塞。data_locks里有表锁。
select THREAD_ID,
OBJECT_SCHEMA,
OBJECT_NAME,
PARTITION_NAME,
SUBPARTITION_NAME,
LOCK_TYPE,
LOCK_MODE,
LOCK_STATUS,
LOCK_DATA
from performance_schema.data_locks
where LOCK_TYPE='TABLE' and LOCK_MODE in ('X','S');
或者
DDL阻塞DDL
select * from performance_schema.metadata_locks where LOCK_TYPE not in('SHARED_READ','SHARED_WRITE') and OBJECT_TYPE='TABLE' and OBJECT_NAME ='t1'; --看PENDING与GRANTED
show open TABLES where in_use>0； ---有数据代表存在锁表，空为无表锁
4.查MDL锁阻塞的源头： -----生产环境，有点常见。
DML阻塞DDL:
data_locks表即没表锁也没行锁记录，sql也不自己停止。 -----最典型的：查询阻塞DDL语句执行 。当然其他DML阻塞DDL也可以
with l as(select OBJECT_SCHEMA,
OBJECT_NAME
from performance_schema.metadata_locks
where LOCK_TYPE='EXCLUSIVE' and LOCK_STATUS='PENDING')
select a.*
from performance_schema.metadata_locks a join l on a.OBJECT_SCHEMA=l.OBJECT_SCHEMA and a.OBJECT_NAME=l.OBJECT_NAME;
5.根据thread_id 查 processlist id:
select * from performance_schema.threads where 各种 threadid = ;
============================
1.长事务查询：
SELECT trx_mysql_thread_id AS PROCESSLIST_ID,
trx_state,
NOW(),
TRX_STARTED,
TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME,
DB,
TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX t
JOIN INFORMATION_SCHEMA.processlist l ON t.trx_mysql_thread_id=l.id
WHERE t.trx_mysql_thread_id != connection_id()
AND TO_SECONDS(now())-TO_SECONDS(t.trx_started) >= 500; -----时间秒
trx_id：InnoDB 内部的唯一事务 ID，不会为只读且非锁定事务创建 ID。
trx_mysql_thread_id：processlist id。
trx_state：事务的执行状态。值为 RUNNING(运行), LOCK WAIT(等待锁), ROLLING BACK(正在回滚), 和 COMMITTING(正在提交)。
TRX_STARTED:开始时间。
trx_query：事务正在执行的 sql。
trx_isolation_level：事务的隔离级别。
