MySQL锁分类
MySQL锁的分类有很多种，其中根据影响范围来划分主要分为全局锁、表锁、行锁。
MySQL锁实现
MySQL数据库里面的锁是基于索引实现的，在Innodb中锁都是作用在索引上面的，当SQL命中索引时，那么锁住的就是命中条件内的索引节点(行锁)，如果没有命中索引的话，那我们锁的就是整个索引树（表锁）。
全局读锁
MySQL 全局锁会申请一个全局的读锁，对整个库加锁。
1.备份时为了得到一致性备份，可能会添加全局读锁。
2.主从复制架构下，主备切换可能会用到全局读锁。
全局锁的实现方式有两种：
//第一种方法Flush tables with read lock（FTWRL）
//第二种方法set global readonly=true
当数据库处于全局锁的状态时，其他线程的以下语句会被阻塞：
数据更新语句（数据的增删改）、数据定义语句（建表、索引变更、修改表结构等）和更新类事务的提交语句。
释放全局锁
unlock tables;
全局读锁问题分析
在MySQL5.7之前的版本，要排查谁持有全局读锁，通常在数据库层面是很难直接查询到有用数据的(innodb_locks表也只能记录InnoDB层的锁信息，而全局读锁是Server层的锁，所以无法查询到)。
从MySQL5.7版本开始提供了performance_schema.metadata_locks表，用来记录一下Server层的锁信息(包括全局读锁和MDL锁等)。
下面通过示例演示如何找出谁持有全局读锁：
数据库版本MySQL5.7.35
MySQL [cjcdb]> select version(); +------------+ | version() | +------------+ | 5.7.35-log | +------------+ 1 row in set (0.00 sec)
创建测试数据
MySQL [cjcdb]> create table t1(id int,age int); Query OK, 0 rows affected (0.04 sec) MySQL [cjcdb]> insert into t1 values(1,100),(2,30),(3,80); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0
开启锁监控
performance_schema参数：
在MySQL 5.7中该参数默认关系，需要手动开启该参数并重启数据库实例生效，MySQL 8.0 开始默认开始该参数。
setup_instruments表：
事件采集配置项表，可指定具体采集项单独开启进行开启，MySQL对指定事件进行监控采集。
setup_consumers表：
事件存储配置表，可指定具体的监控采集项目进行开启，MySQL对setup_instruments表控制采集到的监控数据进行消费存储
PS可以监控哪些类别的事件信息
语句事件监控：有助于分析数据库SQL层面的资源消耗与瓶颈
等待事件监控：有助于分析锁等待相关的问题排查
阶段事件监控：有助于分析数据库各事件资源消耗与瓶颈点
事务事件监控：有助于分析事务相关的信息
文件系统调用事件监控：有助于分析
内存使用事件监控：有助于分析数据库内存的消耗
针对以上各类事件监控，数据库对于这些监控数据的存储主要包括：
当前事件记录信息表： events_xxx_current
历史事件记录信息表：events_xxx_history
长历事件记录信息表：events_xxx_history_long
各维度进行分组聚合后的事件统计表:
events_xxx_summary_by_account_by_event_name events_xxx_summary_by_host_by_event_name events_xxx_summary_by_instance events_xxx_summary_by_thread_by_event_name events_xxx_summary_by_user_by_event_name events_xxx_summary_global_by_event_name
开启锁相关监控
检查锁监控是否启用
MySQL [(none)]> select * from performance_schema.setup_instruments where name like '%lock%' limit 20; +---------------------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +---------------------------------------------------------+---------+-------+ | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc | NO | NO | | wait/synch/mutex/sql/LOCK_des_key_file | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_done | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_flush_queue | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_log | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_end_pos | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync_queue | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_xids | NO | NO | | wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_commit | NO | NO | | wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_commit_queue | NO | NO | | wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_done | NO | NO | | wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_flush_queue | NO | NO | | wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_index | NO | NO | | wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_log | NO | NO | | wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_sync | NO | NO | | wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_sync_queue | NO | NO | +---------------------------------------------------------+---------+-------+ 20 rows in set (0.01 sec)
如未开启，需要手动启动
MySQL [(none)]> update performance_schema.setup_instruments set enabled = 'YES' where name like '%lock%'; Query OK, 173 rows affected (6.27 sec) Rows matched: 180 Changed: 173 Warnings: 0
打开一个会话
MySQL [cjcdb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 11 | +-----------------+ 1 row in set (0.00 sec)
全局锁
MySQL [cjcdb]> flush table with read lock; Query OK, 0 rows affected (0.00 sec)
开启第 二个会 话
mysql> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 8 | +-----------------+ 1 row in set (0.00 sec)
执行update语句，被阻塞
mysql> update t1 set age=200 where id=2;
查看阻塞详细信息
开启第三个会话
MySQL [(none)]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 10 | +-----------------+ 1 row in set (0.00 sec)
查看当前会话信息
MySQL [(none)]> show processlist; +----+------+-----------+-------+---------+------+------------------------------+----------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+-------+---------+------+------------------------------+----------------------------------+ | 8 | root | localhost | cjcdb | Query | 27 | Waiting for global read lock | update t1 set age=200 where id=2 | | 10 | root | localhost | NULL | Query | 0 | starting | show processlist | | 11 | root | localhost | cjcdb | Sleep | 43 | | NULL | +----+------+-----------+-------+---------+------+------------------------------+----------------------------------+ 3 rows in set (0.00 sec)
可以看到,process id为8的线程State为Waiting for global read lock，表示正在等待全局读锁。
如何查询阻塞的源头：
MySQL [cjcdb]> select * from information_schema.innodb_locks; Empty set, 1 warning (0.00 sec) MySQL [cjcdb]> select * from information_schema.innodb_lock_waits; Empty set, 1 warning (0.00 sec) MySQL [cjcdb]> select * from information_schema.innodb_trx\G; Empty set (0.00 sec) MySQL [cjcdb]> show engine innodb status\G; ...... ------------ TRANSACTIONS ------------ Trx id counter 104240 Purge done for trx's n:o < 104240 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 422053759651440, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422053759649616, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422053759650528, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- ......
通过上面的常规手段查询，没有任何有用的信息。
在MySQL 5.7版本开始提供了performance_schema.metadata_locks表。
MySQL [(none)]> select * from performance_schema.metadata_locks where OWNER_THREAD_ID !=sys.ps_thread_id(connection_id())\G; *************************** 1. row *************************** OBJECT_TYPE: GLOBAL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: 140577980262000 LOCK_TYPE: SHARED ###共享锁 LOCK_DURATION: EXPLICIT ###显示 LOCK_STATUS: GRANTED ###已授权 SOURCE: OWNER_THREAD_ID: 36 ###持有锁的内部线程ID为36 OWNER_EVENT_ID: 9 *************************** 2. row *************************** OBJECT_TYPE: COMMIT OBJECT_SCHEMA: NULL OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: 140577970655552 LOCK_TYPE: SHARED ###共享锁 LOCK_DURATION: EXPLICIT ###显示 LOCK_STATUS: GRANTED ###已授权 SOURCE: OWNER_THREAD_ID: 36 ###持有锁的内部线程ID为36 OWNER_EVENT_ID: 9 *************************** 3. row *************************** OBJECT_TYPE: GLOBAL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: 140578306233392 LOCK_TYPE: INTENTION_EXCLUSIVE ###意向排他锁 LOCK_DURATION: STATEMENT ###语句 LOCK_STATUS: PENDING ###状态为PENDING,表示正在等待被授权 SOURCE: OWNER_THREAD_ID: 33 ###被阻塞的内部线程ID为33 OWNER_EVENT_ID: 12 3 rows in set (0.00 sec) ERROR: No query specified
通过查询可知，线程ID为33的会话，被线程ID为36的阻塞了。
那么如何通过线程ID找到对应的会话ID呢？
MySQL [(none)]> select THREAD_ID,PROCESSLIST_ID,name,type from performance_schema.threads where PROCESSLIST_ID is not NULL; +-----------+----------------+--------------------------------+------------+ | THREAD_ID | PROCESSLIST_ID | name | type | +-----------+----------------+--------------------------------+------------+ | 28 | 3 | thread/sql/compress_gtid_table | FOREGROUND | | 33 | 8 | thread/sql/one_connection | FOREGROUND | | 35 | 10 | thread/sql/one_connection | FOREGROUND | | 36 | 11 | thread/sql/one_connection | FOREGROUND | +-----------+----------------+--------------------------------+------------+ 4 rows in set (0.00 sec)
或者通过process id查找thread id。
MySQL [(none)]> select sys.ps_thread_id(8); +---------------------+ | sys.ps_thread_id(8) | +---------------------+ | 33 | +---------------------+ 1 row in set (0.00 sec) MySQL [(none)]> select sys.ps_thread_id(11); +----------------------+ | sys.ps_thread_id(11) | +----------------------+ | 36 | +----------------------+ 1 row in set (0.00 sec)
可以看到，正式process id 11 阻塞了 process id 8，当前process id的Command为Sleep。
MySQL [(none)]> show processlist; +----+------+-----------+-------+---------+------+------------------------------+----------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+-------+---------+------+------------------------------+----------------------------------+ | 8 | root | localhost | cjcdb | Query | 934 | Waiting for global read lock | update t1 set age=200 where id=2 | | 10 | root | localhost | NULL | Query | 0 | starting | show processlist | | 11 | root | localhost | cjcdb | Sleep | 950 | | NULL | +----+------+-----------+-------+---------+------+------------------------------+----------------------------------+ 3 rows in set (0.00 sec)
那么如何知道process id 11执行了什么操作？
通过performance_schema.events_statements_current可以找到process id 11正在执行和最后一次执行完成的语句信息。
这个信息并不一定可靠，因为该表只记录每个线程正在执行和最近一次执行完成的语句信息，一旦这个线程有新的语句执行，原来的语句会被覆盖。
MySQL [(none)]> select * from performance_schema.events_statements_current where thread_id=36\G; *************************** 1. row *************************** THREAD_ID: 36 EVENT_ID: 8 END_EVENT_ID: 8 EVENT_NAME: statement/sql/flush SOURCE: TIMER_START: 3250383126081000 TIMER_END: 3250383282239000 TIMER_WAIT: 156158000 LOCK_TIME: 0 SQL_TEXT: flush table with read lock DIGEST: 172f9471494a101656e6cb75da3e2726 DIGEST_TEXT: FLUSH TABLE WITH READ LOCK CURRENT_SCHEMA: cjcdb OBJECT_TYPE: NULL
在process id 11里执行一次select version();后。
再次查询performance_schema.events_statements_current，SQL信息已经变了。
MySQL [(none)]> select * from performance_schema.events_statements_current where thread_id=36\G; *************************** 1. row *************************** THREAD_ID: 36 EVENT_ID: 9 END_EVENT_ID: 9 EVENT_NAME: statement/sql/select SOURCE: TIMER_START: 4631392503744000 TIMER_END: 4631392573894000 TIMER_WAIT: 70150000 LOCK_TIME: 0 SQL_TEXT: select version() DIGEST: 54cf1693fbf9ac7057b89a6958fe9519 DIGEST_TEXT: SELECT `version` ( ) CURRENT_SCHEMA: cjcdb OBJECT_TYPE: NULL OBJECT_SCHEMA: NULL
释放全局读锁：
在产生阻塞的会话内释放全局读锁。
MySQL [cjcdb]> unlock tables; Query OK, 0 rows affected (0.00 sec)
###chenjuchao 20220416###
