mysql总结

一、mysql 分析锁表

1、查看DB是否有死锁

show engine innodb status;

type:
name:
status:

2、列出当前在表缓存中打开的非临时表

show open tables where In_use > 0

Database: 数据库名称
Table: 表名
In_use: 表锁或锁请求的数量在表中。例如，如果一个客户机使用锁表t1写的表获得一个锁，那么In_use将是1。如果另一个客户端问题锁表t1写，而表仍然锁定，客户端将阻塞等待锁，但是锁请求导致In_use为2。如果计数为零，表是打开的，但目前没有使用。
Name_locked: 是否锁定表名。名称锁定用于操作，例如删除或重命名表

3、当前运行的所有事务

5.5~8.0.13
SELECT * FROM information_schema.INNODB_TRX;

trx_id：事务ID。
trx_state：事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。
trx_started：事务开始时间。
trx_requested_lock_id：事务当前正在等待锁的标识，可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息。
trx_wait_started：事务开始等待的时间。
trx_weight：事务的权重。
trx_mysql_thread_id：事务线程 ID，可以和 PROCESSLIST 表 JOIN。
trx_query：事务正在执行的 SQL 语句。
trx_operation_state：事务当前操作状态。
trx_tables_in_use：当前事务执行的 SQL 中使用的表的个数。
trx_tables_locked：当前执行 SQL 的行锁数量。
trx_lock_structs：事务保留的锁数量。
trx_lock_memory_bytes：事务锁住的内存大小，单位为 BYTES。
trx_rows_locked：事务锁住的记录数。包含标记为 DELETED，并且已经保存到磁盘但对事务不可见的行。
trx_rows_modified：事务更改的行数。
trx_concurrency_tickets：事务并发票数。
trx_isolation_level：当前事务的隔离级别。
trx_unique_checks：是否打开唯一性检查的标识。
trx_foreign_key_checks：是否打开外键检查的标识。
trx_last_foreign_key_error：最后一次的外键错误信息。
trx_adaptive_hash_latched：自适应散列索引是否被当前事务锁住的标识。
trx_adaptive_hash_timeout：是否立刻放弃为自适应散列索引搜索 LATCH 的标识。

4、当前出现的锁

5.5~8.0.13
SELECT * FROM information_schema.INNODB_LOCKs;

lock_id：锁 ID。
lock_trx_id：拥有锁的事务 ID。可以和 INNODB_TRX 表 JOIN 得到事务的详细信息。
lock_mode：锁的模式。有如下锁类型：行级锁包括：S、X、IS、IX，分别代表：共享锁、排它锁、意向共享锁、意向排它锁。表级锁包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁。
lock_type：锁的类型。RECORD 代表行级锁，TABLE 代表表级锁。
lock_table：被锁定的或者包含锁定记录的表的名称。
lock_index：当 LOCK_TYPE=’RECORD’ 时，表示索引的名称；否则为 NULL。
lock_space：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的表空间 ID；否则为 NULL。
lock_page：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的页号；否则为 NULL。
lock_rec：当 LOCK_TYPE=’RECORD’ 时，表示一堆页面中锁定行的数量，亦即被锁定的记录号；否则为 NULL。
lock_data：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的主键；否则为NULL

8.0.13 以上版本
select * from performance_schema.data_locks;

ENGINE: 存储引擎（INNODB）
ENGINE_LOCK_ID 存储引擎内部的锁ID，该值会发生动态变化，外部系统不应该依赖该值
ENGINE_TRANSACTION_ID: 2578 (information_schema.innodb_trx中的trx_id)
THREAD_ID: 持有锁的线程ID
EVENT_ID: 事件id
OBJECT_SCHEMA: 数据库名
OBJECT_NAME: 表名
PARTITION_NAME: 分区名
SUBPARTITION_NAME: 子分区名
INDEX_NAME: 索引名
OBJECT_INSTANCE_BEGIN: 锁的内存空间起始地址（140373282389696）
LOCK_TYPE: 锁类型（TABLE/RECORD）
LOCK_MODE: 锁模式（IX: 表意向排它锁， X： NextKey-Lock, X, REC_NOT_GAP: 行锁， X,GAP: 间隙锁，LOCK_INSERT_INTENTION插入意向锁）
LOCK_STATUS: GRANTED、WAITING
LOCK_DATA： 锁的数据，当LOCK_TYPE为RECORD时才会有值（如果是聚族索引则直接显示主键，如果是非聚族索引则是，当前数据以及主键数据）

5.5~8.0.13

5、锁等待的对应关系

SELECT * FROM information_schema.INNODB_LOCK_waits;
8.0.13 以上版本
select * from performance_schema.data_lock_waits;
1、持有锁的语句
SELECT * FROM PERFORMANCE_SCHEMA.events_statements_history 
WHERE thread_id IN (SELECT b.`THREAD_ID` FROM sys.`innodb_lock_waits` AS a, PERFORMANCE_SCHEMA.threads AS b WHERE a.`blocking_pid` = b.`PROCESSLIST_ID` )
ORDER BY timer_start ASC;
2、等待锁的语句
SELECT * FROM PERFORMANCE_SCHEMA.events_statements_history 
WHERE thread_id IN (SELECT b.`THREAD_ID` FROM sys.`innodb_lock_waits` AS a, PERFORMANCE_SCHEMA.threads AS b WHERE a.`waiting_pid` = b.`PROCESSLIST_ID` )
ORDER BY timer_start ASC;

requesting_trx_id：请求事务的 ID。
requested_lock_id：事务所等待的锁定的 ID。可以和 INNODB_LOCKS 表 JOIN。
blocking_trx_id：阻塞事务的 ID。
blocking_lock_id：某一事务的锁的 ID，该事务阻塞了另一事务的运行。可以和 INNODB_LOCKS 表 JOIN。