MySQL事务、锁、锁等待
一、InnoDB引擎 - 查看当前事务、锁、锁等待
## 当前运行的所有事务 mysql> select * from information_schema.innodb_trx\G *************************** 1. row *************************** trx_id: 613963 trx_state: LOCK WAIT #事务状态 trx_started: 2019-02-22 10:48:48 trx_requested_lock_id: 613963:460:3:4 trx_wait_started: 2019-02-22 11:08:41 trx_weight: 2 trx_mysql_thread_id: 140 trx_query: update tx1 set c1='heyfffff',c2='heyffffff' where id =3 trx_operation_state: starting index read trx_tables_in_use: 1 trx_tables_locked: 1 trx_lock_structs: 2 trx_lock_memory_bytes: 1136 trx_rows_locked: 7 #事务锁住的行数 trx_rows_modified: 0 trx_concurrency_tickets: 0 #事务并发票数 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 *************************** 2. row *************************** trx_id: 613962 trx_state: RUNNING trx_started: 2019-02-22 10:46:29 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 3 trx_mysql_thread_id: 138 trx_query: select * from information_schema.innodb_trx trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 1 trx_lock_structs: 2 trx_lock_memory_bytes: 1136 trx_rows_locked: 1 trx_rows_modified: 1 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 2 rows in set (0.00 sec) ## 当前出现的锁 mysql> select * from information_schema.innodb_locks\G *************************** 1. row *************************** lock_id: 613963:460:3:4 lock_trx_id: 613963 lock_mode: X lock_type: RECORD lock_table: `test`.`tx1` lock_index: PRIMARY lock_space: 460 lock_page: 3 lock_rec: 4 lock_data: 3 *************************** 2. row *************************** lock_id: 613962:460:3:4 lock_trx_id: 613962 lock_mode: X lock_type: RECORD lock_table: `test`.`tx1` lock_index: PRIMARY lock_space: 460 lock_page: 3 lock_rec: 4 lock_data: 3 2 rows in set, 1 warning (0.00 sec) ## 锁等待的对应关系 mysql> select * from information_schema.innodb_lock_waits\G *************************** 1. row *************************** requesting_trx_id: 613963 requested_lock_id: 613963:460:3:4 #请求锁的锁ID blocking_trx_id: 613962 #当前拥有锁的事务ID blocking_lock_id: 613962:460:3:4 1 row in set, 1 warning (0.00 sec)
二、InnoDB引擎 - 查看锁状态,是否锁表
mysql> show status like 'innodb_row_lock_%'; +-------------------------------+--------+ | Variable_name | Value | +-------------------------------+--------+ | Innodb_row_lock_current_waits | 1 | | Innodb_row_lock_time | 479764 | | Innodb_row_lock_time_avg | 39980 | | Innodb_row_lock_time_max | 51021 | | Innodb_row_lock_waits | 12 | +-------------------------------+--------+ 5 rows in set (0.00 sec) 解释如下: Innodb_row_lock_current_waits : 当前等待锁的数量 Innodb_row_lock_time : 系统启动到现在,锁定的总时间长度 Innodb_row_lock_time_avg : 每次平均锁定的时间 Innodb_row_lock_time_max : 最长一次锁定时间 Innodb_row_lock_waits : 系统启动到现在总共锁定的次数 # 查询是否锁表 mysql> show OPEN TABLES where In_use > 0; +----------+-------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+-------+--------+-------------+ | test | tx1 | 1 | 0 | +----------+-------+--------+-------------+ 1 row in set (0.00 sec)
三、InnoDB引擎 - 杀死进程
# 1.查询进程 mysql> show processlist\G # \G 结构旋转90度变成纵向 *************************** 1. row *************************** Id: 138 User: root Host: localhost:55106 db: test Command: Query Time: 0 State: starting Info: show processlist *************************** 2. row *************************** Id: 140 User: root Host: localhost:56158 db: test Command: Sleep # 正在等待客户端向它发送执行语句 Time: 145 State: Info: NULL 2 rows in set (0.00 sec) # 2.杀死对应进程ID mysql> kill 140; Query OK, 0 rows affected (0.00 sec) mysql> show processlist\G *************************** 1. row *************************** Id: 138 User: root Host: localhost:55106 db: test Command: Query #该线程正在执行一个语句 Sleep:线程正在等待客户端向其发送新的语句。 Time: 0 State: starting Info: show processlist 1 row in set (0.00 sec)
四、InnoDB引擎 - 表字段含义
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 的标识。 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。 information_schema.innodb_lock_waits: requesting_trx_id:请求事务的 ID。 requested_lock_id:事务所等待的锁定的 ID。可以和 INNODB_LOCKS 表 JOIN。 blocking_trx_id:阻塞事务的 ID。 blocking_lock_id:某一事务的锁的 ID,该事务阻塞了另一事务的运行。可以和 INNODB_LOCKS 表 JOIN。
五、InnoDB引擎 - SQL分析
EXPLAIN命令,用于显示SQL语句的查询执行计划。EXPLAIN为用于SELECT语句中的每个表返回一行信息。
EXPLAIN EXTENDED命令:显示SQL语句的详细的查询执行计划;之后可以通过"SHOW WARNINGS"命令查看详细信息。 SHOW WARNINGS命令:可以查看MySQL优化器优化后的SQL语句。 EXPLAIN PARTITIONS命令:显示SQL语句的带有分区表信息的查询执行计划。 EXPLAIN命令的输出格式: TRADITIONAL:传统类型,按行隔离,每行标识一个自操作。 JSON:JSON格式。
示例:
mysql> explain select id,c1 from t1 where c1=4398825; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 4992210 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec)
字段含义:
-
id: 按照sql语法解析后分层后的编号,可能重复
-
select_type:
-
SIMPLE,简单的select查询,不使用union及子查询
-
PRIMARY,最外层的select查询
-
UNION,UNION 中的第二个或随后的 select 查询,不依赖于外部查询的结果集
-
DEPENDENT UNION,UNION 中的第二个或随后的 select 查询,依赖于外部查询的结果集
-
SUBQUERY,子查询中的第一个 select 查询,不依赖于外部查询的结果集
-
DEPENDENT SUBQUERY,子查询中的第一个 select 查询,依赖于外部查询的结果集
-
DERIVED,用于 from子句里有子查询的情况。 MySQL会递归执行这些子查询, 把结果放在临时表里。
-
UNCACHEABLE SUBQUERY,结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估。
-
UNCACHEABLE UNION,UNION 中的第二个或随后的 select 查询,属于不可缓存的子查询
-
-
table:涉及的表,如果SQL中表有赋别名,这里出现的是别名
-
type:
-
system,从系统表读一行。这是const联接类型的一个特例。
-
const,表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
-
eq_ref,查询条件为等于
-
ref,条件查询不等于
-
ref_or_null,同ref(条件查询),包含NULL值的行。
-
index_merge,索引联合查询
-
unique_subquery,利用唯一索引进行子查询
-
index_subquery,用非唯一索引进行子查询
-
range,索引范围扫描
-
index,索引全扫描
-
ALL,全表扫描。
-
-
possible_keys:可能使用的索引
-
key:sql中使用的索引
-
key_len:索引长度
-
ref:使用哪个列或常数与key一起从表中选择行。
-
rows:显示MYSQL执行查询的行数,简单且重要,数值越大越不好,说明没有用好索引
-
Extra:该列包含MySQL解决查询的详细信息。
-
Distinct,去重,返回第一个满足条件的值
-
Not exists 使用not exists查询
-
Range checked for each record,有索引,但索引选择率很低
-
Using filesort,有序查询
-
Using index,索引全扫描
-
Using index condition,索引查询
-
Using temporary,临表表检索
-
Using where,where条件查询
-
Using sort_union,有序合并查询
-
Using union,合并查询
-
Using intersect,索引交叉合并
-
Impossible WHERE noticed after reading const tables,读取const tables,查询结果为空
-
No tables used,没有使用表
-
Using join buffer (Block Nested Loop),使用join buffer(BNL算法)
-
Using MRR(Multi-Range Read ) 使用辅助索引进行多范围读
-
六、查看数据库默认配置
# 查看数据库默认存储引擎 mysql> show engines; # 查看当前数据库默认隔离级别 mysql> select @@global.tx_isolation; # 默认自动提交事务 mysql> show global variables like 'autocommit'; mysql> select @@global.autocommit;
七、查看未提交的事务
https://www.cnblogs.com/gaogao67/p/10790520.html